Geek asks and answers

Iterative sums in SQL

I have values ​​in Zabbix DB that load onto it every minute. I need a query that calculates hourly totals for different days, for example, until March 20, 2013, I will have a line for 0:00 containing the sum of values, where time> = 0:00 and <1:00 and so on. Then I will have another line between 1 and 2 in the morning, etc. I use the following query, but I need to constantly change the time. I am looking for a query that will generate 24 rows for me for every hour of the day. Please, help.

SELECT 
    SUM(CASE WHEN itemid = 23661
        THEN value ELSE 0 END) Hits 
    FROM history_uint WHERE 
        clock >= EXTRACT(EPOCH FROM TIMESTAMP '2013-03-24 00:00:00')
         AND clock < EXTRACT(EPOCH FROM TIMESTAMP '2013-03-24 01:00:00')
+5
source share
3 answers

Have you tried to group the results by the hour?

SELECT DATEPART(HOUR, timestamp) [HOUR]
, SUM(CASE WHEN itemid = 23661 THEN value ELSE 0 END) hits 
FROM history_uint
WHERE clock >= EXTRACT(EPOCH FROM timestamp '2013-03-24')
  AND clock < EXTRACT(EPOCH FROM timestamp '2013-03-25')
GROUP BY DATEPART(HOUR, timestamp);
+1
source

Sort of:

select '2013-03-20'::date + delta * '1 hour'::interval
from generate_series(0,23) g(delta)
0
source 
select
    date_trunc('hour', clock) "hour",
    sum((itemid = 23661)::integer) hits 
from history_uint
group by 1
order by 1

Or with all the hours filled:

select
    s.hour, count(itemid = 23661 or null) hits 
from
    (
        select date_trunc('hour', clock) "hour", itemid
        from history_uint
    ) h
    right join (
        select date_trunc('hour', d) "hour"
        from generate_series (
            (select min(clock::date)),
            (select max(clock)::date + 1) - interval '1 hour',
            '1 hour'
        ) s(d)
    ) s on s.hour = h.hour
group by 1
order by 1
0
source

More articles:


All Articles