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...

[ggrothendieck]

Have simplified it down from 6 qsv calls to 3 and from 3 temporary files to 1. Things that made this possible were:

• noticed a difference between join and joinp where the former generates duplicate column names on a self join whereas the latter adds a suffix of _right to keep them unique
• didn't previously realize that the sqlp select statement can refer to stdin as a table name

On the other hand:

• I still haven't been able to determine a way to pipe the output of the first qsv below into the second.
• as before this is pretty inefficient since for each group of n rows the second qsv generates n^2 rows; however, in my case all my groups have only 1 or 2 rows so it is not that bad. Still not really a general solution though from an efficiency viewpoint.
• I wasn't able to combine the last two qsv's below into a single sqlp. Polars kept telling me it couldn't handle it.
• I am hoping that the comparison in the select statement is a numeric one but with an example this small I can't tell

^ is the Windows continuation character as before.

qsv enum file1.csv > tmp.csv
qsv joinp Name tmp.csv Name tmp.csv | ^
qsv sqlp - "select Name, Value, count(*) as seq from stdin where index_right <= index group by Name, Value order by Name, Value"

[ggrothendieck]

I found that this works too but through trial and error it seems that polars via qsv does not handle:

• self joins. I had to make a renamed copy of the input and specify the original file and the copy so that it thinks there are two files
• more than one with statement
• group by columns that are not in the select
• joins which are not simple equijoins

qsv enum file1.csv > tmp.csv
qsv cat rows tmp.csv > tmp2.csv
qsv sqlp tmp.csv tmp2.csv "with t as (select * from tmp a inner join tmp2 b on a.Name = b.Name ) select Name, Value, index, count(*) seq from t where index_right <= index group by Name, Value, index order by index" | ^
qsv select Name,Value,seq

giving

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

Here is the sql statement from above formatted over several lines:

``
with t as (
select *
from tmp a
inner join tmp2 b on a.Name = b.Name
)
select Name, Value, index, count(*) seq
from t
where index_right <= index
group by Name, Value, index
order by index

As previously file1.csv is:

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

[jqnatividad]

sqlp tries to integrate the power of Polars into the qsv suite - optimizing for composable data pipelines.

It's an extended version of the polars-cli, and most of it limits, specially on the SQL parsing front, are largely dictated by Polars' capabilities and limitations.

I'll try to see if the limitations you found above can be overcome through Polars SQL. With your help, maybe we can create an extended Wiki article on sqlp capabilities and limitations, and maybe even develop workarounds like you did above.

[ggrothendieck]

Just one correction. It seems polars does support multiple with statements so the qsv select at the end could be absorbed into the SQL statement:

qsv enum file1.csv > tmp.csv
qsv cat rows tmp.csv > tmp2.csv
qsv sqlp tmp.csv tmp2.csv "with t as (select * from tmp a inner join tmp2 b on a.Name = b.Name ), t2 as (select Name, Value, index, count(*) seq from t where index_right <= index group by Name, Value, index order by index) select Name, Value, seq from t2" 

which looks like this on multiple lines:

with t as (
    select * 
    from tmp a 
    inner join tmp2 b on a.Name = b.Name 
  ), 
 t2 as (
  select Name, Value, index, count(*) seq 
  from t 
  where index_right <= index 
  group by Name, Value, index order by index
 ) 
 select Name, Value, seq from t2

[ggrothendieck]

Regarding your comments, developing an SQL engine is a large amount of work and given the number of problems it suggests that there are others in polars that I didn't find. There is an SQLite crate and I wonder how much work it would be to support two SQL engines. I have used SQLite a fair bit and know of no bugs in it.

[jqnatividad]

