如何编写MySQL语句按book_id分组统计总金额与like行为总条数

问题说明 #

现有数据表结构及示例数据如下：

book_id  order_id  amount    behav
-------- -------   -------  -------
   a      d1         10       like
   a      d1         10       like
   a      d1         10       unlike
   a      d2         20       unlike
   a      d2         20       unlike
   a      d3         30       like
   b      d4         20       like
   b      d4         20       unlike
   b      d5         30       unlike     

需求：按book_id分组，统计两个指标：

• 分组内所有不同order_id对应的amount总和（同一个order_id的amount为重复值，不能重复累加）
• 分组内behav值为like的总记录条数

期望输出结果：

book_id     total_amount    like_count
 --------   -------------   ------------
    a            60             3
    b            50             1

实现方案 #

注意事项 #

不要直接对全表amount执行SUM计算，因为同一个订单对应多条行为记录时，amount会重复出现，直接求和会得到错误的偏高结果，需要先按book_id+order_id去重后再计算金额总和。

全版本兼容SQL #

将下面SQL中的你的表名替换为实际的表名即可运行，兼容MySQL5.x和8.x所有版本：

SELECT 
    t1.book_id,
    SUM(t1.amount) AS total_amount,
    IFNULL(t2.like_count, 0) AS like_count
FROM (
    -- 先去重得到每个book_id下每个order_id对应的唯一金额
    SELECT DISTINCT book_id, order_id, amount
    FROM 你的表名
) t1
LEFT JOIN (
    -- 统计每个book_id下的like行为总条数
    SELECT book_id, COUNT(*) AS like_count
    FROM 你的表名
    WHERE behav = 'like'
    GROUP BY book_id
) t2 ON t1.book_id = t2.book_id
GROUP BY t1.book_id;

MySQL8.0+简化写法 #

如果使用MySQL8.0及以上版本，可通过窗口函数实现更简洁的单查询逻辑，性能更优：

SELECT
    book_id,
    SUM(CASE WHEN rn = 1 THEN amount ELSE 0 END) AS total_amount,
    SUM(CASE WHEN behav = 'like' THEN 1 ELSE 0 END) AS like_count
FROM (
    SELECT
        *,
        -- 给同一个book_id+order_id的行打序号，只有第一行参与金额求和，避免重复累加
        ROW_NUMBER() OVER (PARTITION BY book_id, order_id ORDER BY order_id) AS rn
    FROM 你的表名
) t
GROUP BY book_id;

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