Search

11. 분석함수 - BOOSTER QUIZ 참고 답안

------------------------------------------------------------------------ -- BOOSTER QUIZ 11-1-1 ------------------------------------------------------------------------ ord_no shop_id ord_dtm ord_amt ord_amt_sum_ov ord_cnt_ov max_dtm_ov ------ ------- ------------------- -------- -------------- ---------- ------------------- 124121 S092 2021-09-03 06:32:00 4500.000 17000.000 4 2021-09-10 06:31:00 124135 S092 2021-09-03 06:36:00 4000.000 17000.000 4 2021-09-10 06:31:00 125684 S092 2021-09-09 06:32:00 4000.000 17000.000 4 2021-09-10 06:31:00 125994 S092 2021-09-10 06:31:00 4500.000 17000.000 4 2021-09-10 06:31:00 ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-1-2 ------------------------------------------------------------------------ shop_id shop_nm chair_qty shop_cnt_ov chair_qty_sum_ov ------- ---------------- --------- ----------- ---------------- S014 Columbus-1st 23 3 77 S022 Los Angeles-2nd 20 3 77 S026 Philadelphia-2nd 34 3 77 ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-1-3 ------------------------------------------------------------------------ SELECT t1.ord_no ,t1.ord_dtm ,t1.mbr_id ,t1.ord_amt ,SUM(t1.ord_amt) OVER() ord_amt_sum_ov ,COUNT(*) OVER() ord_cnt_ov FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20220102','%Y%m%d') AND t1.mbr_id = 'M0888' ORDER BY t1.ord_no; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-1-4 ------------------------------------------------------------------------ SELECT t2.* ,ROUND(t2.ord_amt / t2.ord_amt_sum_ov ,2) ord_amt_rat_sum FROM ( SELECT t1.ord_no ,t1.ord_dtm ,t1.mbr_id ,t1.ord_amt ,SUM(t1.ord_amt) OVER() ord_amt_sum_ov ,COUNT(*) OVER() ord_cnt_ov FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20220102','%Y%m%d') AND t1.mbr_id = 'M0888' ORDER BY t1.ord_no ) t2; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-1-5 ------------------------------------------------------------------------ SELECT t1.shop_id ,t1.shop_nm ,t1.chair_qty ,MAX(t1.chair_qty) OVER() chair_qty_max ,ROUND(t1.chair_qty / MAX(t1.chair_qty) OVER(), 2) chair_qty_rat_max FROM startdbmy.ms_shop t1 WHERE t1.chair_qty >= 70 ORDER BY t1.chair_qty DESC; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-2-1 ------------------------------------------------------------------------ ord_no shop_id ord_dtm ord_amt ord_dtm_rank_ov ------ ------- ------------------- -------- --------------- 127776 S261 2021-09-22 06:46:00 8500.000 5 127767 S261 2021-09-22 06:39:00 4000.000 4 127764 S261 2021-09-22 06:38:00 4000.000 3 127751 S261 2021-09-22 06:31:00 4000.000 2 123551 S261 2021-09-01 06:32:00 4000.000 1 ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-2-2 ------------------------------------------------------------------------ SELECT t1.mbr_id ,t1.nick_nm ,t1.join_dtm ,t1.mbr_gd ,RANK() OVER(ORDER BY t1.join_dtm ASC) join_dtm_rank_ov FROM startdbmy.ms_mbr t1 WHERE t1.mbr_gd = 'PLAT' AND t1.join_tp = 'INV' AND t1.join_dtm >= STR_TO_DATE('20220401','%Y%m%d') AND t1.join_dtm < STR_TO_DATE('20220501','%Y%m%d'); ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-3-1 ------------------------------------------------------------------------ SELECT t4.item_cat ,t5.item_cat_nm ,t4.ord_qty_sum ,RANK() OVER(ORDER BY t4.ord_qty_sum DESC) ord_qty_sum_rank_ov FROM ( SELECT t3.item_cat ,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) WHERE t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20220201','%Y%m%d') GROUP BY t3.item_cat ) t4 INNER JOIN startdbmy.ms_item_cat t5 ON (t5.item_cat = t4.item_cat); ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-3-2 ------------------------------------------------------------------------ SELECT t3.shop_oper_tp ,(SELECT MAX(x.base_cd_nm) FROM startdbmy.cm_base_cd x WHERE x.base_cd_dv = 'shop_oper_tp' AND x.base_cd = t3.shop_oper_tp) shop_oper_tp_nm ,t3.ord_amt_sum ,ROUND(t3.ord_amt_sum / SUM(t3.ord_amt_sum) OVER() * 100,2) ord_amt_sum_pct FROM ( SELECT t1.shop_oper_tp ,SUM(t2.ord_amt) ord_amt_sum FROM startdbmy.ms_shop t1 INNER JOIN startdbmy.tr_ord t2 ON (t2.shop_id = t1.shop_id) WHERE t2.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20220201','%Y%m%d') GROUP BY t1.shop_oper_tp ) t3 ORDER BY ord_amt_sum_pct DESC; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-4-1 ------------------------------------------------------------------------ SELECT t1.mbr_id ,t1.ord_dtm ,t1.ord_amt ,SUM(t1.ord_amt) OVER(PARTITION BY t1.mbr_id) ord_amt_ov_mbr FROM startdbmy.tr_ord t1 WHERE t1.mbr_id IN ('M0100','M0200','M0300') AND t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230201','%Y%m%d') ORDER BY t1.mbr_id ,t1.ord_dtm; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-4-2 ------------------------------------------------------------------------ SELECT t1.mbr_id ,t1.ord_dtm ,t1.ord_amt ,SUM(t1.ord_amt) OVER(PARTITION BY t1.mbr_id) ord_amt_ov_mbr ,RANK() OVER(PARTITION BY t1.mbr_id ORDER BY t1.ord_amt DESC) ord_amt_rank_ov_mbr FROM startdbmy.tr_ord t1 WHERE t1.mbr_id IN ('M0100','M0200','M0300') AND t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230201','%Y%m%d') ORDER BY t1.mbr_id ,t1.ord_dtm; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-4-3 ------------------------------------------------------------------------ SELECT t1.ord_no ,t1.shop_id ,t1.ord_dtm ,t1.ord_amt ,SUM(t1.ord_amt) OVER(PARTITION BY DATE_FORMAT(t1.ord_dtm,'%Y%m')) ord_amt_sum_ov_ym FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230401','%Y%m%d') AND t1.ord_amt >= 10000 AND t1.shop_id = 'S282' ORDER BY t1.shop_id, t1.ord_dtm; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-4-4 ------------------------------------------------------------------------ SELECT t1.ord_no ,t4.item_cat ,t4.item_cat_nm ,t3.item_nm ,DATE_FORMAT(t1.ord_dtm,'%H') ord_hour, t2.ord_qty ,SUM(t2.ord_qty) OVER(PARTITION BY DATE_FORMAT(t1.ord_dtm,'%H'), t4.item_cat) ord_qty_sum_ov_hour_cat 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('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230102','%Y%m%d') AND t1.ord_amt >= 10000 AND t1.shop_id IN ('S100') AND t4.item_cat IN ('BKR','COF') ORDER BY t1.shop_id ,ord_hour; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-4-5 ------------------------------------------------------------------------ SELECT t3.* ,RANK() OVER(PARTITION BY t3.ord_ymd ORDER BY t3.ord_amt_sum DESC) ord_amt_rank_ov_ymd FROM ( SELECT t2.shop_id ,MAX(t2.shop_nm) shop_nm ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ord_ymd ,SUM(t1.ord_amt) ord_amt_sum 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('20240101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20240104','%Y%m%d') AND t2.shop_oper_tp = 'FLAG' AND t2.shop_size >= 160 GROUP BY t2.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ORDER BY t2.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ) t3; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-4-6 ------------------------------------------------------------------------ SELECT * FROM ( SELECT t3.* ,RANK() OVER(PARTITION BY t3.ord_ymd ORDER BY t3.ord_amt_sum DESC) ord_amt_rank_ov FROM ( SELECT t2.shop_id ,MAX(t2.shop_nm) shop_nm ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ord_ymd ,SUM(t1.ord_amt) ord_amt_sum 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('20240101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20240104','%Y%m%d') AND t2.shop_oper_tp = 'FLAG' AND t2.shop_size >= 160 GROUP BY t2.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ORDER BY t2.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ) t3 ) t4 WHERE t4.ord_amt_rank_ov = 1; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-4-7 ------------------------------------------------------------------------ SELECT * FROM ( SELECT DATE_FORMAT(t1.ord_dtm,'%Y') ord_yy ,t3.item_id ,MAX(t3.item_nm) item_nm ,SUM(t2.ord_qty) ord_qty_sum ,RANK() OVER(PARTITION BY DATE_FORMAT(t1.ord_dtm,'%Y') ORDER BY SUM(t2.ord_qty) DESC) ord_qty_sum_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) WHERE t1.ord_dtm >= STR_TO_DATE('20200101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20250101','%Y%m%d') AND t3.item_cat = 'COF' GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y') ,t3.item_id ) t4 WHERE t4.ord_qty_sum_rank_ov = 1; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-5-1 ------------------------------------------------------------------------ ord_no shop_id ord_dtm ord_amt ord_amt_bf ------ ------- ------------------- --------- ---------- 124121 S092 2021-09-03 06:32:00 4500.000 NULL 124135 S092 2021-09-03 06:36:00 4000.000 4500.000 125684 S092 2021-09-09 06:32:00 4000.000 4000.000 125994 S092 2021-09-10 06:31:00 4500.000 4000.000 126773 S092 2021-09-14 06:31:00 4500.000 4500.000 126781 S092 2021-09-14 06:38:00 14000.000 4500.000 ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-5-2 ------------------------------------------------------------------------ SELECT t5.item_cat ,t5.item_cat_nm ,t5.ord_ym ,t5.ord_amt_sum ,LAG(t5.ord_amt_sum) OVER(PARTITION BY t5.item_cat ORDER BY t5.ord_ym ASC) ord_amt_sum_bf_cat ,t5.ord_amt_sum - LAG(t5.ord_amt_sum) OVER(PARTITION BY t5.item_cat ORDER BY t5.ord_ym ASC) ord_amt_sum_cat_diff FROM ( SELECT t4.item_cat ,MAX(t4.item_cat_nm) item_cat_nm ,DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym ,SUM(t2.ord_qty * t2.sale_prc) ord_amt_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('20220101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20220401','%Y%m%d') GROUP BY t4.item_cat ,DATE_FORMAT(t1.ord_dtm,'%Y%m') ORDER BY t4.item_cat ,DATE_FORMAT(t1.ord_dtm,'%Y%m') ) t5; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-6-1 ------------------------------------------------------------------------ SELECT t4.item_id ,t4.item_nm ,t4.ord_ymd ,t4.ord_qty_sum ,SUM(t4.ord_qty_sum) OVER(ORDER BY t4.ord_ymd ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ord_qty_sum_run_total FROM ( SELECT t3.item_id ,MAX(t3.item_nm) item_nm ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ord_ymd ,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) WHERE t1.ord_dtm >= STR_TO_DATE('20221220','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20221226','%Y%m%d') AND t2.item_id = 'AMB' GROUP BY t3.item_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ORDER BY t3.item_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ) t4; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-6-2 ------------------------------------------------------------------------ SELECT t4.item_id ,t4.item_nm ,t4.ord_ymd ,t4.ord_qty_sum ,SUM(t4.ord_qty_sum) OVER(PARTITION BY t4.item_id ORDER BY t4.ord_ymd ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ord_qty_sum_run_total_item FROM ( SELECT t3.item_id ,MAX(t3.item_nm) item_nm ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ord_ymd ,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) WHERE t1.ord_dtm >= STR_TO_DATE('20221220','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20221226','%Y%m%d') AND t2.item_id IN ('AMB','IAMB') GROUP BY t3.item_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ORDER BY t3.item_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ) t4; ------------------------------------------------------------------------ -- BOOSTER QUIZ 11-6-3 ------------------------------------------------------------------------ SELECT t1.base_ymd ,IFNULL(t2.join_cnt,0) join_cnt ,MAX(IFNULL(t2.join_cnt,0)) OVER(ORDER BY t1.base_ymd ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) join_cnt_max_ov_10d FROM startdbmy.cm_base_dt t1 LEFT OUTER JOIN ( SELECT DATE_FORMAT(a.join_dtm,'%Y%m%d') join_ymd ,COUNT(*) join_cnt FROM startdbmy.ms_mbr a WHERE a.join_dtm >= STR_TO_DATE('20220701','%Y%m%d') AND a.join_dtm < STR_TO_DATE('20220801','%Y%m%d') GROUP BY DATE_FORMAT(a.join_dtm,'%Y%m%d') ) t2 ON (t2.join_ymd = t1.base_ymd) WHERE t1.base_ymd LIKE '202207%' ORDER BY t1.base_ymd;
SQL
복사