[SQL] Window function optimaliseren: sommeren per n rows.

[EddoH]

Dit is wellicht een hele simpele vraag voor de SQL guru's, maar ik kom er even niet uit.
Ik zoek de efficiëntste manier om (met Postgres) elke n-rows te sommeren. Dus stel:

code:

1    0.2
2    0.1
3    0.7
4    0.6
5    0.5
6    0.9
7    0.9
8    0.1

Hier wil ik, als ik per 2, wil sommeren, het volgende dus bijvoorbeeld uit krijgen.

code:

1    0.3
3    1.3
5    1.4
7    1.0

Of per 4:

code:

1    1.6
5    2.4

Nu gebruik ik hiervoor een window function, waarbij ik "n" en "startindex" natuurlijk invul:

SQL:

SELECT * FROM 
(SELECT sum(value) OVER (order by 'index' ROWS BETWEEN current row AND n-1 following)
FROM mytable
WHERE ('index' % n) = startindex  % n;

Nu werkt dit prima, maar wordt steeds langzamer voor grote waardes van n. Om dit te optimaliseren dacht ik een conditie aan de window function toe te voegen. Iets als

code:

WHEN (currentrow % n) = startindex  % n;

om zo te zorgen dat niet voor elke row, die ik uiteindelijk toch niet wil hebben de window function wordt uitgevoerd, maar alleen voor de n-de rows die ik wil hebben.

Ik krijg echter het gevoel dat een wiundow function misschien helemaal niet is wat ik moet gebruiken om dit probleem op te lossen en ik veel simpeler iets met een SUM en GROUP BY kan doen. Heeft iemand suggesties?

[ Voor 4% gewijzigd door EddoH op 04-03-2016 11:37 ]

[RayNbow]

Helpt dit je op weg? Group by every N records in T-SQL

[EddoH]

RayNbow schreef op vrijdag 04 maart 2016 @ 09:56:
Helpt dit je op weg? Group by every N records in T-SQL

Hmmmm..not really. Is specifiek T-SQL zo te zien, en lijkt ongeveer hetzelfde te doen als wat ik nu doe?
edit: ah nee wacht, hier kan ik misschien wel iets mee, ik zie nu pas dat de RANK gebruikt wordt om te kunnen groeperen.

[ Voor 14% gewijzigd door EddoH op 04-03-2016 10:09 ]

[EddoH]

Gelukt, thanks. Dat ik hier niet zelf opgekomen ben: achteraf is het natuurlijk heel simpel, echter de RANK() functie kende ik niet.

Query is nu voor een bepaalde veelgebruikte n ruim 10x sneller

Voor het nageslacht en als stackoverflow er ooit mee ophoudt:

SQL:

WITH T AS (
  SELECT RANK() OVER (ORDER BY ID) Rank,
    t.index, t.value
  FROM mytable t
)
SELECT (Rank - 1) / [n] groupid, SUM(value)
FROM T
GROUP BY ((Rank - 1) / [n])

[ Voor 78% gewijzigd door EddoH op 04-03-2016 10:58 ]

[Rotterdammertje]

In plaats van RANK() is het in dit geval beter om ROW_NUMBER() te gebruiken. RANK wordt gebruikt om een rangorde op basis van een een bepaalde meetwaarde toe te kennen (bijvoorbeeld de top 10 verkopers gebaseerd op de verkoopcijfers) en kan ook leiden tot gedeelde eerste plaatsen en zo. Je wil gewoon een oplopend nummertje toekennen aan de rows van je result set gebaseerd op een specifieke sortering, en dat is precies wat ROW_NUMBER() doet. Kan misschien nog een fractie schelen in de performance.

[Douweegbertje]

EddoH schreef op vrijdag 04 maart 2016 @ 09:41:

Ik zoek de efficiëntste manier om (met Postgres) elke n-rows te sommeren.

Ik ben echt nieuwsgierig waarvoor je zoiets zou kunnen gebruiken. Care to share?

[EddoH]

Sure: energieverbruik van een installatie (bv electriciteit/gas) die per x interval wordt gelogd in een database. Om analyses op de data te kunnen doen heb je bijvoorbeeld het verbruiktotaal per dag(deel)/week/jaar nodig. De afzonderlijke samples dien je dan simpelweg op te tellen.

[ Voor 3% gewijzigd door EddoH op 10-03-2016 07:10 ]

[Voutloos]

Je zou dan een timestamp kolom verwachten, maar die is er blijkbaar niet ivm ruimte ofzo?

[ Voor 11% gewijzigd door Voutloos op 10-03-2016 08:05 ]

[EddoH]

Voutloos schreef op donderdag 10 maart 2016 @ 08:04:
Je zou dan een timestamp kolom verwachten, maar die is er blijkbaar niet ivm ruimte ofzo?

Die is er weldegelijk. Die wordt nu ook in de query gebruikt om de periode te selecteren. Welke betrekking heeft dit op het sommeren volgens jou?

edit: ik zie net dat dit in de door mijn geposte uiteindelijk query niet helemaal duidelijk is. Die heb ik versimpeld om het concept duidelijk the laten.

Het is uiteindelijk zoiets geworden. De coalesce en generate_series om eventuele gaten in de data op te vangen:

SQL:

WITH T AS (SELECT RANK() OVER (ORDER BY timestamp) rank, x.timestamp, x.value FROM
      (select timestamp, coalesce(m.value, 0) as value from generate_series(:tsFrom, :tsTo, 1) as timestamp
        left join measurements m on (m.timestamp = timestamp AND <nog wat voorwaarden>)) x
      )
      SELECT (t.rank - 1) /  :sumBy groupid, SUM(t.value)
      FROM T
      GROUP BY (t.rank - 1) / :sumBy ORDER BY groupid ASC;

Rotterdammertje schreef op dinsdag 08 maart 2016 @ 10:00:
In plaats van RANK() is het in dit geval beter om ROW_NUMBER() te gebruiken. RANK wordt gebruikt om een rangorde op basis van een een bepaalde meetwaarde toe te kennen (bijvoorbeeld de top 10 verkopers gebaseerd op de verkoopcijfers) en kan ook leiden tot gedeelde eerste plaatsen en zo. Je wil gewoon een oplopend nummertje toekennen aan de rows van je result set gebaseerd op een specifieke sortering, en dat is precies wat ROW_NUMBER() doet. Kan misschien nog een fractie schelen in de performance.

Ik heb jouw post helemaal over het hoofd gezien! Thanks, ik ga daar eens naar kijken, inderdaad wil ik alleen maar een volgnummertje hebben.

[ Voor 76% gewijzigd door EddoH op 10-03-2016 08:48 ]

[Voutloos]

EddoH schreef op donderdag 10 maart 2016 @ 08:34:
[...]


Die is er weldegelijk. Die wordt nu ook in de query gebruikt om de periode te selecteren. Welke betrekking heeft dit op het sommeren volgens jou?

Nou, als het zou kunnen zou group by someDateFunction(timestampCol) 't meest voor de hand liggen.

[ Voor 55% gewijzigd door Voutloos op 10-03-2016 13:15 ]

[EddoH]

Voutloos schreef op donderdag 10 maart 2016 @ 13:14:
[...]
Nou, als het zou kunnen zou group by someDateFunction(timestampCol) 't meest voor de hand liggen.

Die aanpak werd in het verleden ook gebruikt, echter is dat significant langzamer. Hoewel het nog wel eens het bekijken waard is. Overigens is de timestamp in de database niet een direct te gebruiken timestamp maar een custom format (don't ask), die dus voor elke row geconverteerd moet worden.

[ Voor 26% gewijzigd door EddoH op 10-03-2016 13:22 ]