Как применить интервалы времени к существующему запросу SQL

Question

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

Мне нужно запросить в этой таблице сумму максимальных (start_time) связанных значений "data" для каждого "object_id" (с учетом списка) в день. (Другими словами, самая близкая запись к концу каждого дня, для каждого object_id в определенном списке).

Вот основная структура таблицы:

CREATE TABLE checks (
  id SERIAL PRIMARY KEY,
  object_id INTEGER,
  state INTEGER,
  start_time TIMESTAMP,
  data TEXT
);

data это TEXT поле, но имеет numeric значения (я не могу изменить этот аспект, но могу конвертировать с помощью приведений).

И вот запрос, с которым я работаю до сих пор:

WITH object_ids AS ( 
    SELECT object_id FROM objects WHERE object_id in (14845,12504,12451,12452)
),
records AS (
    SELECT
        data,
        start_time,
        MAX(start_time) OVER (PARTITION BY object_id)
    FROM checks
    WHERE
        object_id IN (SELECT object_id FROM object_ids) AND
        state = 0 AND
        start_time BETWEEN '2013-05-01 00:00:00' AND '2013-05-02 00:00:00'
)   
SELECT 
    SUM(data::bigint) 
FROM   
    records
WHERE 
    max = start_time

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

Мне бы очень хотелось изменить этот запрос, чтобы мне не приходилось выполнять отдельные запросы в день, а только один запрос, чтобы вернуть набор значений за день

start_time          | sum
---------------------------
2013-05-01 00:00:00 | 39118
2013-05-02 00:00:00 | 98387
2013-05-03 00:00:00 | 8384

Я исследовал вопросы, связанные с временным интервалом, и они очень полезны (я обязан тем, что использую оконные функции для Stackru!), Но я просто не могу сделать скачок в решении этой проблемы.

Answer 1

SELECT day, sum(data) AS total_per_day
FROM  (
   SELECT DISTINCT ON (object_id, 1)
          start_time::date, data::numeric
   FROM   checks c
   WHERE  object_id in (14845,12504,12451,12452)
   AND    state = 0
   AND    start_time >= '2013-04-01'::date
   AND    start_time <  '2013-05-05'::date   -- any range of days
   ORDER  BY object_id, 1, c.start_time DESC -- seems redundant, see text
   ) x
GROUP BY  1
ORDER BY  1

Это дает вам один день подряд с суммой. Я понимаю, что это очень похоже на то, что @Clodoaldo уже опубликовал, но я демонстрирую правильно DISTINCT ON синтаксис, а также некоторые другие улучшения и некоторые необходимые объяснения.

• Ты можешь использовать DISTINCT ON до значения данных в день. Должно быть проще и быстрее, чем оконная функция:

  • Выберите первую строку в каждой группе GROUP BY?

• ORDER BY пункт должен согласиться с DISTINCT ON выражения (подробности в связанном ответе). Вот причина:

  ORDER  BY 1, start_time::date, start_time DESC
  

  Второй пункт может показаться излишним, но он необходим здесь.

• Получить date из timestampПросто бросьте: start_time::date,

• Осторожно, когда вы включаете start_time::date (который я оптимизировал) в SELECT список. В GROUP BY а также ORDER BY выходные столбцы имеют приоритет над входными столбцами (в отличие от WHERE а также HAVING где вы можете ссылаться только на столбцы ввода). Вы должны использовать другой псевдоним для выходного столбца или квалифицировать таблицу для базового столбца, чтобы ссылаться на него: c.start_time

• С timestamp Вы практически всегда захотите исключить верхнюю границу. Подробности в этом ответе:

  • Найти перекрывающиеся диапазоны дат в PostgreSQL

Answer 2

select
    "day", sum(data) "data"
from (
    select distinct (1, 2)
        object_id,
        date_trunc('day', start_time)::date "day",
        start_time,
        "data"
    from checks
    where
        object_id in (14845,12504,12451,12452)
        and state = 0
    order by 1, 2, 3 desc
) s
group by 1
order by 1