lunes, 12 de diciembre de 2022

Sum scan in sql

`scan` is an adverb in apl/j/k which is used to reduce an array over an operation, while keeping all the intermediate results. It's used in lots of very creative ways in the array programming languages. Here's some sample examples of "scan (\)".

:x:1+!9
1 2 3 4 5 6 7 8 9
+\x
1 3 6 10 15 21 28 36 45
*\x
1 2 6 24 120 720 5040 40320 362880

In most of those programming languages, scan acts as an adverb, which is a higher order function that modifies a verb. In clojure, you can get similar results with `reductions`.

Anyway, the topic of today is how to accumulate intermediate results like these, in sql.

Well, in the AoC 2022 (day 1), the first part was about summing numbers by paragraphs. 

Some people at duckdb discord are sharing their solutions, and we see it's kinda normal to use `row_number() over() as idx` idiom to add ascending numbers to any projection.

Another trick, to indicate the paragraph number in each line (for future group_by purposes), this is what Teggy writes:

 `select sum(i.value is null :: int) over (order by num)`

Here I show generate_series, row_number, and the sum scan based on "is null".


No hay comentarios: