在SQL Server 2008中，是否有更有效的按日期分组方式

Question

我必须创建一个AccountSegment作为行，两周日期范围作为列标题的报告。列值将是表中具有相关段/日期范围的记录数的计数。

因此，所需的输出如下所示：

AcctSeg  4/9/12-4/20/12   4/23/12-5/4/12   5/7/12-5/18/12
Segment1       100             200              300
Segment2       110             220              330
Segment3       120             230              340

下面的查询做了我想要的，但是看起来非常低效和丑陋。我想知道是否有更好的方法来完成同样的事情：

SELECT
    AccountSegment = S.Segment_Name,
    '4/9/2012 - 4/20/2012' = SUM(CASE WHEN date_start BETWEEN '2012-04-09' AND '2012-04-20' THEN 1 END),
    '4/23/2012 - 5/4/2012' = SUM(CASE WHEN date_start BETWEEN '2012-04-23' AND '2012-05-04' THEN 1 END),
    '5/7/2012 - 5/18/2012' = SUM(CASE WHEN date_start BETWEEN '2012-05-07' AND '2012-05-18' THEN 1 END),
    '5/21/2012 - 6/1/2012' = SUM(CASE WHEN date_start BETWEEN '2012-05-21' AND '2012-06-01' THEN 1 END),
    '6/4/2012 - 6/15/2012' = SUM(CASE WHEN date_start BETWEEN '2012-06-04' AND '2012-06-15' THEN 1 END),
    '6/18/2012 - 6/29/2012' = SUM(CASE WHEN date_start BETWEEN '2012-06-18' AND '2012-06-29' THEN 1 END),
    '7/2/2012 - 7/13/2012' = SUM(CASE WHEN date_start BETWEEN '2012-07-02' AND '2012-07-13' THEN 1 END),
    '7/16/2012 - 7/27/2012' = SUM(CASE WHEN date_start BETWEEN '2012-07-16' AND '2012-07-27' THEN 1 END),
    '7/30/2012 - 8/10/2012' = SUM(CASE WHEN date_start BETWEEN '2012-07-30' AND '2012-08-10' THEN 1 END)
FROM
    dbo.calls C
    JOIN dbo.accounts a ON C.parent_id = a.id
    JOIN dbo.accounts_cstm a2 ON a2.id_c = A.id
    JOIN dbo.Segmentation S ON a2.[2012_segmentation_c] = S.Segment_Num
WHERE
    c.deleted = 0 
GROUP BY
    S.Segment_Name
ORDER BY
    MIN(S.Sort_Order)

Answer 1

它看起来很好，但我建议进行一次性能改进：

where c.deleted = 0 and
      date_start between  '2012-04-09' AND '2012-08-10'

这将仅将聚合限制为您需要的行。。。除非您希望所有内容都以空数据列出。

我倾向于将else 0添加到case语句中，这样就会出现0而不是NULLs。

Answer 2

@PaulStock，很高兴这样做。

这种技术发挥了RDMS的强项，即数据检索和集合操作-让其他编程语言来处理，这些语言对它进行了更好的优化，比如C#。

首先你需要一个IndexTable，我把我的放在主数据库里，但是如果你没有写权限，一定要把它放在你的数据库里。

它看起来是这样的：

Id
 0
 1
 2
...
n

其中n对于您的所有场景来说都是足够大的数字，100,000很好，1,000,000更好，10,000,000更好。当然，列id是集群索引的。

我不打算直接把它和你的查询联系起来，因为我真的不理解它，而且我太懒了，不能解决它。

相反，我将把它与这个名为Transactions的表关联起来，在这个表中，我们希望汇总每天(或每周或每月等)发生的所有事务：

Date                       Amount
2012-18-12 04:58:56.453       10
2012-18-12 06:34:21.456      100
etc

以下查询将按天汇总数据

SELECT  i.Id, SUM(t.Amount) AS DailyTotal
FROM    IndexTable i
        INNER JOIN
        Transactions t ON i.Id=DATEDIFF(DAY, 0, t.Date)
GROUP BY i.Id

DATEDIFF函数返回两个日期之间的日期部分的数量，在本例中是1900-01-01 0:00:00.000 ( SQL Server中的DateTime=0)和事务日期之间的天数(顺便说一句，从那以后已经有41,261天了--看看为什么我们需要一个大表)

同一天的所有交易将具有相同的编号。更改为周、月或秒(一个非常大的数字)与更改日期部分一样简单。

当然，您可以添加一个晚于此日期的起始日期，只要它早于您感兴趣的数据，但这对性能影响不大。

我在这里使用了一个INNER JOIN，所以如果在给定的一天没有交易，那么我们就没有行，但是LEFT JOIN将给出这些空日期，并将NULL作为总数(如果您想得到0，请使用ISNULL语句。

有了归一化的数据，您就可以根据需要进行PIVOT，以获得您正在寻找的输出。