I'm partial to Polars since its written in Rust and it seems to be the fastest SQL engine at the moment in the Rust ecosystem, if not of all dataframe libraries - as performance is qsv's top goal (https://github.com/jqnatividad/qsv?tab=readme-ov-file#goals--non-goals).

SQLite is rock solid and stable, but for OLAP type queries that I'm targeting qsv for, it seems duckdb is a better fit as an alternative engine in terms of performance and its focus on OLAP type queries.

Also, I ran some simple benchmarks comparing qsv sqlp vs sqlite and sqlite was much slower -
https://github.com/jqnatividad/qsv/releases/tag/0.123.0

As for duckdb, we actually use it to enable the to feature, and we could possibly have a duckdb powered command behind the to feature.

But since duckdb already has a powerful composable CLI, I'm not convinced its worthwhile. Already, several qsv users use duckdb in their pipelines, and that's perfectly fine by me.

Why not just use sqlite in your pipelines and only use qsv as required?

[ggrothendieck]

Yes, duckdb would be even better.

[ggrothendieck]

Found another approach to this problem assuming the input csv is sorted. Suppose we have this input csv and lua file.

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

enum-by-group.lua:
if Name == (prev or nil) then cum = (cum or 0) + 1 else cum = 1 end
prev = Name
return cum

Then

qsv luau map cum enum-by-group.lua file1.csv

gives

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

[ggrothendieck]

Here is a shorter version. It is now short enough that we don't need a lua file for it. Would be nice if it were simpler though.

In lua b and 1 or 0 converts boolean b to numeric. If prev, the previous Name, has not been defined or does not equal Name then the quantity within the first set of parentheses evaluates to 0 so x is set to 1. If Name and prev are equal then the quantity in the first parentheses equals 1 so x gets set to the previous value of x plus 1.

qsv luau map seq "x = (Name == prev and 1 or 0) * (x or 0) + 1; prev = Name; return x" file1.csv

[ggrothendieck]

This gives us a way to get the first n rows of each group. Here we use n=1.

qsv luau map seq "x = (Name == prev and 1 or 0) * (x or 0) + 1; prev = Name; return x" file1.csv | ^
qsv luau filter "tonumber(seq) == 1"

Actually for n=1 we could simplify it to:

qsv luau map seq "x = (Name == prev and 1 or 0); prev = Name; return x" file1.csv | ^
qsv luau filter "tonumber(seq) == 0" | ^
qsv select !seq

[jqnatividad]

Have you tried using a luau script? You can do pretty sophisticated map/reduce operations doing so.

https://github.com/dathere/qsv-lookup-tables

The same goes for SQL scripts - where instead of one superlong, complex SQL query - you can break it down to simpler queries, creating temporary tables along the way...

qsv/tests/test_sqlp.rs

Lines 823 to 911 in 0978b50

	#[test]
	fn sqlp_boston311_sql_script() {
	let wrk = Workdir::new("sqlp_boston311_sql_script");
	let test_file = wrk.load_test_file("boston311-100.csv");
	wrk.create_from_string(
	"test.sql",
	r#"create table temp_table as select * from "boston311-100" where ontime = 'OVERDUE';
	create table temp_table2 as select * from temp_table limit 10;
	-- we already got what we needed from temp_table into temp_table2
	-- so we can truncate temp_table. Otherwise, the memory taken by temp_table
	-- won't be released until the end of the script
	truncate temp_table;
	select ward,count(*) as cnt from temp_table2 group by ward order by cnt desc, ward asc;"#,
	);
	let mut cmd = wrk.command("sqlp");
	cmd.arg(&test_file).arg("test.sql");
	let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
	let expected = vec![
	svec!["ward", "cnt"],
	svec!["Ward 3", "2"],
	svec![" ", "1"],
	svec!["04", "1"],
	svec!["3", "1"],
	svec!["Ward 13", "1"],
	svec!["Ward 17", "1"],
	svec!["Ward 19", "1"],
	svec!["Ward 21", "1"],
	svec!["Ward 6", "1"],
	];
	assert_eq!(got, expected);
	}
	#[test]
	fn sqlp_boston311_sql_script_json() {
	let wrk = Workdir::new("sqlp_boston311_sql_script_json");
	let test_file = wrk.load_test_file("boston311-100.csv");
	wrk.create_from_string(
	"test.sql",
	r#"create table temp_table as select * from "boston311-100" where ontime = 'OVERDUE';
	create table temp_table2 as select * from temp_table limit 10;
	select ward,count(*) as cnt from temp_table2 group by ward order by cnt desc, ward asc;"#,
	);
	let mut cmd = wrk.command("sqlp");
	cmd.arg(&test_file)
	.arg("test.sql")
	.args(["--format", "json"]);
	let got: String = wrk.stdout(&mut cmd);
	let expected = r#"[{"ward":"Ward 3","cnt":2},{"ward":" ","cnt":1},{"ward":"04","cnt":1},{"ward":"3","cnt":1},{"ward":"Ward 13","cnt":1},{"ward":"Ward 17","cnt":1},{"ward":"Ward 19","cnt":1},{"ward":"Ward 21","cnt":1},{"ward":"Ward 6","cnt":1}]"#;
	assert_eq!(got, expected);
	}
	#[test]
	fn sqlp_boston311_sql_script_jsonl() {
	let wrk = Workdir::new("sqlp_boston311_sql_script_jsonl");
	let test_file = wrk.load_test_file("boston311-100.csv");
	wrk.create_from_string(
	"test.sql",
	r#"create table temp_table as select * from "boston311-100" where ontime = 'OVERDUE';
	create table temp_table2 as select * from temp_table limit 10;
	select ward,count(*) as cnt from temp_table2 group by ward order by cnt desc, ward asc;"#,
	);
	let mut cmd = wrk.command("sqlp");
	cmd.arg(&test_file)
	.arg("test.sql")
	.args(["--format", "jsonl"]);
	let got: String = wrk.stdout(&mut cmd);
	let expected = r#"{"ward":"Ward 3","cnt":2}
	{"ward":" ","cnt":1}
	{"ward":"04","cnt":1}
	{"ward":"3","cnt":1}
	{"ward":"Ward 13","cnt":1}
	{"ward":"Ward 17","cnt":1}
	{"ward":"Ward 19","cnt":1}
	{"ward":"Ward 21","cnt":1}
	{"ward":"Ward 6","cnt":1}"#;
	assert_eq!(got, expected);
	}

[ggrothendieck]

Thanks for your reply. I tried the table part of the example using the cmd line and got the code shown below. At any rate I will look closer at the material you mention although I think it is unlikely I will want to write significant lua scripts given that I know R. The main uses I would have for qsv involve speed and simplicity:

1. preprocess large data being read into R mainly to cut it down to something that will fit into memory. I have used xsv, gawk and other utilities but qsv may offer other possibilities for preprocessing.
2. communicate simple csv transformations to others who don't use R such as Excel users who would be more comfortable with just a single command or small pipeline of qsv commands. Key would be that it is compact and straight forward.

qsv apply operations trim Abbreviation us-states-example.csv | ^
qsv select 1,6 | ^
qsv rename State,Tax > us-states.csv

qsv joinp State data.csv State us-states.csv | ^
qsv luau map "AmountTax" "x = Amount * (1 + Tax / 100); return x" | ^
qsv luau map "cumBeforeTax" "x = (x or 0) + Amount; return x" | ^
qsv luau map "cumAfterTax" "x = (x or 0) + AmountTax; return x" | ^
qsv luau map "Amount with Tax" "string.format('%.2f', AmountTax)" | ^
qsv luau map "Running Total - Before Tax" "string.format('%.2f', cumBeforeTax)" | ^
qsv luau map "Running Total - After Tax" "string.format('%.2f', cumAfterTax)" | ^
qsv select !5-7 | ^
qsv table

[jqnatividad]

Wow! You're really exercising qsv there!

If it was me, I'd use a luau script so as not to repeatedly pay the startup tax with each separate qsv luau invocation if I really want to optimize for speed.

But your pipeline above is perfectly valid too.

[ggrothendieck]

One thing that this brought out is that if one does a luau map to a column name that exists it creates a second column of the same name. Is it the intention that the columns be immutable?

This is data.csv from your table lookup example.

qsv luau map State 3 data.csv

Note that it did not replace that data in the State column with 3 but created a second State column. The documentation does say that new columns are added after existing columns but it wasn't clear that doing this defines a new column. Is this intentional?

Order ID,Amount,State,State
20230301001,13.50,NJ,3
20230301002,15.00,NY,3
20230301003,72.00,TX,3
20230301004,12.00,CA,3
20230301005,33.70,DE,3

[jqnatividad]

Yes. That was intentional. I should take another pass at improving the luau documentation...

Also, I should check for duplicate column names when mapping, perhaps adding a sequence suffix in the event of a name collision, i.e.

Order ID,Amount,State,State1

WDYT?

[ondohotola]

I usually map to uppercase column names, which I then select and rename, so I never noticed.

[ggrothendieck]

Here is the us-states-example.csv example table slightly improved.

• join trims whitespace so we moved from joinp to join eliminating the separate command to trim whitespace
• that implied that we could combine the two pipelines into one
• in one case return was used unnecessarily
• used column indexing to avoid problems with some names having spaces

To get the column indexes qsv headers was used but unexpectedly it does not default to stdin and one must specify - . (If you are using numeric column indexing a typical action would presumably be to pipe the result to qsv headers before writing the qsv command in the pipeline that uses such indexes.)

qsv join State data.csv Abbreviation us-states-example.csv | ^
qsv luau map --colindex AmountTax "Amount * (1 + col[9] / 100)" | ^
qsv luau map "cumBeforeTax" "x = (x or 0) + Amount; return x" | ^
qsv luau map "cumAfterTax" "x = (x or 0) + AmountTax; return x" | ^
qsv luau map --colindex "Rate (%)" "col[9]" | ^
qsv luau map "Amount with Tax" "string.format('%.2f', AmountTax)" | ^
qsv luau map "Running Total - Before Tax" "string.format('%.2f', cumBeforeTax)" | ^
qsv luau map "Running Total - After Tax" "string.format('%.2f', cumAfterTax)" | ^
qsv select !4-12 | ^
qsv table

[ggrothendieck]

Regarding your comment about adding sequence numbers to the ends of duplicate column names in luau map I think I would have expected that if you assign to a column name that already exists then that column would be overwritten with the new values just like in most programming languages where assigning to a variable that already exists clobbers it.

[jqnatividad]

Yep, that sounds sensible. Will look into modifying the behavior accordingly...

[ggrothendieck]

In looking at the us-states example more it seems that the key complication is calculating the cumulative sums. Not only is the calculation itself somewhat complex but the need for a separate line of code to format it seems unfortunate. If there were a luau function to perform a cumulative sum the code would be a bit shorter and also simpler. We show what it would like below. This is not executable because the cumsum function does not currently exist.

qsv join State data.csv Abbreviation us-states-example.csv | ^
qsv luau map --colindex "Rate (%)" "col[9]"
qsv luau map --colindex "Amount with Tax" "string.format('%.2f', Amount*(1+col[9]/100))" | ^
qsv luau map "Running Total - Before Tax" "string.format('%.2f', cumsum(Amount))" | ^
qsv luau map --colindex "Running Total - After Tax" "string.format('%.2f',cumsum(Amount*(1+col[9]/100))" | ^
qsv select !4-12 | ^
qsv table

[jqnatividad]

cumsum would indeed be a good addition to the built-in qsv helper functions. To be consistent with the other functions, I'll name it qsv_cumsum.

[ggrothendieck]

If you are implementing cumsum there are a number of other related functions that have proven to be useful in other languages which follow a similar pattern and so could be readily implemented at the same time.

cumprod, cumany, cumall, cummax, cummin
These are like cumsum but in place of + they use *, or, and, max amd min. cummean is also useful but does not fit exactly into the same pattern.

accumulate
Has three arguments. A column, a function of two arguments and an optional initial value. If   y = accum(x, f, init)then y[1] = init and for i >1 we have y[i] = f(y[i-1], x[i]). The default for init is x[1]. Note that if f is +, *, or, and, max or min we get the above cum... functions.

Other

The following are also useful and are related in so far as they also involve storing the previous value.

lag
It has three arguments. The column, how many positions to lag (default is 1) and in the case that the lag is off the front of the column use default. If y = lag(x, k, default) then y[i] = x[i-k] if i > k and default otherwise. Negative k could be considered too if not too hard to implement.
Recall we discussed enum by group with the shortest solution being the following where Name is the column to group by:

qsv luau map seq "x = (Name == prev and 1 or 0) * (x or 0) + 1; prev = Name; return x" file1.csv

With lag we could omit setting prev

qsv luau map seq "x = (Name == lag(Name, 1) and 1 or 0) * (x or 0) + 1; return x" file1.csv

diff
Same args as lag. Defined as x - lag(x, k, default)

[jqnatividad]

Thanks @ggrothendieck for this great, detailed enhancement request. Converted it to an issue and added it to the backlog!

#1782