无法将SQL语句分组并计算返回率

这是我当前的表格：

first_date any_date id
2018-12-01 2018-12-01 1234
2018-12-01 2018-12-01 1234
2018-12-01 2018-12-02 1234
2018-12-01 2018-12-01 2434
2018-12-02 2018-12-02 1111


到目前为止，我对MS SQL Server 2017的查询如下：

select
min(any_date) over (PARTITION by id) AS first_time,any_date,id
from sales


但是，我想合计并添加一个返回率计算，该计算将引用每个唯一的first_date和每个any_date组合。我怎么得到这个？

first_date any_date count id return_rate
2018-12-01 2018-12-01 2 100% (because this is the cohort start)
2018-12-01 2018-12-02 1 50%
2018-12-02 2018-12-02 1 100%


您可以通过first_date和any_date进行汇总，然后使用窗口函数first_value()提取同类群组第一天的唯一ID计数：

select
t.*,1 .0 * count_id
/ first_value(count_id) over(partition by first_date order by any_date) return_rate
from (
select first_date,any_date,count(distinct id) count_id
from sales
group by first_date,any_date
) t


Demo on DB Fidde ：

first_date | any_date | count_id | return_rate
:------------------ | :------------------ | -------: | :-------------
01/12/2018 00:00:00 | 01/12/2018 00:00:00 | 2 | 1.000000000000
01/12/2018 00:00:00 | 02/12/2018 00:00:00 | 1 | 0.500000000000
02/12/2018 00:00:00 | 02/12/2018 00:00:00 | 1 | 1.000000000000

,

我想你想要

select first_date,count(*) as on_this_date,count(*) * 1.0 / max(count(*)) over (partition by first_date) as ratio
from (select distinct first_date,id
from t
) t
group by first_date,any_date;


这假定最大值是第一个值。