Partitioned Outer Join

아래 글에 이어서 Partitioned Outer Join에 대해서 소개해드리겠습니다.

이게 알고보니 Oracle 10g부터 소개된 기능인데 저는 이제서야 알았네요.

 

예제는 모든 고객의 매일 주문 수를 계산하는 SQL입니다.

매일 주문 수를 계산하는데, 주문이 없는 날은 0으로 표시해야하니 LEFT JOIN이 필요할 것 같습니다.

그러려면 LEFT JOIN과 UNION ALL을 조합해서 사용해야 하는데 실제로도 그런 경우를 많이 보았습니다.

OVER PARTITION BY 절은 몇번 사용해보았습니다만 조인절에 PARTITION BY 절을 사용하니 생소하네요.

 

아래는 기준이 되는 고객번호를 가지고 PARTITION BY절을 사용한 예제입니다.

with calendar_dates as (  
  select date'2018-12-31' + level calendar_date   
  from   dual  
  connect by level <= 3  
), ords as (  
  select * from co.orders o  
  where  o.order_datetime >= date '2019-01-01'   
  and    o.order_datetime < date '2019-01-04'   
)  
select customer_id, calendar_date, count ( o.order_datetime )   
from   calendar_dates  
left   join ords o  
  partition by ( o.customer_id )  
on    o.order_datetime >= calendar_date  
and   o.order_datetime < calendar_date + 1  
group  by calendar_date, customer_id  
order  by customer_id, calendar_date

 

CUSTOMER_ID	CALENDAR_DATE	COUNT(O.ORDER_DATETIME)
15	01-JAN-19	0
15	02-JAN-19	1
15	03-JAN-19	0
41	01-JAN-19	0
41	02-JAN-19	0
41	03-JAN-19	1
68	01-JAN-19	0
68	02-JAN-19	0
68	03-JAN-19	1
76	01-JAN-19	0
76	02-JAN-19	0
76	03-JAN-19	1
91	01-JAN-19	0
91	02-JAN-19	0
91	03-JAN-19	1
172	01-JAN-19	1
172	02-JAN-19	0
172	03-JAN-19	0
194	01-JAN-19	1
194	02-JAN-19	0
194	03-JAN-19	0
211	01-JAN-19	0
211	02-JAN-19	1
211	03-JAN-19	0

좋은 기능이 많은데.. 저도 있는지도 몰랐네요. 다른 DBMS에는 기능이 아직 구현되지 않은 것 같습니다.

 

https://livesql.oracle.com/apex/livesql/file/content_I7LPER4LAN3DWSWHBOTVTCNIE.html?fbclid=IwAR3XMq8b6JWWXgCAXvJn_8vF__OyPvR2YSGAkntCO09wTGFIkrIFlK9Csws