Search

5. GROUP BY - BOOSTER QUIZ 참고 답안

------------------------------------------------------------------------ -- BOOSTER QUIZ 5-1-1 ------------------------------------------------------------------------ -- (결과-2) ord_st ------ MFGC PKUP PREP -- (결과-3) shop_id ------- S100 S280 ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-2-1 ------------------------------------------------------------------------ -- (결과-2) shop_id CNT SUM_AMT ------- --- --------- S047 4 20500.000 S064 1 4000.000 S068 2 18000.000 ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-2-2 ------------------------------------------------------------------------ SELECT t1.item_size_cd ,COUNT(*) item_cnt FROM startdbmy.ms_item t1 WHERE item_cat = 'COF' GROUP BY t1.item_size_cd ORDER BY t1.item_size_cd; ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-2-3 ------------------------------------------------------------------------ SELECT t1.mbr_id ,SUM(t1.ord_amt) ord_amt_sum ,COUNT(*) ord_cnt FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20220105','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20220106','%Y%m%d') GROUP BY t1.mbr_id ORDER BY ord_amt_sum DESC; ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-3-1 ------------------------------------------------------------------------ 2, GROUP BY에 없는 lach_dt를 집계함수 없이 사용중 ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-3-2 ------------------------------------------------------------------------ SELECT MIN(t1.ord_dtm) first_ord_dtm ,MAX(t1.ord_dtm) last_ord_dtm ,t1.mbr_id FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20220103','%Y%m%d') AND t1.shop_id IN ('S012','S212') GROUP BY t1.mbr_id; ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-4-1 ------------------------------------------------------------------------ 3: GROUP BY에 없는 item_size_cd를 SELECT 절에서 사용하고 있음 6: GROUP BY에 없는 item_id를 HAVING 절에서 사용하고 있음 ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-4-2 ------------------------------------------------------------------------ SELECT t1.mbr_id ,SUM(t1.ord_amt) ord_amt_sum FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20240101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20240201','%Y%m%d') GROUP BY t1.mbr_id HAVING SUM(t1.ord_amt) >= 400000 ORDER BY ord_amt_sum DESC; ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-5-1 ------------------------------------------------------------------------ ord_ym cnt ------ --- 202109 3 202110 2 202111 2 ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-5-2 ------------------------------------------------------------------------ SELECT DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym ,COUNT(*) ord_cnt ,SUM(t1.ord_amt) ord_amt_sum FROM startdbmy.tr_ord t1 WHERE t1.shop_id = 'S246' AND t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230101','%Y%m%d') GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ORDER BY ord_ym DESC; ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-5-3 ------------------------------------------------------------------------ SELECT DATE_FORMAT(t1.ord_dtm) ord_hour ,COUNT(*) ord_cnt FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230101','%Y%m%d') GROUP BY DATE_FORMAT(t1.ord_dtm) ORDER BY ord_cnt DESC; ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-6-1 ------------------------------------------------------------------------ SELECT t1.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ord_ymd ,COUNT(*) ord_cnt FROM startdbmy.tr_ord t1 WHERE t1.shop_id IN ('S002','S003') AND t1.ord_dtm >= STR_TO_DATE('20230601','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230604','%Y%m%d') GROUP BY t1.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ORDER BY t1.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d'); ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-6-2 ------------------------------------------------------------------------ SELECT t1.shop_id ,TIMESTAMPDIFF(MINUTE, t1.ord_dtm, t1.prep_cmp_dtm) 제조완료분수 ,COUNT(*) ord_cnt FROM startdbmy.tr_ord t1 WHERE t1.shop_id IN ('S213','S214') AND t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20220201','%Y%m%d') GROUP BY t1.shop_id ,TIMESTAMPDIFF(MINUTE, t1.ord_dtm, t1.prep_cmp_dtm) ORDER BY t1.shop_id ,TIMESTAMPDIFF(MINUTE, t1.ord_dtm, t1.prep_cmp_dtm); ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-8-1 ------------------------------------------------------------------------ SELECT COUNT(*) ord_cnt ,COUNT(DISTINCT t1.shop_id) ord_shop_cnt FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20250101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20250201','%Y%m%d'); ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-9-1 ------------------------------------------------------------------------ mbr_id + ord_ymd ------------------------------------------------------------------------ -- BOOSTER QUIZ 5-9-2 ------------------------------------------------------------------------ shop_id + ord_hour + ord_st
SQL
복사