如何使用SQL识别每月均完成指定交易类型的用户

我正在对三个月内有交易记录的用户开展数据分析，需要识别出在数据覆盖时间段内**每个月都完成指定交易类型（Credit）**的客户。规则如下：

• 用户若在1、2、3月均有Credit交易，标记为Frequent
• 用户若并非每月都有Credit交易，标记为Infrequent

数据示例：

我已经尝试了下面的SQL语句，但希望有更简洁的实现方式：

SELECT
   Date, User, Amount, Transaction_Type,
   CASE WHEN Count(present) = 3 THEN 'Frequent' ELSE 'Infrequent'

FROM Transactions

LEFT JOIN (
                SELECT 
                    User,Month(Date),Count(Transaction_Type) as present
                FROM 
                    Transactions
                WHERE 
                    Transaction_Type = 'Credit'
                GROUP BY 
                    User,Month(Date)
                Having 
                    Count(Transaction_Type) > 0
            ) subquery
                ON subquery.User = Transaction.User
GROUP BY    
        Date,User,Amount,Transaction_Type

核心思路 #

先统计每个用户有Credit交易的唯一月份数量，再判断该数量是否等于数据覆盖的总月份数（这里是3），最后关联回原表完成标记。

方案一：CTE拆分写法（可读性优先） #

WITH user_credit_month_stats AS (
    SELECT 
        User,
        -- 统计用户有Credit交易的不同月份数
        COUNT(DISTINCT DATE_TRUNC('month', Date)) AS credit_month_count
    FROM Transactions
    WHERE Transaction_Type = 'Credit'
    GROUP BY User
)
SELECT
    t.Date,
    t.User,
    t.Amount,
    t.Transaction_Type,
    CASE 
        WHEN ucm.credit_month_count = 3 THEN 'Frequent' 
        ELSE 'Infrequent' 
    END AS Flag
FROM Transactions t
LEFT JOIN user_credit_month_stats ucm ON t.User = ucm.User;

说明

• CTE部分单独拆分统计逻辑，代码结构清晰，便于维护
• 关联原表后通过CASE直接判断标记，避免冗余的分组和连接操作

方案二：子查询嵌入写法（紧凑优先） #

SELECT
    t.Date,
    t.User,
    t.Amount,
    t.Transaction_Type,
    CASE
        WHEN (SELECT COUNT(DISTINCT DATE_TRUNC('month', Date)) 
              FROM Transactions 
              WHERE User = t.User AND Transaction_Type = 'Credit') = 3 
        THEN 'Frequent'
        ELSE 'Infrequent'
    END AS Flag
FROM Transactions t;

说明

• 把统计逻辑直接嵌入CASE语句，省去CTE步骤，代码更紧凑
• 逻辑和方案一完全一致，适合追求代码精简的场景

数据库适配注意点 #

DATE_TRUNC('month', Date)是标准SQL的月份截断写法，不同数据库有对应替代语法：

• MySQL：DATE_FORMAT(Date, '%Y-%m')
• SQL Server：DATEPART(month, Date)
• Oracle：TRUNC(Date, 'MM')

如果数据覆盖的月份数不固定（比如不是固定3个月），可以把硬编码的3替换为总月份数的统计：

(SELECT COUNT(DISTINCT DATE_TRUNC('month', Date)) FROM Transactions)

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