sql - 雪花上带有 RANGE 的滑动窗框的替代品

Question

我正在将一些 BigQuery 代码迁移到 snowflake，但在使用带范围的 Window 函数时遇到了一些麻烦。这是因为在 Snowflake 范围内不支持滑动窗口框架。我需要使用“RANGE BETWEEN 2 PRECEDING AND CURRENT ROW”逻辑关于我们如何在雪花上复制这种逻辑有什么想法吗？

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

预期结果:enter image description here

Answer 1

从评论中提取具体问题:您可以使用子查询解决此问题。

例如，这里我将查找每一行的 MIN(先前值)，回顾 365 天，并按客户进行分区:

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;