Snowflake SQL实现按行计算用户过往前两大销量及二者平均值

可行性结论 #

该需求完全可以通过支持窗口函数的标准SQL实现，无需额外工具处理。

核心实现逻辑 #

• 按uid分组、visit date升序排列所有记录，确定每行的时间顺序
• 对每一行关联同uid下所有到访日期早于当前行的历史到访记录
• 对关联到的历史销量做排名，提取第一、第二高的销量值
• 边界兼容：历史记录不足2条时，第二高值返回空，平均值仅基于存在的有效值计算

示例SQL脚本 #

假设表名为sales_visit，字段统一调整为下划线命名格式（如果你的字段包含空格，可按数据库语法增加引号包裹），兼容MySQL 8.0+、PostgreSQL、Oracle等主流支持窗口函数的数据库：

WITH history_sales_ranked AS (
    SELECT
        curr.uid,
        curr.visit_name,
        curr.visit_date,
        curr.sales_quantity,
        his.sales_quantity AS history_sales,
        -- 对历史销量做去重排名，并列销量同排名
        DENSE_RANK() OVER (
            PARTITION BY curr.uid, curr.visit_date 
            ORDER BY his.sales_quantity DESC
        ) AS sales_rank
    FROM sales_visit curr
    -- 关联同uid下早于当前行的所有历史记录
    LEFT JOIN sales_visit his
        ON curr.uid = his.uid
        AND his.visit_date < curr.visit_date
)
SELECT
    uid,
    visit_name,
    visit_date,
    sales_quantity,
    -- 历史最高销量
    MAX(CASE WHEN sales_rank = 1 THEN history_sales END) AS max_history_sales,
    -- 历史第二高销量，无符合数据则返回NULL
    MAX(CASE WHEN sales_rank = 2 THEN history_sales END) AS second_max_history_sales,
    -- 前两名销量的平均值，自动忽略空值
    AVG(CASE WHEN sales_rank IN (1,2) THEN history_sales END) AS top2_avg_history_sales
FROM history_sales_ranked
GROUP BY uid, visit_name, visit_date, sales_quantity
ORDER BY uid, visit_date;

注意事项 #

• 若你的字段名包含空格（如visit name），需按对应数据库语法包裹字段：MySQL用反引号`visit name`，PostgreSQL/Oracle用双引号"visit name"
• 若需要按记录条数计算排名（即出现并列最高时，第二高也取最高值），可将DENSE_RANK替换为ROW_NUMBER
• 若同一uid存在同一天多笔到访记录，可在关联条件中额外增加行唯一标识的判断，避免同天记录的计入逻辑错误

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