Search

10. 데이터 분석을 위한 작은 기술 - BOOSTER QUIZ 참고 답안

------------------------------------------------------------------------ -- BOOSTER QUIZ 10-1-1 ------------------------------------------------------------------------ SELECT CASE WHEN GROUPING(t1.mbr_gd) = 1 THEN 'Total' ELSE t1.mbr_gd END mbr_gd ,COUNT(*) mbr_cnt FROM startdbmy.ms_mbr t1 WHERE t1.mbr_st = 'ACTV' GROUP BY t1.mbr_gd WITH ROLLUP; ------------------------------------------------------------------------ -- BOOSTER QUIZ 10-1-2 ------------------------------------------------------------------------ SELECT CASE WHEN GROUPING(t1.mbr_gd) = 1 THEN 'Total' ELSE t1.mbr_gd END mbr_gd ,CASE WHEN GROUPING(t1.join_tp) = 1 THEN 'Total' ELSE t1.join_tp END join_tp ,COUNT(*) mbr_cnt FROM startdbmy.ms_mbr t1 WHERE t1.mbr_st = 'ACTV' GROUP BY t1.mbr_gd, t1.join_tp WITH ROLLUP; ------------------------------------------------------------------------ -- BOOSTER QUIZ 10-1-3 ------------------------------------------------------------------------ SELECT CASE WHEN GROUPING(t1.join_tp) = 1 THEN 'Total' ELSE t1.join_tp END join_tp ,CASE WHEN GROUPING(t1.mbr_gd) = 1 THEN 'Total' ELSE t1.mbr_gd END mbr_gd ,COUNT(*) mbr_cnt FROM startdbmy.ms_mbr t1 WHERE t1.mbr_st = 'ACTV' GROUP BY t1.join_tp ,t1.mbr_gd WITH ROLLUP; ------------------------------------------------------------------------ -- BOOSTER QUIZ 10-1-4 ------------------------------------------------------------------------ SELECT CASE WHEN t3.gr_join_tp = 1 THEN 'Total' ELSE t4.base_cd_nm END join_tp_nm ,CASE WHEN t3.gr_ord_ym = 1 THEN 'Total' ELSE t3.ord_ym END ord_ym ,CASE WHEN t3.gr_pay_tp = 1 THEN 'Total' ELSE t5.base_cd_nm END pay_tp_nm ,t3.ord_amt_sum FROM ( SELECT t1.join_tp ,GROUPING(t1.join_tp) gr_join_tp ,DATE_FORMAT(t2.ord_dtm,'%Y%m') ord_ym ,GROUPING(DATE_FORMAT(t2.ord_dtm,'%Y%m')) gr_ord_ym ,t2.pay_tp ,GROUPING(t2.pay_tp) gr_pay_tp ,SUM(t2.ord_amt) ord_amt_sum FROM startdbmy.ms_mbr t1 INNER JOIN startdbmy.tr_ord t2 ON (t2.mbr_id = t1.mbr_id) WHERE t2.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20230301','%Y%m%d') AND t1.mbr_gd = 'PLAT' GROUP BY t1.join_tp ,DATE_FORMAT(t2.ord_dtm,'%Y%m') ,t2.pay_tp WITH ROLLUP ) t3 LEFT OUTER JOIN startdbmy.cm_base_cd t4 ON (t4.base_cd_dv = 'join_tp' AND t4.base_cd = t3.join_tp) LEFT OUTER JOIN startdbmy.cm_base_cd t5 ON (t5.base_cd_dv = 'pay_tp' AND t5.base_cd = t3.pay_tp) ; ------------------------------------------------------------------------ -- BOOSTER QUIZ 10-1-5 ------------------------------------------------------------------------ SELECT CASE WHEN t3.gr_join_tp = 1 THEN 'Total' ELSE t4.base_cd_nm END join_tp_nm ,CASE WHEN t3.gr_ord_ym = 1 THEN 'Total' ELSE t3.ord_ym END ord_ym ,CASE WHEN t3.gr_pay_tp = 1 THEN 'Total' ELSE t5.base_cd_nm END pay_tp_nm ,t3.ord_amt_sum FROM ( SELECT t1.join_tp ,GROUPING(t1.join_tp) gr_join_tp ,DATE_FORMAT(t2.ord_dtm,'%Y%m') ord_ym ,GROUPING(DATE_FORMAT(t2.ord_dtm,'%Y%m')) gr_ord_ym ,t2.pay_tp ,GROUPING(t2.pay_tp) gr_pay_tp ,SUM(t2.ord_amt) ord_amt_sum FROM startdbmy.ms_mbr t1 INNER JOIN startdbmy.tr_ord t2 ON (t2.mbr_id = t1.mbr_id) WHERE t2.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20230301','%Y%m%d') AND t1.mbr_gd = 'PLAT' GROUP BY t1.join_tp ,DATE_FORMAT(t2.ord_dtm,'%Y%m') ,t2.pay_tp WITH ROLLUP ) t3 LEFT OUTER JOIN startdbmy.cm_base_cd t4 ON (t4.base_cd_dv = 'join_tp' AND t4.base_cd = t3.join_tp) LEFT OUTER JOIN startdbmy.cm_base_cd t5 ON (t5.base_cd_dv = 'pay_tp' AND t5.base_cd = t3.pay_tp) WHERE ( t3.gr_pay_tp = 0 -- 기본 데이터(GROUP BY 마지막 컬럼의 GROUPING이 0) OR (t3.gr_join_tp = 0 AND t3.gr_ord_ym = 1 AND t3.gr_pay_tp = 1) -- 중간합계(join_tp별 집계 데이터만) ); ------------------------------------------------------------------------ -- BOOSTER QUIZ 10-2-1 ------------------------------------------------------------------------ SELECT t1.mbr_id ,SUM(CASE WHEN DATE_FORMAT(t1.ord_dtm,'%Y%m%d') = '20230101' THEN t1.ord_amt END) ord_amt_0101 ,SUM(CASE WHEN DATE_FORMAT(t1.ord_dtm,'%Y%m%d') = '20230102' THEN t1.ord_amt END) ord_amt_0102 ,SUM(CASE WHEN DATE_FORMAT(t1.ord_dtm,'%Y%m%d') = '20230103' THEN t1.ord_amt END) ord_amt_0103 FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230104','%Y%m%d') AND t1.shop_id = 'S028' GROUP BY t1.mbr_id ORDER BY t1.mbr_id; ------------------------------------------------------------------------ -- BOOSTER QUIZ 10-2-2 ------------------------------------------------------------------------ SELECT DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ord_ymd ,IFNULL(SUM(CASE WHEN t1.mbr_id = 'M0027' THEN 1 END),0) ord_cnt_M0027 ,IFNULL(SUM(CASE WHEN t1.mbr_id = 'M3627' THEN 1 END),0) ord_cnt_M3627 ,IFNULL(SUM(CASE WHEN t1.mbr_id = 'M3927' THEN 1 END),0) ord_cnt_M3927 FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230104','%Y%m%d') AND t1.shop_id = 'S028' GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ORDER BY DATE_FORMAT(t1.ord_dtm,'%Y%m%d'); ------------------------------------------------------------------------ -- BOOSTER QUIZ 10-2-3 ------------------------------------------------------------------------ SELECT DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym ,t1.shop_id ,t3.item_cat ,SUM(t2.ord_qty) ord_qty_sum FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.tr_ord_det t2 ON (t1.ord_no = t2.ord_no) INNER JOIN startdbmy.ms_item t3 ON (t3.item_id = t2.item_id) WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230301','%Y%m%d') AND t1.shop_id IN ('S001','S002') GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t1.shop_id ,t3.item_cat ORDER BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t1.shop_id ,t3.item_cat; ------------------------------------------------------------------------ -- BOOSTER QUIZ 10-2-4 ------------------------------------------------------------------------ SELECT t4.ord_ym ,t4.item_cat ,SUM(CASE WHEN t4.shop_id = 'S001' THEN t4.ord_qty_sum END) qty_S001 ,SUM(CASE WHEN t4.shop_id = 'S002' THEN t4.ord_qty_sum END) qty_S002 FROM ( SELECT DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym ,t1.shop_id ,t3.item_cat ,SUM(t2.ord_qty) ord_qty_sum FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.tr_ord_det t2 ON (t1.ord_no = t2.ord_no) INNER JOIN startdbmy.ms_item t3 ON (t3.item_id = t2.item_id) WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230301','%Y%m%d') AND t1.shop_id IN ('S001','S002') GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t1.shop_id ,t3.item_cat ORDER BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t1.shop_id ,t3.item_cat ) t4 GROUP BY t4.ord_ym ,t4.item_cat ORDER BY t4.ord_ym ,t4.item_cat; ------------------------------------------------------------------------ -- BOOSTER QUIZ 10-2-5 ------------------------------------------------------------------------ WITH w1 AS ( SELECT t4.ord_ym ,t4.item_cat ,SUM(CASE WHEN t4.shop_id = 'S001' THEN t4.ord_qty_sum END) qty_S001 ,SUM(CASE WHEN t4.shop_id = 'S002' THEN t4.ord_qty_sum END) qty_S002 FROM ( SELECT DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym ,t1.shop_id ,t3.item_cat ,SUM(t2.ord_qty) ord_qty_sum FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.tr_ord_det t2 ON (t1.ord_no = t2.ord_no) INNER JOIN startdbmy.ms_item t3 ON (t3.item_id = t2.item_id) WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230301','%Y%m%d') AND t1.shop_id IN ('S001','S002') GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t1.shop_id ,t3.item_cat ORDER BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t1.shop_id ,t3.item_cat ) t4 GROUP BY t4.ord_ym ,t4.item_cat ORDER BY t4.ord_ym ,t4.item_cat ) SELECT CASE WHEN GROUPING(t5.ord_ym) = 1 THEN 'Total' ELSE t5.ord_ym END ord_ym ,CASE WHEN GROUPING(t5.item_cat) = 1 THEN 'Total' ELSE t5.item_cat END item_cat ,CASE WHEN GROUPING(t5.item_cat) = 1 THEN 'Total' ELSE (SELECT MAX(x.item_cat_nm) FROM startdbmy.ms_item_cat x WHERE x.item_cat = t5.item_cat) END item_cat_nm ,SUM(t5.qty_S001) qty_S001 ,SUM(t5.qty_S002) qty_S002 FROM w1 t5 GROUP BY t5.ord_ym ,t5.item_cat WITH ROLLUP ORDER BY t5.ord_ym, t5.item_cat; ------------------------------------------------------------------------ -- BOOSTER QUIZ 10-2-6 ------------------------------------------------------------------------ WITH w1 AS ( SELECT t3.mbr_gd ,t2.shop_oper_tp ,COUNT(*) ord_cnt FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.ms_shop t2 ON (t2.shop_id = t1.shop_id) INNER JOIN startdbmy.ms_mbr t3 ON (t3.mbr_id = t1.mbr_id) WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20240101','%Y%m%d') GROUP BY t3.mbr_gd ,t2.shop_oper_tp ) SELECT CASE WHEN GROUPING(t4.mbr_gd) = 1 THEN 'Total' ELSE (SELECT max(x.base_cd_nm) FROM startdbmy.cm_base_cd x WHERE x.base_cd_dv = 'mbr_gd' AND x.base_cd = t4.mbr_gd) END 회원등급 ,SUM(CASE WHEN t4.shop_oper_tp = 'FLAG' THEN t4.ord_cnt END) Flagship ,SUM(CASE WHEN t4.shop_oper_tp = 'DRCT' THEN t4.ord_cnt END) Directly ,SUM(CASE WHEN t4.shop_oper_tp = 'DIST' THEN t4.ord_cnt END) Distributor FROM w1 t4 GROUP BY t4.mbr_gd WITH ROLLUP ORDER BY GROUPING(t4.mbr_gd) DESC ,CASE WHEN t4.mbr_gd = 'PLAT' THEN 1 WHEN t4.mbr_gd = 'GOLD' THEN 2 WHEN t4.mbr_gd = 'SILV' THEN 3 END; ------------------------------------------------------------------------ -- BOOSTER QUIZ 10-3-1 ------------------------------------------------------------------------ SELECT t1.base_ymd ord_ymd ,t1.mbr_id ,t1.nick_nm ,IFNULL(t2.ord_amt_sum,0) ord_amt_sum FROM ( SELECT b.base_ymd ,a.mbr_id ,a.nick_nm FROM startdbmy.ms_mbr a CROSS JOIN startdbmy.cm_base_dt b WHERE a.nick_nm IN ('Air','Wind3') AND b.base_dt >= STR_TO_DATE('20230101','%Y%m%d') AND b.base_dt < STR_TO_DATE('20230104','%Y%m%d') ) t1 LEFT OUTER JOIN ( SELECT DATE_FORMAT(c.ord_dtm,'%Y%m%d') ord_ymd ,c.mbr_id ,SUM(c.ord_amt) ord_amt_sum FROM startdbmy.tr_ord c WHERE c.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND c.ord_dtm < STR_TO_DATE('20230104','%Y%m%d') GROUP BY DATE_FORMAT(c.ord_dtm,'%Y%m%d') ,c.mbr_id ) t2 ON (t2.mbr_id = t1.mbr_id AND t2.ord_ymd = t1.base_ymd) ORDER BY t1.base_ymd, t1.mbr_id; ------------------------------------------------------------------------ -- BOOSTER QUIZ 10-3-2 ------------------------------------------------------------------------ SELECT t1.ym ,IFNULL(t2.shop_open_cnt,0) shop_open_cnt ,IFNULL(t3.mbr_join_cnt,0) mbr_join_cnt ,IFNULL(t4.ord_cnt,0) ord_cnt FROM ( SELECT DISTINCT DATE_FORMAT(a.base_dt,'%Y%m') ym FROM startdbmy.cm_base_dt a WHERE a.base_dt >= STR_TO_DATE('20210401','%Y%m%d') AND a.base_dt < STR_TO_DATE('20210701','%Y%m%d') ) t1 LEFT OUTER JOIN ( SELECT SUBSTR(b.shop_start_ymd,1,6) ym ,COUNT(*) shop_open_cnt FROM startdbmy.ms_shop b WHERE b.shop_start_ymd >= '20210401' AND b.shop_start_ymd < '20210701' GROUP BY SUBSTR(b.shop_start_ymd,1,6) ) t2 ON (t2.ym = t1.ym) LEFT OUTER JOIN ( SELECT DATE_FORMAT(c.join_dtm,'%Y%m') ym ,COUNT(*) mbr_join_cnt FROM startdbmy.ms_mbr c WHERE c.join_dtm >= STR_TO_DATE('20210401','%Y%m%d') AND c.join_dtm < STR_TO_DATE('20210701','%Y%m%d') GROUP BY DATE_FORMAT(c.join_dtm,'%Y%m') ) t3 ON (t3.ym = t1.ym) LEFT OUTER JOIN ( SELECT DATE_FORMAT(d.ord_dtm,'%Y%m') ym ,COUNT(*) ord_cnt FROM startdbmy.tr_ord d WHERE d.ord_dtm >= STR_TO_DATE('20210401','%Y%m%d') AND d.ord_dtm < STR_TO_DATE('20210701','%Y%m%d') GROUP BY DATE_FORMAT(d.ord_dtm,'%Y%m') ) t4 ON (t4.ym = t1.ym) ORDER BY t1.ym;
SQL
복사