如何按指定条件过滤SQL orders表数据：含已发生/未发生事件判断

表结构与数据 #

我有一张名为orders的SQL表，数据如下：

user_id    email            segment    destination    revenue    
1          joe@smith.com    basic      New York       500
1          joe@smith.com    luxury     London         750
1          joe@smith.com    luxury     London         500
1          joe@smith.com    basic      New York       625
1          joe@smith.com    basic      Miami          925
1          joe@smith.com    basic      Los Angeles    218
1          joe@smith.com    basic      Sydney         200
2          mary@jones.com   basic      Chicago        375
2          mary@jones.com   luxury     New York       1500
2          mary@jones.com   basic      Toronto        2800
2          mary@jones.com   basic      Miami          750
2          mary@jones.com   basic      New York       500
2          mary@jones.com   basic      New York       625
3          mike@me.com      luxury     New York       650
3          mike@me.com      basic      New York       875
4          sally@you.com    luxury     Chicago        1300
4          sally@you.com    basic      New York       1200
4          sally@you.com    basic      New York       1000
4          sally@you.com    luxury     Sydney         725
5          bob@gmail.com    basic      London         500
5          bob@gmail.com    luxury     London         750

筛选需求 #

返回符合条件的唯一user_id和对应的email，需满足：

• 满足以下任一条件：
  1. segment = 'luxury' 且 destination = 'New York'
  2. segment = 'luxury' 且 destination = 'London'
  3. segment = 'basic' 且 destination = 'New York'，且该用户此类记录的revenue总和超过2000美元
• 同时满足：用户从未有destination = 'Miami'的记录

期望结果 #

user_id     email
3           mike@me.com
4           sally@you.com
5           bob@gmail.com

我的尝试 #

我写出了部分查询语句，但无法处理条件3和4：

SELECT
   DISTINCT(user_id),
   email
FROM orders o

WHERE
(o.segment = 'luxury' AND o.destination = 'New York')
OR
(o.segment = 'luxury' AND o.destination = 'London')

解决方案 #

这里提供两种可行的实现方式：

方式一：使用窗口函数计算用户聚合指标 #

SELECT DISTINCT user_id, email
FROM (
    SELECT
        user_id,
        email,
        -- 计算用户basic+New York的总营收
        SUM(CASE WHEN segment = 'basic' AND destination = 'New York' THEN revenue ELSE 0 END) OVER (PARTITION BY user_id) AS basic_ny_total,
        -- 判断用户是否有Miami的记录
        MAX(CASE WHEN destination = 'Miami' THEN 1 ELSE 0 END) OVER (PARTITION BY user_id) AS has_miami,
        segment,
        destination
    FROM orders
) AS user_metrics
WHERE
    has_miami = 0
    AND (
        (segment = 'luxury' AND destination IN ('New York', 'London'))
        OR
        (basic_ny_total > 2000)
    );

逻辑解释：

1. 子查询user_metrics中：
   • 通过SUM() OVER (PARTITION BY user_id)计算每个用户在basic段且目的地为New York的总营收
   • 通过MAX() OVER (PARTITION BY user_id)标记用户是否有过Miami的记录（1表示有，0表示无）
2. 外层查询先过滤掉有Miami记录的用户，再筛选满足任意核心条件的用户，最后用DISTINCT确保用户唯一。

方式二：使用分组聚合筛选 #

SELECT user_id, email
FROM orders
GROUP BY user_id, email
HAVING
    -- 条件4：无Miami记录
    SUM(CASE WHEN destination = 'Miami' THEN 1 ELSE 0 END) = 0
    AND (
        -- 条件1或2：存在luxury+NY或luxury+London的记录
        SUM(CASE WHEN segment = 'luxury' AND destination IN ('New York', 'London') THEN 1 ELSE 0 END) > 0
        -- 条件3：basic+NY的总营收>2000
        OR SUM(CASE WHEN segment = 'basic' AND destination = 'New York' THEN revenue ELSE 0 END) > 2000
    );

逻辑解释：

• 按user_id和email分组（同一user_id对应唯一email）
• HAVING子句中：
  • 用SUM(CASE...) = 0确保用户没有Miami的记录
  • 第一个SUM(CASE...) > 0判断用户是否有符合条件1或2的订单
  • 第二个SUM(CASE...) > 2000判断用户basic+NY的总营收是否超过2000
  • 两个条件满足其一即可

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