Another SQLP question - row_number()

[ondohotola]

As part of m current little project (see #2425) I had to resort to sqlite3 where I can do something like

-- Create the table in memory
CREATE TABLE data (FQDN TEXT, HOST TEXT, ns TEXT, ip TEXT);

-- Import the CSV into the table
.mode csv
.import '$CSVFILE' data

-- Assign unique indices to each ip for every FQDN
CREATE TABLE numbered AS
SELECT 
    FQDN,
    HOST || '|' || ip AS host_ip,
    ip,
    (SELECT COUNT(*) 
     FROM data d2 
     WHERE d2.FQDN = d1.FQDN AND d2.rowid <= d1.rowid) AS ip_index
FROM data d1;

-- Turn headers back on for clean output
.headers on
.mode csv
.nullvalue ''

-- Pivot the data into 12 columns, using NS1-NS12 as column names
SELECT 
    FQDN,
    MAX(CASE WHEN ip_index = 1 THEN host_ip END) AS NS1,
    MAX(CASE WHEN ip_index = 2 THEN host_ip END) AS NS2,
    MAX(CASE WHEN ip_index = 3 THEN host_ip END) AS NS3,
    MAX(CASE WHEN ip_index = 4 THEN host_ip END) AS NS4,
    MAX(CASE WHEN ip_index = 5 THEN host_ip END) AS NS5,
    MAX(CASE WHEN ip_index = 6 THEN host_ip END) AS NS6,
    MAX(CASE WHEN ip_index = 7 THEN host_ip END) AS NS7,
    MAX(CASE WHEN ip_index = 8 THEN host_ip END) AS NS8,
    MAX(CASE WHEN ip_index = 9 THEN host_ip END) AS NS9,
    MAX(CASE WHEN ip_index = 10 THEN host_ip END) AS NS10,
    MAX(CASE WHEN ip_index = 11 THEN host_ip END) AS NS11,
    MAX(CASE WHEN ip_index = 12 THEN host_ip END) AS NS12
FROM numbered
WHERE FQDN != 'FQDN'
GROUP BY FQDN;

When I issue qsv sqlp data.csv t.sql with t.sql having:

CREATE TABLE numbered AS
SELECT 
    FQDN,
    HOST || '|' || ip AS host_ip,
    ip,
    (SELECT COUNT(*) 
     FROM data d2 
     WHERE d2.FQDN = d1.FQDN AND d2.rowid <= d1.rowid) AS ip_index
FROM data d1;

I get this error message

Failed to execute query: CREATE TABLE numbered AS
SELECT
    FQDN,
    HOST || '|' || ip AS host_ip,
    ip,
    (SELECT COUNT(*)
     FROM data d2
     WHERE d2.FQDN = d1.FQDN AND d2.rowid <= d1.rowid) AS ip_index
FROM data d1: unexpected subquery

[ondohotola]

Switched to duckDB, and ran this query (the bash variables equate to CSV files>

COPY(
   SELECT fqdn, 
   MAX(CASE WHEN row_num = 1 THEN host END) AS NS1,
   MAX(CASE WHEN row_num = 2 THEN host END) AS NS2, 
   MAX(CASE WHEN row_num = 3 THEN host END) AS NS3, 
   MAX(CASE WHEN row_num = 4 THEN host END) AS NS4,
   MAX(CASE WHEN row_num = 5 THEN host END) AS NS5,
   MAX(CASE WHEN row_num = 6 THEN host END) AS NS6,
   MAX(CASE WHEN row_num = 7 THEN host END) AS NS7,
   MAX(CASE WHEN row_num = 8 THEN host END) AS NS8,
   MAX(CASE WHEN row_num = 9 THEN host END) AS NS9,
   MAX(CASE WHEN row_num = 10 THEN host END) AS NS10,
   MAX(CASE WHEN row_num = 11 THEN host END) AS NS11,
   MAX(CASE WHEN row_num = 12 THEN host END) AS NS12,
   FROM 
   (
      SELECT fqdn, host, ROW_NUMBER() 
      OVER (PARTITION BY fqdn ORDER BY host) AS row_num 
      from '$GLUEIN'
   ) Z
   group by Z.fqdn
   order by Z.fqdn
) TO '$GLUEOUT' (HEADER, DELIMITER ',');

works great but when I tried to run it (sans the COPY of course) under qsv sqlp it tells me it doesn't have a ROW_NUMBER() feature.

Is there a way around that, at present?

I really would love to see how fast this can get :-)-O

[jqnatividad]

I'm afraid ROW_NUMBER() is still in Polar's SQL interface backlog

pola-rs/polars#16968

I can imagine using a combination of the partition and enum commands to prepare the equivalent of your subselect, and then taking advantage of sqlp's script capabilities.

But TBH, that's too much work and if duckDB is working already, I'd stay with it and use qsv for other data-wrangling that duckDB is not able to do.

That's why composability is one of qsv's goals. 😉

[ondohotola]

It does what it does for my little project faster than weasel droppings sliding of a teflon coated shovel :-)-O

