求SQL查询语句：按EOMONTH分组标记新客户状态

需求概述 #

编写SQL查询实现：

• 首次产生发票的新客户标记为1（代表"NEW"），非新客户标记为0
• 按当月最后一天（EOMONTH(Date)）分组展示

数据表字段 #

• Date：发票日期
• Account_Id：客户账户ID
• Account_Name：客户名称
• Revenue：发票收入

期望输出 #

解决方案（SQL Server） #

1. 按月聚合收入的版本

SELECT
    EOMONTH(Date) AS Date,
    Account_Id AS [Account Id],
    Account_Name AS [Account Name],
    SUM(Revenue) AS Revenue,
    CASE
        WHEN EOMONTH(MIN(Date) OVER (PARTITION BY Account_Id)) = EOMONTH(Date)
        THEN 1
        ELSE 0
    END AS Status
FROM
    YourTableName
GROUP BY
    EOMONTH(Date),
    Account_Id,
    Account_Name,
    CASE
        WHEN EOMONTH(MIN(Date) OVER (PARTITION BY Account_Id)) = EOMONTH(Date)
        THEN 1
        ELSE 0
    END
ORDER BY
    Date, [Account Id];

2. 单条发票记录的版本（无需聚合）

SELECT
    EOMONTH(Date) AS Date,
    Account_Id AS [Account Id],
    Account_Name AS [Account Name],
    Revenue,
    CASE
        WHEN EOMONTH(MIN(Date) OVER (PARTITION BY Account_Id)) = EOMONTH(Date)
        THEN 1
        ELSE 0
    END AS Status
FROM
    YourTableName
ORDER BY
    Date, [Account Id];

关键逻辑说明 #

• 用窗口函数MIN(Date) OVER (PARTITION BY Account_Id)获取每个客户的首次开票日期
• 通过EOMONTH()将日期转换为当月月末，实现按月分组和新客户判断
• CASE语句对比当前记录的月末与客户首次开票的月末，一致则标记为新客户（1），否则为老客户（0）

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