如何在SQL中按用户和商品查询最大、平均及最新利润

针对你的需求——按user_id和item_id分组计算最大利润、平均利润，同时获取分组内最新profit_date对应的利润值，以下提供两种高效实现方案：

方法一：窗口函数实现（最优方案） #

利用窗口函数标记分组内的最新记录，配合聚合函数一次性完成所有计算，仅需扫描表一次，性能最优，代码简洁。

SELECT 
    user_id,
    item_id,
    MAX(profit) AS max_profit,
    AVG(profit) AS avg_profit,
    MAX(CASE WHEN rn = 1 THEN profit END) AS latest_profit
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY user_id, item_id ORDER BY profit_date DESC) AS rn
    FROM sample
) t
GROUP BY user_id, item_id;

逻辑说明： #

1. 子查询中，通过ROW_NUMBER()按user_id和item_id分组，每组内按profit_date倒序排序，最新的记录会被标记为rn=1。
2. 外层查询按user_id和item_id聚合，用MAX(CASE...)提取出rn=1对应的利润值，同时直接计算最大、平均利润。

方法二：关联子查询实现（兼容旧版本数据库） #

如果你的数据库不支持窗口函数（如MySQL 5.x及以下），可以采用分步关联的方式实现：

WITH agg_data AS (
    SELECT 
        user_id,
        item_id,
        MAX(profit) AS max_profit,
        AVG(profit) AS avg_profit
    FROM sample
    GROUP BY user_id, item_id
),
latest_profit_data AS (
    SELECT s.user_id, s.item_id, s.profit AS latest_profit
    FROM sample s
    JOIN (
        SELECT user_id, item_id, MAX(profit_date) AS latest_date
        FROM sample
        GROUP BY user_id, item_id
    ) t ON s.user_id = t.user_id AND s.item_id = t.item_id AND s.profit_date = t.latest_date
)
SELECT 
    a.user_id,
    a.item_id,
    a.max_profit,
    a.avg_profit,
    l.latest_profit
FROM agg_data a
JOIN latest_profit_data l ON a.user_id = l.user_id AND a.item_id = l.item_id;

逻辑说明： #

1. agg_data CTE先计算每个分组的最大、平均利润。
2. latest_profit_data CTE先获取每个分组的最新日期，再关联原表得到该日期对应的利润。
3. 最后将两个CTE的结果关联，得到完整输出。

验证结果 #

执行任意方案后，将得到符合预期的输出：

方案对比 #

• 方法一：仅需一次表扫描，性能更优，代码简洁，适合支持窗口函数的现代数据库（MySQL 8.0+、PostgreSQL、SQL Server等）。
• 方法二：需多次扫描表，性能略逊，但兼容不支持窗口函数的旧版本数据库。

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