如何用SQL Window Functions为交易记录分配行程编号

要实现需求中的行程编号规则，核心是先识别行程起始点，再通过累计求和为同一行程的交易分配一致的编号，最后处理本币交易的0值。

实现步骤 #

1. 标记行程起始点：
   使用LAG()窗口函数获取当前交易的上一笔交易货币，当当前交易为非本币（!= 'ABC'）且上一笔为ABC时，标记为行程起始（值为1），否则标记为0。第一笔交易如果是非本币，LAG()的默认值设为ABC，确保能正确识别为起始点。

2. 累计生成行程编号：
   对每个用户按交易时间排序，累计起始点的标记值。同一行程内的非本币交易，累计值保持不变；每次遇到新的起始点，累计值加1，从而生成唯一的行程编号。

3. 处理本币交易：
   将所有本币（ABC）交易的行程编号设为0，非本币交易直接使用累计得到的行程编号。

完整SQL代码 #

WITH TripMarkers AS (
  SELECT
    user_id,
    transaction_timestamp,
    transaction_currency,
    -- 标记行程起始点：非ABC且上一笔为ABC则标记为1
    CASE
      WHEN transaction_currency != 'ABC' 
           AND LAG(transaction_currency, 1, 'ABC') OVER (PARTITION BY user_id ORDER BY transaction_timestamp) = 'ABC'
      THEN 1
      ELSE 0
    END AS is_trip_start
  FROM transactions
),
TripNumbers AS (
  SELECT
    *,
    -- 累计起始点标记，生成连续的行程编号
    SUM(is_trip_start) OVER (PARTITION BY user_id ORDER BY transaction_timestamp) AS trip_num
  FROM TripMarkers
)
SELECT
  user_id AS 用户,
  transaction_timestamp AS 交易时间戳,
  transaction_currency AS 交易货币,
  -- 本币交易设为0，非本币使用累计的行程编号
  CASE WHEN transaction_currency = 'ABC' THEN 0 ELSE trip_num END AS 行程编号
FROM TripNumbers
ORDER BY user_id, transaction_timestamp;

原代码问题说明 #

你之前使用ROW_NUMBER()的方式会对每一行单独计数，导致同一行程内的交易编号持续递增。而通过累计起始点标记的方式，能让同一行程内的所有非本币交易共享同一个编号，完全符合需求中的规则。

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