-- Title : [2k5] CROSS JOIN을 활용한 집계 처리 예 - II
-- Reference : dbrang.tistory.com
-- Key word : cross join union all group by order by 그룹핑
********************************************************************************************/
-- 오라클 한참 할 때 분기별/전체 년도별 합계 구하는 쿼리를 통으로 구현하는데 dual 테이블로
-- 하나의 셋을 만들어 활용한 통쿼리 구현이 생각나서 MS-SQL로 함 해봤다..
-- 이젠 머리가 예전같지 않아선지 이것도 너무 힘들어..ㅡ.ㅡ''
-- 전개1이 전개2보다 쿼리는 간단 한데 실행계획상 비용은 전개2가 좋드만...흥>.<
| (원본) | (결과) | |||||||
| id | 달 | 매출액 | 매입액 | 월 | 매출액 | 매입액 | 이익금 | |
| 1 | 1월 | 220 | 140 | 1월 | 220 | 140 | 80 | |
| 2 | 2월 | 370 | 170 | 2월 | 370 | 170 | 200 | |
| 3 | 3월 | 250 | 210 | ► | 3월 | 250 | 210 | 40 |
| 4 | 4월 | 290 | 190 | 1/4분기합 | 840 | 520 | 320 | |
| 5 | 5월 | 340 | 120 | 4월 | 290 | 190 | 100 | |
| 6 | 6월 | 260 | 220 | 5월 | 340 | 120 | 220 | |
| 6월 | 260 | 220 | 40 | |||||
| 2/4분기합 | 890 | 530 | 360 | |||||
| 전체합 | 1730 | 1050 | 680 | |||||
USE tempdb;
GO
DROP TABLE ttt;
GO
CREATE TABLE ttt
( id INT NOT NULL IDENTITY PRIMARY KEY
, 달 NVARCHAR(5) NOT NULL
, 매출액 INT NOT NULL
, 매입액 INT NOT NULL
);
INSERT INTO ttt
SELECT '1월', 220, 140 UNION ALL
SELECT '2월', 370, 170 UNION ALL
SELECT '3월', 250, 210 UNION ALL
SELECT '4월', 290, 190 UNION ALL
SELECT '5월', 340, 120 UNION ALL
SELECT '6월', 260, 220;
SELECT * FROM ttt;
GO
-- cross join 결과
SELECT a.id, a.달, a.매출액, a.매입액, a.매출액-a.매입액 "이익금", b.b1
FROM ttt a CROSS JOIN (SELECT 'a' "b1" UNION ALL
SELECT 'b'
) b;
-- 집계 처리
SELECT CASE WHEN b.b1 = 'a' AND a.id <= 3 THEN 1400 --1/4분기합
WHEN b.b1 = 'a' AND a.id >= 4 THEN 2400 --2/4분기합
WHEN b.b1 = 'b' THEN 9999 --전체합
WHEN b.b1 = 'c' THEN a.id
END "SEQ"
, CASE WHEN b.b1 = 'a' AND a.id <= 3 THEN '1/4분기합'
WHEN b.b1 = 'a' AND a.id >= 4 THEN '2/4분기합'
WHEN b.b1 = 'b' THEN '전체합'
WHEN b.b1 = 'c' THEN a.달
END "월"
, a.매출액, a.매입액, a.매출액-a.매입액 "이익금"
FROM ttt a CROSS JOIN (SELECT 'a' "b1" UNION ALL
SELECT 'b' UNION ALL
SELECT 'c'
) b
ORDER BY 1;
-- 전개 1.
SELECT MAX(u.월) "월", SUM(u.매출액) "매출액"
, SUM(u.매입액) "매입액", SUM(u.이익금) "이익금"
FROM
( SELECT CASE WHEN b.b1 = 'a' AND a.id <= 3 THEN 1400 --1/4분기합
WHEN b.b1 = 'a' AND a.id >= 4 THEN 2400 --2/4분기합
WHEN b.b1 = 'b' THEN 9999 --전체합
WHEN b.b1 = 'c' THEN a.id
END "SEQ"
, CASE WHEN b.b1 = 'a' AND a.id <= 3 THEN '1/4분기합'
WHEN b.b1 = 'a' AND a.id >= 4 THEN '2/4분기합'
WHEN b.b1 = 'b' THEN '전체합'
WHEN b.b1 = 'c' THEN a.달
END "월"
, a.매출액, a.매입액, a.매출액-a.매입액 "이익금"
FROM ttt a CROSS JOIN (SELECT 'a' "b1" UNION ALL
SELECT 'b' UNION ALL
SELECT 'c'
) b
) u
GROUP BY seq, 월
ORDER BY CASE WHEN u.seq = 1400 THEN 3.5
WHEN u.seq = 2400 THEN 6.5
WHEN u.seq = 9999 THEN 12.5
ELSE CAST(u.seq AS FLOAT)
END;
-- 전개 2.
SELECT CASE WHEN b.b1 = 'a' AND a.id <= 3 THEN 3500 --1/4분기합
WHEN b.b1 = 'a' AND a.id >= 4 THEN 6500 --2/4분기합
WHEN b.b1 = 'b' THEN 12500 --전체합
WHEN b.b1 = 'c' THEN a.id * 1000
END "SEQ"
, MAX(CASE WHEN b.b1 = 'a' AND a.id <= 3 THEN '1/4분기합'
WHEN b.b1 = 'a' AND a.id >= 4 THEN '2/4분기합'
WHEN b.b1 = 'b' THEN '전체합'
WHEN b.b1 = 'c' THEN a.달
END) "월"
, SUM(a.매출액) "매출액", SUM(a.매입액) "매입액"
, SUM(a.매출액-a.매입액) "이익금"
FROM ttt a CROSS JOIN (SELECT 'a' "b1" UNION ALL
SELECT 'b' UNION ALL
SELECT 'c'
) b
GROUP BY (CASE WHEN b.b1 = 'a' AND a.id <= 3 THEN 3500 --1/4분기합
WHEN b.b1 = 'a' AND a.id >= 4 THEN 6500 --2/4분기합
WHEN b.b1 = 'b' THEN 12500 --전체합
WHEN b.b1 = 'c' THEN a.id * 1000
END
) -- seq 그룹핑
ORDER BY CASE WHEN b.b1 = 'a' AND a.id <= 3 THEN 3500 --1/4분기합
WHEN b.b1 = 'a' AND a.id >= 4 THEN 6500 --2/4분기합
WHEN b.b1 = 'b' THEN 12500 --전체합
WHEN b.b1 = 'c' THEN a.id * 1000
END;