SQL Wizardry 第八部分 - Tally Tables
4.96/5 (19投票s)
关于如何创建 Tally Tables 的最佳方法以及如何使用它们的说明
引言
在 SQL 世界中，有一个常见的缩写。那就是 RBAR，代表“逐行痛苦地逐行”（row by agonising row）。SQL 是基于集合的，这意味着您通过定义规则来操作数据集，以清楚地说明这些数据集是什么。任何时候您偏离这一点，并使用一次处理一行数据的过程式代码来处理数据，您都会面临使您的进程变慢的巨大风险。
这种情况的替代方案，通常被称为“Tally Table”。它只是一个包含一系列数字的表。我在之前的文章中曾使用过这样的表，今天我将深入研究创建它的代码，并讨论除了我之前向您展示的那些用途之外，还有一些额外的用途。
创建表
这是来自示例文件的第一个 SQL 代码块
WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*10^2 or 10000 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b) -- 1*10^2 or 100000000 rows
select 'E1', count(1) from e1 union all
select 'E2', count(1) from e2 union all
select 'E4', count(1) from e4 union all
select 'E8', count(1) from e8
;
它返回以下内容
E1 10 E2 100 E4 10000 E8 100000000
SQL 中的逗号是交叉连接的简写。这不是在通用值上进行连接的连接，它只是获取表 a 中的每个值，然后对于每个值，返回表 b 中的每个值。其效果显然是指数级的。因此，我们从一个创建 10 行的 union all 块开始。然后我们进行交叉连接，得到 10 * 10，即 100 行。在此表上进行交叉连接会得到 100 * 100，即 10000 行。对这个最终表进行交叉连接会得到 100000000 行，在大多数情况下应该足够了。最后的 select 通过返回行名称和每行的数量来显示这一点。
请注意，这非常快。网上有很多关于创建 Tally Table 方法的说法，请相信我，这是最有效的方法。
第二个 SQL 代码块如下所示
WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*10^2 or 10000 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), -- 1*10^2 or 100000000 rows
E(N) as(select row_number() over (order by n) from e8)
select distinct top 100 e8.n as 'E8', e.n as e from e, e8
;
第一个代码块的问题在于，我们得到了一个包含 100000000 个 1 的表。现在我们使用 row_number() 来创建数字序列，这是使其真正有用的关键。select 语句从 e8 中选择 100 个值，但通常，如果我需要少于 10000 行，我只需减少创建的表数量。因此，在这种情况下，删除 E4、E8 和 top 语句会更有效。
生成二进制序列
Tally Table 最简单的用途，就是使用序列结合一些数学运算来生成除了 1, 2, 3, 4 等之外的序列。这是一个二进制序列。
;WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
E(N) as(select row_number() over (order by n) from e2)
select power(2, n) from e where n < 30
;
日期范围
我们可能需要不时进行的另一项操作是生成日期范围。如果您需要的范围不包含所有日期，我们可以将此示例与接下来的示例混合使用，以创建数字序列，该序列不是以 1 递增，或者添加一个 where 子句来跳过我们想要跳过的值，如果规则不是纯数字的话。这是简单版本。请注意，您不需要返回日、月和年，我在这里仅为说明目的这样做
DECLARE @BeginDate DATE = '2011-01-01', @EndDate DATE = '2012-06-30'
;WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*10^2 or 10000 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), -- 1*10^2 or 100000000 rows
E(N) as(select row_number() over (order by n) from e8)
SELECT DATEADD(DD, N-1, @BeginDate) [Date]
,DAY(DATEADD(DD, N-1, @BeginDate)) [Day]
,MONTH(DATEADD(DD, N-1, @BeginDate)) [Month]
,YEAR(DATEADD(DD, N-1, @BeginDate)) [Year]
FROM E
WHERE N <= DATEDIFF(DD, @BeginDate, @EndDate) + 1
;
查找缺失的日期
那么，我们如何处理可以生成日期范围之后的事情呢？关注这些文章的任何人都会知道，我喜欢使用 AdventureWorks 数据库来获取数据进行处理。请现在使用它并运行此命令
DECLARE @DateStart DATETIME = '2005-07-01 00:00:00.000'
;WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*100^2 or 10000 rows
E(N) as(select row_number() over (order by n) from e4)
select convert(date, dateadd(dd, t.N, @DateStart))
from Sales.SalesOrderHeader o
right join E t on dateadd(dd, t.N, @DateStart) = o.OrderDate
where o.orderdate is null and dateadd(dd, t.N, @DateStart) < getdate()
我们可以将最低日期提取到 SQL 中，但这可能不会改变，所以将其提取并硬编码（假设这是一个经常使用的存储过程）效率更高。我们在这里做了一些事情。我们使用外部连接来获取 null 值以识别连接失败的位置。我们使用 Tally Table 来生成日期。Adventureworks 使用 DateTime 仅用于日期，我假设时间部分始终为空，这很容易修复，但会使示例混乱。我们唯一要做的就是确保我们不会计算到“今天”之后，因为我们不关心未来没有收到订单。最终结果是没有收到订单的日期。
另一个使它更有用的方法是，剔除与我们无关的值。此版本仅返回我们未收到订单的日期，且这些日期不是星期六或星期日。
DECLARE @DateStart DATETIME = '2005-07-01 00:00:00.000'
;WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*100^2 or 10000 rows
E(N) as(select row_number() over (order by n) from e4)
select convert(date, dateadd(dd, t.N, @DateStart))
from Sales.SalesOrderHeader o
right join E t on dateadd(dd, t.N, @DateStart) = o.OrderDate
where o.orderdate is null
and dateadd(dd, t.N, @DateStart) < getdate()
and datepart(dw, dateadd(dd, t.N, @DateStart)) not in (1,7)
查找子字符串位置
在 SQL 中处理数据单元（字符串）作为一个项目组，总是很麻烦，最好避免。但是，我们经常需要在现有系统上工作，而无法更改它们。以下 SQL 将返回分隔符在字符串中的位置。如果我们愿意，将分隔符改为多字符也非常简单。只需将 substring 中的 1 更改为 len(@delimiter)。
DECLARE @val varchar(max) = 'Christian;Donna;Hannah;Calvin;Jenny;Joe', @delimiter char(1) = ';'
;WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*100^2 or 10000 rows
E(N) as(select row_number() over (order by n) from e4)
SELECT n AS [Index]
FROM E
WHERE n <= LEN(@val)
AND SUBSTRING(@val, n, 1) = @delimiter
ORDER BY N
go
提取子字符串
现在，编写返回这些子字符串而不是位置的代码很简单。这是 SS2012 版本
DECLARE @val varchar(max) = 'Christian;Donna;Hannah;Calvin;Jenny;Joe', @delimiter char(1) = ';'
;WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*100^2 or 10000 rows
E(N) as(select row_number() over (order by n) from e4),
ind(N) as
(
SELECT n AS [Index]
FROM E
WHERE n <= LEN(@val)
AND SUBSTRING(@val, n, 1) = @delimiter
),
words(start, [end]) as
(
select lag(n, 1, -1) over (order by n) + 1, n - (lag(n, 1, -1) over (order by n)) - 1 from ind
)
select substring(@val, start, [end]) from words as Names
GO
如果您运行此代码，会发现它不会输出最后一个字符串。这是因为它通过查找分隔符来工作。我看到有两种方法可以解决这个问题。第一，始终在字符串末尾添加分隔符。第二，将字符串末尾视为第一个分隔符。此 SQL 采用第二种方法，并将最后一个索引之后的分隔符长度作为最终位置返回（因此我们向后退以删除分隔符，并得到完整的字符串）。感谢 Duke Carey 指出这一点）
DECLARE @val varchar(max) = 'Christian;Donna;Hannah;Calvin;Jenny;Joe', @delimiter char(1) = ';'
;WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*100^2 or 10000 rows
E(N) as(select row_number() over (order by n) from e4),
ind(N) as
(
SELECT n AS [Index]
FROM E
WHERE n <= LEN(@val) + len(@delimiter)
AND (
SUBSTRING(@val, n, 1) = @delimiter or n = len(@val) + len(@delimiter)
)
),
words(start, [end]) as
(
select lag(n, 1, -1) over (order by n) + 1, n - (lag(n, 1, -1) over (order by n)) - 1 from ind
)
select substring(@val, start, [end]) from words as Names
GO
这是旧版本 SQL Server 的版本
DECLARE @val varchar(max) = 'Christian;Donna;Hannah;Calvin;Jenny;Joe', @delimiter char(1) = ';'
;WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*100^2 or 10000 rows
E(N) as(select row_number() over (order by n) from e4),
ind(N, r) as
(
SELECT n AS [Index], row_number() over (order by n)
FROM E
WHERE n <= LEN(@val)
AND SUBSTRING(@val, n, 1) = @delimiter
),
words(start, [end]) as
(
select isnull(ind1.n, -1) + 1, isnull(ind2.n,len(@val) + 1) from ind ind1 full join ind ind2 on ind1.r = ind2.r-1
)
select substring(@val, start, [end] - start) from words as Names
计算子字符串的出现次数
鉴于我们正在查找子字符串（分隔符），计算它们很简单
DECLARE @val varchar(max) = 'Christian;Donna;Hannah;Calvin;Jenny;Joe', @find char(2) = ';J'
;WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*100^2 or 10000 rows
E(N) as(select row_number() over (order by n) from e4)
SELECT COUNT(1) AS [COUNT]
FROM (
SELECT N AS POS
FROM e
WHERE N <= LEN(@VAL)
AND SUBSTRING(@VAL, N, LEN(@FIND)) = @Find
) T
在这种情况下，我们的子字符串包括分隔符，这意味着我们找到了两个以 J 开头的名字。这种方法的弱点是，如果我们想计算以 C 开头的名字怎么办？解决方法是添加一个起始分隔符，如下所示
DECLARE @val varchar(max) = 'Christian;Donna;Hannah;Calvin;Jenny;Joe', @find char(2) = ';C'
;WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*100^2 or 10000 rows
E(N) as(select row_number() over (order by n) from e4)
SELECT COUNT(1) AS [COUNT]
FROM (
SELECT N AS POS
FROM e
WHERE N <= LEN(@find + @VAL)
AND SUBSTRING(@find + @VAL, N, LEN(@FIND)) = @Find
) T
结论
我有点改变了我对这个系列名称的想法。我认为更好的名称是“SQL 思维”，因为如果您习惯于编写过程式代码，那么停止那样思考并以集合为基础的逻辑来思考可能是一种挑战。Tally Table 只是利用数据库引擎以其最擅长的方式执行重复任务的一种方式，即通过集合来工作。下次您忍不住要编写一次增加一个计数器的代码，无论是为了计算某事的发生频率，还是为了将一系列数字应用于某个操作，请停下来想想 Tally Table 是否是解决您问题的更好方法。我曾考虑创建一个基于表的函数来创建我的 Tally Table，但是，基于表的函数会在 tempdb 中创建一个表，这正是我试图通过我创建它们的方式来避免的。如果您有任何进一步的见解想提供关于这种可能性，我洗耳恭听，但目前我还是谨慎行事，并动态创建它们。