데이터베이스 질문과 답변
코호트 표 출력 ㅠ 고수님들의 도움을 간절하게 요청드려요 ㅠ
0
4
2,029
by mangoos [SQL Query] Mysql query [2017.06.01 23:53:44]
아래와 같은 테이블을 기준으로 코호트 표를 만들려고 하는데요 ㅠ
| user | date |
| a | 2015-11 |
| b | 2015-11 |
| b | 2015-11 |
| a | 2015-11 |
| c | 2015-12 |
| a | 2016-01 |
| c | 2015-12 |
| d | 2016-01 |
| e | 2016-01 |
| f | 2016-01 |
| g | 2016-01 |
| 월별주문건 | Total | 2015-11 | 2015-12 | 2016-01 |
| 2015-11 첫구매 고객 | 5 | 4 | 0 | 1 |
| 2015-12 첫구매 고객 | 3 | 0 | 2 | 1 |
| 2016-01 첫구매 고객 | 3 | 0 | 0 | 3 |
위 표로 출력시키고 싶은데요 ㅠ
SELECT SUBSTR(date,1,7)'Month', COUNT(*)total, COUNT(CASE SUBSTR(date,1,7) WHEN '2015-11' THEN 'a' END) '2015-11', COUNT(CASE SUBSTR(date,1,7) WHEN '2015-12' THEN 'a' END) '2015-12', COUNT(CASE SUBSTR(date,1,7) WHEN '2016-01' THEN 'a' END) '2016-01' FROM order_list GROUP BY SUBSTR(date,1,7);
| 주문건수 | Total | 2015-11 | 2015-12 | 2016-01 |
| 2015-11 | 5 | 5 | 0 | 0 |
| 2015-12 | 3 | 0 | 3 | 0 |
| 2016-01 | 3 | 0 | 0 | 3 |
위와 같이 나옵니다. ㅠ 왜 이렇게 나오는 지 잘 이해도 안되는데 ㅠ
고수님들 도움을 요청드립니다. ~~ ㅠ
아래 고객수 출력 쿼리도 정말 정말 부탁드립니다. ~~~ 제발~~~
| 고객수 | Total | 2015-11 | 2015-12 | 2016-01 |
| 2015-11 첫구매 고객 | 3 | 2 | 0 | 1 |
| 2015-12 첫구매 고객 | 2 | 1 | 1 | |
| 2016-01 첫구매 고객 | 3 | 3 |
SELECT SUBSTR(date,1,7)`Month`, user, COUNT(*),
COUNT(CASE SUBSTR(date,1,7) WHEN '2015-11' THEN '2015-11' END) '2015-11',
COUNT(CASE SUBSTR(date,1,7) WHEN '2015-12' THEN '2015-12' END) '2015-12',
COUNT(CASE SUBSTR(date,1,7) WHEN '2016-01' THEN '2016-01' END) '2016-01'
FROM cohort GROUP BY SUBSTR(date,1,7),user;
요렇게 하면 아래까지는 나오긴 하는 데요 ㅠ
참고로 이제 막 sql 배우고 있는 초짜입니다.
| Month | user | COUNT(*) | 2015-11 | 2015-12 | 2016-01 |
| 2015-11 | a | 2 | 2 | 0 | 0 |
| 2015-11 | b | 2 | 2 | 0 | 0 |
| 2015-12 | c | 2 | 0 | 2 | 0 |
| 2016-01 | a | 1 | 0 | 0 | 1 |
| 2016-01 | d | 1 | 0 | 0 | 1 |
| 2016-01 | e | 1 | 0 | 0 | 1 |
| 2016-01 | f | 1 | 0 | 0 | 1 |
| 2016-01 | g | 1 | 0 | 0 | 1 |
by 마농
[2017.06.02 08:20:33]
WITH t AS
(
SELECT 'a' id, '2015-11-01' dt FROM dual
UNION ALL SELECT 'b', '2015-11-02' FROM dual
UNION ALL SELECT 'b', '2015-11-03' FROM dual
UNION ALL SELECT 'a', '2015-11-04' FROM dual
UNION ALL SELECT 'c', '2015-12-05' FROM dual
UNION ALL SELECT 'a', '2016-01-06' FROM dual
UNION ALL SELECT 'c', '2015-12-07' FROM dual
UNION ALL SELECT 'd', '2016-01-08' FROM dual
UNION ALL SELECT 'e', '2016-01-09' FROM dual
UNION ALL SELECT 'f', '2016-01-10' FROM dual
UNION ALL SELECT 'g', '2016-01-11' FROM dual
)
SELECT ym_1st
, SUM(cnt) tot
, NVL(SUM(DECODE(ym, '2015-11', cnt)), 0) "2015-11"
, NVL(SUM(DECODE(ym, '2015-12', cnt)), 0) "2015-12"
, NVL(SUM(DECODE(ym, '2016-01', cnt)), 0) "2016-01"
FROM (SELECT id
, SUBSTR(dt, 1, 7) ym
, MIN(SUBSTR(dt, 1, 7)) OVER(PARTITION BY id) ym_1st
, COUNT(*) cnt
FROM t
GROUP BY id, SUBSTR(dt, 1, 7)
)
GROUP BY ym_1st
ORDER BY ym_1st
;
by mangoos
[2017.06.02 09:04:14]
아 감사합니다. ~~~
마농님 위 표를 돌릴 user 수가 10만이 넘어가는데 ㅠ
다른 방법이 없을까요 ? 엉엉
방법을 찾아주시면 공부 열씨미 하고 싶습니다.
가르침을 주세요 제발~~~
Mysql 쿼리 기준으로 알려주시면 정말 정말 감사하겠습니다. ~~~~
by 마농
[2017.06.02 09:20:39]
10만건을 union all 로 붙이라는 의미가 아닙니다.
with 문은 제가 테이블을 대신하여 테스트 하기 위해 사용한 것일 뿐입니다.
Select 문을 어떤 형태로 작성했는지를 보고 이해하시면 됩니다.
오라클엔 분석함수가 있어 한번에 조회가 가능한데요.
mySQL 엔 분석함수가 없어서 두개로 나누어 조회하고 조인하여 합쳐야 합니다.
SELECT a.ym_1st
, SUM(b.cnt) tot
, IFNULL(SUM(CASE b.ym WHEN '2015-11' THEN b.cnt END), 0) "2015-11"
, IFNULL(SUM(CASE b.ym WHEN '2015-12' THEN b.cnt END), 0) "2015-12"
, IFNULL(SUM(CASE b.ym WHEN '2016-01' THEN b.cnt END), 0) "2016-01"
FROM (SELECT id
, LEFT(MIN(dt), 7) ym_1st
FROM t
GROUP BY id
) a
INNER JOIN
(SELECT id
, LEFT(dt, 7) ym
, COUNT(*) cnt
FROM t
GROUP BY id, LEFT(dt, 7)
) b
ON a.id = b.id
GROUP BY a.ym_1st
;
by mangoos
[2017.06.02 14:04:05]
마농님 정말 감사합니다. 우아~~~~~~
열씨미 공부하겠습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.