Re: How to group this?

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