Группировка записей по часам или по дням и заполнение пробелов нулями или нулями

Я написал запрос, который считает записи час за часом:

select TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24'),count(*) from req group by
TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24');

результат:

2012-02-22 13    2280
2012-02-22 15    1250
2012-02-22 16    1245
2012-02-22 19    1258

Но мне нужен такой результат:

2012-02-22 13    2280
2012-02-22 14    0
2012-02-22 15    1250
2012-02-22 16    1245
2012-02-22 17    0
2012-02-22 18    0
2012-02-22 19    1258

Также у меня есть эти запросы, которые группируются по дням и месяцам!

select TO_CHAR(copied_timestamp, 'YYYY-MM-DD'),count(*)  from req
group by TO_CHAR(copied_timestamp, 'YYYY-MM-DD');

select TO_CHAR(copied_timestamp, 'YYYY-MM'),count(*)  from req
group by TO_CHAR(copied_timestamp, 'YYYY-MM');

Мне нужно, чтобы их пробелы также были заполнены нулем или нулем. Любая помощь очень ценится.


oracle group-by days hour
person Mehdi    schedule 29.05.2012    source источник
comment
Я использую нативные запросы JPA, поэтому думаю, что проблем с pl/sql не будет.   -  person Mehdi    schedule 29.05.2012
comment
Кто-нибудь знает, как обрабатывать такие запросы в Mysql?   -  person Mehdi    schedule 25.06.2012

Ответы (1)


arrow_upward
8
arrow_downward

try:
первый запрос (по часам):

with t as (
  select mnd + ((level-1)/24) ddd
  from
  (select trunc(min(copied_timestamp),'hh') mnd, trunc(max(copied_timestamp),'hh') mxd from req) v
  connect by mnd + ((level-1)/24) <= mxd
  )
select to_char(trunc(d1, 'hh'), 'yyyy-mm-dd hh24'), count(d2) from 
(select nvl(copied_timestamp, ddd) d1, copied_timestamp d2 from req right outer join (
  select ddd from t) ad on ddd = trunc(copied_timestamp, 'hh'))
group by trunc(d1, 'hh');

второй запрос (по дням):

with t as (
      select mnd + level-1 ddd
      from
      (select trunc(min(copied_timestamp),'dd') mnd, trunc(max(copied_timestamp),'dd') mxd from req) v
      connect by mnd + level-1 <= mxd
      )
    select to_char(trunc(d1, 'dd'), 'yyyy-mm-dd'), count(d2) from 
    (select nvl(copied_timestamp, ddd) d1, copied_timestamp d2 from req right outer join (
      select ddd from t) ad on ddd = trunc(copied_timestamp, 'dd'))
    group by trunc(d1, 'dd');

третий запрос (по месяцам):

with t as (
      select add_months(mnd, level-1) ddd
      from
      (select trunc(min(copied_timestamp),'mm') mnd, trunc(max(copied_timestamp),'mm') mxd from req) v
      connect by add_months(mnd, level-1) <= mxd
      )
    select to_char(trunc(d1, 'mm'), 'yyyy-mm'), count(d2) from 
    (select nvl(copied_timestamp, ddd) d1, copied_timestamp d2 from req right outer join (
      select ddd from t) ad on ddd = trunc(copied_timestamp, 'mm'))
    group by trunc(d1, 'mm');
person A.B.Cade    schedule 29.05.2012
comment
Спасибо, ABCade, это работает, но я не вижу часа в первом столбце. я просто вижу день-месяц-год! Я думаю, это из-за функции trunc. как я могу использовать его, чтобы показать час тоже? может быть это из-за разработчика sql, который не показывает час? Кстати, как насчет второго и третьего запроса? - person Mehdi; 29.05.2012
comment
Попробуйте заменить trunc(d1, 'hh') на to_char(trunc(d1, 'hh'), 'yyyy-mm-dd hh24'). Что касается других запросов, они должны быть такими же, просто обрежьте их по дню с помощью dd или по месяцу с помощью mm (просто замените hh) - person A.B.Cade; 29.05.2012
comment
@A.B.Cade Я думаю, что этот запрос возвращает неверные результаты для dd и mm. Я не знаком с подключением по команде, но я думаю, что мы должны изменить число 24 при использовании dd или mm. Он отлично работает для hh. даже в вашей скрипке результат для mm и dd неверен. выполните соединение по частям с dd и mm, и вы увидите, что для каждого дня или месяца существует более одной записи! - person Mehdi; 30.05.2012
comment
это правильно: select to_char(trunc(mnd + (level/24),'hh'),'yyyy-mm-dd hh24') ddd from (select min(copied_timestamp) mnd, max(copied_timestamp) mxd from req) v connect by mnd + (level/24) <= mxd; но это неправильно select to_char(trunc(mnd + (level/24),'dd'),'yyyy-mm-dd') ddd from (select min(copied_timestamp) mnd, max(copied_timestamp) mxd from req) v connect by mnd + (level/24) <= mxd; - person Mehdi; 30.05.2012
comment
Вы правы, конечно, уровень — это целое число, представляющее собой итерацию, если вы прибавляете его к типу date, он добавляет дни, поэтому только часы имеет смысл делить на 24. Исправление простое: для дней используйте mnd+level, а для месяцев используйте add_months(mnd, level). Я подумал о другом: уровень начинается с 1, поэтому в основном вам нужно использовать level-1. Я также подумал, что лучше обрезать минимальные/максимальные даты во внутреннем запросе. Итак, вот новая скрипка: sqlfiddle.com/#!4/6b5f5/21 .Если все работает правильно, я обновлю свой ответ (скрипка не останется навсегда...) - person A.B.Cade; 30.05.2012
comment
@A.B.Cade отлично. Они работают очень хорошо. Прежде чем вы ответите, я исправил запрос, и когда я хотел ответить, я потерял POWER и после включения потерял весь исправленный код в sqlDeveloper;) - person Mehdi; 30.05.2012
comment
Кстати, этот запрос немного медленный при выборе группы по часам, потому что соединение сгенерировано большим количеством записей для больших диапазонов. Но нет проблем, если это эффективный способ для такого рода запросов. Я отмечу этот ответ принятым;) и, пожалуйста, отредактируйте ответ. Большое спасибо A.B.Cade - person Mehdi; 30.05.2012
comment
ВАУ, мы одновременно писали комментарии! - person Mehdi; 30.05.2012