#sql #postgresql
#sql #postgresql
Вопрос:
Исходная таблица User
ID Created_Date SubmittedAt ApprovedAt
Исходная таблица Campaign
User_ID Clicked_At
Теперь я хочу сгенерировать отчет со столбцами типа
Month Year #Applicants #Submitted #Approved
Я писал запросы с использованием postgresql:
SELECT To_char(C.clicked_at, 'MON') AS MON,
Extract(year FROM C.clicked_at) AS YYYY,
CASE
WHEN created_date IS NOT NULL THEN Count(user_id)
END AS APPLICANTS,
CASE
WHEN submittedat IS NOT NULL THEN Count(user_id)
END AS SUBMITTED,
CASE
WHEN approvedat IS NOT NULL THEN Count(user_id)
END AS APPROVED
FROM campaign C,
users U
WHERE C.user_id = U.id
GROUP BY 1,2
Я получил сообщение об ошибке « u.created_date
должно отображаться в GROUP BY
предложении или использоваться в агрегатной функции. Однако я просто хочу, чтобы мои результаты были сгруппированы по year
и month
.
Комментарии:
1. Какая у вас версия Postgres?
2. @KamilG.postgres 3 1.22.1
Ответ №1:
Во-первых, используйте правильный, явный JOIN
синтаксис. Во-вторых, если вы хотите подсчитать количество ненулевых значений, вы можете упростить код:
SELECT TO_CHAR(C.CLICKED_AT, 'MON') AS MON,
EXTRACT(YEAR FROM C.CLICKED_AT) AS YYYY,
COUNT(CREATED_DATE) AS APPLICANTS,
COUNT(SUBMITTEDATE) AS SUBMITTED,
COUNT(APPROVEDAT) AS APPROVED
FROM CAMPAIGN C JOIN
USERS U
ON C.USER_ID = U.ID
GROUP BY 1, 2;
При таком упрощении у вас больше нет никакой (явной) условной логики вообще, так group by
что предложение в порядке.
Я бы посоветовал вам объединить месяц и год и отсортировать результаты:
SELECT TO_CHAR(C.CLICKED_AT, 'YYYY-MM') AS yyyymm,
COUNT(CREATED_DATE) AS APPLICANTS,
COUNT(SUBMITTEDATE) AS SUBMITTED,
COUNT(APPROVEDAT) AS APPROVED
FROM CAMPAIGN C JOIN
USERS U
ON C.USER_ID = U.ID
GROUP BY 1
ORDER BY 1;
Ответ №2:
Вам нужно поместить ваши операторы CASE в агрегатную функцию:
SELECT TO_CHAR(C.CLICKED_AT,'MON') AS MON,
EXTRACT(YEAR FROM C.CLICKED_AT) AS YYYY,
COUNT(CASE WHEN CREATED_DATE IS NOT NULL THEN USER_ID END) AS APPLICANTS,
COUNT(CASE WHEN SUBMITTEDAT IS NOT NULL THEN USER_ID END) AS SUBMITTED,
COUNT(CASE WHEN APPROVEDAT IS NOT NULL THEN USER_ID END) AS APPROVED
FROM
CAMPAIGN C
JOIN USERS U ON C.USER_ID = U.ID
GROUP BY 1,2
Примечания:
- если вы используете Postgres 9.4 , вы можете использовать
FILTER
предложение. - используйте явное
JOIN
предложение вместо предложения WHERE для соединения таблиц - возможно, вам лучше расширить свой
TO_CHAR()
, чтобы принять год и месяц
Пример:
SELECT TO_CHAR(C.CLICKED_AT,'YYYY-MM') AS date_year_month,
COUNT(CASE WHEN CREATED_DATE IS NOT NULL THEN USER_ID END) AS APPLICANTS,
COUNT(CASE WHEN SUBMITTEDAT IS NOT NULL THEN USER_ID END) AS SUBMITTED,
COUNT(CASE WHEN APPROVEDAT IS NOT NULL THEN USER_ID END) AS APPROVED
FROM
CAMPAIGN C
JOIN USERS U ON C.USER_ID = U.ID
GROUP BY 1