SQL中如何根据账号状态排除指定行并按日期范围聚合数据？

问题背景 #

现有retail_account表，存储零售账号的状态变更记录，结构如下：

• account: 账号ID
• createddate: 账号创建日期
• closed_date: 账号关闭日期
• account_type: 账号类型
• initial_amount: 初始金额
• record_date: 状态变更记录日期
• status: 账号状态（如ACTIVE、CLOSED）

需求：根据用户指定的日期范围（格式DD/MM/YYYY），按月份和账号类型聚合统计：

1. 有效账号数量
2. 初始金额总和

核心规则：如果某个账号在指定日期范围内存在CLOSED状态记录，该账号的所有ACTIVE记录必须被排除。

解决思路 #

核心是先锁定需要排除的账号范围，再过滤统计：

1. 先筛选出日期范围内出现过CLOSED状态的所有账号，形成排除列表
2. 主查询中仅保留两类记录：
   • 记录日期在指定范围内的非ACTIVE状态记录
   • 记录日期在指定范围内、状态为ACTIVE且不在排除列表中的账号记录
3. 按记录日期的月份和账号类型分组聚合统计

正确SQL实现 #

以下是通用SQL方案（不同数据库需调整日期函数，示例基于Oracle，其他数据库适配见下文）：

WITH closed_accounts AS (
    -- 提取日期范围内出现过CLOSED状态的账号
    SELECT DISTINCT account
    FROM retail_account
    WHERE status = 'CLOSED'
      AND record_date BETWEEN TO_DATE('{start_date}', 'DD/MM/YYYY') 
                          AND TO_DATE('{end_date}', 'DD/MM/YYYY')
)
SELECT
    -- 格式化月份为YYYY-MM格式，可按需调整
    TO_CHAR(record_date, 'YYYY-MM') AS month,
    account_type,
    COUNT(DISTINCT account) AS account_count,
    SUM(initial_amount) AS total_initial_amount
FROM retail_account
WHERE
    record_date BETWEEN TO_DATE('{start_date}', 'DD/MM/YYYY') 
                    AND TO_DATE('{end_date}', 'DD/MM/YYYY')
    -- 排除需屏蔽的ACTIVE记录
    AND NOT (status = 'ACTIVE' AND account IN (SELECT account FROM closed_accounts))
GROUP BY TO_CHAR(record_date, 'YYYY-MM'), account_type
ORDER BY month, account_type;

数据库适配调整 #

• MySQL：替换TO_DATE为STR_TO_DATE，TO_CHAR为DATE_FORMAT(record_date, '%Y-%m')
• SQL Server：替换TO_DATE为CONVERT(DATE, '{start_date}', 103)，TO_CHAR为FORMAT(record_date, 'yyyy-MM')

逻辑验证 #

假设账号1234在2024-03的状态变更记录中出现CLOSED，当查询范围包含2024-03时：

• 账号1234的所有ACTIVE记录会被直接排除
• 仅统计该账号的非ACTIVE记录（若存在），或完全不统计该账号

此逻辑严格匹配需求：只要账号在指定范围内有CLOSED状态，其ACTIVE记录不会参与统计。

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