Split timestamps into 24 hours and get an overview for a week

Question

I am a beginner in programming and especially SQL. I am working with the SQLDeveloper on the AOLDATA-database. The structure of the table I am working on looks like this: AOLDATA.querydata

I did not figured out how to write a statement that provides the timestamps splitted into different parts. I want to know how often was searched for a term, for the whole interval(01.03.2006-28.05.2006). My approach to solve the problem is:

SELECT COUNT (id)
FROM AOLDATA.querydata
WHERE querytime between ('01.03.2006 00:00:00') 
AND ('01.03.2006 00:59:00')
AND QUERY LIKE '%burgerking%';

But when I do it this way, I have to start more than 2000 requests to cover the whole timeframe.As a result the best output would be to have in a format like this.

Term: burgerking

Monday(00:00-00:59 (40 results),…,23:00-23:59(13 results))
…
…
…
…
…
Sunday(00:00-00:59 (40 results),…,23:00-23:59(13 results))

Is there any good solution like a loop or something else easy way to do it?

`SELECT trunc(querytime, 'HH'), COUNT (id) from ... GROUP BY TRUNC(querytime, 'HH')` — Wernfried Domscheit, Nov 21 '16 at 20:41
Please don't assume we have access to the same data sources you do. Instead do provide sufficient sample data to test and illustrate your issue, preferably as create table statements and insert statements. Then also include expected results. — Sentinel, Nov 21 '16 at 20:42
Hey I didn´t created the Table, I just get it from my university, I only can make requests and get results. Please follow the link, there you can see how the table is built and hopefully you get an idea of what I´m looking for. — , Nov 21 '16 at 21:04

score 0 · Answer 1 · answered Nov 21 '16 at 21:25

Try:

SELECT *
FROM (
      select to_char( querytime, 'Day' ) my_day,
             to_char( querytime, 'D' ) my_day_of_week,
             to_char( querytime, 'hh24') my_hour, 
             id
      from querydata q
)  
pivot  (
  count(id) for (my_hour) in (
     '00' as "00:00-00:59",
     '01' as "01:00-01:59",
     '02' as "02:00-02:59",
     '03' as "03:00-03:59",
     '04' as "04:00-04:59",
     '05' as "05:00-05:59",
     '06' as "06:00-06:59",
     '07' as "07:00-07:59",
     '08' as "08:00-08:59",
     '09' as "07:00-09:59",
     '10' as "10:00-10:59",
     '11' as "11:00-11:59",
     '12' as "12:00-12:59",
     '13' as "13:00-13:59",
     '14' as "14:00-14:59",
     '15' as "15:00-15:59",
     '16' as "16:00-16:59",
     '17' as "17:00-17:59",
     '18' as "18:00-18:59",
     '19' as "19:00-19:59",
     '20' as "20:00-20:59",
     '21' as "21:00-21:59",
     '22' as "22:00-22:59",
     '23' as "23:00-23:59"
  )
)
ORDER BY my_day_of_week

thanks for the solution I only need to modify the code. The query aborts here:`select to_char( querytime, 'Day' ) my_day, to_char( querytime, 'D' ) my_day_of_week, to_char( querytime, 'hh24') my_hour, id` and the message with the message ORA-00904 "%s: invalid identifier" — , Nov 21 '16 at 22:09

Split timestamps into 24 hours and get an overview for a week

1 Answers1