带有不规则时间跨度桶的SQL Server直方图查询

Question

给定每个行有两个datetime字段表示开始和结束时间的50,000条记录，我如何编写Server查询以构建一个直方图，其中包含起始日期和结束日期之间的任意时间桶，例如: 30分钟、0-1小时、1-2小时、2-4小时、4-8小时、8-24小时、24-48小时、48-72小时、3-5天、7+天。

我希望有一种聪明的方法来避免对每个桶执行查询(在本例中是10)。我正在使用作为一个轻量级的ORM，但是原始SQL也可以。

我天真的方法是首先在60分钟前对所有的东西进行桶，然后执行一个子查询来提取每个不规则的桶。

编辑：版本的加分，因为我刚刚了解到生成LINQ语句是可能的。工期表和CASE语句之间有任何性能考虑吗？

Answer 1

试试这个：

SELECT Periods.Period, SUM(Price)
FROM
(
    SELECT '2013-01-01 10:00:00' AS StartDate, '2013-01-01 10:30:00' AS EndDate, 10 AS Price
    UNION ALL
    SELECT '2013-01-01 09:00:00' AS StartDate, '2013-01-01 10:00:00' AS EndDate, 20 AS Price
    UNION ALL
    SELECT '2013-01-01 11:00:00' AS StartDate, '2013-01-01 13:00:00' AS EndDate, 30 AS Price
    UNION ALL
    SELECT '2013-01-01 13:00:00' AS StartDate, '2013-01-01 15:00:00' AS EndDate, 40 AS Price
    UNION ALL
    SELECT '2013-01-01 10:00:00' AS StartDate, '2013-01-01 13:00:00' AS EndDate, 50 AS Price
) AS Prices
INNER JOIN 
(
    SELECT 1 AS Period
    UNION ALL
    SELECT 2 AS Period
    UNION ALL 
    SELECT 3 AS Period
) AS Periods
ON DATEDIFF(HOUR, Prices.StartDate, Prices.EndDate) < Periods.Period
GROUP BY Periods.Period

在您的表中，您可以计算开始日期和结束日期之间的持续时间，然后将它与您的句点(如我所理解的-x轴值)连接到表中，并按这些句点分组。

Answer 2

说你有

SomeTable(Key,StartDate,EndDate)

Select Key,DateDiff(minute,StartDate,EndDate) From SomeTable as RawValue

会给你每把钥匙和每分钟的差别

所以

Select Key,
Case When RawValue < 30 Then "Less than 30 minutes"
Case When RawValue between 30 and 60 then "Less than an hour"
...
else 'Over 7 days' as HistValue
From
(
Select Key,DateDiff(minute,StartDate,EndDate) From SomeTable as RawValue
) RawValues

会给你每把钥匙和不同的范围

所以

Select HistValue,Count(*) From
(
Select Key,
Case When RawValue < 30 Then "Less than 30 minutes"
Case When RawValue between 30 and 60 then "Less than an hour"
...
else 'Over 7 days' as HistValue
From
(
Select Key,DateDiff(minute,StartDate,EndDate) From SomeTable as RawValue
) RawValues
) UncountedValues

你能一次给你一次机会吗，不管怎么说，从我的头顶上。

如果您想要一个更通用的解决方案，那么一种方法是定义一个工期表。

例如：

Category        MinMinutes MaxMinutes
"Less than 30"  0         30

取出硬编码并进行连接。

e.g

Inner join Duration On BucketMinutes between MinMinutes and MaxMinutes

Answer 3

linq方法(只有几个时间间隔就能得到这个想法)

var i30m = TimeSpan.FromMinutes(30).TotalMinutes;
var i60m = TimeSpan.FromMinutes(60).TotalMinutes;
var i2h = TimeSpan.FromHours(2).TotalMinutes;

context.Records.Select(t => SqlMethods.DateDiffMinute(t.StartTime, t.EndTime))
      .GroupBy(i => i < i30m
                      ? "0-30 m"
                      : i < i60m
                          ? "30-60 m"
                          : i < i2h
                              ? "1-2 h"
                              : "Long")
      .Select(i => new {i.Key, Count = i.Count()})

SqlMethods为linq到sql提供Server功能。