Блог

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

Фактический результат: Мой код в настоящее время продолжает заполнять статус даже после закрытия проблемы.

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

 WITH cte (cal_month) as  (  SELECT date_trunc('month', timestamp '2021-07-01T00:00:00')  UNION ALL   SELECT date_trunc('month', timestamp '2021-08-01T00:00:00')  UNION ALL   SELECT date_trunc('month', timestamp '2021-09-01T00:00:00')  UNION ALL   SELECT date_trunc('month', timestamp '2021-10-01T00:00:00')  UNION ALL   SELECT date_trunc('month', timestamp '2021-11-01T00:00:00')  UNION ALL   SELECT date_trunc('month', timestamp '2021-12-01T00:00:00') ), cte_2 (cal_month, issue_id, version, action_day, action_month, issue_status) as  ( SELECT m.cal_month  , issue_id  , version   , date_trunc('day', action_timestamp) as action_day   , date_trunc('month', action_timestamp) as action_month  , issue_status   , SUM(CASE WHEN issue_status IS NOT NULL THEN 1 END) OVER (ORDER BY cal_month, issue_status, version) as grp FROM cte m LEFT JOIN issue_history h  ON m.cal_month = date_trunc('month', action_timestamp)  AND issue_id IN (23865) ORDER BY cal_month, issue_id, version ) SELECT *  , first_value(issue_status) over (partition by grp) as derived_issue_status FROM cte_2 ORDER BY cal_month, issue_id, version  

Пример вывода из кода

Попробуйте это :

 WITH cte_2 AS  ( SELECT m.cal_month  , issue_id  , version   , date_trunc('day', action_timestamp) as action_day   , date_trunc('month', action_timestamp) as action_month  , issue_status   , count(*) FILTER (WHERE issue_status IS NOT NULL) OVER (ORDER BY cal_month, issue_status, version) as grp  , (jsonb_agg(to_jsonb(issue_status)) FILTER (WHERE issue_status IS NOT NULL) OVER (ORDER BY cal_month, issue_status, version))-gt;'-1' as derived_issue_status  FROM generate_series(date '2021-07-01', date '2021-12-01', interval ' 1 month') m  LEFT JOIN issue_history h  ON m.cal_month = date_trunc('month', action_timestamp)  AND issue_id IN (23865)  ORDER BY cal_month, issue_id, version ) SELECT m.cal_month  , issue_id  , version  , action_day   , action_month  , issue_status   , grp  , CASE  WHEN derived_issue_status = 'Closed' AND issue_status IS NULL  THEN NULL  ELSE derived_issue_status  END AS derived_issue_status FROM cte_2 ORDER BY cal_month, issue_id, version  

Предложения об изменениях :

(1) Как предложено @a_horse_with_no_name, cte подавляется и заменяется generate_series(date '2021-07-01', date '2021-12-01', interval ' 1 month') в cte2

(2) В cte2 :

 SUM(CASE WHEN issue_status IS NOT NULL THEN 1 END) OVER (ORDER BY cal_month, issue_status, version) as grp  

заменено на

 count(*) FILTER (WHERE issue_status IS NOT NULL) OVER (ORDER BY cal_month, issue_status, version) as grp  

даже если grp in больше не используется в окончательном запросе, потому derived_issue_status что вычисляется в cte2 с помощью оператора new :

 (jsonb_agg(to_jsonb(issue_status)) FILTER (WHERE issue_status IS NOT NULL) OVER (ORDER BY cal_month, issue_status, version))-gt;gt;'-1' as derived_issue_status  

(3) A CASE добавляется в окончательный запрос для отображения derived_issue_status = NULL, когда текущее значение issue_status равно NULL, а последнее ненулевое значение issue_status = «Закрыто».