꿈꾸는 개발자, DBA 커뮤니티 구루비

안녕하세요 원하는 조회값을 뽑아내기가 어려워 질문드립니다. ㅠ

by 겸둥이k [2022.05.03 16:10:42]

캡처.PNG (24,688Bytes)

안녕하세요 공공API에 데이터를 알맞게 쿼리를짜서 통계치를 낼려고하는데 어려워서 질문드립니다.

SELECT
           CASE WHEN UPDATE_NM = '식육(숯불구이)' OR UPDATE_NM ='냉면집' OR UPDATE_NM ='탕류(보신용)' OR UPDATE_NM ='복어취급' OR UPDATE_NM='한식' OR UPDATE_NM= '횟집' THEN '한식음식점업'
               WHEN UPDATE_NM = '중국식' THEN '중식음식점업'
               WHEN UPDATE_NM = '일식' THEN '일식음식점업'
               WHEN UPDATE_NM = '경양식' OR UPDATE_NM ='외국음식전문점(인도,태국등)' OR UPDATE_NM ='패밀리레스트랑' THEN '서양식 음식점업'
               WHEN UPDATE_NM = '이동조리' OR UPDATE_NM='출장조리' THEN '출장 및 이동 음식점업'
               WHEN UPDATE_NM = '패스트푸드' THEN '패스트푸드'
               WHEN UPDATE_NM = '통닭(치킨)' OR UPDATE_NM='호프/통닭' THEN '치킨'
               WHEN UPDATE_NM = '김밥(도시락)' OR UPDATE_NM='분식' THEN '분식'
               WHEN UPDATE_NM = '커피숍' OR UPDATE_NM='까페' OR UPDATE_NM='전통찻집' OR UPDATE_NM='다방' THEN '커피 및 음료점업'
               WHEN UPDATE_NM = '간이주점' OR UPDATE_NM='감성주점' OR UPDATE_NM='라이브카페' OR UPDATE_NM='정종/대포집/소주방' THEN '주점업'
               WHEN UPDATE_NM = '뷔페식'OR UPDATE_NM='기타 휴게음식점' OR UPDATE_NM='기타' OR UPDATE_NM='미분류' THEN '기타'
               END AS STATE_UPDT_NM
               ,CASE WHEN DTL_STATE_GBN = '01' THEN DTL_STATE_NM
               END AS STATE_DT1
               ,CASE WHEN DTL_STATE_GBN = '02' THEN DTL_STATE_NM
               END AS STATE_DT2
               ,DATE_FORMAT(APV_PERM_YMD, '%Y') AS STATE_DT3
               ,DATE_FORMAT(APV_PERM_YMD, '%Y') APV_PERM_YMD
           FROM TB_BUSINESS_STATUS_TEMP
           WHERE DATE_FORMAT(APV_PERM_YMD, '%Y') >= '2019' AND DATE_FORMAT(APV_PERM_YMD, '%Y') <= DATE_FORMAT(NOW(), '%Y')

1.데이터값은 : state_update_Nm : 일식음식점업,중국식 등

steate_dt1 : 폐업 ,영업

state_dt3 : 2019~2022

apv_perm_ymd: 2019~2022 입니다.

파일첨부와같이 데이터 리스트가 뜹니다.

이걸 STATE_UPDT_NM(음식점별) 과 APV_PERM_YMD(년도) 별로 나열하고 STATE_DT1 ,STATE_DT2 , STATE_DT3 카운트하고싶습니다.

GROUP BY 어떻게 잘하면될거같은데 ㅠㅠ 어렵네요 방법이있을까요 답변부탁드립니다.

by 마농 [2022.05.03 17:51:39]

원하시는 결과표를 보여주세요.

by 겸둥이k [2022.05.04 13:16:02]

해결하였습니다. 단순히 GROUP BY를 잘못한거였더라구요

