Блог

Исходная таблица 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 .

Во-первых, используйте правильный, явный 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;
  

Вам нужно поместить ваши операторы 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