CODEWITHSUNDEEP
sqloracle-databasewindow-functions
Dizzy2510
Dizzy2510

Reputation: 47

SQL Window Functions - filter by specific conditions possible?

I want to count the number of records partitioned by a group and ordered by a date but with certains conditions, in this case that two column values must both be higher than the current row compared to previous rows.

    (
select 'a' grouper,5 num1,55 num2,to_date('01.01.2022','DD.MM.YYYY') as dt,null as expected_res from dual union all
select 'a' grouper,3 num1,40 num2,to_date('02.01.2022','DD.MM.YYYY') as dt,1 as expected_res from dual union all
select 'a' grouper,4 num1,100 num2,to_date('03.01.2022','DD.MM.YYYY') as dt,null as expected_res from dual
    )
select base.* from base order by grouper,dt;
grouper num1 num2 dt expected
a 5 55 01.01.2022 null (For this partition there is now previous record order by date)
a 3 40 02.01.2022 1 (For this partition there is a previous record and both num1 and num2 are eacher higher than those of this row
a 4 100 03.01.2022 null (For this partition there are two previous records but in the previous row [row2] both nums are lower so i dont want to count this row and for the first row num1 is higher than num1 of this row but num2 is lower, so i dont want to count it

I know that i can use a corrected subquery for this problem but the performance will be bad due to high amount of data. Is this problem solvable by using window functions?

I would need something like this:

count(*) over (partition by grouper order by dt asc rows between unbounded preceding and 1 preceding where num1 AND num2 in window > num1 AND num2 of current row

Upvotes: 0

Views: 402

Answers (1)

d r
d r

Reputation: 7786

Maybe you could try to use MODEL clause. It is supposed to be fast.

WITH      -- Sample data
    base AS
        (
            Select 'a' "GROUPER", 5 "NUM1",  55 "NUM2", To_Date('01.01.2022','DD.MM.YYYY') "DT" From Dual Union All
            Select 'a',           3,         40,        To_Date('02.01.2022','DD.MM.YYYY')      From Dual Union All
            Select 'a',           4,        100,        To_Date('03.01.2022','DD.MM.YYYY')      From Dual
        )

M a i n   S Q L
Select GROUPER, NUM1, NUM2, DT, EXPECTED_RESULT  
From 
    ( Select  ROWNUM "RN",GROUPER, NUM1, NUM2, DT, 0 "EXPECTED_RESULT"
      From    base
      Order By DT 
    )
    MODEL   Partition By (GROUPER)
            Dimension By (RN)
            Measures (NUM1, NUM2, DT,EXPECTED_RESULT)
    RULES   (   EXPECTED_RESULT[ANY] = CASE WHEN CV(RN) = 1 THEN Null 
                                       ELSE 
                                          CASE WHEN NUM1[CV() - 1] > NUM1[CV()] And NUM2[CV() - 1] > NUM2[CV()] THEN 1 END
                                       END 
            )

R e s u l t :
GROUPER       NUM1       NUM2 DT        EXPECTED_RESULT
------- ---------- ---------- --------- ---------------
a                5         55 01-JAN-22                 
a                3         40 02-JAN-22               1 
a                4        100 03-JAN-22

Updated:
The above code compares values with 1 previous row only. If you want to compare values in current row with any of the previous rows and count those that has both values greater than in current row - check the code below. Notice that the sample data is changed a bit.

WITH
    base AS
        (
            Select 'a' "GROUPER", 5 "NUM1",  57 "NUM2", To_Date('01.01.2022','DD.MM.YYYY') "DT" From Dual Union All
            Select 'a',           3,         56,        To_Date('02.01.2022','DD.MM.YYYY')      From Dual Union All
            Select 'a',           2,         51,        To_Date('03.01.2022','DD.MM.YYYY')      From Dual Union All
            Select 'a',           3,         50,        To_Date('04.01.2022','DD.MM.YYYY')      From Dual Union All
            Select 'a',           4,         55,        To_Date('05.01.2022','DD.MM.YYYY')      From Dual
        )

Select GROUPER, NUM1, NUM2, DT, EXPECTED_RESULT
From ( Select  ROWNUM "RN", GROUPER, NUM1, NUM2, DT, 0 "EXPECTED_RESULT"
      From    base
      Order By DT 
     )
    MODEL   Partition By (GROUPER)
            Dimension By (RN, NUM1, NUM2)
            Measures (DT, EXPECTED_RESULT)
    RULES   (   EXPECTED_RESULT[ANY, ANY, ANY] = Count(DT)[RN < CV(RN), NUM1 > CV(NUM1), NUM2 > CV(NUM2) ]  )

GROUPER       NUM1       NUM2 DT        EXPECTED_RESULT
------- ---------- ---------- --------- ---------------
a                5         57 01-JAN-22               0 
a                3         56 02-JAN-22               1 
a                2         51 03-JAN-22               2 
a                3         50 04-JAN-22               1 
a                4         55 05-JAN-22               1

Upvotes: 1

Related Questions