DataBaser.Net: Cross Join

1 개요
2 약간 복잡한 예제
3 Cross Join의 원리
4 응용 예제1
5 응용 예제2
6 응용 예제3

1 개요 #

Cross Join을 상호조인 또는 카테시안곱이라고도 한다. Cross Join은 조인되는 테이블끼리 곱의 연산을 한다. 그러므로 1 * 1은 1이므로 관계가 전혀 없는 테이블도 1:1의 관계만 만들어 준다면 Cross Join을 해도 부하가 없다. Cross Join은 통계를 내기 위한 데이터 복제에 자주 이용된다. 또한 곱해지는 특수성에 의해 때로는 상당히 유용할 경우가 자주 있다. 다음은 Cross Join을 응용한 것이다.

구구단1

with temp
as
(
	select 1 id union all
	select 2 id union all
	select 3 id union all
	select 4 id union all
	select 5 id union all
	select 6 id union all
	select 7 id union all
	select 8 id union all
	select 9 id 
)
select  
	cast(a.id + 1 as varchar) + '*' + 
	cast(b.id + 1 as varchar) + '=' + 
	cast((a.id + 1)  * (b.id + 1) as varchar)
from   temp a cross join temp b
order by a.id, b.id

구구단2

with temp
as
(
	select 1 seq
	union all
	select seq + 1 from temp
	where seq + 1 <= 12
)
select
	min(case when md = 1 then val1 end) val1
,	min(case when md = 1 then val2 end) val2
,	min(case when md = 1 then result end) result1

,	min(case when md = 2 then val1 end) val1
,	min(case when md = 2 then val2 end) val2
,	min(case when md = 2 then result end) result2

,	min(case when md = 0 then val1 end) val1
,	min(case when md = 0 then val2 end) val2
,	min(case when md = 0 then result end) result3
from (
	select  
		a.seq val1
	,	b.seq val2
	,	(a.seq)  * (b.seq) result
	,	a.seq % 3 md
	,	row_number() over(partition by a.seq % 3 order by a.seq, b.seq) rowno
	from   temp a cross join temp b
) t cross join temp c
where c.seq <= 3
group by
	rowno

0 ~ 999 번 만들기

select * into #temp
from (
         select 0 id union all
         select 1 id union all
         select 2 id union all
         select 3 id union all
         select 4 id union all
         select 5 id union all
         select 6 id union all
         select 7 id union all
         select 8 id union all
         select 9 id ) t
 
select cast(cast(c.id  as varchar) + 
          cast(b.id as varchar) + 
          cast(a.id as varchar) as int) number
from #temp a cross join #temp b cross join #temp c
order by 1

[edit]

2 약간 복잡한 예제 #

아주 기본적인 사용이다. 앞에도 이야기 하였듯이 Cross Join을 이용한 데이터 복제로 많은 일을 할 수 있다. 내가 원하는 결과집합을 만들기 위해서 동일한 집합이 더 필요하다면 Cross Join을 이용하여 데이터를 복제하여 그것을 사용하면 된다. 다음의 예가 그런 예이다. 온라인 도움말에 있는 것을 변형해 본 것이다.

