MySQL多条件查询：统计每个用户的最爱店铺及总有效订单数

shops表 #

shop_id | name 
-----------------------
20      | PizzaShop
34      | SushiShop

orders表 #

orders_id | creation_time       | user_id | shop_id | Status
------------------------------------------------------------------
1         | 2021-01-01 14:00:00 | 1       | 20      | OK
2         | 2021-02-01 14:00:00 | 1       | 34      | Cancelled
3         | 2021-03-01 14:00:00 | 1       | 20      | OK
4         | 2021-04-01 14:00:00 | 1       | 34      | OK
5         | 2021-05-01 14:00:00 | 2       | 20      | OK
6         | 2021-06-01 14:00:00 | 2       | 20      | OK
7         | 2021-07-01 14:00:00 | 2       | 34      | OK
8         | 2021-08-01 14:00:00 | 2       | 34      | OK

查询每个用户的「最爱店铺」，判定规则：

• 优先取用户Status为OK的订单数量最多的店铺
• 若多个店铺OK订单数持平，取最近下单时间最新的店铺

期望输出：

user_id | total_number_OK_orders | favourite_shop_name 
------------------------------------------------------------------
1       | 3                      | PizzaShop
2       | 4                      | SushiShop

目前已完成每个用户总OK订单数的统计逻辑：

SELECT 
    orders.user_id,
    SUM(if(orders.Status = 'OK', 1, 0)) AS total_number_OK_orders
FROM orders
LEFT JOIN shops
    ON orders.shop_id = shops.shop_id
GROUP BY orders.user_id;

通过窗口函数对用户维度下的店铺做优先级排序，取排名第一的即为最爱店铺，完整SQL如下：

WITH user_total_ok AS (
    -- 统计每个用户的总OK订单数
    SELECT 
        user_id,
        SUM(IF(Status = 'OK', 1, 0)) AS total_number_OK_orders
    FROM orders
    GROUP BY user_id
),
user_shop_rank AS (
    -- 按用户+店铺分组统计OK订单数、最近下单时间，按规则排序
    SELECT
        o.user_id,
        s.name AS shop_name,
        ROW_NUMBER() OVER(
            PARTITION BY o.user_id 
            ORDER BY COUNT(1) DESC, MAX(o.creation_time) DESC
        ) AS rn
    FROM orders o
    LEFT JOIN shops s ON o.shop_id = s.shop_id
    WHERE o.Status = 'OK'
    GROUP BY o.user_id, o.shop_id, s.name
)
-- 关联结果取每个用户排名第一的店铺
SELECT
    ut.user_id,
    ut.total_number_OK_orders,
    usr.shop_name AS favourite_shop_name
FROM user_total_ok ut
LEFT JOIN user_shop_rank usr ON ut.user_id = usr.user_id
WHERE usr.rn = 1;

逻辑说明 #

1. 第一个CTE user_total_ok 保留原有统计逻辑，计算每个用户的总OK订单数
2. 第二个CTE user_shop_rank 按用户+店铺分组，使用ROW_NUMBER()窗口函数按「OK订单数倒序、最近下单时间倒序」的规则给每个用户的店铺排序，排名第一的就是符合规则的最爱店铺
3. 最后关联两个CTE的结果，过滤出每个用户排名第一的店铺即可得到期望输出

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