求T-SQL语句：统计公司交易日期前5年内的交易数量

现有一组记录各公司交易及对应交易日期的数据，已通过dateadd(year, -5, deal_date)生成deal_date_5y_back字段（该交易日期前5年的日期），数据示例如下：

需要新增num_deals_5yb字段，统计每个公司每笔交易的deal_date_5y_back至deal_date前一日之间的交易数量。尝试使用count over partition by子句实现但存在问题，尝试的代码如下：

select 
    company_id
    , deal_date
    , count(distinct deal_id) over (partition by company_id, deal_date where deal_date between deal_date_5yb and dateadd(day, -1, deal_date) ) num_deals_5y_back
from 
    (select 
         company_id, deal_id, deal_date, 
         dateadd(year, -5, deal_date) deal_date_5y_back
     from 
         DEALS_TABLE
     where 
         deal_date is not null) T
group by 
    company_id, deal_date

期望输出如下：

需要正确的T-SQL实现方案。

方法1：使用APPLY子查询（逻辑直观） #

这种方式针对每一行数据，单独查询符合日期范围条件的交易数量，逻辑清晰易维护：

SELECT
    t.company_id,
    t.deal_id,
    t.deal_date,
    t.deal_date_5y_back AS deal_date_5yb,
    COALESCE(c.num_deals, 0) AS num_deals_5yb
FROM (
    SELECT 
        company_id, 
        deal_id, 
        deal_date, 
        DATEADD(YEAR, -5, deal_date) AS deal_date_5y_back
    FROM DEALS_TABLE
    WHERE deal_date IS NOT NULL
) t
OUTER APPLY (
    SELECT COUNT(deal_id) AS num_deals
    FROM DEALS_TABLE d
    WHERE d.company_id = t.company_id
      AND d.deal_date >= t.deal_date_5y_back
      AND d.deal_date < t.deal_date
) c

方法2：使用窗口函数+条件聚合 #

通过窗口函数结合条件判断，在同公司分组内统计符合日期范围的交易数量：

SELECT
    t.company_id,
    t.deal_id,
    t.deal_date,
    t.deal_date_5y_back AS deal_date_5yb,
    COUNT(CASE WHEN d.deal_date >= t.deal_date_5y_back AND d.deal_date < t.deal_date THEN d.deal_id END)
        OVER(PARTITION BY t.company_id) AS num_deals_5yb
FROM (
    SELECT 
        company_id, 
        deal_id, 
        deal_date, 
        DATEADD(YEAR, -5, deal_date) AS deal_date_5y_back
    FROM DEALS_TABLE
    WHERE deal_date IS NOT NULL
) t
LEFT JOIN DEALS_TABLE d ON t.company_id = d.company_id
GROUP BY t.company_id, t.deal_id, t.deal_date, t.deal_date_5y_back

原代码问题说明 #

1. 窗口函数的OVER子句中，PARTITION BY后不能直接添加WHERE条件，条件判断需放在聚合函数内部或使用FILTER子句（部分场景支持）。
2. 原代码用partition by company_id, deal_date会将每个交易日期单独分组，无法统计同公司其他日期的交易数据。
3. 无需使用count(distinct deal_id)，因为deal_id本身是唯一标识，直接统计即可。

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