CREATE TABLE Pivot
( Year      SMALLINT,
  Quarter   TINYINT, 
  Amount      DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO
 
SELECT MIN(CASE WHEN ID = 1 THEN CAST(Year AS VARCHAR) 
ELSE '분기별 총합' END) AS '년도', 
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS '1분기',
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS '2분기',
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS '3분기',
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS '4분기',
    SUM(Amount) '년도별 합계'
FROM Pivot CROSS JOIN (SELECT 1 AS ID UNION ALL SELECT 2) T
GROUP BY CASE WHEN ID = 1 THEN Year END
ORDER BY 1

[edit]

3 Cross Join의 원리 #

실제로 결과만 대충 보면 별것이 아닌 것 같이 보인다. 그러나 자세히 보면 얼마나 유용한 것인가를 알 수 있을 것이다. 년도별 합계, 해당 년도의 분기별 합계, 모든 년도의 분기별 합계까지 그리고 모든 합계까지 나온 것이다. 그야말로 엄청난 쿼리임이 아닐 수 없다. 이 마법의 원리는 GROUP BY에 있다. 원리를 이해하도록 하자. 위 쿼리에서 ‘분기별 총합’을 뺀다면 다음과 같이 쉽게 Cross Tab Query를 할 수 있다. Cross Tab의 원리는 SUM(CASE ~ ) 에 있다. SUM()함수가 Group에 대해 한 개의 값만 반환하는데 그 원리가 있다.

SELECT Year AS '년도', 
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS '1분기',
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS '2분기',
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS '3분기',
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS '4분기',
    SUM(Amount) '년도별 합계'
FROM Pivot 
GROUP BY Year

문제는 역시 ‘분기별 총합’을 만드는데 있다. 그냥 생각해 본다면 다음과 같이 UNION ALL을 사용하여 우리가 원하는 결과를 만들 수도 있다.

SELECT CAST(Year AS VARCHAR) AS '년도', 
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS '1분기',
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS '2분기',
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS '3분기',
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS '4분기',
    SUM(Amount) '년도별 합계'
FROM Pivot 
GROUP BY Year
UNION ALL
SELECT '분기별 총합', SUM(a), SUM(b), SUM(c), SUM(d), SUM(YearSum)
FROM (
            SELECT Year AS 'Year', 
                SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS 'a',
                SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS 'b',
                SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS 'c',
                SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS 'd',
                SUM(Amount) 'YearSum'
            FROM Pivot 
            GROUP BY Year) T

대부분의 개발자는 결과 나왔다고 좋아라 한다. 그러나 위와 같은 SQL문은 같은 테이블을 2번 읽어야 하는 비효율이 발생을 한 것이다. 그러므로 Cross Join으로 똑 같은 그룹을 2개를 만든다.

SELECT *    
FROM Pivot CROSS JOIN (SELECT 1 AS ID UNION ALL SELECT 2) T

ID가 1인 집합의 경우는 Year로 Group By를 하고,

SELECT 
    MIN(CASE WHEN ID = 1 THEN CAST(Year AS VARCHAR) ELSE '분기별 총합' END) AS '년도', 
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS '1분기',
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS '2분기',
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS '3분기',
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS '4분기',
    SUM(Amount) '년도별 합계'
FROM Pivot CROSS JOIN (SELECT 1 AS ID UNION ALL SELECT 2) T
GROUP BY CASE WHEN ID = 1 THEN Year END

ID가 1이 아닌 집합의 경우는 NULL이 된다. 그러므로 GROUP BY로 그룹을 지어줄 대상은 ID가 1이 아닌 집합 전체가 된다. 그러므로 집합 2의 경우는 전체 집계의 결과가 나오는 것이다.

SELECT MIN(CASE WHEN ID = 1 THEN CAST(Year AS VARCHAR) 
ELSE '분기별 총합' END) AS '년도', 
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS '1분기',
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS '2분기',
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS '3분기',
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS '4분기',
    SUM(Amount) '년도별 합계'
FROM Pivot CROSS JOIN (SELECT 1 AS ID UNION ALL SELECT 2) T
GROUP BY NULL

[edit]

4 응용 예제1 #

실제 사이트의 예로 다음과 같은 쿼리가 있다.

SELECT MIN(CASE WHEN T.ID = '1' THEN CAST(a.eDate AS VARCHAR) 
ELSE '품목별 총합' END) '기간',
        SUM(CASE WHEN b.CommDetail = 'CS' THEN a.sqty ELSE '0' END) '남방셔츠    ',
        SUM(CASE WHEN b.CommDetail = 'DS' THEN a.sqty ELSE '0' END) '와이셔츠    ',
        SUM(CASE WHEN b.CommDetail = 'JP' THEN a.sqty ELSE '0' END) '잠    바    ',
        SUM(CASE WHEN b.CommDetail = 'NT' THEN a.sqty ELSE '0' END) '넥 타 이    ',
        SUM(CASE WHEN b.CommDetail = 'TS' THEN a.sqty ELSE '0' END) '티 셔 츠    ',
        SUM(CASE WHEN b.CommDetail = 'SU' THEN a.sqty ELSE '0' END) '양    복    ',
        SUM(CASE WHEN b.CommDetail = 'JL' THEN a.sqty ELSE '0' END) '가죽잠바    ',
        SUM(CASE WHEN b.CommDetail = 'JA' THEN a.sqty ELSE '0' END) '자    켓    ',
        SUM(CASE WHEN b.CommDetail = 'VS' THEN a.sqty ELSE '0' END) '조    끼    ',
        SUM(CASE WHEN b.CommDetail = 'PS' THEN a.sqty ELSE '0' END) '양복바지    ',
        SUM(CASE WHEN b.CommDetail = 'PD' THEN a.sqty ELSE '0' END) '일반바지    ',
        SUM(CASE WHEN b.CommDetail = 'SW' THEN a.sqty ELSE '0' END) '스 웨 트    ',
        SUM(CASE WHEN b.CommDetail = 'OL' THEN a.sqty ELSE '0' END) '롱 코 트    ',
        SUM(CASE WHEN b.CommDetail = 'OT' THEN a.sqty ELSE '0' END) '바 바 리    ',
        SUM(CASE WHEN b.CommDetail = 'OH' THEN a.sqty ELSE '0' END) '반 코 트    ',
        SUM(CASE WHEN b.CommDetail = 'AC' THEN a.sqty ELSE '0' END) '악세사리    ',
        SUM(CASE WHEN b.CommDetail = 'ES' THEN a.sqty ELSE '0' END) '내 의 류    ',
        SUM(a.amount) '합계'
FROM
         (SELECT code, MIN(eDate) 'eDate', SUM(sqty) 'sqty', SUM(amount) 'amount' 
         FROM saledetail_m 
         WHERE edate between '2004-04-01' and '2004-04-10' 
         GROUP BY code) a INNER JOIN BaseGoods_Head b
ON a.code = b.code CROSS JOIN (SELECT 1 AS ID UNION ALL SELECT 2) T
WHERE a.edate between '2004-04-01' and '2004-04-30'
GROUP BY  CASE WHEN T.ID = 1 THEN a.eDate END
ORDER BY 1 DESC

실제 In-Line View 부분은 인라인뷰가 아니였고, 그냥 Join으로 연결되었었다. 위와 같은 결과집합을 보기 위해서 약 7초 정도의 시간의 소요되었다. 그러나 Group By를 이용하여 조인의 횟수를 줄임으로써 5초를 절약할 수 있었다. 또 다른 예로 이번에는 차원의 수를 더 높였다. 3개의 복사본을 만들어 3개의 레벨로 접근을 시도하였다. 만약 이러한 연산이 빈번히 읽어난다면 계층형 테이블 구조를 만드는 것도 하나의 방법이 될 수 있다.

select min(case when id in(1, 3) then cast(datepart(yyyy, tsSupportDate) as varchar) 
else '총 지원 건수' end)'년도', 
           min(case when id = 1 then tsSupporter 
                           when id = 2 then '------------->'
                           when id = 3 then '------------->' end) '지원자', 
           count(case when datepart(mm, tsSupportDate) = '1' then tsSupporter end) '1월',
           count(case when datepart(mm, tsSupportDate) = '2' then tsSupporter end) '2월',
           count(case when datepart(mm, tsSupportDate) = '3' then tsSupporter end) '3월',
           count(case when datepart(mm, tsSupportDate) = '4' then tsSupporter end) '4월',
           count(case when datepart(mm, tsSupportDate) = '5' then tsSupporter end) '5월',
           count(case when datepart(mm, tsSupportDate) = '6' then tsSupporter end) '6월',
           count(case when datepart(mm, tsSupportDate) = '7' then tsSupporter end) '7월',
           count(case when datepart(mm, tsSupportDate) = '8' then tsSupporter end) '8월',
           count(case when datepart(mm, tsSupportDate) = '9' then tsSupporter end) '9월',
           count(case when datepart(mm, tsSupportDate) = '10' then tsSupporter end) '10월',
           count(case when datepart(mm, tsSupportDate) = '11' then tsSupporter end) '11월',
           count(case when datepart(mm, tsSupportDate) = '12' then tsSupporter end) '12월',
           count(*) '총 지원 건수 합계',
           sum(tsDuration) '총 지원시간 합계'
from tblTechnicalSupport cross join (select 1 as id union all select 2 union all select 3) tmp
group by  case when id = 1 then tsSupporter end,
                case when id in(1, 3) then datepart(yyyy, tsSupportDate) end
order by 1, 2 desc

결과를 보면 년도별, 지원자별, 월별 지원건수 집계, 년도별 월별 총지원 건수 및 지원시간 집계, 등의 상당히 많은 레벨로 집계를 한 것을 알 수 있다.

[edit]

5 응용 예제2 #

SELECT CASE WHEN ID = 2 THEN CAST(DATEPART(YYYY, SALEDATE) AS VARCHAR) 
ELSE '' END '년도', 
           MIN(CASE WHEN ID = 1 THEN EMPID ELSE '합계' END) '사원', 
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '1' THEN EMPID END) '1월',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '2' THEN EMPID END) '2월',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '3' THEN EMPID END) '3월',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '4' THEN EMPID END) '4월',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '5' THEN EMPID END) '5월',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '6' THEN EMPID END) '6월',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '7' THEN EMPID END) '7월',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '8' THEN EMPID END) '8월',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '9' THEN EMPID END) '9월',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '10' THEN EMPID END) '10월',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '11' THEN EMPID END) '11월',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '12' THEN EMPID END) '12월',
           COUNT(*) '판매개수 합계'