SELECT
       A.STATE_UPDT_NM AS stateUpdtNm
       ,A.APV_PERM_YMD AS apvPermYmd
       ,A.STATE_DT1 AS stateDt1
       ,A.STATE_DT2 AS stateDt2
       ,A.STATE_DT3 AS stateDt3
       ,A.STATE_DT1+STATE_DT3 AS stateSumDt1
   FROM(
       SELECT
           CASE WHEN UPDATE_NM = '식육(숯불구이)' OR UPDATE_NM ='냉면집' OR UPDATE_NM ='탕류(보신용)' OR UPDATE_NM ='복어취급' OR UPDATE_NM='한식' OR UPDATE_NM= '횟집' THEN '한식음식점업'
               WHEN UPDATE_NM = '중국식' THEN '중식음식점업'
               WHEN UPDATE_NM = '일식' THEN '일식음식점업'
               WHEN UPDATE_NM = '경양식' OR UPDATE_NM ='외국음식전문점(인도,태국등)' OR UPDATE_NM ='패밀리레스트랑' THEN '서양식 음식점업'
               WHEN UPDATE_NM = '이동조리' OR UPDATE_NM='출장조리' THEN '출장 및 이동 음식점업'
               WHEN UPDATE_NM = '패스트푸드' THEN '패스트푸드'
               WHEN UPDATE_NM = '통닭(치킨)' OR UPDATE_NM='호프/통닭' THEN '치킨'
               WHEN UPDATE_NM = '김밥(도시락)' OR UPDATE_NM='분식' THEN '분식'
               WHEN UPDATE_NM = '커피숍' OR UPDATE_NM='까페' OR UPDATE_NM='전통찻집' OR UPDATE_NM='다방' THEN '커피 및 음료점업'
               WHEN UPDATE_NM = '간이주점' OR UPDATE_NM='감성주점' OR UPDATE_NM='라이브카페' OR UPDATE_NM='정종/대포집/소주방' THEN '주점업'
               WHEN UPDATE_NM = '뷔페식'OR UPDATE_NM='기타 휴게음식점' OR UPDATE_NM='기타' OR UPDATE_NM='미분류' THEN '기타'
               ELSE '없음'
               END AS STATE_UPDT_NM
               ,DATE_FORMAT(APV_PERM_YMD, '%Y') APV_PERM_YMD
               ,COUNT(CASE WHEN DTL_STATE_GBN = '01' THEN DTL_STATE_NM
               END) AS STATE_DT1
               ,COUNT(CASE WHEN DTL_STATE_GBN = '02' THEN DTL_STATE_NM
               END) AS STATE_DT2
               ,COUNT(DATE_FORMAT(APV_PERM_YMD, '%Y')) AS STATE_DT3
           FROM TB_BUSINESS_STATUS_TEMP
           WHERE DATE_FORMAT(APV_PERM_YMD, '%Y') >= 2019 AND DATE_FORMAT(APV_PERM_YMD, '%Y') <= DATE_FORMAT(NOW(), '%Y')
           GROUP BY STATE_UPDT_NM,DATE_FORMAT(APV_PERM_YMD, '%Y')
           )A

이쿼리를 돌렸을시

stateUpdtNm apvPermYmd stateDt1 stateDt2 stateDt3 stateSumDt1

기타   2019   9429   4165   13594   23023
기타   2020   11275   2259   13534   24809
기타   2021   13512   1184   14696   28208
기타   2022   3272   123   3395   6667
분식   2019   2715   1272   3987   6702
분식   2020   3010   783   3793   6803
분식   2021   3670   327   3997   7667
분식   2022   656   13   669   1325
서양식 음식점업   2019   3051   1146   4197   7248
서양식 음식점업   2020   4007   788   4795   8802
서양식 음식점업   2021   5252   377   5629   10881
서양식 음식점업   2022   1161   20   1181   2342
없음   2019   67   25   92   159
없음   2020   27   3   30   57
없음   2021   22   1   23   45
없음   2022   8   0   8   16
일식음식점업   2019   1624   542   2166   3790
일식음식점업   2020   1836   324   2160   3996
일식음식점업   2021   2658   193   2851   5509
일식음식점업   2022   608   12   620   1228
주점업   2019   1250   468   1718   2968
주점업   2020   1210   232   1442   2652
주점업   2021   977   51   1028   2005
주점업   2022   204   1   205   409
중식음식점업   2019   1661   445   2106   3767
중식음식점업   2020   1576   244   1820   3396
중식음식점업   2021   2285   141   2426   4711
중식음식점업   2022   567   4   571   1138
출장 및 이동 음식점업   2019   44   10   54   98
출장 및 이동 음식점업   2020   27   5   32   59
출장 및 이동 음식점업   2021   22   3   25   47
출장 및 이동 음식점업   2022   4   0   4   8
치킨   2019   4603   1676   6279   10882
치킨   2020   5642   939   6581   12223
치킨   2021   5111   342   5453   10564
치킨   2022   942   10   952   1894
커피 및 음료점업   2019   9   2   11   20
커피 및 음료점업   2020   10   2   12   22
커피 및 음료점업   2021   2   0   2   4
패스트푸드   2019   3   1   4   7
패스트푸드   2020   1   0   1   2
패스트푸드   2021   3   0   3   6
한식음식점업   2019   21260   9107   30367   51627
한식음식점업   2020   23376   4918   28294   51670
한식음식점업   2021   25111   1956   27067   52178
한식음식점업   2022   5722   87   5809   11531

