-- [SQL-11-4-1-a] SELECT t1.shop_id ,t1.ord_no ,t1.ord_dtm ,t1.ord_amt ,SUM(t1.ord_amt) OVER() ord_amt_sum_ov ,SUM(t1.ord_amt) OVER(PARTITION BY t1.shop_id) ord_amt_sum_ov_shop_id FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20220110','%Y%m%d') AND t1.shop_id IN ('S078','S079') ORDER BY t1.shop_id ,t1.ord_dtm ASC; -- [SQL-11-4-1-b] SELECT t1.shop_id ,t1.mbr_id ,t1.pay_tp ,t2.mbr_gd ,t1.ord_amt ,SUM(t1.ord_amt) OVER(PARTITION BY t1.pay_tp ,t2.mbr_gd) ord_amt_sum_ov_pay_mbr FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.ms_mbr t2 ON (t2.mbr_id = t1.mbr_id) WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230201','%Y%m%d') AND t1.shop_id = 'S281' AND t2.join_tp = 'SNS' ORDER BY t1.pay_tp ,t2.mbr_gd; -- [SQL-11-4-2-a] SELECT t1.shop_id ,t1.shop_nm ,t1.chair_qty ,t1.shop_oper_tp ,RANK() OVER(ORDER BY t1.chair_qty DESC) chair_qty_rank_ov ,RANK() OVER(PARTITION BY t1.shop_oper_tp ORDER BY t1.chair_qty DESC) chair_qty_rank_ov_oper FROM startdbmy.ms_shop t1 WHERE t1.shop_start_ymd = '20180316' ORDER BY t1.shop_oper_tp ASC, t1.chair_qty DESC; -- [SQL-11-4-3-a] SELECT t1.shop_id ,t1.shop_nm ,t1.chair_qty ,t1.shop_oper_tp ,CASE WHEN t1.shop_oper_tp in ('DIST','DRCT') THEN 'DIST&DRCT' ELSE 'FLAG' END new_shop_oerp_tp ,RANK() OVER(PARTITION BY CASE WHEN t1.shop_oper_tp in ('DIST','DRCT') THEN 'DIST&DRCT' ELSE 'FLAG' END ORDER BY t1.chair_qty DESC) chair_qty_rank_ov_shop_oper FROM startdbmy.ms_shop t1 WHERE t1.shop_start_ymd = '20180316' ORDER BY new_shop_oerp_tp ASC, t1.chair_qty DESC; -- [SQL-11-4-3-b] SELECT t1.shop_id ,t2.shop_nm ,t1.ord_dtm ,t1.ord_amt ,SUM(t1.ord_amt) OVER(PARTITION BY DATE_FORMAT(t1.ord_dtm,'%Y%m%d')) ord_amt_sum_ov_ymd FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.ms_shop t2 ON (t2.shop_id = t1.shop_id) WHERE t1.ord_dtm >= STR_TO_DATE('20241224','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20241227','%Y%m%d') AND t1.shop_id = 'S001'; -- [SQL-11-4-4-a] SELECT DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym ,t2.join_tp ,COUNT(*) ord_cnt FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.ms_mbr t2 ON (t2.mbr_id = t1.mbr_id) WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230301','%Y%m%d') GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t2.join_tp ORDER BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,ord_cnt DESC; -- [SQL-11-4-4-b] SELECT DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym ,t2.join_tp ,COUNT(*) ord_cnt ,RANK() OVER(PARTITION BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ORDER BY COUNT(*) DESC) ord_rank_ov_ord_ym FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.ms_mbr t2 ON (t2.mbr_id = t1.mbr_id) WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230301','%Y%m%d') GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t2.join_tp ORDER BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,ord_cnt DESC; -- [SQL-11-4-4-c] SELECT t3.* ,RANK() OVER(PARTITION BY t3.ord_ym ORDER BY t3.ord_cnt DESC) ord_rank_ov_ord_ym FROM ( SELECT DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym ,t2.join_tp ,COUNT(*) ord_cnt FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.ms_mbr t2 ON (t2.mbr_id = t1.mbr_id) WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230301','%Y%m%d') GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t2.join_tp ORDER BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,ord_cnt DESC ) t3; -- [SQL-11-4-5-a] SELECT t1.shop_id ,t1.shop_nm ,t1.shop_oper_tp ,t1.shop_size ,RANK() OVER(PARTITION BY t1.shop_oper_tp ORDER BY t1.shop_size DESC) shop_size_rank_ov FROM startdbmy.ms_shop t1 WHERE t1.shop_start_ymd >= '20220601' AND t1.shop_oper_tp IN ('DIST','DRCT'); -- [SQL-11-4-5-b] SELECT t2.* FROM ( -- [SQL-11-4-5-a] SELECT t1.shop_id ,t1.shop_nm ,t1.shop_oper_tp ,t1.shop_size ,RANK() OVER(PARTITION BY t1.shop_oper_tp ORDER BY t1.shop_size DESC) shop_size_rank_ov FROM startdbmy.ms_shop t1 WHERE t1.shop_start_ymd >= '20220601' AND t1.shop_oper_tp IN ('DIST','DRCT') ) t2 WHERE t2.shop_size_rank_ov = 1; -- [SQL-11-4-5-c] SELECT t3.item_cat ,MAX(t4.item_cat_nm) item_cat_nm ,t2.item_id ,MAX(t3.item_nm) item_nm ,SUM(t2.ord_qty) ord_qty_sum FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.tr_ord_det t2 ON (t2.ord_no = t1.ord_no) INNER JOIN startdbmy.ms_item t3 ON (t3.item_id = t2.item_id) INNER JOIN startdbmy.ms_item_cat t4 ON (t4.item_cat = t3.item_cat) WHERE t1.ord_dtm >= STR_TO_DATE('20250101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20250201','%Y%m%d') GROUP BY t3.item_cat ,t2.item_id ORDER BY t3.item_cat ,t2.item_id; -- [SQL-11-4-5-d] SELECT t4.* ,RANK() OVER(PARTITION BY t4.item_cat ORDER BY t4.ord_qty_sum DESC) ord_qty_rank_ov FROM ( -- [SQL-11-4-5-c] SELECT t3.item_cat ,MAX(t4.item_cat_nm) item_cat_nm ,t2.item_id ,MAX(t3.item_nm) item_nm ,SUM(t2.ord_qty) ord_qty_sum FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.tr_ord_det t2 ON (t2.ord_no = t1.ord_no) INNER JOIN startdbmy.ms_item t3 ON (t3.item_id = t2.item_id) INNER JOIN startdbmy.ms_item_cat t4 ON (t4.item_cat = t3.item_cat) WHERE t1.ord_dtm >= STR_TO_DATE('20250101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20250201','%Y%m%d') GROUP BY t3.item_cat ,t2.item_id ORDER BY t3.item_cat ,t2.item_id ) t4; -- [SQL-11-4-5-e] SELECT t5.* FROM ( -- [SQL-11-4-5-d] SELECT t4.* ,RANK() OVER(PARTITION BY t4.item_cat ORDER BY t4.ord_qty_sum DESC) ord_qty_rank_ov FROM ( -- [SQL-11-4-5-c] SELECT t3.item_cat ,MAX(t4.item_cat_nm) item_cat_nm ,t2.item_id ,MAX(t3.item_nm) item_nm ,SUM(t2.ord_qty) ord_qty_sum FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.tr_ord_det t2 ON (t2.ord_no = t1.ord_no) INNER JOIN startdbmy.ms_item t3 ON (t3.item_id = t2.item_id) INNER JOIN startdbmy.ms_item_cat t4 ON (t4.item_cat = t3.item_cat) WHERE t1.ord_dtm >= STR_TO_DATE('20250101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20250201','%Y%m%d') GROUP BY t3.item_cat ,t2.item_id ORDER BY t3.item_cat ,t2.item_id ) t4 ) t5 WHERE t5.ord_qty_rank_ov = 1; -- [SQL-11-4-5-f] SELECT t4.* FROM ( SELECT t3.item_cat ,MAX(t4.item_cat_nm) item_cat_nm ,t2.item_id ,MAX(t3.item_nm) ,SUM(t2.ord_qty) ord_qty_sum ,RANK() OVER(PARTITION BY t3.item_cat ORDER BY SUM(t2.ord_qty) DESC) ord_qty_rank_ov FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.tr_ord_det t2 ON (t2.ord_no = t1.ord_no) INNER JOIN startdbmy.ms_item t3 ON (t3.item_id = t2.item_id) INNER JOIN startdbmy.ms_item_cat t4 ON (t4.item_cat = t3.item_cat) WHERE t1.ord_dtm >= STR_TO_DATE('20250101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20250201','%Y%m%d') GROUP BY t3.item_cat ,t2.item_id ORDER BY t3.item_cat ,t2.item_id ) t4 WHERE t4.ord_qty_rank_ov = 1; -- [SQL-11-4-5-g] SELECT t2.shop_id ,t3.shop_nm ,t2.mbr_id ,t4.nick_nm ,t2.ord_dtm ,t2.ord_amt ,t2.ord_dtm_rank_ov FROM ( SELECT t1.shop_id ,t1.mbr_id ,t1.ord_dtm ,t1.ord_amt ,RANK() OVER(PARTITION BY t1.shop_id ORDER BY t1.ord_dtm ASC) ord_dtm_rank_ov FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20210401','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20210402','%Y%m%d') ) t2 INNER JOIN startdbmy.ms_shop t3 ON (t3.shop_id = t2.shop_id) INNER JOIN startdbmy.ms_mbr t4 ON (t4.mbr_id = t2.mbr_id) WHERE t2.ord_dtm_rank_ov = 1 ORDER BY t2.shop_id;

11. 분석함수

11-1. 분석함수 이해하기

11-2. 순위 분석함수

11-3. GROUP BY와 분석함수

11-4. 분석함수와 PARTITION BY

11-5. LAG와 LEAD

11-6. 분석대상 세밀하게 나누기

11-7. 기타 분석함수