sql windows functions

[ggrothendieck]

For purposes of example suppose we have the csv file

Name,Value
A,1
A,10
B,11
C,12

In SQL one can add sequence numbers by group using windowing functions like this:

select *, row_number() over (partition by Name) as seq from dat

but it seems this does not work on polars or if it does it is not supported by qsv. Would like to be be able to do this using SQL. If polars does not support this would it be feasible to add SQLite from qsv or other database that supports this?

[ondohotola]

You can add a seq column with 1 or (rather) 2 QSV passes:

| qsv enum -c seq --start 1 | qsv select seq,Name,Value

[ggrothendieck]

That does not sequence by group. The SQL statement shown would produce

Name,Value,seq
A,1,1
A,10,2
B,11,1
C,12,1

[ondohotola]

There is no SQL statement, and given an SQL producing

Name,Value
A,1
A,10
B,11
C,12

the pipe will put the seq in front
seq,Name,Value
1,A,1
2,A,10
3,B,11
4,C,12

[ggrothendieck]

My initial post in this thread shows the SQL statement that gives sequence numbers by group. Here is that same statement giving sequence numbers by the grouping column Name using R and SQLite.

library(sqldf) # uses SQLite
Lines <- "Name,Value\nA,1\nA,10\nB,11\nC,12"
dat <- read.csv(text = Lines)
sqldf("select *, row_number() over (partition by Name) as seq from dat")
##   Name Value seq
## 1    A     1   1
## 2    A    10   2
## 3    B    11   1
## 4    C    12   1

[jqnatividad]

As you pointed out @ggrothendieck , it's apparently not currently supported in Polars.

I did re-ping the request for more extensive Window functions support in Polars (pola-rs/polars#7227 (comment)). Given how fast the team iterates, it shouldn't be too long till its supported and qsv will leverage it as soon as it's released.

[ggrothendieck]

I found a workaround if there are only a small number of rows in each group (which is my case). It is fairly awkward and complex, it is probably pretty inefficient, it involves generating a bunch of temporary files which w ould later have to be deleted and it seems it can't be represented as single pipeline but it seems to work on this small example.

Suppose we have this file1.csv

Name,Value
A,1
A,11
B,2
C,3

The code follows assuming MS-Windows. First we create ordinary sequence numbers in tmp1.csv and then make a copy in temp2 except we make the names distinct from tmp1.csv. We then join tmp1.csv and tmp2.csv by group (the Name column) and only keep rows for which the index in the second file in the join is less than or equal to the first.
We then select out the first two columns and perform a count by Name and Value. This count equals the sequence numbers by group. It would be nice if this were simpler.

qsv enum file1.csv > tmp1.csv
qsv rename Name2,Value2,index2 tmp1.csv > tmp2.csv
qsv join Name tmp1.csv Name2 tmp2.csv | ^
qsv luau filter "index2 <= index"  |^
qsv select 1-2 > tmp3.csv
qsv sqlp tmp3.csv "select Name,Value,count(*) as seq from tmp3 group by Name, Value order by Name,Value"

This gives:

Name,Value,seq
A,1,1
A,11,2
B,2,1
C,3,1

[jqnatividad]

Your workaround really exercises qsv's composability goal :)

I'll move this to Discussions so other folks can learn from your example...