Получение запросов, когда час начальной временной метки меньше часа конечной временной метки

У меня есть запрос на основе CTE, в котором я получаю общее количество пользовательских запросов между двумя заданными временными метками и показываю, сколько запросов делается в каждый час между двумя датами. Мой запрос работает следующим образом:

1) Начало и конец даты и времени (скажем, 07-13-2011 10:21:09 и 07-31-2011 15:11:21)

2) Получение количества запросов в каждый час с интервалом в 1 час, то есть для заданных временных меток. (если час начальной временной метки равен 10, а час конечной временной метки равен 15, как указано выше, я должен отображать почасовое количество запросов для часов 10,11,12,13,14,15 на каждый день с 13 по 31 июля)

Запрос такой:

WITH cal AS (
    SELECT generate_series('2-2-2011 00:00:00'::timestamp , '1-4-2012 05:00:00'::timestamp , '1 hour'::interval) AS stamp
        )
, qqq AS (
        SELECT date_trunc('hour', calltime) AS stamp
        , count(*) AS zcount
        FROM mytable
    WHERE calltime >= '13-7-2011 10:21:09' 
          AND calltime <= '31-7-2011 15:11:21' AND calltime::time >= '10:00:00' AND
          calltime::time <= '15:59:59' AND date_part('hour', calltime) >= 8 AND 
          date_part('hour', calltime) <= 15
 GROUP BY date_trunc('hour', calltime)
)
SELECT cal.stamp
        , COALESCE (qqq.zcount, 0) AS zcount
FROM cal
LEFT JOIN qqq ON cal.stamp = qqq.stamp
WHERE cal.stamp >= '13-7-2011 10:00:00' AND cal.stamp <= '31-7-2011 15:11:21' 
       AND date_part('hour', cal.stamp) >= 10 AND 
       date_part('hour', cal.stamp) <= 15
ORDER BY stamp ASC;

Этот запрос показывает количество запросов между 10 и 15 часами в часовых интервалах (X запросов с 10:21:09 до 11:00, Y запросов с 11:00 до 11:59, Z запросов с 12:00 до 12:00). :59..., запросы P с 15:00 до 15:11:21) за каждый день, начиная с 13 июля с 10:00 до 15:00 и заканчивая 31 июля с 10:00 до 15:00.

Это хорошо работает для входных данных, в которых часовое значение первой метки времени меньше, чем часовое значение второго, например. здесь 10 для первого часа метки времени и 15 для второго часа метки времени, но есть проблема. Когда я хочу применить это к таким входам, как 07-13-2011 22:11:43 и 07-31-2011 04:06:04, -так что я получаю количество запросов в час между 22 и 04 для каждого отдельного день, у меня возникли проблемы - скорее всего, из-за структуры моего запроса. Мне нужно получить общее количество запросов, например следующее:

    stamp                 zcount
    "2011-07-13 22:00:00"  123
    "2011-07-13 23:00:00"  338
    "2011-07-14 00:00:00"  184
    "2011-07-13 01:00:00"  298
    "2011-07-13 02:00:00"  162
    "2011-07-13 03:00:00"  293
    "2011-07-14 04:00:00"  216
    "2011-07-14 22:00:00"  392
    "2011-07-14 23:00:00"  268
    "2011-07-15 00:00:00"  483
    "2011-07-15 01:00:00"  327
    "2011-07-15 02:00:00"  419
    "2011-07-15 03:00:00"  121
    "2011-07-15 04:00:00"  231
     .
     . 
    "2011-07-30 22:00:00"  392
    "2011-07-30 23:00:00"  268
    "2011-07-31 00:00:00"  483
    "2011-07-31 01:00:00"  327
    "2011-07-31 02:00:00"  419
    "2011-07-31 03:00:00"  544
    "2011-07-31 04:00:00"  231

Что мне нужно изменить в моем запросе, чтобы убедиться, что он работает в случаях, когда часовое значение второй временной метки меньше, чем часовое значение первой временной метки - например, 22 для первой временной метки и 4 для второй временной метки здесь-? В настоящее время я использую обходной путь для этого, но он не очень элегантный, и я хотел бы это изменить.


postgresql timestamp datetime common-table-expression
person sm90901    schedule 12.09.2012    source источник
comment
Этот вопрос очень трудно понять. в чем именно проблема?: Удалите ненужное и проясните проблему.   -  person Erwin Brandstetter    schedule 12.09.2012
comment
Какой смысл использовать date_part('hour'...) в ваших предложениях WHERE?   -  person Mike Sherrill 'Cat Recall'    schedule 12.09.2012
comment
@Catcall используется только для того, чтобы распечатать результаты с меткой времени от 10 до 15 часов. Если я удалю это, другие результаты, содержащие часы метки времени, такие как 8,9,16,17 - в основном каждый час вместе с исходными 10- также распечатанный. Поскольку я использую массив для отслеживания результатов запроса на стороне C++, запасные результаты тратят место впустую, мне все время нужен только определенный интервал времени, а не весь 24-часовой период.   -  person sm90901    schedule 12.09.2012
comment
Понимаю. Я прочитал оператор SQL и увидел «13» и «31» как одно и то же число.   -  person Mike Sherrill 'Cat Recall'    schedule 12.09.2012
comment
@ErwinBrandstetter Я немного отредактировал и попытался дать немного лучшее объяснение, дайте мне знать, если вопрос все еще неясен.   -  person sm90901    schedule 12.09.2012

Ответы (1)


arrow_upward
4
arrow_downward

Я буду честен. Я не уверен, что я даже близко.

Я исключил большую часть материала из предложения WHERE в qqq, потому что оно повторяется в предложении where внешнего запроса. Я также переключился на временные метки ISO, потому что мне их бесконечно легче читать.

Вы, вероятно, захотите переписать это как функцию, которой вы можете передавать аргументы.

WITH cal AS (
    SELECT generate_series('2011-02-02 00:00:00'::timestamp , 
                           '2012-04-01 05:00:00'::timestamp , 
                           '1 hour'::interval) AS stamp
),
qqq AS (
  SELECT date_trunc('hour', calltime) AS stamp, count(*) AS zcount
  FROM mytable
  WHERE calltime >= '2011-07-13 20:21:09' 
    AND calltime <= '2011-07-31 15:11:21'
 GROUP BY date_trunc('hour', calltime)
)
SELECT cal.stamp, COALESCE (qqq.zcount, 0) AS zcount
FROM cal
LEFT JOIN qqq ON cal.stamp = qqq.stamp
WHERE cal.stamp >= '2011-07-13 20:00:00' 
  AND cal.stamp <= '2011-07-31 15:11:21' 
  AND (
    extract ('hour' from cal.stamp) >= extract ('hour' from '2011-07-13 20:00:00'::timestamp) or
    extract ('hour' from cal.stamp) <= extract ('hour' from '2011-07-31 15:11:21'::timestamp) 
  )
ORDER BY stamp ASC;

Если здесь и есть какое-то волшебство, то оно, вероятно, в этом пункте. Обратите внимание на использование «или».

AND (
  extract ('hour' from cal.stamp) >= extract ('hour' from '2011-07-13 20:00:00'::timestamp) or
  extract ('hour' from cal.stamp) <= extract ('hour' from '2011-07-31 15:11:21'::timestamp) 
)
person Mike Sherrill 'Cat Recall'    schedule 12.09.2012
comment
Ничего себе, почти там. Теперь мне нужно только найти способ убедиться, что изменение минутных и секундных частей меток времени начала/окончания также изменяет количество запросов на каждый день, а не только начальный час первого дня и конечный час последнего дня -для по какой-то причине код принимает интервал количества запросов 15:00:00 - 15:59:59 вместо 15:00:00 - 15:11:21 для каждого дня, кроме последнего дня. Кроме того, все дни, кроме первого дня, получают общее количество запросов с 20:00:00 до 20:59:59 вместо 20:21:09 - 20:59:59 - я посмотрю и постараюсь прийти с исправлением, большое спасибо. - person sm90901; 12.09.2012