CODEWITHSUNDEEP
functionpostgresqldateloopsaggregate-functions
Pablo Tapia
Pablo Tapia

Reputation: 353

Postgresql loop function

I have a table with many rows so when I make this query:

SELECT     
    date_trunc('hour', fecha) AS momento
    ,(extract(minute FROM fecha)::int / 5) AS min
    ,count(fecha) AS cuenta
FROM   table1
WHERE fk_id_busqueda=$id_busqueda 
GROUP  BY 1, 2
ORDER  BY 1, 2;

It takes a lot of time. One solution that I am thinking is make a postgres function where I can order the data by date and then sum one everytime momento of the previous row is the same than the actual one with a loop. And when is different then reset the value and pass to the Next row. The idea is that the function recives id_busqueda (int).

The output should be like this: 

 2013-01-20 00:00:00 |         0 |    16
 2013-01-20 00:00:00 |         1 |    30
 2013-01-20 00:00:00 |         2 |    27
 2013-01-20 00:00:00 |         3 |    30
 2013-01-20 00:00:00 |         4 |    33
 2013-01-20 00:00:00 |         5 |    21
 2013-01-20 00:00:00 |         6 |    27
 2013-01-20 00:00:00 |         7 |    27
 2013-01-20 00:00:00 |         8 |    19
 2013-01-20 00:00:00 |         9 |    13
 2013-01-20 00:00:00 |        10 |    17
 2013-01-20 00:00:00 |        11 |    24
 2013-01-20 01:00:00 |         0 |    12
 2013-01-20 01:00:00 |         1 |    15
 2013-01-20 01:00:00 |         2 |    25
 2013-01-20 01:00:00 |         3 |    19
 2013-01-20 01:00:00 |         4 |    10
 2013-01-20 01:00:00 |         5 |    12
 2013-01-20 01:00:00 |         6 |    13

Upvotes: 3

Views: 1107

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125304

In addition to the index creation on the fk_id_busqueda column this should shave a bit more from the execution time:

SELECT     
    date_trunc('minute', fecha) - 
    (extract(minute from fecha)::integer % 5) * interval '1 minute' as momento
    ,count(fecha) AS cuenta
FROM   table1
WHERE fk_id_busqueda=$id_busqueda 
GROUP  BY 1
ORDER  BY 1

Upvotes: 2

klin
klin

Reputation: 121654

I hope this function do the task.

create or replace function myfunc(id_busqueda integer)
returns table (momento timestamp, min int, cuenta int)
language plpgsql as $$
declare
    t record;
    momento timestamp;
    imomento timestamp;
    mins integer;
    imins integer;
    was boolean;
begin
    was := false;
    cuenta := 1;
    for t in 
        select fecha 
        from table1 
        where fk_id_busqueda = id_busqueda
        order by fecha
    loop
        momento := date_trunc('hour', t.fecha);
        mins := extract(minute FROM t.fecha)::int / 5;
        if imins = mins and imomento = momento then
            cuenta := cuenta+ 1;
        else
            if was then
                return query select imomento, imins, cuenta;
            end if;
            cuenta := 1;
            imins := mins;
            imomento := momento;
            was := true;
        end if;
    end loop;
    if was then
        return query select imomento, imins, cuenta;
    end if;
end; $$;

Upvotes: 1

Related Questions