sql - 是否有按范围分组的 SQL 查询的既定模式？

Question

我看到了lot of questions关于如何在 SQL 查询中按范围对数据进行分组的 SO。

确切的场景有所不同，但每个场景的一般潜在问题是按一系列值进行分组，而不是按 GROUP BY 列中的每个离散值进行分组。换句话说，按照比数据库表中存储的精度更低的粒度进行分组。

在现实世界中生成直方图、日历表示、数据透视表和其他定制报告输出等内容时，这种情况经常出现。

一些示例数据(不相关的表):

|      OrderHistory       |       |         Staff        |                
---------------------------       ------------------------
|    Date    |  Quantity  |       |   Age     |   Name   |
---------------------------       ------------------------       
|01-Jul-2012 |     2      |       |    19     |   Barry  |
|02-Jul-2012 |     5      |       |    53     |   Nigel  |
|08-Jul-2012 |     1      |       |    29     |   Donna  |
|10-Jul-2012 |     3      |       |    26     |   James  |
|14-Jul-2012 |     4      |       |    44     |   Helen  |
|17-Jul-2012 |     2      |       |    49     |   Wendy  |
|28-Jul-2012 |     6      |       |    62     |   Terry  |
---------------------------       ------------------------

现在假设我们要使用 OrderHistory 表的 Date 列按周(即 7 天范围)进行分组。或者也许将员工分为 10 岁的年龄范围:

|       Week      |  QtyCount  |        |  AgeGroup | NameCount |         
--------------------------------        -------------------------
|01-Jul to 07-Jul |     7      |        |   10-19   |    1      |
|08-Jul to 14-Jul |     8      |        |   20-29   |    2      | 
|15-Jul to 21-Jul |     2      |        |   30-39   |    0      |
|22-Jul to 28-Jul |     6      |        |   40-49   |    2      |
--------------------------------        |   50-59   |    1      |
                                        |   60-69   |    1      |
                                        -------------------------

GROUP BY Date 和 GROUP BY Age 单独使用是不行的。

我看到的最常见的答案(没有一个一致被评为“正确”)是使用以下一个或多个:

• 一堆 CASE 语句，每个分组一个
• 一堆 UNION 查询，每个分组有不同的 WHERE 子句
• 因为我正在使用 SQL Server，PIVOT() and UNPIVOT()
• 使用子选择、临时表或 View 构造的两阶段查询

是否有一个既定的通用模式来处理此类查询？

Answer 1

您可以使用一些维度建模技术，例如fact tables和 dimension tables 。订单历史记录可以充当事实表，具有与日期维度相关的 DateKey 外键。日期维度可以具有如下所示的架构:

请注意，日期表预先填充了最多 N 年的数据。

使用上面的示例，下面是获取结果的示例查询:

select CalendarWeek, sum(Quantity)
from OrderHistory a
join DimDate b
    on a.DateKey = b.DateKey
group by CalendarWeek

对于 Staff 表，您可以存储生日 key 而不是年龄，并让查询计算年龄和范围。

这里是SQL Fiddle

日期维度人口脚本取自 here .