꿈꾸는 개발자, DBA 커뮤니티 구루비

월별 통계시 없는 월 포함시키는 방법

by 언제쯤 [SQL Query] mysql mariadb 없는달 [2023.08.16 13:05:36]

안녕하세요. mariaDB에서 없는 월에 대해 표시하고 싶습니다.

데이터 구조는 아래와 같습니다.

샘플데이터로 했을 경우에는 right outer join 이 잘 나오는데 회사에서 아래와 같은 구조로 되어 있고 동일하게 조인을 걸었을 경우에

join이 제대로 잘 되지 않습니다.

회사에서는 a 테이블에 having 절에 code 값을 줘야 right outer join 이 제대로 됩니다.

제대로 안될 경우로 a.year is null 조건을 주면 null로 나옵니다.

아무래도 code값이 없어서 그런 거 같은데..방법을 모르겠습니다.

--- 샘플 쿼리

with temp as

(

select 't_1' as vendor, '2019' as year, '01' as month, '7010000' as code, 10000 as g_amt

union all

select 't_1' as vendor, '2019' as year, '01' as month, '7010000' as code, 15000 as g_amt

union all

select 't_1' as vendor, '2019' as year, '02' as month, '7010000' as code, 8000 as g_amt

union all

select 't_1' as vendor, '2019' as year, '02' as month, '7010000' as code, 50000 as g_amt

union all

select 't_1' as vendor, '2019' as year, '04' as month, '7010000' as code, 5900 as g_amt

union all

select 't_1' as vendor, '2019' as year, '04' as month, '7010000' as code, 8000 as g_amt

union all

select 't_1' as vendor, '2019' as year, '01' as month, '8030000' as code, 100 as g_amt

union all

select 't_1' as vendor, '2019' as year, '02' as month, '8030000' as code, 900 as g_amt

union all

select 't_1' as vendor, '2019' as year, '02' as month, '8030000' as code, 55000 as g_amt

union all

select 't_1' as vendor, '2019' as year, '04' as month, '8030000' as code, 100000 as g_amt

union all

select 't_1' as vendor, '2019' as year, '04' as month, '8030000' as code, 570 as g_amt

date_t as

(

SELECT DATE_FORMAT(DD.Date, '%Y') AS year,

DATE_FORMAT(DD.Date, '%m') AS month

FROM (

SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY AS Date

from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a

cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b

) DD

WHERE DATE_FORMAT(DD.DATE, '%Y-%m') >= '2019-01'

and DATE_FORMAT(DD.DATE, '%Y-%m') <= '2023-06'

GROUP BY DATE_FORMAT(DD.Date, '%Y-%m')

)

select *

from

(

select vendor, year, month, code, sum(g_amt) g_amt

from temp

group by vendor, year, month, code

-- having code = '8030000' -- 회사 데이터는 여기에 코드 값을 줘야 right outer join이 제대로 됨

) a

right join date_t b

on a.year = b.year

and a.month = b.month

order by b.year, b.month;

by 마농 [2023.08.16 14:27:06]

1. 아우터 조인의 기준을 정확하게 이해하셔야 합니다.
-- 조인의 기준이 연월만 있는게 아닙니다.
-- vendor 와 code 도 월과 함께 기준이 되어야 합니다.
2. 컬럼명칭 관련하여
- year, month 등의 시스템 예약어를 명칭으로 사용하는 것은 좋지 않습니다.
- yyyy, mm, v_year, v_month 등 예약어가 아닌 명칭을 사용하는 것을 권장합니다.

WITH temp AS
(
select 't_1' vendor, '2019' year, '01' month, '7010000' code, 10000 g_amt
UNION ALL SELECT 't_1', '2019', '01', '7010000',  15000
UNION ALL SELECT 't_1', '2019', '02', '7010000',   8000
UNION ALL SELECT 't_1', '2019', '02', '7010000',  50000
UNION ALL SELECT 't_1', '2019', '04', '7010000',   5900
UNION ALL SELECT 't_1', '2019', '04', '7010000',   8000
UNION ALL SELECT 't_1', '2019', '01', '8030000',    100
UNION ALL SELECT 't_1', '2019', '02', '8030000',    900
UNION ALL SELECT 't_1', '2019', '02', '8030000',  55000
UNION ALL SELECT 't_1', '2019', '04', '8030000', 100000
UNION ALL SELECT 't_1', '2019', '04', '8030000',    570
)
SELECT a.vendor
     , a.code
     , b.yyyy
     , b.mm
     , IFNULL(SUM(c.g_amt), 0) g_amt
  FROM (SELECT DISTINCT vendor, code FROM temp) a
 CROSS JOIN 
       (SELECT DATE_FORMAT(CONCAT('2019-01', '-01') + INTERVAL seq-1 MONTH, '%Y') yyyy
             , DATE_FORMAT(CONCAT('2019-01', '-01') + INTERVAL seq-1 MONTH, '%m') mm
          FROM seq_1_to_99
         WHERE DATE_FORMAT(CONCAT('2019-01', '-01') + INTERVAL seq-1 MONTH, '%Y-%m') <= '2023-06'
        ) b
  LEFT OUTER JOIN temp c
    ON a.vendor = c.vendor
   AND a.code   = c.code
   AND b.yyyy   = c.year
   AND b.mm     = c.month
 GROUP BY a.vendor, a.code, b.yyyy, b.mm
 ORDER BY a.vendor, a.code, b.yyyy, b.mm
;

by 언제쯤 [2023.08.17 14:11:06]

알려주셔서 감사합니다.

조인기준에 대해 명확하게 알게 된거 같습니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변