12 개월, 24 개월, 36 개월 등의 고객 생존율 (즉, 활성 고객 수 / 총 고객 수)을 계산하려고합니다. 문제는 "총 고객"이 "최소한 테스트 기간만큼 오래된 고객"이어야한다는 것입니다 (예 : 최소 12 개월 전에 시작한 고객이 12 개월 후에도 여전히 활동중인 고객 수).
세 가지 관련 열이 있습니다.
그리고 다음 코드를 시도했습니다.
select PROD_CDE,
(SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 11) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 11) AS survival_12m,
(SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 23) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 23) AS survival_24m,
(SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 35) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 35) AS survival_36m,
(SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 47) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 47) AS survival_48m,
(SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 59) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 59) AS survival_60m,
(SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 71) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 71) AS survival_72m,
(SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 83) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 83) AS survival_84m,
(SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 95) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 95) AS survival_96m
from TABLENAME
where START_DATE >= '2011-01-01'
group by PROD_CDE;
이 오류 메시지가 나타납니다.
Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'local.TABLENAME.START_DATE' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
몇 가지 다른 관련 질문을 확인했지만 아직 해결책을 찾지 못했습니다.
조건부 집계를 직접 사용하십시오.
SELECT PROD_CDE,
SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 11) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 11) AS survival_12m,
SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 23) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 23) AS survival_24m,
SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 35) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 35) AS survival_36m,
SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 47) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 47) AS survival_48m,
SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 59) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 59) AS survival_60m,
SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 71) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 71) AS survival_72m,
SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 83) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 83) AS survival_84m,
SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 95) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 95) AS survival_96m
FROM TABLENAME
WHERE START_DATE >= '2011-01-01'
GROUP BY PROD_CDE
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다