具有重复日期的时间范围内的最大值

Question

我有一个像这样的表，

DateTime start_time not null,
DateTime end_time not null,
Status_Id int not null,
Entry_Id int not null

我想获取一段时间内每个状态的计数，其中只有最后一个启动的状态对于给定的entry_id 有效。

我现在使用的是这个（带有动态日期）：

with c (Status_Id, Entry_Id, Start_Date) AS (
  select Status_Id, Entry_Id, Start_Date from tbl where
  (End_Date BETWEEN '19000101' AND '21000101')
  AND ((Start_Date BETWEEN '19000101' AND '21000101')
  OR End_Date <= '21000101'))
select Status_Id, count(*) as cnt from 
 (select Entry_Id, max(start_date) as start_date from c
  group by Entry_Id) d inner join
c on c.Entry_Id = d.Entry_Id
and c.start_date = d.start_date
GROUP BY Status_Id WITH ROLLUP

问题是，当某些entry_id具有多个相同start_date的条目时，它会计数错误。 （我并不特别关心在这种情况下选择哪种状态，只是只选择1）

一些测试数据：

status_id   Entry_id    Start_date
496 45173   2010-09-29 18:04:33.000
490 45173   2010-09-29 18:48:20.100
495 45173   2010-09-29 19:25:29.300
489 45174   2010-09-29 18:43:01.500
493 45175   2010-09-29 18:48:00.500
493 45175   2010-09-29 21:16:02.700
489 45175   2010-09-30 17:52:12.100
493 45176   2010-09-29 17:55:21.300
492 45176   2010-09-29 18:20:52.200 <------ This is the one that gives the problems
493 45176   2010-09-29 18:20:52.200 <------ This is the one that gives the problems

结果应该是

495 1
489 2
492 1 (or 493 1)

Answer 1

如果我正确理解，您想要计算您的时间段内特定状态的不同条目...如果是这样，您应该在 count()DISTINCT 子句code> 从 count(*) 更改为 count(distinct Entry_id)

with c (Status_Id, Entry_Id, Start_Date) AS (
  select Status_Id, Entry_Id, Start_Date from tbl where
  (End_Date BETWEEN '19000101' AND '21000101')
  AND ((Start_Date BETWEEN '19000101' AND '21000101')
  OR End_Date <= '21000101'))
select Status_Id, count(distinct Entry_Id) as cnt from 
 (select Entry_Id, max(start_date) as start_date from c
  group by Entry_Id) d inner join
c on c.Entry_Id = d.Entry_Id
and c.start_date = d.start_date
GROUP BY Status_Id WITH ROLLUP

编辑

只要您不关心给定条目返回哪个状态，我认为您可以修改内部查询以返回第一个状态并加入状态

with c (Status_Id, Entry_Id, Start_Date) AS (
  select Status_Id, Entry_Id, Start_Date from tbl where
  (End_Date BETWEEN '19000101' AND '21000101')
  AND ((Start_Date BETWEEN '19000101' AND '21000101')
  OR End_Date <= '21000101'))
select c.Status_Id, count(c.Entry_Id) as cnt from 
 (select Entry_Id, Start_Date, (select top 1 Status_id from c where Entry_Id = CC.Entry_Id and Start_Date = CC.Start_Date) as Status_Id
  from (select Entry_Id, max(start_date) as start_date from c
  group by Entry_Id) as CC) d inner join
c on c.Entry_Id = d.Entry_Id
and c.start_date = d.start_date
and c.status_id = d.status_id
GROUP BY c.Status_Id

结果

Status_id Count
 489       2
 492       1
 495       1

Answer 2

基于 OP 可爱评论的替代答案。

WITH
   [sequenced_data]
AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY entry_id ORDER BY start_time DESC, status_id DESC) AS [sequence_id]
  FROM
    tbl
  WHERE
    start_time < '21:00' AND end_time > '19:00'
)
SELECT status_id, COUNT(*)
FROM [sequenced_data]
WHERE sequence_id = 1
GROUP BY status_id

仅当没有可以唯一标识各个记录的单个字段时才需要 ROW_NUMBER() 函数。可以在数据中存在唯一标识列的地方编写替代查询。然而，SQL Server 在优化 ROW_NUMBER() 查询（如上面的查询）方面非常有效，并且它应该（假设有相关索引）有效。

编辑

有人刚刚向我建议，人们不喜欢长代码，他们更喜欢紧凑的代码。因此，CTE 版本已替换为内联版本（CTE 实际上只是出于解释性原因帮助分解查询，并且如果需要，则位于编辑历史记录中）...

EDIT

ROW_NUMBER() 不能正如 OP 所发现的那样，构成 WHERE 子句的一部分。通过放回一个 CTE 来更新查询。

Answer 3

我自己找到了解决方案：

with c (Status_Id, Entry_Id, Start_Date) AS (
  select Status_Id, Entry_Id, Start_Date from tbl where
  (End_Date BETWEEN '19000101' AND '21000101')
  AND ((Start_Date BETWEEN '19000101' AND '21000101')
  OR End_Date <= '21000101'))
select Status_Id, count(*) as cnt from 
(select max(Status_Id) as Status_Id, c.Entry_Id from --<--- ADDED
 (select Entry_Id, max(start_date) as start_date from c
  group by Entry_Id) d inner join
c on c.Entry_Id = d.Entry_Id
and c.start_date = d.start_date
group by c.Entry_Id) y  --<--- ADDED
GROUP BY Status_Id WITH ROLLUP