Блог

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

В таблице истории есть validity_date_start и validity_date_end, и последнее значение равно НУЛЮ, если оно обновлено.

Это запрос, который я сделал до сих пор (используя временные таблицы для удобства воспроизведения):

 SELECT
  time.year,
  time.month,
  t1.name,
  FIRST_VALUE(t2.value1) OVER(ORDER BY t2.validity_date_start DESC) AS value, -- take the last valid t2 part for the month
  (CASE WHEN t1.id = 1 AND time.date >= timestamp '2020-07-01 00:00:00' THEN 27
     ELSE CASE WHEN t1.id = 1 AND time.date >= timestamp '2020-03-01 00:00:00' THEN 1
       ELSE CASE WHEN t1.id = 2 AND time.date >= timestamp '2020-05-01 00:00:00' THEN 42 END
     END
   END) AS expected_value
FROM
  (SELECT year(ts.date) year, month(ts.date) month, ts.date FROM (
    (VALUES (SEQUENCE(date '2020-01-01', current_date, INTERVAL '1' MONTH))) AS ts(ts_array)
    CROSS JOIN UNNEST(ts_array) AS ts(date)
  ) GROUP BY ts.date) time

  CROSS JOIN (VALUES (1, 'Hal'), (2, 'John'), (3, 'Jack')) AS t1 (id, name)
  
  LEFT JOIN (VALUES
    (1, 1, timestamp '2020-01-03 10:22:33', timestamp '2020-07-03 23:59:59'),
    (1, 27, timestamp '2020-07-04 00:00:00', NULL),
    (2, 42, timestamp '2020-05-29 10:22:31', NULL)
  ) AS t2 (id, value1, validity_date_start, validity_date_end)
    ON t1.id = t2.id
    AND t2.validity_date_start <= (CAST(time.date as timestamp)   interval '1' month - interval '1' second)
    AND (t2.validity_date_end IS NULL OR t2.validity_date_end >= (CAST(time.date as timestamp)   interval '1' month - interval '1' second)) -- last_day_of_month (Athena doesn't have the fn)
  
  GROUP BY time.date, time.year, time.month, t1.id, t1.name, t2.value1, t2.validity_date_start
  ORDER BY time.year, time.month, t1.id
  

value и expected_value должны совпадать, но они этого не делают (значение всегда пустое). Я, очевидно, неправильно понял, как FIRST_VALUE(...) OVER(...) это работает.

Не могли бы вы мне помочь?

Большое вам спасибо!

В конце концов я выяснил, что я здесь делал неправильно.

В документах написано:

Спецификация раздела, которая разделяет входные строки на разные разделы. Это аналогично тому, как предложение GROUP BY разделяет строки на разные группы для агрегатных функций

Это привело меня к мысли, что если у меня уже есть GROUP BY оператор, это бесполезно. Это не так: обычно, если вы хотите получить данные для данной группы, вы также должны указать их в PARTITION BY инструкции (или, лучше, размеры, которые вы проецируете в SELECT детали).

 SELECT
  time.year,
  time.month,
  t1.name,
  FIRST_VALUE(t2.value1) OVER(PARTITION BY (time.year, time.month, t1.name) ORDER BY t2.validity_date_start DESC) AS value, -- take the last valid t2 part for the month
  (CASE WHEN time.date >= timestamp '2020-07-01 00:00:00' AND t1.id = 1 THEN 27
     ELSE CASE WHEN time.date >= timestamp '2020-05-01 00:00:00' AND t1.id = 2 THEN 42
       ELSE CASE WHEN time.date >= timestamp '2020-03-01 00:00:00' AND t1.id = 1 THEN 1 END
     END
   END) AS expected_value
FROM
  (SELECT year(ts.date) year, month(ts.date) month, ts.date FROM (
    (VALUES (SEQUENCE(date '2020-01-01', current_date, INTERVAL '1' MONTH))) AS ts(ts_array)
    CROSS JOIN UNNEST(ts_array) AS ts(date)
  ) GROUP BY ts.date) time

  CROSS JOIN (VALUES (1, 'Hal'), (2, 'John'), (3, 'Jack')) AS t1 (id, name)
  
  LEFT JOIN (VALUES
    (1, 1, timestamp '2020-03-01 10:22:33', timestamp '2020-07-03 23:59:59'),
    (1, 27, timestamp '2020-07-04 00:00:00', NULL),
    (2, 42, timestamp '2020-05-29 10:22:31', NULL)
  ) AS t2 (id, value1, validity_date_start, validity_date_end)
    ON t1.id = t2.id
    AND t2.validity_date_start <= (CAST(time.date as timestamp)   interval '1' month - interval '1' second)
    AND (t2.validity_date_end IS NULL OR t2.validity_date_end >= (CAST(time.date as timestamp)   interval '1' month - interval '1' second)) -- last_day_of_month (Athena doesn't have the fn)
  
  GROUP BY time.date, time.year, time.month, t1.id, t1.name, t2.value1, t2.validity_date_start
  ORDER BY time.year, time.month, t1.id