I am just trying to see how fast it can possibly be :-)-O

I commented in the polars issue, so I get an email if it’s ever implememted :-)-O

Tahnk you so very much

[jqnatividad]

I converted the issue to a discussion so other users can find it more easily.

Hope you don't mind @ondohotola .

[ondohotola]

No, not at all.

[alexander-beedie]

Coming soon: ROW_NUMBER, RANK, and DENSE_RANK 😉
pola-rs/polars#25409

[jqnatividad]

And its now available!
#3115

qsv/tests/test_sqlp.rs

Lines 3827 to 3917 in 0e05622

	fn sqlp_rank_funcs() {
	let wrk = Workdir::new("sqlp_rank_funcs");
	wrk.create(
	"data.csv",
	vec![
	svec!["id", "category", "value"],
	svec!["1", "A", "20"],
	svec!["2", "A", "10"],
	svec!["3", "A", "25"],
	svec!["4", "B", "10"],
	svec!["5", "B", "40"],
	svec!["6", "B", "25"],
	svec!["7", "C", "35"],
	],
	);
	let mut cmd = wrk.command("sqlp");
	cmd.arg("data.csv").arg(
	r#"SELECT
	value,
	ROW_NUMBER() OVER (ORDER BY value DESC, category DESC) AS row_num,
	RANK() OVER (ORDER BY value DESC, category DESC) AS rank,
	DENSE_RANK() OVER (ORDER BY value DESC, category DESC) AS dense_rank
	FROM data
	ORDER BY value, id DESC"#,
	);
	wrk.assert_success(&mut cmd);
	let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
	let expected = vec![
	svec!["value", "row_num", "rank", "dense_rank"],
	svec!["10", "6", "6", "6"],
	svec!["10", "7", "7", "7"],
	svec!["20", "5", "5", "5"],
	svec!["25", "3", "3", "3"],
	svec!["25", "4", "4", "4"],
	svec!["35", "2", "2", "2"],
	svec!["40", "1", "1", "1"],
	];
	assert_eq!(got, expected);
	let mut cmd = wrk.command("sqlp");
	cmd.arg("data.csv").arg(
	r#"SELECT
	value,
	ROW_NUMBER() OVER (ORDER BY value) AS row_num,
	RANK() OVER (ORDER BY value) AS rank,
	DENSE_RANK() OVER (ORDER BY value) AS dense_rank
	FROM data
	ORDER BY value, id"#,
	);
	wrk.assert_success(&mut cmd);
	let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
	let expected = vec![
	svec!["value", "row_num", "rank", "dense_rank"],
	svec!["10", "1", "1", "1"],
	svec!["10", "2", "1", "1"],
	svec!["20", "3", "3", "2"],
	svec!["25", "4", "4", "3"],
	svec!["25", "5", "4", "3"],
	svec!["35", "6", "6", "4"],
	svec!["40", "7", "7", "5"],
	];
	assert_eq!(got, expected);
	let mut cmd = wrk.command("sqlp");
	cmd.arg("data.csv").arg(
	r#"SELECT
	category,
	value,
	ROW_NUMBER() OVER (PARTITION BY category ORDER BY value) AS row_num,
	RANK() OVER (PARTITION BY category ORDER BY value) AS rank,
	DENSE_RANK() OVER (PARTITION BY category ORDER BY value) AS dense
	FROM data
	ORDER BY category, value"#,
	);
	wrk.assert_success(&mut cmd);
	let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
	let expected = vec![
	svec!["category", "value", "row_num", "rank", "dense"],
	svec!["A", "10", "1", "1", "1"],
	svec!["A", "20", "2", "2", "2"],
	svec!["A", "25", "3", "3", "3"],
	svec!["B", "10", "1", "1", "1"],
	svec!["B", "25", "2", "2", "2"],
	svec!["B", "40", "3", "3", "3"],
	svec!["C", "35", "1", "1", "1"],
	];
	assert_eq!(got, expected);
	}

Thanks @alexander-beedie!