데이터에서의 월과 연도의 갭 메우기

데이터에서의 월과 연도의 갭 메우기

날짜 기반 항목의 표를 가지고 있는데, 그 중 많은 항목이 월과 년 사이에 차이가 있습니다.예를 들어, 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 |
+-------+------+-------+

빈칸/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 |
+-------+------+-------+

내가 뭘 망치고 있는지 잘 모르겠어.

다음과 같이 연도를 포함하도록 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);

내 테이블이 그렇게 되어 있다면

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 |     61 | 2020 |     50 | 2020 |     40 | 2020 |     33 | 2020 |     21 | 2020 |     10 | 2019 |    120 | 2019 |    119 | 2019 |    100 | 2019 |     92 | 2019 |     80 | 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 | 61 | 2020 |     5특수 | 2020 | 4특수 | 2020 | 33 | 2020 |     21 | 2020 |     1null | 2019 | 12null | 2019 | 119 | 2019 |    10null | 2019 | 92 | 2019 |     8null | 2019 |7

예: https://dbfiddle.uk/ ?rdbms = adb _ 10 . 4 & flash = 2ee3594614494 d3397 a996 d7ff815859

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에 몇 년 분량의 데이터를 저장할 수 있습니다.

다른 옵션은 스토어드 프로시저를 생성하여 다음 예제를 기반으로 온디맨드로 데이터를 채우는 것입니다.테이블에 날짜 범위를 채우려면 어떻게 해야 합니까?

사연에 대한 .n CTE를 "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 데모

MariaDB를 사용하고 있다고 가정하면...

UNIONs하다, 사용하다seq_0_to_100 ★★★★★★★★★★★★★★★★★」+ INTERVAL sea MONTH.

언급URL : https://stackoverflow.com/questions/62226866/filling-month-and-year-gaps-in-data