를 원했습니다.

그러면 혹시 댓글쿼리 상태에서 4초이상 되는데 group by 때문에 느려지더라구요 혹시 개선할 쿼리쪽이 있을까요?

by 마농 [2022.05.09 09:58:22]

1. 컬럼 가공 비교
- 컬럼을 가공하여 조건과 비교하지 말고 : DATE_FORMAT(apv_perm_ymd, '%Y') >= 2019
- 컬럼은 그대로 두고 조건을 가공하여 비교 : apv_perm_ymd >= '2019-01-01'
2. 컬럼 카운트
- 컬럼을 카운트 하지 말고 상수나 * 로 카운트
- 변경전 -
, COUNT(CASE WHEN dtl_state_gbn = '01' THEN dtl_state_nm END) AS state_dt1
, COUNT(CASE WHEN dtl_state_gbn = '02' THEN dtl_state_nm END) AS state_dt2
, COUNT(DATE_FORMAT(apv_perm_ymd, '%Y')) AS state_dt3
- 변경후 -
, COUNT(CASE WHEN dtl_state_gbn = '01' THEN 1 END) AS state_dt1
, COUNT(CASE WHEN dtl_state_gbn = '02' THEN 1 END) AS state_dt2
, COUNT(*) AS state_dt3
4. 정체 불명의 항목? 제거
- a.state_dt1 + state_dt3 AS stateSumDt1
5. 표준 구문 권장
- Select 절의 별칭을 이용한 "GROUP BY 별칭" 은 표준이 아닙니다. (MySQL 에서만 허용되는 구문)

by 마농 [2022.05.11 10:12:25]

SELECT stateUpdtNm
     , apvPermYmd
     , COUNT(CASE dtl_state_gbn WHEN '01' THEN 1 END) stateDt1
     , COUNT(CASE dtl_state_gbn WHEN '02' THEN 1 END) stateDt2
     , COUNT(*)                                       stateDt3
  FROM (SELECT CASE WHEN update_nm IN ('식육(숯불구이)', '냉면집', '탕류(보신용)', '복어취급', '한식', '횟집') THEN '한식음식점업'
                    WHEN update_nm IN ('중국식'                                                              ) THEN '중식음식점업'
                    WHEN update_nm IN ('일식'                                                                ) THEN '일식음식점업'
                    WHEN update_nm IN ('경양식', '외국음식전문점(인도,태국등)', '패밀리레스트랑'             ) THEN '서양식 음식점업'
                    WHEN update_nm IN ('이동조리', '출장조리'                                                ) THEN '출장 및 이동 음식점업'
                    WHEN update_nm IN ('패스트푸드'                                                          ) THEN '패스트푸드'
                    WHEN update_nm IN ('통닭(치킨)', '호프/통닭'                                             ) THEN '치킨'
                    WHEN update_nm IN ('김밥(도시락)', '분식'                                                ) THEN '분식'
                    WHEN update_nm IN ('커피숍', '까페', '전통찻집', '다방'                                  ) THEN '커피 및 음료점업'
                    WHEN update_nm IN ('간이주점', '감성주점', '라이브카페', '정종/대포집/소주방'            ) THEN '주점업'
                    WHEN update_nm IN ('뷔페식', '기타 휴게음식점', '기타', '미분류'                         ) THEN '기타'
                    ELSE '없음' END stateUpdtNm
             , DATE_FORMAT(apv_perm_ymd, '%Y') apvPermYmd
             , dtl_state_gbn
          FROM tb_business_status_temp
         WHERE apv_perm_ymd >= '2019-01-01'
           AND apv_perm_ymd <   CONCAT(DATE_FORMAT(NOW(), '%Y') + 1, '-01-01')
        ) a
 GROUP BY stateUpdtNm, apvPermYmd
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변