FROM SALES CROSS JOIN (SELECT 1 AS ID UNION ALL SELECT 2) TMP
GROUP BY  DATEPART(YYYY, SALEDATE), ID,
          CASE WHEN ID = 2 THEN CAST(DATEPART(YYYY, SALEDATE) AS VARCHAR) 
ELSE '' END ,
          CASE WHEN ID = 1 THEN EMPID END

[edit]

6 응용 예제3 #

이 문제는 sqler.pe.kr에서 발견한 문제( [http]

여기)입니다. 링크로 가보면 문제가 두 개인데 비슷한 방식으로 푸는 문제입니다. 재미있을꺼 같아 한 개만 풀어봤습니다. (원리만 이해한다면 트래이닝 건덕지도 아니지요.)

원하는 결과

num         a    b    c    d    e
----------- ---- ---- ---- ---- ----
5                     ▒         
4                     ▒         
3                ▒    ▒         
2                ▒    ▒         ▒
1           ▒    ▒    ▒         ▒
0           ▒    ▒    ▒    ▒    ▒
NULL        1    3    5    0    2

--쿼리 결과는 텍스트로 보고 고정길이 폰트(~~체)로 보면 된다. 옵션 바꾸는거 귀찮으면 메모장으로 복사해서 보면 된다. 
with temp(num)
as
(
        select 6 numm
        union all
        select num - 1 from temp
        where num - 1 >= 0
),
val(a,b,c,d,e)
as
(
	select '1' a, '3' b, '5' c, '0' d, '2' e
)
select 
	case when b.num = 0 then null else b.num - 1 end num
,	case when b.num = 0 then a when b.num - 1 <= a.a then '▒' else '' end a
,	case when b.num = 0 then b when b.num - 1 <= a.b then '▒' else '' end b	
,	case when b.num = 0 then c when b.num - 1 <= a.c then '▒' else '' end c
,	case when b.num = 0 then d when b.num - 1 <= a.d then '▒' else '' end d
,	case when b.num = 0 then e when b.num - 1 <= a.e then '▒' else '' end e
from val a
	cross join temp b

Contents

1 개요 #

2 약간 복잡한 예제 #

3 Cross Join의 원리 #

4 응용 예제1 #

5 응용 예제2 #

6 응용 예제3 #