Для строк с одинаковыми значениями A, B, C, как выбрать только «самую раннюю» строку (указанную столбцом D) в заданном временном диапазоне?

Question

В настоящее время у меня есть запрос (упрощенный ниже):

      SELECT name, node, points, <bunch of other fields>,
  DATE(snapshot_date) AS snap_date,
  HOUR(snapshot_date) AS snap_hour,
  CASE
    WHEN MINUTE(snapshot_date) IN (5, 6) THEN 0
    WHEN MINUTE(snapshot_date) IN (35, 36) THEN 30
  END AS snap_mins,
from some_table
where 
  <bunch of conditions here> 

По сути, источник данных, из которого мы получаем данные, записывает свои временные метки, когда их задание завершено, а не когда данные действительно поступили (мы ничего не можем с этим поделать с нашей стороны). Их задания обычно заканчиваются через 5-6 минут после поступления данных, поэтому мы делаем вывод о фактическом значении минут для данных, устанавливая snap_mins самостоятельно. (По причинам, которые я не буду вдаваться в подробности, мы можем использовать значения только за полчаса и должны иметь строку для данного имени / идентификатора / узла каждые полчаса.)

Однако я знаю, что это действительно ненадежно, и я хочу попытаться вместо поиска конкретных минутных значений просто захватить первую строку, значение минут в которой snapshot_date находится где-то между 4-10 минутами через каждые полчаса (XX:00 или XX:30). Например, если таблица выглядит так:

      | name  | node | points | ... | snapshot_date | 
| Jane  | 1    | 1      | ... | 1/1/21 22:02  |
| Jane  | 1    | 2      | ... | 1/1/21 22:05  | // take this value
| Jane  | 1    | 3      | ... | 1/1/21 22:09  |
| Jane  | 1    | 4      | ... | 1/1/21 22:38  | // take this value
| Jane  | 1    | 5      | ... | 1/1/21 22:41  |

| Jane  | 1    | 1      | ... | 1/1/21 23:05  | // take this value
| Jane  | 1    | 2      | ... | 1/1/21 23:06  |
| Jane  | 1    | 3      | ... | 1/1/21 23:35  | // take this value
| Jane  | 1    | 4      | ... | 1/1/21 23:38  |

| Jane  | 3    | 1      | ... | 1/1/21 23:02  |
| Jane  | 3    | 2      | ... | 1/1/21 23:07  | // take this value (current query wouldn't pick this up because it's only looking for snapshots where the minute value is 5 or 6) 
| Jane  | 3    | 3      | ... | 1/1/21 23:10  |
| Jane  | 3    | 4      | ... | 1/1/21 23:35  | // take this value
| Jane  | 3    | 5      | ... | 1/1/21 23:38  |

Итак, результаты запроса, которые я хочу получить, будут такими:

      | name  | node | points | ... | snap_date | snap_hour | snap_mins
| Jane  | 1    | 2      | ... | 1/1/21    | 22        | 0
| Jane  | 1    | 4      | ... | 1/1/21    | 22        | 30
| Jane  | 1    | 1      | ... | 1/1/21    | 23        | 0
| Jane  | 1    | 3      | ... | 1/1/21    | 23        | 30
| Jane  | 3    | 2      | ... | 1/1/21    | 23        | 0
| Jane  | 3    | 4      | ... | 1/1/21    | 23        | 30

Однако я изо всех сил пытался определить, возможно ли это вообще. Любая помощь будет принята с благодарностью!

Answer 1

Один вариант

1. Добавьте столбцы, определяющие связанный 30-минутный интервал
2. Порядковый номер строк в 30-минутной группе
3. Возьмите только последовательность №1 из каждой группы.

      WITH x AS (
  SELECT name, node, points, snapshot_date, <bunch of other fields>,
  DATE(snapshot_date) AS snap_date,
  HOUR(snapshot_date) AS snap_hour,
  CASE
    WHEN MINUTE(snapshot_date) < 31 THEN 0
    ELSE 30
  END AS snap_mins
FROM some_table
where 
  <bunch of conditions here> 
), 
y as (
  SELECT x.*,
  ROW_NUMBER() OVER (PARTITION BY name, node, snap_date, snap_hour, snap_minute ORDER BY snapshot_date) AS seq
  FROM x
)
SELECT * FROM y WHERE seq = 1

Если вам действительно нужно исключить из рассмотрения строки из MINUTE 0–3 и 30–33, добавьте условие WHERE к первому CTE.