为何两个窗口函数返回不同排序结果？Snowflake SQL问题排查

1. 行号不符合预期的原因 #

你的ROW_NUMBER窗口函数仅按txn_date DESC排序，但同一日期的多条记录没有指定额外排序规则，Snowflake会随机分配行号顺序。此外，你预期最终结果按txn_date升序排列时，行号从8到1连续递减，当前逻辑并未满足这一点——核心问题在于同一日期内缺少明确排序字段，且行号生成逻辑未匹配你想要的“逆序连续编号”需求。

2. 修正行号的查询语句 #

方案一：明确排序规则，确保最新记录行号为1 #

如果要让客户的最新（最晚日期）记录行号为1，且同一日期内的记录行号有固定顺序，可在ORDER BY中补充字段（比如txn_type、txn_amount）：

SELECT 
  customer_id
  ,txn_type
  ,txn_date
  ,txn_amount
  ,CASE WHEN txn_type = 'deposit'
        THEN txn_amount
        ELSE txn_amount * -1
   END AS new_amount
  ,SUM(new_amount) OVER (
   PARTITION BY customer_id
   ORDER BY txn_date
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
  ,ROW_NUMBER() OVER (
   PARTITION BY customer_id
   ORDER BY txn_date DESC, txn_type DESC, txn_amount DESC) AS rn
FROM
  transactions
ORDER BY
  customer_id
  ,txn_date;

方案二：生成连续递减的行号（匹配你的预期值） #

如果希望最终结果按txn_date升序排列时，行号从8到1连续递减，可以先计算升序行号，再用分区内总行数倒转：

WITH base_data AS (
  SELECT 
    customer_id
    ,txn_type
    ,txn_date
    ,txn_amount
    ,CASE WHEN txn_type = 'deposit'
          THEN txn_amount
          ELSE txn_amount * -1
     END AS new_amount
    ,SUM(new_amount) OVER (
     PARTITION BY customer_id
     ORDER BY txn_date
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
    ,ROW_NUMBER() OVER (
     PARTITION BY customer_id
     ORDER BY txn_date, txn_type, txn_amount) AS asc_rn
    ,COUNT(*) OVER (PARTITION BY customer_id) AS total_rows
  FROM
    transactions
)
SELECT
  customer_id
  ,txn_type
  ,txn_date
  ,txn_amount
  ,new_amount
  ,running_total
  ,total_rows - asc_rn + 1 AS rn
FROM base_data
ORDER BY
  customer_id
  ,txn_date;

这个方案会生成你预期的8、7、6、5、4、3、2、1行号序列。

3. 按客户+月份分区获取每月最新记录的滚动总额 #

要实现按customer_id和交易月份分区，先提取月份字段（用DATE_TRUNC('month', txn_date)），再在窗口函数中同时按这两个字段分区，最后筛选行号为1的记录（即每月最新交易）：

WITH monthly_data AS (
  SELECT 
    customer_id
    ,DATE_TRUNC('month', txn_date) AS txn_month
    ,txn_type
    ,txn_date
    ,txn_amount
    ,CASE WHEN txn_type = 'deposit'
          THEN txn_amount
          ELSE txn_amount * -1
     END AS new_amount
    ,SUM(new_amount) OVER (
     PARTITION BY customer_id
     ORDER BY txn_date
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
    ,ROW_NUMBER() OVER (
     PARTITION BY customer_id, DATE_TRUNC('month', txn_date)
     ORDER BY txn_date DESC, txn_type DESC, txn_amount DESC) AS rn
  FROM
    transactions
)
SELECT
  customer_id
  ,txn_month
  ,txn_date AS latest_txn_date_in_month
  ,running_total AS latest_running_total_in_month
FROM monthly_data
WHERE rn = 1
ORDER BY
  customer_id
  ,txn_month;

该查询会返回每个客户每个月最后一笔交易对应的滚动总额。

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