我有一组数据，显示每家公司的交易情况和交易发生的日期.
我创建了一个新的列，显示交易发生前5年的日期(deal_date_5y_back).
| company_id | deal_id | deal_date | deal_date_5y_back |
|---|---|---|---|
| 86 | 1 | 2006-02-27 | 2001-02-27 |
| 86 | 2 | 2012-04-01 | 2007-04-01 |
| 86 | 3 | 2015-03-23 | 2010-03-23 |
| 86 | 4 | 2018-09-19 | 2013-09-19 |
我想创建一个新列，统计每一家公司和每一家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
我预计的输出是:
| company_id | deal_id | deal_date | deal_date_5yb | num_deals_5yb |
|---|---|---|---|---|
| 86 | 1 | 2006-02-27 | 2001-02-27 | 0 |
| 86 | 2 | 2012-04-01 | 2007-04-01 | 0 |
| 86 | 3 | 2015-03-23 | 2010-03-23 | 1 |
| 86 | 4 | 2018-09-19 | 2013-09-19 | 0 |
如有任何帮助，我们不胜感激！