填补数据中的月份和年份空白

laawzig2 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(314)

我有一个基于日期的项目表，其中许多项目在月和年之间有间隔。例如，如果一月份创建了一个帖子，四月份创建了五个，那么我在二月、三月、五月和六月都会有空白。我四处搜索，发现要做的一件事是使用一个数字表，或者创建一个临时的月份表，并将其连接起来，但我似乎仍然无法让它工作。以下是我目前掌握的情况：

CREATE OR REPLACE TABLE temp_months (id INT unsigned PRIMARY KEY);
INSERT INTO temp_months
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12);

SELECT
    COUNT(p.ID) AS COUNT,
    YEAR(p.created_date) as YEAR,
    tm.id as MONTH
FROM
    temp_months tm
LEFT OUTER JOIN
    my_table p
        ON
            MONTH(p.created_date) = tm.id
WHERE
    p.company_id = 123456
GROUP BY
    MONTH, YEAR
ORDER BY
    p.created_date DESC

这给了我以下格式，带有间隙（几乎就像我根本没有将它连接到temp表一样）

+-------+------+-------+
| COUNT | YEAR | MONTH |
+-------+------+-------+
|     1 | 2020 |     5 |
|     3 | 2020 |     2 |
|     1 | 2020 |     1 |
|     9 | 2019 |    10 |
|     2 | 2019 |     8 |
+-------+------+-------+

我想让它做的是用一个空的/null/0来填补空白 COUNT ，例如：

+-------+------+-------+
| COUNT | YEAR | MONTH |
+-------+------+-------+
|  NULL | 2020 |     6 |
|     1 | 2020 |     5 |
|  NULL | 2020 |     4 |
|  NULL | 2020 |     3 |
|     3 | 2020 |     2 |
|     1 | 2020 |     1 |
|  NULL | 2019 |    12 |
|  NULL | 2019 |    11 |
|     9 | 2019 |    10 |
|  NULL | 2019 |     9 |
|     2 | 2019 |     8 |
|  NULL | 2019 |     7 |
+-------+------+-------+

我只是不太清楚我把事情搞砸了。

sql mysql mariadb

来源：https://stackoverflow.com/questions/62226866/filling-month-and-year-gaps-in-data

3条答案

按热度按时间

rmbxnbpk1#

您可以尝试更改temp\u months表，将年份包括在内，如下所示：

create table temp_months (yr int, mth int, primary key (yr, mth));
insert into temp_months values
(2020, 1), (2020, 2), (2020, 3), (2020, 4), (2020, 5), (2020, 6),
(2019, 7), (2019, 8), (2019, 9), (2019, 10), (2019, 11), (2019, 12);

假设你的table是这样的，

create table my_table (created_date date, company_id int, id int);
insert into my_table values
('2020-05-01', 123456, 1),
('2020-02-01', 123456, 1),('2020-02-01', 123456, 1),('2020-02-01', 123456, 1),
('2020-01-01', 123456, 1),
('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),
('2019-08-01', 123456, 1),('2019-08-01', 123456, 1);

您可以运行这种查询：

select count(p.id), yr as year, mth as month
from temp_months tm
left join my_table p
  on month(created_date)=tm.mth
  and year(created_date)=tm.yr
group by yr, mth
order by yr desc, mth desc

结果将是

count(p.id) | year | month
----------: | ---: | ----:
          0 | 2020 |     6
          1 | 2020 |     5
          0 | 2020 |     4
          0 | 2020 |     3
          3 | 2020 |     2
          1 | 2020 |     1
          0 | 2019 |    12
          0 | 2019 |    11
          9 | 2019 |    10
          0 | 2019 |     9
          2 | 2019 |     8
          0 | 2019 |     7

如果要显示null，可以使用：

with result as (
  select count(p.id) as counter, yr as year, mth as month
  from temp_months tm
  left join my_table p
    on month(created_date)=tm.mth
    and year(created_date)=tm.yr
  group by yr, mth
  order by yr desc, mth desc
)
select
  case when counter = 0 then NULL else counter end as counter,
  year, month
from result;

结果将是

counter | year | month
------: | ---: | ----:
   null | 2020 |     6
      1 | 2020 |     5
   null | 2020 |     4
   null | 2020 |     3
      3 | 2020 |     2
      1 | 2020 |     1
   null | 2019 |    12
   null | 2019 |    11
      9 | 2019 |    10
   null | 2019 |     9
      2 | 2019 |     8
   null | 2019 |     7

例子：https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=2ee3594614494d3397a996d7ff815859
要手动但快速地填充temp\u months表，我输入一年值，如下所示：

insert into temp_table values
(2019, 1), (2019, 2), (2019, 3), (2019, 4), (2019, 5), (2019, 6),
(2019, 7), (2019, 8), (2019, 9), (2019, 10), (2019, 11), (2019, 12);

然后，我将其复制到文本编辑器中，查找/替换2019到2020，然后再次执行…以此类推。几秒钟之内，我就可以在临时表格里找到几年的数据了。
另一个选项是创建一个存储过程，根据这里的示例按需填充它：如何用日期范围填充表？

赞(0）回复(0）举报 2021-07-26

hgncfbus2#

下面的查询将为您提供最后一个 n 月，使用递归cte生成上一个月的年/月组合 n 几个月之后 LEFT JOIN 将这些值 my_table 获取每年/每月组合的计数。此查询是为过去12个月设置的( 11 在cte的递归部分），更改为 24 您可以将该值更改为 23 .

WITH RECURSIVE dates AS (
  SELECT MAX(created_date) AS mdate, CONCAT(LEFT(MAX(created_date), 8), '01') AS cdate
  FROM my_table
  UNION ALL
  SELECT mdate, cdate - INTERVAL 1 MONTH
  FROM dates
  WHERE cdate > mdate - INTERVAL 11 MONTH
)
SELECT COUNT(p.id) AS `count`, YEAR(cdate) AS yr, MONTH(cdate) AS mth
FROM dates d
LEFT JOIN my_table p ON p.created_date BETWEEN d.cdate AND LAST_DAY(d.cdate)
GROUP BY cdate
ORDER BY cdate DESC

输出（对于@zedfoxus示例数据）：

count   yr      mth
1       2020    5
0       2020    4
0       2020    3
3       2020    2
1       2020    1
0       2019    12
0       2019    11
9       2019    10
0       2019    9
2       2019    8
0       2019    7
0       2019    6

在dbfiddle上演示
该查询从表中的最大日期开始运行，要从当前日期开始运行，请按如下所示更改递归cte：

WITH RECURSIVE dates AS (
  SELECT CONCAT(LEFT(CURDATE(), 8), '01') AS mdate, CONCAT(LEFT(CURDATE(), 8), '01') AS cdate
  UNION ALL
  SELECT mdate, cdate - INTERVAL 1 MONTH
  FROM dates
  WHERE cdate > mdate - INTERVAL 11 MONTH
)
SELECT COUNT(p.id) AS `count`, YEAR(cdate) AS yr, MONTH(cdate) AS mth
FROM dates d
LEFT JOIN my_table p ON p.created_date BETWEEN d.cdate AND LAST_DAY(d.cdate)
GROUP BY cdate
ORDER BY cdate DESC

在dbfiddle上演示

赞(0）回复(0）举报 2021-07-26

cgfeq70w3#

假设你用的是mariadb。。。
而不是乱七八糟的 UNIONs ，使用 seq_0_to_100 以及 + INTERVAL sea MONTH .

赞(0）回复(0）举报 2021-07-26