데이터베이스 질문과 답변
안녕하세요. 쿼리하는 마케터 질문드립니다 ㅠ
select TC.nTargetType, TC.nTargetCtgName, count(T.sTargetCtgCode) as cnt
from nitpick.Contents C, nitpick.Target T, nitpick.TargetCategory TC
where C.nTargetId = T.nTargetId
and T.sTargetCtgCode = TC.sTargetCtgCode
and date(C.dtwrite) >= '2020-09-01'
group by (T.sTargetCtgCode)
order by cnt desc;
위의 쿼리를 가지고 아래와 같은 값을 구했는데요.
| nTargetType | nTargetCtgName | cnt |
| 2 | 상품기타 | 11749 |
| 1 | 기타 | 7282 |
| 2 | 식품 | 6746 |
| 1 | 음식점 | 4852 |
| 2 | 디지털/가전 | 4224 |
| 2 | 여가/생활편의 | 4172 |
| 3 | 앱서비스 | 3751 |
| 2 | 생활/건강 | 1679 |
| 1 | 카페 | 1473 |
| 2 | 화장품/미용 | 1148 |
| 1 | 학교 | 1131 |
위의 값에서 nTargetType 별로 가장 많은 cnt를 가진 nTargetCtgName 만 추출하려면 어떤 쿼리를 작성해야 할까요?
도움 부탁드립니다 ㅠㅠ 서브쿼리 아무리해도 안나오네요 ㅠ
by 춘
[2020.12.13 18:25:39]
WITH t AS
(
SELECT 2 nTargetType, '상품기타' nTargetCtgName, 11749 cnt FROM dual
UNION ALL
SELECT 1, '기타', 7282 FROM dual
UNION ALL
SELECT 2, '식품', 6746 FROM dual
UNION ALL
SELECT 1, '음식점', 4852 FROM dual
UNION ALL
SELECT 2, '디지털/가전', 4224 FROM dual
UNION ALL
SELECT 2, '여가/생활편의', 4172 FROM dual
UNION ALL
SELECT 3, '앱서비스', 3751 FROM dual
UNION ALL
SELECT 2, '생활/건강', 1679 FROM dual
UNION ALL
SELECT 1, '카페', 1473 FROM dual
UNION ALL
SELECT 2, '화장품/미용', 1148 FROM dual
UNION ALL
SELECT 1, '학교', 1131 FROM dual
)
SELECT
nTargetType
, nTargetCtgName
, cnt
FROM
(
SELECT
nTargetType
, nTargetCtgName
, cnt
, ROW_NUMBER() OVER (PARTITION BY nTargetType ORDER BY cnt DESC) AS RNUM
FROM t
ORDER BY nTargetType
)
WHERE RNUM = 1
;
by 마농
[2020.12.14 09:59:27]
SELECT *
FROM (SELECT TC.nTargetType
, TC.nTargetCtgName
, COUNT(*) cnt
, RANK() OVER(PARTITION BY TC.nTargetType ORDER BY COUNT(*) DESC) rk
FROM nitpick.Contents C
, nitpick.Target T
, nitpick.TargetCategory TC
WHERE C.nTargetId = T.nTargetId
AND T.sTargetCtgCode = TC.sTargetCtgCode
AND date(C.dtwrite) >= '2020-09-01'
GROUP BY T.sTargetCtgCode, TC.nTargetType, TC.nTargetCtgName
) a
WHERE rk = 1
;
) 버튼을 클릭하여 작성 하시면 됩니다.