amazon redshift - Recursive SQL not returning full scope of data

I have a logic to calculate number of patients and med dosage per period, however my redhsift recursive SQL is not returning full scope of data:

WITH recursive build (period_start_date, peq, prev_peq, repeated_patient, cur_rn, max_rn) AS 
(
    SELECT 
        period_start_date,
        ROUND(peq, 1) peq,
        ROUND(CAST(prev_peq AS FLOAT), 1) prev_peq,
        ROUND(CAST(repeated_patient AS FLOAT), 1) repeated_patient,
        cur_rn,
        max_rn
    FROM  
        (SELECT  
             period_start_date,
             peq,
             CAST(prev_peq AS FLOAT),
             CAST(repeated_patient AS FLOAT),
             ROW_NUMBER() OVER (ORDER BY period_start_date) AS rn,
             2::INT             AS cur_rn ,
             COUNT(1) OVER ()   AS max_rn
         FROM  
             (SELECT  
                  period_start_date,
                  ROUND(1.0 * su_value / 6, 1)::float peq,
                  LAG(ROUND(1.0 * su_value / 6, 1), 1) OVER (ORDER BY period_start_date)::float prev_peq,
                  CAST(0 AS FLOAT) AS repeated_patient
              FROM  testoss 
            )
        WHERE  peq != prev_peq 
      )
   WHERE rn = 1 
   UNION ALL
   SELECT b.period_start_date,
     round(b.peq, 1),
     round(b.prev_peq, 1),
     round(cast(
     CASE
        WHEN b.prev_peq - nvl(t.repeated_patient,0) > b.peq THEN b.peq
        ELSE b.prev_peq - nvl(t.repeated_patient,0)
     END AS FLOAT), 1) AS repeated_patient,
     b.cur_rn + 1  AS cur_rn,
     b.max_rn
   FROM  build b
   join
     (
        SELECT  period_start_date,
           peq,
           prev_peq,
           repeated_patient,
           lag(period_start_date) over(ORDER BY period_start_date)  prev_period,
           row_number() over (ORDER BY period_start_date)    AS rn
        FROM  (
              SELECT  period_start_date,
                 round(1.0*su_value /6, 1)::float                peq,
                 lag(round(1.0*su_value/6,1 ),1) over (ORDER BY period_start_date )::float  prev_peq,
                 cast(0 AS FLOAT)                  AS repeated_patient
              FROM  testoss )
      ) t
   ON  t.prev_period = b.period_start_date
   WHERE t.rn = b.cur_rn
   AND  b.cur_rn <= b.max_rn
)
SELECT period_start_date,
   peq,
   prev_peq,
   repeated_patient
FROM  build;

it returns only one row of one period

instead of full scope of periods

This is the input test data:

CREATE TABLE TESTOSS
   (  PERIOD_START_DATE date, 
    SU_VALUE integer
   );

Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-SEP-17','DD-MON-YY'),69);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-OCT-17','DD-MON-YY'),263);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-NOV-17','DD-MON-YY'),684);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-DEC-17','DD-MON-YY'),938);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JAN-18','DD-MON-YY'),1352);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-FEB-18','DD-MON-YY'),1174);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-MAR-18','DD-MON-YY'),1123);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-APR-18','DD-MON-YY'),1649);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-MAY-18','DD-MON-YY'),1402);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JUN-18','DD-MON-YY'),1548);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JUL-18','DD-MON-YY'),1448);
commit;

Could you please help me to correct this query?

I have a logic to calculate number of patients and med dosage per period, however my redhsift recursive SQL is not returning full scope of data:

WITH recursive build (period_start_date, peq, prev_peq, repeated_patient, cur_rn, max_rn) AS 
(
    SELECT 
        period_start_date,
        ROUND(peq, 1) peq,
        ROUND(CAST(prev_peq AS FLOAT), 1) prev_peq,
        ROUND(CAST(repeated_patient AS FLOAT), 1) repeated_patient,
        cur_rn,
        max_rn
    FROM  
        (SELECT  
             period_start_date,
             peq,
             CAST(prev_peq AS FLOAT),
             CAST(repeated_patient AS FLOAT),
             ROW_NUMBER() OVER (ORDER BY period_start_date) AS rn,
             2::INT             AS cur_rn ,
             COUNT(1) OVER ()   AS max_rn
         FROM  
             (SELECT  
                  period_start_date,
                  ROUND(1.0 * su_value / 6, 1)::float peq,
                  LAG(ROUND(1.0 * su_value / 6, 1), 1) OVER (ORDER BY period_start_date)::float prev_peq,
                  CAST(0 AS FLOAT) AS repeated_patient
              FROM  testoss 
            )
        WHERE  peq != prev_peq 
      )
   WHERE rn = 1 
   UNION ALL
   SELECT b.period_start_date,
     round(b.peq, 1),
     round(b.prev_peq, 1),
     round(cast(
     CASE
        WHEN b.prev_peq - nvl(t.repeated_patient,0) > b.peq THEN b.peq
        ELSE b.prev_peq - nvl(t.repeated_patient,0)
     END AS FLOAT), 1) AS repeated_patient,
     b.cur_rn + 1  AS cur_rn,
     b.max_rn
   FROM  build b
   join
     (
        SELECT  period_start_date,
           peq,
           prev_peq,
           repeated_patient,
           lag(period_start_date) over(ORDER BY period_start_date)  prev_period,
           row_number() over (ORDER BY period_start_date)    AS rn
        FROM  (
              SELECT  period_start_date,
                 round(1.0*su_value /6, 1)::float                peq,
                 lag(round(1.0*su_value/6,1 ),1) over (ORDER BY period_start_date )::float  prev_peq,
                 cast(0 AS FLOAT)                  AS repeated_patient
              FROM  testoss )
      ) t
   ON  t.prev_period = b.period_start_date
   WHERE t.rn = b.cur_rn
   AND  b.cur_rn <= b.max_rn
)
SELECT period_start_date,
   peq,
   prev_peq,
   repeated_patient
