Как я могу выбрать одну строку данных в час, из таблицы временных марок?

Извините, если это сбивает с толку, поскольку я не очень хорошо знаком с postgresql. У меня есть база данных postgres с таблицей, полной "сайтов". Каждый сайт сообщает о часах раз в час, и когда он сообщает, он делает запись в этой таблице, например:

site | tstamp
-----+--------------------
6000 | 2013-05-09 11:53:04
6444 | 2013-05-09 12:58:00
6444 | 2013-05-09 13:01:08
6000 | 2013-05-09 13:01:32
6000 | 2013-05-09 14:05:06
6444 | 2013-05-09 14:06:25
6444 | 2013-05-09 14:59:58
6000 | 2013-05-09 19:00:07

Как вы можете видеть, отметки времени почти никогда не бывают на носу, и иногда их будет два или более в течение нескольких минут/секунд друг от друга. Кроме того, некоторые сайты не будут сообщать часами за раз (иногда). Я хочу только выбрать одну запись на сайт в час (как можно ближе к каждому часу, как я могу получить). Как я могу сделать это эффективно? Мне также нужно будет распространить это на другие временные рамки (например, одну запись на сайт в день - как можно ближе к полуночи).

Спасибо за любые предложения.

3 ответа

Вы можете использовать DISTINCT ON:

select distinct on (date_trunc('hour', tstamp)) site, tstamp
from t
order by date_trunc('hour', tstamp), tstamp

Будьте осторожны с ORDER BY, если вы заботитесь о том, какую запись вы получаете.

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

select site, tstamp
from (
 select site, tstamp,
 row_number() over (partition by date_trunc('hour', tstamp) order by tstamp) as r
 from t
) as dt
where r = 1

Опять же, вы должны настроить ORDER BY, чтобы выбрать конкретную строку интереса для каждой даты.


Вы ищете самую близкую стоимость в час. Некоторые из них до часа, а некоторые - после. Это делает эту трудную проблему.

Во-первых, нам нужно определить диапазон значений, которые работают в течение определенного часа. Для этого я буду рассматривать что-либо с 15 минут до часа до 45 минут после того, как будет на этот час. Итак, период рассмотрения на 2:00 идет с 1:45 до 2:45 (произвольно, но кажется разумным для ваших данных). Мы можем сделать это, сдвинув отметки времени на 15 минут.

Во-вторых, нам нужно получить самое близкое значение к часу. Итак, мы предпочитаем 1:57 - 2:05. Мы можем сделать это, рассмотрев первое значение в (57, 60 - 57, 5, 60 - 5).

Мы можем поместить эти правила в инструкцию SQL, используя row_number():

select site, tstamp, usedTimestamp
from (select site, tstamp,
 date_trunc('hour', tstamp + 'time 00:15') as usedTimestamp
 row_number() over (partition by site, to_char(tstamp + time '00:15', 'YYYY-MM-DD-HH24'),
 order by least(extract(minute from tstamp), 60 - extract(minute from tstamp))
 ) as seqnum
 from t
 ) as dt
where seqnum = 1;


Для аспекта расширяемости вашего вопроса.

I also will need to extend this to other time frames (like one entry per site per day

Из различного набора идентификаторов сайта и использования (рекурсивного) CTE я бы построил набор, состоящий из одной записи на сайт в час (или другого заданного интервала), в пределах указанного диапазона StartDateTime, EndDateTime.

SITE..THE DATE-TIME-HOUR
 6000 12.1.2013 00:00:00
 6000 12.1.2013 01:00:00
 .
 .
 . 
 6000 12.1.2013 24:00:00 
 7000 12.1.2013 00:00:00 
 7000 12.1.2013 01:00:00
 .
 .
 . 
 7000 12.1.2013 24:00:00

Затем я оставил бы присоединение к этому CTE против вашего идентификатора сайта SITES и наименьшей абсолютной разности между точками CTE и временем входа LOG.

Таким образом, вы уверены в строке для каждого сайта за интервал.

P.S. Для сайта, который долгое время не звонил домой, его самая последняя временная метка в телефоне будет повторяться несколько раз, как ближайший доступный.

licensed under cc by-sa 3.0 with attribution.