sql - 无法对 SQL 语句进行分组和计算返回率

Question

这是我当前的表：

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%

标签： sqlsql-server

Answer 1

first_date您可以通过and聚合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

DB Fidde 上的演示：

第一日期 | 任何日期 | count_id | 回报率   
:----------------- | :----------------- | --------: | :-------------
2018 年 1 月 12 日 00:00:00 | 2018 年 1 月 12 日 00:00:00 | 2 | 1.000000000000
2018 年 1 月 12 日 00:00:00 | 2018 年 2 月 12 日 00:00:00 | 1 | 0.500000000000
2018 年 2 月 12 日 00:00:00 | 2018 年 2 月 12 日 00:00:00 | 1 | 1.000000000000