Alternatives to sliding window frames with RANGE on snowflake

user_14415185 0
Viewed 1143

I'm migrate some BigQuery code to snowflake, and I'm having a little trouble to using Window function with range. This is because on Snowflake range sliding window frames is not supported. I need to use "RANGE BETWEEN 2 PRECEDING AND CURRENT ROW" logic any thoughts on how we can replicate this logic on snowflake?

WITH base AS (
  SELECT * FROM (
    SELECT 7 AS num
    UNION ALL SELECT 2 AS num
    UNION ALL SELECT 2 AS num
    UNION ALL SELECT 5 AS num
    UNION ALL SELECT 3 AS num
    UNION ALL SELECT 7 AS num
    UNION ALL SELECT 7 AS num
))
SELECT
num,
COUNT(*) OVER (ORDER BY num ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS row_count,
COUNT(*) OVER (ORDER BY num RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS range_count,
SUM(num) OVER (ORDER BY num ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS row_sum,
SUM(num) OVER (ORDER BY num RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS range_sum
FROM base
ORDER BY num

Expecting result:enter image description here

snowflake-cloud-data-platform sql
1 Answers

Taking the specific question from the comments: You can solve this with a sub-query.

For example, here I will find the MIN(previous value) for each row, looking back 365 days, and partitioning by customer:

with DATA as (
  select *
  from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
)

select O_CUSTKEY, O_ORDERDATE, O_TOTALPRICE
  , (
    select min(O_TOTALPRICE) 
    from DATA 
    where A.O_CUSTKEY=O_CUSTKEY
    and O_ORDERDATE between A.O_ORDERDATE-365 and A.O_ORDERDATE
  ) min_value
from DATA A
order by O_CUSTKEY, O_ORDERDATE
limit 10;

enter image description here

user_1324380
Related