时间戳

首页 > 解决方案 > 使用声明性查询进行每日人口普查

sql - 使用声明性查询进行每日人口普查

问题描述

给定如下数据集：

ID  admission_date  discharge_date
1   2016-02-27      2016-05-06
2   2016-03-06      2016-05-13
3   2016-03-14      2016-05-03
4   2016-04-01      2016-05-19
5   2016-04-03      2016-06-15
6   2016-04-06      2016-05-14
7   2016-04-27      2016-05-03
8   2016-04-27      2016-05-10
9   2016-04-28      2016-05-04
10  2016-04-28      2016-05-01
11  2016-04-28      2016-05-14
12  2016-04-29      2016-05-02
13  2016-04-29      2016-05-01

我想计算给定范围内每行中出现的行数（即每天进行一次人口普查）。对于从 '2016-05-01' 到 '2016-05-02' 的每日人口普查，预期结果是：

date          census
2016-05-01    13
2016-05-02    11 (rows 10 and 13 do not count because patient
                  was not hospitalized on 2016-05-02)

我正在努力寻找一种方法来正确计算所有日子的人口普查人数。我沿着窗口函数的路线走了一段时间，但我想不出一种排除行以实现上述结果的方法。目前我正在尝试通过使用游标来解决这个问题。

所以问题是：是否可以使用纯粹的声明性 SQL 查询来做到这一点？

为了展示人们认为的“人口普查”的不同观点，Tableau 论坛中的这个线程讨论了一个几乎相同的想法，但主要区别在于那里讨论的方法假设，对于 2016-05-01，您不会计算例如，于 2016 年 5 月 1 日出院（这个想法使您能够使用窗口函数总结每一天）。这会将 2016-05-01 的上述总数减少到 11。

标签： sqlsql-server

解决方案

如果您有日期表，这是可能的。如果没有，您可以使用递归 cte 生成它们并将其用于查询。

--Use the recursive cte only if there isn't a dates table in the database   
--Generates all dates in 2016
with dates(dt) as (select cast('2016-01-01' as date) 
                   union all
                   select dateadd(day,1,dt) 
                   from dates
                   where dt<'2017-01-01'
                  )
--Actual query
select d.dt,count(distinct t.id)
from tbl t
join dates d on d.dt>=t.admission_date and d.dt<=t.dishcarge_date
where d.dt>='2016-05-01' and d.dt<='2016-05-02' --change this as needed
group by d.dt
option (maxrecursion 0)

推荐阅读