计算日期之间的行数

Question

我正在使用 CTE 生成一系列日期。

12/02/2010 10:00:00 12/02/2010 10:59:59
12/02/2010 11:00:00 12/02/2010 11:59:59
12/02/2010 12:00:00 12/02/2010 12:59:59

然后，我将其连接到包含大量日期的索引视图。

我有 2 个选项用于在日期范围之间进行计数

1) 我会 SUM(case) 测试 log_date 来测试它是否在开始日期和结束日期之间，+ 1 表示 true，0 表示 false - 所以如果没有结果，我总是会得到'0'

12/02/2010 10:00:00 12/02/2010 10:59:59    0
12/02/2010 11:00:00 12/02/2010 11:59:59    1
12/02/2010 12:00:00 12/02/2010 12:59:59    0

2) 我可以对每个日期范围使用 WHERE 子句进行计数(*)。

12/02/2010 11:00:00 12/02/2010 11:59:59    1

正如您所期望的那样，1) 有效，但对性能有巨大的开销 2) 可能效率提高 8000%，但如果应用的过滤器在指定日期范围内返回空结果，则无法返回范围。

有没有办法使用有效的 WHERE 子句但保留详细说明“0”的日期范围行？

这是案例解决方案的一些 SQL：

SELECT     [LABEL], [Display Start Date], [Display End Date], 
    SUM(CASE WHEN ([LOG].line_date BETWEEN [Start Date] AND [End Date]) THEN 1 ELSE 0 END) AS [Total Calls], 
    SUM(CASE WHEN ([LOG].line_date BETWEEN [Start Date] AND [End Date]) AND ([LOG].line_result = 1) THEN 1 ELSE 0 END) AS [1 Calls], 

FROM         [DATE RANGE FUNCTION] LEFT JOIN
                      dbo.vCallLog WITH (noexpand) as [LOG] on 0 > -1
GROUP BY [Start Date], [End Date], [Display Start Date], [Display End Date], [LABEL]

这是 WHERE 解决方案的一些 SQL：

SELECT     [LABEL], [Display Start Date], [Display End Date], 
                    COUNT(dbo.vCallLog.line_id) AS [Total Calls], 
                    SUM(CASE WHEN ([LOG].line_result = 1) THEN 1 ELSE 0 END) AS [1 Calls], 
FROM         [DATE RANGE FUNCTION] LEFT JOIN
                      dbo.vCallLog WITH (noexpand) as [LOG] on 0> -1
WHERE     ([LOG].line_date BETWEEN [Start Date] AND [End Date]) 
GROUP BY [Start Date], [End Date], [Display Start Date], [Display End Date], [LABEL]

Answer 1

好吧，有点狡猾，我意识到我给自己制造了一个小问题：

问题是 WHERE 子句及其相对于 COUNT 子句的位置。如果我使用 WHERE &依靠相同的结果集，那么日期之间的零行我什么也得不到。但是，如果我对所有内容进行计数并省略同一结果集中的 WHERE，并将 WHERE 子句放在 JOIN 中，即 [x] 左连接（从 [b] 中选择 [a]，其中 @x & 之间的 [a] ; @y) as [c] 我返回所有行，然后按字面意思计数。

我认为问题是 WHERE 子句先前省略了计数选择，因为没有采取任何操作（根据编译器）

Answer 2

如果我理解正确的话，你可以尝试类似

DECLARE @DateRanges TABLE(
        StartDate DATETIME,
        EndDate DATETIME
)

INSERT INTO @DateRanges (StartDate,EndDate) SELECT '12/02/2010 10:00:00','12/02/2010 10:59:59' 
INSERT INTO @DateRanges (StartDate,EndDate) SELECT '12/02/2010 11:00:00','12/02/2010 11:59:59' 
INSERT INTO @DateRanges (StartDate,EndDate) SELECT '12/02/2010 12:00:00','12/02/2010 12:59:59'

DECLARE @DateValues TABLE(
        DateVal DATETIME
)

INSERT INTO @DateValues (DateVal) SELECT '12/02/2010 11:00:00'
INSERT INTO @DateValues (DateVal) SELECT '12/02/2010 11:01:00'
INSERT INTO @DateValues (DateVal) SELECT '12/02/2010 12:01:00'

SELECT  t.StartDate,
        t.EndDate,
        COUNT(tv.DateVal) CountVal
FROM    @DateRanges t LEFT JOIN
        @DateValues tv ON tv.DateVal BETWEEN t.StartDate AND t.EndDate
GROUP BY    t.StartDate,
            t.EndDate

输出的东西

StartDate               EndDate                 CountVal
----------------------- ----------------------- -----------
2010-12-02 10:00:00.000 2010-12-02 10:59:59.000 0
2010-12-02 11:00:00.000 2010-12-02 11:59:59.000 2
2010-12-02 12:00:00.000 2010-12-02 12:59:59.000 1

Answer 3

啊，臭名昭著的 WHERE 陷阱。当您有一个 LEFT JOIN 和一个测试右侧列中的条件的 WHERE 子句时，您必须包括

WHERE (<Condition based on rightHandTable.Column> OR rightHandTable.Column IS NULL)

当您有复合 where 条件时，将尾随 OR 语句放在括号中也是一个好主意：

WHERE a=1 AND b=1 OR b iS NULL

当 a and 时，此计算结果为 true b = 1 或当 b 为 null

且与此不同时，

WHERE a=1 AND (b=1 OR b is NULL)

意味着 a 必须为 1 并且 b 必须为 1 或 null