FROM  build;

it returns only one row of one period

instead of full scope of periods

This is the input test data:

CREATE TABLE TESTOSS
   (  PERIOD_START_DATE date, 
    SU_VALUE integer
   );

Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-SEP-17','DD-MON-YY'),69);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-OCT-17','DD-MON-YY'),263);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-NOV-17','DD-MON-YY'),684);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-DEC-17','DD-MON-YY'),938);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JAN-18','DD-MON-YY'),1352);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-FEB-18','DD-MON-YY'),1174);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-MAR-18','DD-MON-YY'),1123);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-APR-18','DD-MON-YY'),1649);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-MAY-18','DD-MON-YY'),1402);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JUN-18','DD-MON-YY'),1548);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JUL-18','DD-MON-YY'),1448);
commit;

Could you please help me to correct this query?

Share Improve this question edited Nov 27, 2024 at 21:35 marc_s 756k184 gold badges1.4k silver badges1.5k bronze badges asked Nov 27, 2024 at 21:29 Kondjitsu 211 gold badge1 silver badge6 bronze badges

The output you have shown is it the whole output? – samhita Commented Nov 27, 2024 at 22:00
What is the expected output? – samhita Commented Nov 27, 2024 at 22:17

Add a comment |

1 Answer 1

Sorted by: Reset to default 0

Your code fetches just one row because you put the where clause condition to WHERE rn = 1 (Line: 29).
There are some other issues with the code too, and there is no explanation about the logic for the column repeated_patient.

--      S a m p l e    D a t a :  
CREATE TABLE TESTOSS ( PERIOD_START_DATE date, SU_VALUE integer );

Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-SEP-17','DD-MON-YY'),69);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-OCT-17','DD-MON-YY'),263);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-NOV-17','DD-MON-YY'),684);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-DEC-17','DD-MON-YY'),938);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JAN-18','DD-MON-YY'),1352);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-FEB-18','DD-MON-YY'),1174);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-MAR-18','DD-MON-YY'),1123);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-APR-18','DD-MON-YY'),1649);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-MAY-18','DD-MON-YY'),1402);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JUN-18','DD-MON-YY'),1548);
Insert into TESTOSS (PERIOD_START_DATE,SU_VALUE) values (to_date('01-JUL-18','DD-MON-YY'),1448);

... the way I understood your question, one of the options is to create a cte with basic calculated data and later self join the cte by previous month/date and use case expressions to fetch what you want.

WITH 
  grid AS
    ( Select   period_start_date, 
               Row_Number() Over(Partition By period_start_date) as rn_month, 
               Count(1) Over(Partition By period_start_date) as cnt_month,
               su_value, 
               Round(1.00 * su_value / 6, 2) as peq, 
               period_start_date - INTERVAL '1' Month as prev_peq_date
      From     TESTOSS
    )

--      M a i n    S Q L : 
SELECT period_start_date, peq, prev_peq, 
       Case When prev_peq > Coalesce(g2_next_peq, 0.00)
            Then prev_peq - Coalesce(g2_next_peq, 0.00)
       Else 0.00
       End as repeated_patient    -- this column is most likely wrong and you should adjust it ...
FROM (  Select      g.period_start_date, g.peq, 
                    Case When g.rn_month = g.cnt_month 
                         Then Round(Coalesce(g2.peq, 0.0), 1) 
                    Else 0.0 
                    End as prev_peq, 
                        LAG(g2.peq) Over(Order By g.period_start_date, g.rn_month Desc) as g2_next_peq
        From        grid g
        Left Join   grid g2   ON(   g2.period_start_date = g.prev_peq_date And 
                                    g2.rn_month = 1 And       -- joining first row of prev month
                                    g.rn_month = g.cnt_month  -- with last row of actual month (in case that is needed as your expected result shows multiple rows per month)
                                )
        Order By g.period_start_date, g.rn_month
     )

R e s u l t :

period_start_date	peq	prev_peq	repeated_patient
2017-09-01	11.50	0.0	0.00
2017-10-01	43.83	11.5	11.50
2017-11-01	114.00	43.8	32.30
2017-12-01	156.33	114.0	70.17
2018-01-01	225.33	156.3	42.30
2018-02-01	195.67	225.3	68.97
2018-03-01	187.17	195.7	0.00
2018-04-01	274.83	187.2	0.00
2018-05-01	233.67	274.8	87.63
2018-06-01	258.00	233.7	0.00
2018-07-01	241.33	258.0	24.33

fiddle

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

amazon redshift - Recursive SQL not returning full scope of data - Stack Overflow

1 Answer 1

与本文相关的文章

评论列表(0)