On Mon Feb 10, 2020 at 12:24:33AM +0000, Bart Smissaert wrote:
> I should get:
>
> 127/81
> 132/82
> 141/85
> 143/94
>
> What should be the SQL to group like this?
Here is one way that appears to generate the correct result.
CREATE TABLE pressure(
id INTEGER PRIMARY KEY,
userid INTEGER NOT NULL,
date TEXT NOT NULL,
term text NOT NULL,
reading INTEGER NOT NULL
);
INSERT INTO
pressure(userid,date,term,reading,id)
VALUES
(1308,'15/Mar/2013','Systolic',127,701559),
(1308,'15/Mar/2013','Diastolic',81,701568),
(1308,'27/Jun/2013','Systolic',132,701562),
(1308,'27/Jun/2013','Systolic',141,701563),
(1308,'27/Jun/2013','Systolic',143,701564),
(1308,'27/Jun/2013','Diastolic',82,701571),
(1308,'27/Jun/2013','Diastolic',85,701572),
(1308,'27/Jun/2013','Diastolic',94,701573),
(278975701,'08/Mar/2018','Systolic',136,1583551),
(278975701,'08/Mar/2018','Diastolic',99,1583591),
(278975701,'04/Apr/2018','Systolic',119,1583552),
(278975701,'04/Apr/2018','Systolic',124,1583553),
(278975701,'04/Apr/2018','Systolic',130,1583554),
(278975701,'04/Apr/2018','Diastolic',74,1583592),
(278975701,'04/Apr/2018','Diastolic',75,1583593),
(278975701,'04/Apr/2018','Diastolic',85,1583594);
WITH
systolic AS (
SELECT
row_number() OVER (order by id) AS row,
*
FROM
pressure
WHERE
term='Systolic'
),
diastolic AS (
SELECT
row_number() OVER (order by id) AS row,
*
FROM
pressure
WHERE
term='Diastolic'
)
SELECT
d.userid AS User,
d.date AS Date,
s.reading || '/' || d.reading AS Reading
FROM
systolic s
LEFT JOIN
diastolic d
ON
d.row=s.row
ORDER BY
d.userid,
d.date,
d.id
;
I'm sure others will produce more elegant solutions - I was just using
your question as an exercise to learn about window functions. The query
plan of the above looks a little scary though:
id parent notused detail
2 0 0 CO-ROUTINE 1
9 2 0 CO-ROUTINE 4
12 9 0 SCAN TABLE pressure
26 2 0 SCAN SUBQUERY 4
72 0 0 MATERIALIZE 2
79 72 0 CO-ROUTINE 5
82 79 0 SCAN TABLE pressure
97 72 0 SCAN SUBQUERY 5
144 0 0 SCAN SUBQUERY 1 AS s
159 0 0 SEARCH SUBQUERY 2 AS d USING AUTOMATIC COVERING INDEX
(row=?)
180 0 0 USE TEMP B-TREE FOR ORDER BY
Presumably if you index the date/userid and filter the CTE tables it
gets a little better.
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
