That's good, but this not screw up later userid/date if an entry is AWOL.
WITH systolic
AS (
select userid,
date,
rank() over (partition by userid, date order by id) as rank,
reading
from pressure
where term == 'Systolic'
),
diastolic
AS (
select userid,
date,
rank() over (partition by userid, date order by id) as rank,
reading
from pressure
where term == 'Diastolic'
)
SELECT d.userid,
d.date,
s.reading || '/' || d.reading AS Reading
FROM systolic s
JOIN diastolic d
ON d.userid == s.userid
AND d.date == s.date
AND d.rank == s.rank
;
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <[email protected]> On
>Behalf Of [email protected]
>Sent: Sunday, 9 February, 2020 18:26
>To: SQLite mailing list <[email protected]>
>Subject: Re: [sqlite] How to group this?
>
>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
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
