Блог

У меня есть таблица, в которой есть дата и время, соответствующие разным статусам: нажата, заявлена и куплена для каждого пользователя.

Я сворачиваю записи каждого пользователя и помещаю каждую дату и время, соответствующие каждому статусу, в их собственный столбец: clicked_date, claimed_date и bought_date. У меня также есть дата смерти.

Теперь мне нужна еще одна колонка, которая вычисляет количество дней до крайнего срока. Новый столбец (number_days) всегда должен вычитаться из deadline_date минус clicked_date. Однако, если clicked_date имеет значение «2999-12-31», то он должен вычесть deadline_date минус claimed_date. Если clicked_date имеет значение «2999-12-31», а claimed_date имеет значение = «299-12-31», то он должен вычесть deadline_date минус bought_date. И если clicked_date имеет значение «2999-12-31», а claimed_date имеет значение «2999-12-31», а bought_date имеет значение «2999-12-31» ИЛИ deadline_date = «/N», то оно должно быть 0.

 In the examples above, The first record should be (deadline_date - bought_date) The second record should be (deadline_date - clicked_date)  The third record should be 0. The fourth record should be (deadline_date - claimed_date) The fifth record should be (deadline_date - claimed_date)  The sixth record should be (deadline_date - clicked_date)   

У меня возникли проблемы с формулировкой этого запроса. Кто-нибудь знает, как я могу рассчитать количество дней в зависимости от того, что находится в каждом из трех разных столбцов дат для кликов, заявок и покупок?

Я пробовал что-то подобное, но это не работает:

 select  user, (array_agg(STATUS) within group(order by UPDATED_AT_DATETIME desc)[0])::varchar as last_status,  coalesce(max(case when STATUS = 'clicked' THEN UPDATED_AT_DATETIME END),'2999-12-31'::datetime) as clicked_date,  coalesce(max(case when STATUS = 'claimed' THEN UPDATED_AT_DATETIME END),'2999-12-31'::datetime) as claimed_date,  coalesce(max(case when STATUS = 'bought' THEN ACCOUNT_REWARD_UPDATED_AT_DATETIME END),'2999-12-31'::datetime) as bought_date,  **case when CLICKED_DATE lt;gt; '2999-12-31 00:00:00.000' then DATEDIFF('days', TRY_TO_TIMESTAMP(clicked_date) ,TRY_TO_TIMESTAMP(DEADLINE_DATETIME))  when claimed_date lt;gt; '2999-12-31 00:00:00.000' then DATEDIFF('days', TRY_TO_TIMESTAMP(claimed_date) ,TRY_TO_TIMESTAMP(DEADLINE_DATETIME))  when bought_date lt;gt; '2999-12-31 00:00:00.000' then DATEDIFF('days', TRY_TO_TIMESTAMP(bought_date) ,TRY_TO_TIMESTAMP(DEADLINE_DATETIME))  END as number_days** from TBL_A a group by 1,2  

без его запуска это должно сработать:

 SELECT   user,  FIRST_VALUE(status) OVER (PARITION BY user ORDER BY updated_at_datetime DESC) AS last_status,  MAX(IFF(status = 'clicked', UPDATED_AT_DATETIME, null)) AS clicked_date,  MAX(IFF(status = 'claimed', UPDATED_AT_DATETIME, null)) AS claimed_date,  MAX(IFF(status = 'bought', UPDATED_AT_DATETIME, null)) AS bought_date,  COALESCE(bought_date, claimed_date, clicked_date, '2999-12-31'::datetime) as date_of_interest,  DATEDIFF('days', date_of_interest, deadline_datetime) AS number_days FROM tbl_a AS a GROUP BY 1,2;  

возможно, вам придется привести «даты», как в вашем примере, если они действительно уже являются строками.

«date_of_interest» может быть встроен. Есть небольшой шанс, что МАКС и СЛИЯНИЕ не будут играть хорошо, в случае, если просто оговорите это как:

 SELECT a.*  COALESCE(bought_date, claimed_date, clicked_date, '2999-12-31'::datetime) as date_of_interest,  DATEDIFF('days', date_of_interest, deadline_datetime) AS number_days FROM (  SELECT   user,  FIRST_VALUE(status) OVER (PARITION BY user ORDER BY updated_at_datetime DESC) AS last_status,  MAX(IFF(status = 'clicked', UPDATED_AT_DATETIME, null)) AS clicked_date,  MAX(IFF(status = 'claimed', UPDATED_AT_DATETIME, null)) AS claimed_date,  MAX(IFF(status = 'bought', UPDATED_AT_DATETIME, null)) AS bought_date  FROM tbl_a AS a  GROUP BY 1,2 ) AS a;