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 <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of no...@null.net >Sent: Sunday, 9 February, 2020 18:26 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >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 >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users