Snowflake中计算交易前唯一客户数的高效实现方案咨询

Snowflake不支持在带ORDER BY的窗口函数中使用DISTINCT，针对大数据量交易表，要计算每笔交易发生前的唯一Client ID数量，可采用以下两种高效方案，避免自连接：

方案一：基于首次交易日期标记累计 #

先聚合每个Client的首次交易日期，再标记每条交易是否为该Client的首笔交易，最后累计首笔交易的数量得到结果：

WITH client_first_transaction AS (
    SELECT 
        client_id,
        MIN(transaction_date) AS first_transaction_date
    FROM <table_name>
    GROUP BY client_id
),
transaction_with_first_flag AS (
    SELECT 
        t.transaction_id,
        t.client_id,
        t.transaction_date,
        CASE WHEN t.transaction_date = cft.first_transaction_date THEN 1 ELSE 0 END AS is_first_transaction
    FROM <table_name> t
    JOIN client_first_transaction cft ON t.client_id = cft.client_id
)
SELECT 
    transaction_id,
    client_id,
    transaction_date,
    -- 处理最早交易的NULL情况，转为0
    COALESCE(SUM(is_first_transaction) OVER (
        ORDER BY transaction_date ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ), 0) AS unique_clients_before
FROM transaction_with_first_flag
ORDER BY transaction_date, transaction_id;

方案二：基于行号标记首次交易（更简洁） #

直接用窗口函数给每个Client的交易按日期排序，标记首笔交易后累计：

WITH transaction_with_row_num AS (
    SELECT 
        transaction_id,
        client_id,
        transaction_date,
        -- 按Client分组，交易日期+ID排序，标记首笔交易
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY transaction_date, transaction_id) AS rn
    FROM <table_name>
),
transaction_with_first_flag AS (
    SELECT 
        *,
        CASE WHEN rn = 1 THEN 1 ELSE 0 END AS is_first_transaction
    FROM transaction_with_row_num
)
SELECT 
    transaction_id,
    client_id,
    transaction_date,
    COALESCE(SUM(is_first_transaction) OVER (
        ORDER BY transaction_date ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ), 0) AS unique_clients_before
FROM transaction_with_first_flag
ORDER BY transaction_date, transaction_id;

方案说明 #

• 两种方案均通过标记首次出现的Client交易，再累计这些标记的数量，得到当前交易前的唯一Client数，避免了重复计数。
• 无需自连接，Snowflake对窗口函数和CTE的优化能高效处理大规模数据。
• 若同一Client在同一天有多笔交易，仅第一笔会被标记为新Client，符合需求。

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