SQL查询：筛选用户首日后营收高于首日的记录

刚学SQL遇到这种组合查询很正常，咱们把问题拆成两步来搞定就简单多了——首先得找到每个用户的首日营收，然后再用这个数据去筛选原表中符合条件的记录。我给你两种常用的实现方式，你可以根据自己用的数据库选择：

方法1：用窗口函数（推荐，适合支持窗口函数的数据库：MySQL 8+、PostgreSQL、SQL Server等） #

窗口函数可以很方便地给每条记录标记出对应用户的首日营收，不用额外关联表：

SELECT user_id, purchase_date, revenues
FROM (
    SELECT 
        user_id,
        purchase_date,
        revenues,
        -- 找到当前用户的首次购买日期
        MIN(purchase_date) OVER (PARTITION BY user_id) AS first_purchase_date,
        -- 根据首次日期拿到首日营收
        FIRST_VALUE(revenues) OVER (PARTITION BY user_id ORDER BY purchase_date) AS first_revenue
    FROM your_table_name
) AS user_purchases
-- 筛选营收大于首日营收的记录
WHERE revenues > first_revenue;

代码解释： #

• PARTITION BY user_id：把数据按用户分组，每个用户单独计算自己的首日数据
• MIN(purchase_date) OVER (...)：得到每个用户的最早购买日期
• FIRST_VALUE(revenues) OVER (...)：按购买日期排序后，取每个用户第一条记录的营收（也就是首日营收）
• 外层查询只保留revenues > first_revenue的记录，就是你要的结果

方法2：用子查询+关联（适合不支持窗口函数的旧版本数据库，比如MySQL 5.x） #

如果你的数据库不支持窗口函数，咱们可以先查每个用户的首日营收，再和原表关联筛选：

SELECT t.user_id, t.purchase_date, t.revenues
FROM your_table_name t
JOIN (
    -- 先查询每个用户的首日营收
    SELECT 
        user_id,
        MIN(purchase_date) AS first_purchase_date,
        revenues AS first_revenue
    FROM your_table_name
    GROUP BY user_id, purchase_date, revenues
    HAVING purchase_date = MIN(purchase_date)
) AS first_day_data
ON t.user_id = first_day_data.user_id
-- 筛选当前记录营收大于首日营收的行
WHERE t.revenues > first_day_data.first_revenue;

代码解释： #

• 子查询first_day_data：先按用户分组，找到每个用户的最早购买日期，以及对应的营收
• 把原表t和子查询关联，通过user_id匹配，然后筛选出营收超过首日的记录

两种方法运行后，都会得到你预期的结果：

user_id purchase_date revenues #

A 2018-01-04 45
A 2018-01-06 55
B 2018-01-05 21

刚学SQL不用急，这种多步骤的查询多练几次就会越来越顺手啦～

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