SQL Review

사용자의 액션 수 집계 액션과 관련된 지표 집계하기 with stats as( select count(distinct session) as total_uu from action_log ) select l.action, count(distinct l.session) as action_uu, count(1) as action_count, r.total_uu, 100.0 * count(distinct l.session) / r.total_uu as usage_rate, 1.0 * count(1) / count(distinct l.session) as count_per_user from action_log as l cross join stats as r group by l.action, r.total_uu; U..

1. 카테고리별 매출과 소계를 동시에 출력 with sub_category_amount as( select category, sub_category, sum(price) as amount from purchase_detail_log group by category, sub_category), category_amount as( select category, 'all' as sub_category, sum(price) as amount from purchase_detail_log group by category), total_category_amount as( select 'all' as category, 'all' as sub_category, sum(price) as amount from purchas..

1. 날짜별 매출과 평균 구매액 집계 select dt, count(dt) as purchase_count, sum(purchase_amount) as total_amount, avg(purchase_amount) as avg_amount from purchase_log group by dt order by dt; 2. 날짜별 매출과 7일 이동평균 집계 select dt, sum(purchase_amount) as total_amount, avg(sum(purchase_amount)) over(order by dt rows between 6 preceding and current row) as seven_day_avg_amount, case when 7 = count(*) over (order by dt..

1. union all을 사용해 테이블을 세로로 결합 select 'app1' as app_name, user_id, name, email from app1_mst_users union all select 'app2' as app_name, user_id, name, NULL as email from app2_mst_users; union all : 유사한 구조의 테이블 데이터를 일괄 처리하고 싶을 때, 세로로 결합하기 위해 사용. 단, 데이터가 많아지면 계산량이 늘어나므로 주의 한쪽 테이블에만 존재하는 column은 select에서 제외하거나, default value를 지정해야 함. 2. 여러 개의 테이블을 결합해서 가로로 정렬 select m.category_id, m.name, c.sales, p..