时间戳

首页 > 解决方案 > 填补数据中的月份和年份空白

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

问题描述

我有一个基于日期的项目表，其中许多项目在月份和年份之间存在差距。例如，如果 1 月份创建了一个帖子，4 月份创建了 5 个帖子，那么我将在 2 月、3 月、5 月和 6 月有间隔。我一直在四处寻找，发现要做的一件事是使用数字表，或创建一个临时月份表，然后加入其中，但我似乎仍然无法让它工作。这是我到目前为止所拥有的：

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

这给了我以下格式，有间隙（几乎就像我根本没有将它加入临时表一样）

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

我想做的是用 empty/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 |
+-------+------+-------+

我只是不太确定我在哪里搞砸了。

标签： mysqlsqlmariadb

解决方案

您可以尝试更改您的 temp_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);

假设你的 my_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

结果将是

计数（p.id）| 年份 | 月
----------: | ---: | ----:
          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;

结果将是

柜台 | 年份 | 月
------: | ---: | ----:
   空| 2020 | 6
      1 | 2020 | 5
   空| 2020 | 4
   空| 2020 | 3
      3 | 2020 | 2
      1 | 2020 | 1
   空| 2019 | 12
   空| 2019 | 11
      9 | 2019 | 10
   空| 2019 | 9
      2 | 2019 | 8
   空| 2019 | 7

示例：https ://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=2ee3594614494d3397a996d7ff815859

为了手动但快速地填充 temp_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 并再次执行......等等。几秒钟之内，我就会在 temp_table 中获得数年的数据。

另一种选择是创建一个存储过程以根据此处的示例按需填充它：如何使用日期范围填充表？

推荐阅读