Snowflake中替代RANGE BETWEEN INTERVAL的区间交易计数方案问询

需求说明 #

需要为每条交易记录计算Desired_Output列，规则为：同一客户，当前交易时间往前推2天（含当前时间）范围内的所有交易数量。示例中第一条记录的12，就是客户100在3/13至3/15（含）期间的所有交易总数。

示例数据 #

Customerid  txn_timestamp   txn_date    txn_id  Desired_Output
100     3/15/24 5:00 PM     15-Mar      100     12
100     3/15/24 4:00 PM     15-Mar      200     11
100     3/15/24 3:00 PM     15-Mar      300     10
100     3/15/24 2:00 PM     15-Mar      400      9
100     3/14/24 5:00 PM     14-Mar      500     10
100     3/14/24 4:00 PM     14-Mar      600      9
100     3/14/24 3:00 PM     14-Mar      700      8
100     3/14/24 2:00 PM     14-Mar      800      7
100     3/14/24 1:00 PM     14-Mar      900      6
100     3/14/24 12:00 PM    14-Mar      1000     5
100     3/13/24 5:00 PM     13-Mar      1100     7
100     3/13/24 4:00 PM     13-Mar      1200     6
100     3/12/24 5:00 PM     12-Mar      1300    
100     3/12/24 4:00 PM     12-Mar      1400    
100     3/11/24 5:00 PM     11-Mar      1500    
100     3/11/24 4:00 PM     11-Mar      1600    
100     3/11/24 3:00 PM     11-Mar      1700

已尝试的失效方法 #

• 窗口函数写法（Snowflake中未生效）：

  count(txn_id) over (partition by Customerid order by txn_timestamp range between interval '2' DAY PRECEDING and CURRENT ROW) as calc
  

• 自连接子查询（字段名不匹配、逻辑有疏漏）：

  select customerid, tran_id, count(*) 
  from ( 
     Select a.* from tbl1 a 
     join tbl1 b 
         on a.customerid = b.customerid 
       and (b.tran_tms between date(a.txn_tms)-2 and a.txn_tms) 
     )
  group by 1,2
  

有效解决方案 #

方案1：修正窗口函数写法 #

确保txn_timestamp为TIMESTAMP类型（若为字符串需先转换），使用符合Snowflake语法的窗口函数：

SELECT
    Customerid,
    txn_timestamp,
    txn_date,
    txn_id,
    COUNT(txn_id) OVER (
        PARTITION BY Customerid
        ORDER BY txn_timestamp
        RANGE BETWEEN INTERVAL '2 DAY' PRECEDING AND CURRENT ROW
    ) AS Desired_Output
FROM tbl1
ORDER BY txn_timestamp DESC;

若txn_timestamp是字符串类型，需先转换：TO_TIMESTAMP(txn_timestamp, 'MM/DD/YY HH:MI PM')

方案2：修正自连接逻辑 #

通过左连接匹配时间范围内的交易，统计计数：

SELECT
    a.Customerid,
    a.txn_timestamp,
    a.txn_date,
    a.txn_id,
    COUNT(b.txn_id) AS Desired_Output
FROM tbl1 a
LEFT JOIN tbl1 b
    ON a.Customerid = b.Customerid
    AND b.txn_timestamp BETWEEN DATEADD(DAY, -2, a.txn_timestamp) AND a.txn_timestamp
GROUP BY a.Customerid, a.txn_timestamp, a.txn_date, a.txn_id
ORDER BY a.txn_timestamp DESC;

该方法兼容性强，无需依赖窗口函数的RANGE INTERVAL特性，适合复杂时间范围场景。

内容的提问来源于stack exchange，提问作者sparkstars