Redshift: запуск запроса с использованием GETDATE() в указанный список раз

Итак, у меня есть запрос, который использует GETDATE() в предложениях WHERE и HAVING:

SELECT GETDATE(), COUNT(*) FROM (
  SELECT 1 FROM events
  WHERE (event_time > (GETDATE() - interval '25 hours'))
  GROUP BY id
  HAVING MAX(event_time) BETWEEN (GETDATE() - interval '25 hours') AND (GETDATE() - interval '24 hours')
)

Я в основном пытаюсь найти количество уникальных id, у которых есть свои последние event_time между 25 и 24 часами назад по отношению к текущему времени.

Проблема: у меня есть другая таблица query_dts, которая содержит один столбец, содержащий метки времени. Вместо того, чтобы запускать приведенный выше запрос в текущее время, используя GETDATE(), мне нужно выполнить отметку времени каждой записи таблицы query_dts. Есть идеи?

Примечание. На самом деле я нигде не храню query_dts. Я создал его следующим образом:

WITH query_dts AS (
  SELECT (
    DATEADD(hour,-(row_number() over (order by true)), getdate())
  ) as n
  FROM events LIMIT 48
),

который я получил из здесь


sql amazon-redshift getdate
person shashwat    schedule 14.03.2016    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Как насчет того, чтобы вообще избежать генератора и вместо этого просто разделить интервалы:

SELECT
  dateadd(hour, -distance, getdate()),
  count(0) AS event_count
FROM (
       SELECT
         id,
         datediff(hour, max(event_time), getdate()) AS distance
       FROM events
       WHERE event_time > getdate() - INTERVAL '2 days'
       GROUP BY id) AS events_with_distance
GROUP BY distance;
person denismo    schedule 17.03.2016

arrow_upward
0
arrow_downward

Вы можете использовать JOIN для объединения двух запросов. Затем вам просто нужно заменить значения для вашего выражения даты. Я думаю, что это логика:

WITH query_dts AS (
      SELECT DATEADD(hour, -(row_number() over (order by true)), getdate()) as n
      FROM events
      LIMIT 48
     )
SELECT d.n, COUNT(*)
FROM (SELECT d.n
      FROM events e JOIN
           query_dts d
      WHERE e.event_time > d.n
      GROUP BY id
      HAVING MAX(event_time) BETWEEN n - interval '25 hours' AND n
     ) i;
person Gordon Linoff    schedule 14.03.2016
comment
Это почти первое, что я попробовал. Проблемы в том, что (1) я не могу выбрать d.n, если я группирую по id. (2) Если я также группирую по n, это дает неверные значения. - person shashwat; 14.03.2016

arrow_upward
0
arrow_downward

Вот что я в итоге сделал:

WITH max_time_table AS
(
  SELECT id, max(event_time) AS max_time
  FROM events
  WHERE (event_time > GETDATE() - interval '74 hours')
  GROUP BY id
),
query_dts AS
(
  SELECT (DATEADD(hour,-(row_number() over (ORDER BY TRUE) - 1), getdate()) ) AS n
  FROM events LIMIT 48
)
SELECT query_dts.n, COUNT(*)
FROM max_time_table JOIN query_dts
  ON max_time_table.max_time BETWEEN (query_dts.n - interval '25 hours') AND (query_dts.n - interval '24 hours')
GROUP BY query_dts.n
ORDER BY query_dts.n DESC

Здесь я выбрал 74 часа, потому что хотел 48 часов назад + 25 часов назад = 73 часа назад.

Проблема в том, что это не универсальный способ сделать это. Это очень конкретное решение для этой конкретной проблемы. Может ли кто-нибудь придумать более общий способ выполнения запроса, зависящего от GETDATE(), с использованием столбца дат в другой таблице?

person shashwat    schedule 14.03.2016