Блог

У меня есть набор данных, который включает идентификаторы пользователей, соответствующие им статусы (например, активированный, истекший и т.д.) и дату этого статуса («создан»).

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

Например, пользователь в 2019-01-01 18:30:00 имеет статус «АКТИВИРОВАН», затем в 2020-01-01 19:53:01 имеет статус «ИСТЕК», затем в 2020-02-01 снова имеет статус «АКТИВИРОВАН» до текущей даты.

Таким образом, я бы хотел, чтобы идентификатор пользователя и статус отображались между первой и второй датой как «АКТИВИРОВАННЫЙ», между второй и третьей датой как «ИСТЕКШИЙ», а с третьей даты до текущей даты как «АКТИВИРОВАННЫЙ».

Я довольно запутался в том, как это сделать, но из некоторых исследований в Интернете я получил / изменил этот фрагмент кода, который написан на Vertica. Однако он написан не на правильном диалекте и не включает статус так, как мне хотелось бы.

 SELECT id,
       status_dt,
       last_order_dt,
       CASE
           WHEN status_dt::date - last_order_dt::date < 30 then CASE
           WHEN nord = 1 then 'New'
           ELSE 'Active'
           END
           WHEN status_dt::date - last_order_dt::date < 90 then 'Active'
           WHEN status_dt::date - last_order_dt::date < 180 then 'Passive'
           ELSE 'Inactive'
       END AS status
FROM
  (SELECT id,
          last_order_dt,
          status_dt,
          conditional_true_event (first_order_dt is null
          OR last_order_dt > lag(last_order_dt)) OVER(partition BY id
          ORDER BY status_dt) AS nord
   FROM
     ( SELECT id,
              ts_first_value(created) AS first_order_dt,
              ts_last_value(created) AS last_order_dt,
              dt::date AS status_dt
      FROM
        (SELECT id,
                created
         FROM enabled
         UNION all SELECT distinct(id) AS id,
                          current_date   1 AS created
         FROM enabled ) z timeseries dt AS '1 day' OVER(partition BY id
         ORDER BY created) ) x ) y
WHERE status_dt <= current_date
ORDER BY 1,
         2 ;

with t AS
  ( SELECT id,
           [created],
           current_date,
           status
   FROM postgres_public.loc_application_status
   UNION all SELECT id,
                    dateadd(mm, 1, [created]),
                    current_date,
                    status
   FROM t
   WHERE dateadd(mm, 1, [created]) < coalesce(current_date, getdate()) )
SELECT id,
       [created],
       (CASE
            WHEN eomonth([created]) <= coalesce(current_date, cast(getdate() AS date)) then eomonth([created])
            ELSE coalesce(current_date, eomonth([created]))
        END) AS current_date,
       status
FROM t
ORDER BY id;