SQL按年份统计首次执行的新客户与新办公室数量问题

原始数据 #

Id   CustomerId  ExecutionDate            OfficeId
-------------------------------------------------
1      1         2016-11-17 00:00:00.000  1
2    133         2016-12-23 00:00:00.000  3
3      1         2017-01-27 00:00:00.000  1
4    230         2023-04-11 00:00:00.000  2
5    133         2023-04-12 00:00:00.000  3
6      1         2024-04-13 00:00:00.000  1
7      1         2023-04-17 00:00:00.000  1

需求说明 #

1. 年度新增客户统计 #

统计每年有多少客户是首次被执行（即该客户的首次ExecutionDate所在年份），以此衡量年度客户增长，期望结果：

Year    NewCustomers
--------------------------
2016    2
2017    1
2023    3
2024    1

2. 年度新增办公室统计 #

统计每年首次出现的办公室数量，期望结果：

Year    NewOffices
--------------------------
2016    2
2023    1

同时询问：是否只需替换对应字段即可实现？

你的错误尝试 #

SELECT DISTINCT TOP 10 
    YEAR(MIN(ExecutionDate)) 
FROM
    ExecRegistry
GROUP BY
    CustomerId, YEAR(MIN(ExecutionDate))
ORDER BY
    YEAR(MIN(ExecutionDate));

正确实现方案 #

1. 年度新增客户统计 #

思路：先按CustomerId分组，找出每个客户的首次执行年份；再按年份分组，统计每个年份下的客户数量。

SELECT
    YEAR(first_exec_date) AS [Year],
    COUNT(CustomerId) AS NewCustomers
FROM (
    SELECT
        CustomerId,
        MIN(ExecutionDate) AS first_exec_date
    FROM ExecRegistry
    GROUP BY CustomerId
) AS customer_first_exec
GROUP BY YEAR(first_exec_date)
ORDER BY [Year];

2. 年度新增办公室统计 #

是的，只需要把统计维度从CustomerId替换为OfficeId即可，SQL代码如下：

SELECT
    YEAR(first_office_date) AS [Year],
    COUNT(OfficeId) AS NewOffices
FROM (
    SELECT
        OfficeId,
        MIN(ExecutionDate) AS first_office_date
    FROM ExecRegistry
    GROUP BY OfficeId
) AS office_first_appear
GROUP BY YEAR(first_office_date)
ORDER BY [Year];

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