DuckDB实战：高级数据聚合和分析
这章的目的是启发您思考如何使用 DuckDB 等分析型数据库来生成报表，相比于使用命令式编程语言编写的庞大代码，这种方法更加高效。虽然我们将以第三章为基础进行讲解，但我们将很快跳过简单的 SELECT xzy FROM abc 语句。学习现代 SQL 不会浪费您的时间，这里介绍的结构可以在任何可以运行或嵌入 DuckDB 的地方使用，从而丰富您的应用程序。
在数据摄入时预聚合
让我们继续进行我们的示例场景。在第3.4.1节中，我们处理了光伏电网的数据，虽然存在一些一致性问题，但非常适合我们的架构和想法。请记住，目标是以15分钟的间隔存储测量值。如果您查看您在第3.2.1节中下载的其他数据集，您会注意到一些数据集的间隔不是15分钟。快速查看文件的一种方法是使用tail命令，该命令返回文件的最后n行（head也可以使用）。对2020_10.csv文件使用它会显示该文件包含1分钟间隔的测量值：
> duckdb -s ".maxwidth 40" -s "FROM read_csv_auto('2020_10.csv') LIMIT 3"
┌────────┬─────────────────────┬─────────────────┬─────────────────┬───────────────────┐
│ SiteID │ Date-Time │ ... │ module_temp_3 │ poa_irradiance │
│ int64 │ timestamp │ │ double │ double │
├────────┼─────────────────────┼─────────────────┼─────────────────┼───────────────────┤
│ 10 │ 2020-01-23 11:20:00 │ ... │ 14.971 │ 748.36 │
│ 10 │ 2020-01-23 11:21:00 │ ... │ 14.921 │ 638.23 │
│ 10 │ 2020-01-23 11:22:00 │ ... │ 14.895 │ 467.67 │
├────────┴─────────────────────┴─────────────────┴─────────────────┴───────────────────┤
│ 3 rows 16 columns (4 shown) │
└──────────────────────────────────────────────────────────────────────────────────────┘
当然，2020_1200.csv有另一个间隔，这次是5分钟，但整体结构看起来也不同：
> duckdb -s ".maxwidth 40" -s "FROM read_csv_auto('2020_1200.csv') LIMIT 3"
┌────────┬─────────────────────┬───────────────┬────────────────────┬───────────────┐
│ SiteID │ Date-Time │ ... │ ac_power_metered │ power_factor │
│ int64 │ timestamp │ │ int64 │ double │
├────────┼─────────────────────┼───────────────┼────────────────────┼───────────────┤
│ 1200 │ 2020-01-01 00:00:00 │ ... │ 20 │ 0.029 │
│ 1200 │ 2020-01-01 00:05:00 │ ... │ 20 │ 0.029 │
│ 1200 │ 2020-01-01 00:10:00 │ ... │ 20 │ 0.029 │
├────────┴─────────────────────┴───────────────┴────────────────────┴───────────────┤
│ 3 rows 6 columns (4 shown) │
└───────────────────────────────────────────────────────────────────────────────────┘
记住，这些数据文件来自同一池。即使它们在不同来源之间也是不一致的。数据分析往往就是处理这些确切的问题。让我们使用DuckDB提供的众多函数之一来处理日期、时间和时间戳，例如time_bucket()。time_bucket()将时间戳截断到给定的间隔，并将它们对齐到可选的偏移量，创建一个时间桶。时间桶是聚合传感器读数等数据的强大机制。结合GROUP BY和avg作为聚合函数，我们可以根据我们的要求准备并最终摄入数据：我们在15分钟的间隔内创建时间桶，并计算落入特定桶的所有读数的平均产生功率。
当您查看查询时，您会注意到一个CASE WHEN THEN ELSE END结构，即CASE语句，它的工作原理类似于if/else结构。在这里，它的作用是在计算平均值之前将值低于零或根本没有值的读数转换为零。这是这个数据集的一个奇怪之处：也许传感器有问题，也许网络有问题，你永远不会知道，但你必须处理这些数据。在这里，我们决定将空值视为负值并将其限制为零是可以接受的。在计算中，如果这种情况影响到了您的计算，您可以考虑对聚合进行筛选。我们将在第46.3节讨论这一点。
INSERT INTO readings(system_id, read_on, power)
SELECT any_value(SiteId), -- #1
time_bucket(INTERVAL '15 Minutes', CAST("Date-Time" AS timestamp)) AS read_on, -- #2
avg(CASE WHEN ac_power < 0 OR ac_power IS NULL THEN 0 ELSE ac_power END) -- #3
FROM read_csv_auto('https://developer.nrel.gov/api/pvdaq/v3/data_file?api_key=DEMO_KEY&system_id=10&year=2019')
GROUP BY read_on
ORDER BY read_on;
剩余数据集的导入方式是相同的，您需要相应地在FROM子句中更改文件名。
提示：DuckDB中有更多基于日期和时间的函数，如果有疑问，请查看参考文档：duckdb.org/docs/sql/fu…。您将能够将几乎任何字符串解析为正确的日期或时间戳。
是否根本不进行摄入，而是基于外部文件在内存中进行各种类型的分析，是否在摄入过程中在一定程度上进行聚合，或者仅在分析过程中进行聚合，通常是一种权衡，取决于数据集的大小、长期存储的目标、进一步处理的需求等等。在此处试图制定一种通用的解决方案往往会失败。在这个场景中，我们决定出于教育目的进行摄入和聚合，并且使数据集足够小，可以共享。
汇总数据
通常，在进行深入分析之前，您希望了解新数据集的一些特征，例如值的数量（在我们的示例中，读数的数量），数值值的分布和大小（如果不知道我们处理的是瓦特还是千瓦，我们的报告将明显错误），以及时间序列的间隔大小。
DuckDB具有独特的SUMMARIZE命令，可以快速为您提供关于任何数据集的这些信息。在您的数据库中运行SUMMARIZE readings;。您的结果应该类似于以下内容：
┌─────────────┬───────────────┬─────────────────────┬───────┬─────────┬────────┐
│ column_name │ column_type │ max │ ... │ q75 │ count │
│ varchar │ varchar │ varchar │ │ varchar │ int64 │
├─────────────┼───────────────┼─────────────────────┼───────┼─────────┼────────┤
│ system_id │ INTEGER │ 1200 │ ... │ 1200 │ 151879 │
│ read_on │ TIMESTAMP │ 2020-06-26 11:00:00 │ ... │ │ 151879 │
│ power │ DECIMAL(10,3) │ 133900.000 │ ... │ 5125 │ 151879 │
└─────────────┴───────────────┴─────────────────────┴───────┴─────────┴────────┘
还有许多列，但为了可读性，我们对列表进行了缩写。通过运行.mode line将您的CLI切换到行模式，然后使用SUMMARIZE SELECT read_on, power FROM readings WHERE system_id = 1200;来对读数的子集进行汇总：
column_name = read_on
column_type = TIMESTAMP
min = 2019-01-01 00:00:00
max = 2020-06-26 11:00:00
approx_unique = 50833
avg =
std =
q25 =
q50 =
q75 =
count = 52072
null_percentage = 0.0%
column_name = power
column_type = DECIMAL(10,3)
min = 0.000
max = 47873.333
approx_unique = 6438
avg = 7122.5597121293595
std = 11760.089219586542
q25 = 20
q50 = 27
q75 = 9532
count = 52072
null_percentage = 0.0%
SUMMARIZE直接作用于表格，但如上所示，也可以用于查询结果。在应用SUMMARIZE之前，您甚至不必先摄入数据，它也可以针对CSV文件或Parquet文件运行。
关于子查询
假设您想要计算您管理的系统产生的总功率的平均值。为此，您需要应用两个聚合函数，avg和sum。结果发现，您不能嵌套它们。类似SELECT avg(sum(kWh)) FROM v_power_per_day GROUP BY system_id的天真方法会失败，错误信息为：绑定器错误：聚合函数调用不能嵌套。您需要对该计算进行分阶段处理，子查询是实现此目的的一种方法：
SELECT avg(sum_per_system)
FROM (
SELECT sum(kWh) AS sum_per_system
FROM v_power_per_day
GROUP BY system_id
);
这个语句现在如实地返回了avg(sum_per_system) = 133908.087。此语句中的内部查询具有两个特点：
- 它返回多行
- 它不依赖于外部查询的值
这个查询被称为无关子查询。无关子查询只是嵌套在另一个查询中的查询，并且像外部查询在内部查询的结果上执行一样。
现在我们继续下一个可能的任务：在哪一天和哪个系统产生的功率最高？解决这个问题的一种方法是在WHERE子句中使用子查询作为比较的右操作数。
SELECT read_on, power
FROM readings
WHERE power = (
SELECT max(power)
FROM readings
);
这个子查询与第一个不同，因为它只返回一个单一的标量值。它被称为标量无关子查询。
注意：arg_min和arg_max是聚合函数，用于计算出现最小值或最大值的行的表达式。如果您只对一个表达式感兴趣，那么与上面类似的任务相比，它们是首选的解决方案。如果您对多个表达式感兴趣或需要评估最小值或最大值之外的其他值，那么在条件中避免不了使用子查询。
结果基本上表明：“在5个不同的时间点产生了最大输出功率133900瓦特”：
┌─────────────────────┬───────────────┐
│ read_on │ power │
│ timestamp │ decimal(10,3) │
├─────────────────────┼───────────────┤
│ 2019-05-08 12:15:00 │ 133900.000 │
│ 2019-05-23 10:00:00 │ 133900.000 │
│ 2019-05-23 11:30:00 │ 133900.000 │
│ 2019-05-28 11:45:00 │ 133900.000 │
│ 2020-04-02 11:30:00 │ 133900.000 │
└─────────────────────┴───────────────┘
如果我们想要根据每个系统确定最大功率和读取时间怎么办？使用原始子查询可能会很棘手，因为它只显示了整体最大功率产量的值。我们需要子查询返回不同行的不同值；为此，我们可以使用相关子查询，它在内部查询中使用外部查询的字段，如下所示：
SELECT system_id, read_on, power
FROM readings r1
WHERE power = (
SELECT max(power)
FROM readings r2
WHERE r2.system_id = r1.system_id -- #1
)
ORDER BY ALL;
现在这个子查询是一个标量、相关的子查询。内部查询与外部查询相关联，数据库必须对外部查询的每一行进行评估。
在结果中，我们再次看到了最高值的5天，以及现在也看到了系统10和1200产生的最高值：
┌───────────┬─────────────────────┬───────────────┐
│ system_id │ read_on │ power │
│ int32 │ timestamp │ decimal(10,3) │
├───────────┼─────────────────────┼───────────────┤
│ 10 │ 2019-02-23 12:45:00 │ 1109.293 │
│ 34 │ 2019-05-08 12:15:00 │ 133900.000 │
│ 34 │ 2019-05-23 10:00:00 │ 133900.000 │
│ 34 │ 2019-05-23 11:30:00 │ 133900.000 │
│ 34 │ 2019-05-28 11:45:00 │ 133900.000 │
│ 34 │ 2020-04-02 11:30:00 │ 133900.000 │
│ 1200 │ 2020-04-16 12:15:00 │ 47873.333 │
└───────────┴─────────────────────┴───────────────┘
当作为表达式使用时，子查询可以重写为连接，计算嵌套聚合是个例外。对于最后一个示例，它会像这样：
SELECT r1.system_id, read_on, power
FROM readings r1
JOIN (
SELECT r2.system_id, max(power) AS value
FROM readings r2
GROUP BY ALL
) AS max_power ON (
max_power.system_id = r1.system_id AND
max_power.value = r1.power
)
ORDER BY ALL;
读者可以判断这是否增加了可读性。在其他关系型数据库中，人们经常这样做，因为对于大表中的每一行都评估相关子查询可能会很慢。另一方面，DuckDB使用一个子查询解耦优化器，总是使子查询独立于外部查询，因此允许用户自由地使用子查询来创建具有表现力的查询，而无需担心手动将子查询重写为连接。通过重新编写SQL，手动解耦某些子查询并不总是可能的。在内部，DuckDB使用特殊类型的连接来解耦所有子查询。实际上，DuckDB不支持执行未解耦的子查询。
对你来说好的一点是，你可以专注于查询的可读性和表达能力，以及你试图解决的业务问题，而不需要担心使用哪种类型的子查询。
子查询作为表达式
所有形式的子查询，无论是相关的还是无关的，在不用作连接中的关系时都是表达式。因此，可以使用更多的运算符。等于运算符=和不等运算符<、<=、>=和>要求子查询是标量子查询，返回确切的一行。在标量和非标量子查询中工作时，还存在其他运算符。这些是IN、EXISTS、ANY和ALL，它们通过进行集合比较来工作。
子查询也可以用于集合比较，回答诸如“给我所有与另一个查询返回的所有行中的任意行进行成功比较的行”的问题。
本节中的人造示例都将返回v = 7。
'EXISTS'
您可能希望选择具有可能存在于另一张表的一行中的值的表的所有行。为此，可以使用EXISTS表达式：
.mode line
SELECT * FROM VALUES (7), (11) s(v)
WHERE EXISTS (SELECT * FROM range(10) WHERE range = v);
'IN'
通常可以使用IN运算符将EXISTS重写为一个非相关的子查询：当外部值至少在子查询结果中包含一次时，此运算符评估为true。
.mode line
SELECT * FROM VALUES (7), (11) s(v)
WHERE v IN (SELECT * FROM range(10));
当您使用除DuckDB之外的其他关系型数据库时，了解这一点是很有用的，因为它们可能不会对子查询进行所有类型的优化。
'ANY'
IN运算符使用每个值的等值比较。您可能会发现自己处于这样一种情况：您想要回答任何值是否满足不等式条件。在这种情况下，您需要将ANY运算符与所需的比较一起使用。当外部值与任何内部值的比较评估为true时，整个表达式将评估为true。
.mode line
SELECT * FROM VALUES (7), (11) s(v)
WHERE v <= ANY (SELECT * FROM range(10)); -- #1
'ALL'
最后但并非最不重要的是，ALL运算符，当外部值与所有内部值的比较都评估为true时，它将评估为true。它可以帮助您找到其中一个值满足子查询的所有值之间的比较的行。虽然您可以用IN()替换= ANY()，但是对于ALL运算符没有这样的简化。
.mode line
SELECT * FROM VALUES (7), (11) s(v)
WHERE v = ALL (SELECT 7);
分组集
在列表3.1中，我们创建了一个名为readings的表，其中包含日期、时间以及该时间产生的实际功率值。我们还建议了从美国国家可再生能源实验室导入的几个示例数据集。当查看这样的数据集时，了解属性的最小值和最大值，或者平均值总是有帮助的。有时您可能会在其中有一些异常值需要删除，或者可能您在单位上犯了一个错误。最简单的计算方法就是在一个查询中使用它们，而不使用任何GROUP BY子句，这样聚合就会在一个桶中进行：整个表。
SELECT count(*),
min(power) AS min_W,
max(power) AS max_W,
round(sum(power) / 4 / 1000, 2) AS kWh -- #1
FROM readings;
如果您按照建议操作，您的readings表应该具有类似以下的关键数据，这是上述查询的结果：
┌──────────────┬───────┬────────────┬───────────┐
│ count_star() │ min_W │ max_W │ kWh │
├──────────────┼───────┼────────────┼───────────┤
│ 151879 │ 0.000 │ 133900.000 │ 401723.22 │
└──────────────┴───────┴────────────┴───────────┘
读数看起来是合理的，甚至最小值为零：夜间确实没有生产。正如我们在图像3.2中已经了解到的GROUP BY子句一样，我们可以进一步查看每小时每个系统的产量。我们还将选择每个系统的读数数量。我们导入了几年的数据，将读数截断为15分钟间隔，因此我们应该每年找到大约35040个读数。通过按系统ID、年份分组的方式确认了这一假设，如下一条语句所示：
SELECT year(read_on) AS year,
system_id,
count(*),
round(sum(power) / 4 / 1000, 2) AS kWh
FROM readings
GROUP BY year, system_id
ORDER BY year, system_id;
结果是符合预期的。我们确实有一堆无效值，而第二年在2020年中途结束：
┌──────┬───────────┬──────────────┬───────────┐
│ year │ system_id │ count_star() │ kWh │
├──────┼───────────┼──────────────┼───────────┤
│ 2019 │ 10 │ 33544 │ 1549.34 │
│ 2019 │ 34 │ 35040 │ 205741.9 │
│ 2019 │ 1200 │ 35037 │ 62012.15 │
│ 2020 │ 10 │ 14206 │ 677.14 │
│ 2020 │ 34 │ 17017 │ 101033.35 │
│ 2020 │ 1200 │ 17035 │ 30709.34 │
└──────┴───────────┴──────────────┴───────────┘
现在，对于总数，即每年和每个系统以及总体的读数总数和总发电量是多少？换句话说：我们能否创建一个钻取报告，显示每个组的不同详细级别？虽然我们现在可以逐个将数字输入计算器并手动求和，或者编写一个额外的没有分组键的计数查询，就像最初的那个，但有一个更好的选择叫做“分组集”：
SELECT year(read_on) AS year,
system_id,
count(*) AS count_star,
round(sum(power) / 4 / 1000, 2) AS kWh
FROM readings
GROUP BY GROUPING SETS ((year, system_id), year, ())
ORDER BY year NULLS FIRST, system_id NULLS FIRST;
在我们解析GROUP BY GROUPING SETS ((system_id, year), year, ())之前，让我们先看一下结果：
┌──────┬───────────┬──────────────┬───────────┐
│ year │ system_id │ count_star() │ kWh │
├──────┼───────────┼──────────────┼───────────┤
│ │ │ 151879 │ 401723.22 │
│ 2019 │ │ 103621 │ 269303.39 │
│ 2019 │ 10 │ 33544 │ 1549.34 │
│ 2019 │ 34 │ 35040 │ 205741.9 │
│ 2019 │ 1200 │ 35037 │ 62012.15 │
│ 2020 │ │ 48258 │ 132419.83 │
│ 2020 │ 10 │ 14206 │ 677.14 │
│ 2020 │ 34 │ 17017 │ 101033.35 │
│ 2020 │ 1200 │ 17035 │ 30709.34 │
└──────┴───────────┴──────────────┴───────────┘
分组集创建了几个桶来计算聚合值：
- 由 system_id 和 year 的组合值定义的一个桶（在我们的示例中有 6 个不同的组合，因此有 6 行）
- 仅由 year 定义的一个桶。对于未包含在此桶中但包含在其他集中的键，提供空值（此处为 system_id）
- 最后一个（()）可以描述为空的桶或组：为所有其他键提供空值
结果包含了列表 4.11 返回的所有内容，再加上每年的读数数量（仅按年分组），以及总体计数（不进行分组）。
使用缩写子句 ROLLUP 也可以达到相同的结果。ROLLUP 子句会自动为您生成上面讨论的集合，数量为 n+1，其中 n 是 ROLLUP 子句中的术语数量：
SELECT year(read_on) AS year,
system_id,
count(*) AS count_star,
ROUND(SUM(power) / 4 / 1000, 2) AS kWh
FROM readings
GROUP BY ROLLUP (year, system_id)
ORDER BY year NULLS FIRST, system_id NULLS FIRST;
如果我们想要查看所有年份中每个系统的总计，这也是完全可能的。与其从ROLLUP退回到GROUP BY GROUPING SETS并手动添加，您可以使用GROUP BY CUBE。GROUP BY CUBE不会产生子组，而是实际的组合（2^n个分组集）。在我们的例子中，有（year, system_id）、（year）、（system）和（）这四种组合：
SELECT year(read_on) AS year,
system_id,
count(*) AS count_star,
round(sum(power) / 4 / 1000, 2) AS kWh
FROM readings
GROUP BY CUBE (year, system_id)
ORDER BY year NULLS FIRST, system_id NULLS FIRST;
现在生成的结果如下：
┌──────┬───────────┬──────────────┬───────────┐
│ year │ system_id │ count_star() │ kWh │
├──────┼───────────┼──────────────┼───────────┤
│ │ │ 151879 │ 401723.22 │
│ │ 10 │ 47750 │ 2226.48 │
│ │ 34 │ 52057 │ 306775.25 │
│ │ 1200 │ 52072 │ 92721.48 │
│ 2019 │ │ 103621 │ 269303.39 │
│ 2019 │ 10 │ 33544 │ 1549.34 │
│ 2019 │ 34 │ 35040 │ 205741.9 │
│ 2019 │ 1200 │ 35037 │ 62012.15 │
│ 2020 │ │ 48258 │ 132419.83 │
│ 2020 │ 10 │ 14206 │ 677.14 │
│ 2020 │ 34 │ 17017 │ 101033.35 │
│ 2020 │ 1200 │ 17035 │ 30709.34 │
└──────┴───────────┴──────────────┴───────────┘
现在我们通过一个简洁的查询获得了我们电力生产读数的完整概览，而不是多次查询。我们在过程中添加的所有额外细分都可以用分组集来表达。最小值和最大值之所以被省略，是为了保持清晰可读。
窗口函数
窗口和在窗口上应用的函数是现代 SQL 和分析的重要组成部分。通常，窗口函数允许您查看其他行。通常情况下，SQL 函数一次只能看到当前行，除非您正在进行聚合。然而，在这种情况下，您会减少行数。
与常规聚合函数不同，使用窗口函数不会导致行被聚合成单个输出行——行保留其各自的身份。如果您想要查看其他行，您可以使用窗口函数。窗口由 OVER() 子句引入，跟随您想要应用到窗口内数据的函数。窗口本身是对正在处理的行的定义，您可以将其视为沿着定义大小的行以定义顺序移动的窗口，覆盖数据集。窗口化通过将关系分解为独立的分区，可选地对这些分区进行排序，然后根据附近值的函数计算每一行的新列来实现。
要查看所有行，您可以使用空窗口 OVER()。如果要查看所有具有与另一个字段匹配的相同值的行，请为该字段使用一个分区。最后但并非最不重要的是，如果要查看附近的行，您可以使用一个框架。
窗口的大小不等于分区的大小，两者可以独立定义。最终，窗口的内容将被馈送给函数来计算新值。虽然有一些专门的函数仅在窗口上下文中工作，但所有常规的聚合函数都可以用作窗口函数。
这允许诸如以下用例：
- 排名
- 计算每个窗口的独立聚合
- 计算每个窗口的累积总数
- 通过 lag 或 lead 访问前后行来计算变化
让我们看一个具体的例子。假设您想要检索出系统以及每个季度的顶部 3 个功率量产生的时间。一个简单的方法是按功率产生的结果排序并限制为 3 个：
SELECT *
FROM readings
ORDER BY power DESC
LIMIT 3;
┌───────────┬─────────────────────┬───────────────┐
│ system_id │ read_on │ power │
│ int32 │ timestamp │ decimal(10,3) │
├───────────┼─────────────────────┼───────────────┤
│ 34 │ 2019-05-08 12:15:00 │ 133900.000 │
│ 34 │ 2019-05-23 10:00:00 │ 133900.000 │
│ 34 │ 2019-05-23 11:30:00 │ 133900.000 │
└───────────┴─────────────────────┴───────────────┘
上述结果展示了系统34在不同日期的读数，你会注意到它们在功率列上具有相同的值。这可能已经足够了，但不一定是我们被要求的。就功率产生的原始值而言，只有排名最高的那个，而不是前三个功率值的所有读数。为了计算一个正确的前三名，我们将使用窗口函数dense_rank()。这个函数计算一行的排名，而不跳过相等排名。dense_rank返回当前行的排名而不留空。这意味着在排名为1的5行之后，下一个排名将是2，而不是6。如果你需要后者，你会使用rank。
WITH ranked_readings AS (
SELECT *,
dense_rank() OVER (ORDER BY power DESC) AS rnk -- #1
FROM readings
)
SELECT *
FROM ranked_readings
WHERE rnk <= 3;
结果现在看起来截然不同，显示了3个不同且递减的功率值，如图4.1所示。
我们将在第46.2节学习 QUALIFY 子句时重新审视上面的陈述，避免在 WHERE 子句中使用略显奇怪的条件对排名进行筛选。
窗口定义中 OVER() 子句的 ORDER 子句是可选的，与语句末尾的 ORDER BY 子句不同，它不会对查询结果进行排序。当作为 OVER() 子句的一部分使用时，ORDER BY 定义了窗口函数执行的顺序。如果省略 ORDER BY，则窗口函数将以任意顺序执行。在我们上面的示例中省略它是没有意义的，因为无序的密集排名总是为 1。在下一节中，我们将看到一个可以安全省略 ORDER BY 的示例。
定义分区
上述排名的功率数值更好了，但它们还不是特别有用，因为系统的生产值相差甚远。如果不区分系统地计算排名可能不是我们所需要的。实际上，在这种情况下，我们需要的是针对每个系统获取前三个读数，每个系统都构成自己数据的一个分区。分区将关系分割成独立的、不相关的部分，在这些部分中应用窗口函数。如果我们不使用 PARTITION BY 子句定义分区的方式，整个关系将被视为一个单独的分区。窗口函数无法访问包含正在评估的行之外的值。
请求每个系统的前 n 个测量值是一个分区任务。为了结果的可读性，我们只请求了每个系统的前两个功率生产值。
WITH ranked_readings AS (
SELECT *,
dense_rank()
OVER (
PARTITION BY system_id
ORDER BY power DESC
) AS rnk
FROM readings
)
SELECT *
FROM ranked_readings
WHERE rnk <= 2
ORDER BY system_id, rnk ASC;
现在仔细观察一下结果中排名的重复次数，如图4.2所示。现在它们在各自的分区内被单独计算，这对数据集提出了一种完全不同的说法。
我们看到所有系统都有1和2名的排名，系统34有5次生产最高功率133900瓦的情况，第二名出现了两次。系统1200只有一个第一名，但有两个第二名。窗口首先按系统进行了分区，然后按生产功率值进行了排序。
当然，排名任务不是可以在分区内应用的唯一内容。诸如平均值、总和、最大值和最小值等聚合函数也是在窗口上下文中使用的其他很好的选择。在窗口上下文中使用聚合函数的区别在于它们不会改变产生的行数。假设你想选择每天每个系统的生产量，并在额外的列中显示系统的总体平均生产量。你可能会考虑使用GROUP BY ROLLUP，你的想法也没有错。然而，这个分组集会相当大（GROUP BY ROLLUP (system_id, day, kwh)），并且不会在额外的列中生成平均值，而是生成额外的行。你想要的值（每个系统的总体产量）将在具有系统值但没有日期值的行中找到。
避免处理额外行的一种方法是进行自连接，在自连接中，你通过一个键选择所需的聚合，以再次连接同一张表。虽然它确实产生了我们想要的结果，但很难阅读，并且很可能性能不佳，因为整个表将被扫描两次。在分区窗口上下文中使用avg要容易得多，并且性能良好。在这种情况下，聚合函数avg(kWh)是在后续的窗口中计算的，它不会改变行数，并且将出现在每一行中。它将根据分区为每个系统计算：
SELECT *,
avg(kWh) OVER (PARTITION BY system_id) AS average_per_system
FROM v_power_per_day;
你会在额外的列中找到所请求的值：
┌───────────┬────────────┬────────┬────────────────────┐
│ system_id │ day │ kWh │ average_per_system │
│ int32 │ date │ double │ double │
├───────────┼────────────┼────────┼────────────────────┤
│ 10 │ 2019-01-01 │ 2.19 │ 4.444051896207586 │
│ 10 │ 2019-01-04 │ 5.37 │ 4.444051896207586 │
│ ... │ ... │ ... │ ... │
│ ... │ ... │ ... │ ... │
│ ... │ ... │ ... │ ... │
│ 1200 │ 2019-07-25 │ 232.37 │ 170.75771639042347 │
│ 1200 │ 2019-04-29 │ 210.97 │ 170.75771639042347 │
├───────────┴────────────┴────────┴────────────────────┤
│ 1587 rows (4 shown) 4 columns │
└──────────────────────────────────────────────────────┘
请注意，我们省略了窗口定义内部的 ORDER BY，因为对于计算平均值来说，值被传递到聚合函数的顺序是无关紧要的。
一般而言，当你考虑为查询添加聚合而不改变行数时，你可能希望每次都使用窗口函数，而不是像上面的自连接那样。
窗口范围
Top-N查询很有用，例如，如果你有一个流媒体服务，并且想要呈现Top-N图表。在我们的例子中，一个更有趣的问题是：“系统范围内能源产量的7天滑动平均值是多少？”为了回答这个问题，我们必须：
- 将每个15分钟间隔的读数聚合到天中（分组和求和）
- 按天和系统进行分区
- 创建7天的窗口
这就是窗口范围发挥作用的地方。窗口范围指定了相对于每一行的一组行，其中函数被评估。与当前行的距离表示为在当前行之前或之后的表达式。这个距离可以被指定为一定数量的行，也可以根据排序表达式的值指定为一个范围增量表达式。
为了提高可读性并使下面的示例集中于窗口定义，我们将使用在第3章中定义的v_power_per_day视图，该视图返回每个系统每天产生的能量量（以千瓦时为单位）。我们也可以将v_power_per_day表达为一个CTE（通用表达式）。
以下语句计算了一个窗口范围内每个系统的平均功率，该窗口范围沿着日期移动，宽度为7天（前3天，当前日期和后3天）。该语句利用了定义窗口的所有选项。
SELECT system_id,
day,
kWh,
avg(kWh) OVER (
PARTITION BY system_id -- #1
ORDER BY day ASC -- #2
RANGE BETWEEN INTERVAL 3 Days PRECEDING -- #3
AND INTERVAL 3 Days FOLLOWING
) AS "kWh 7-day moving average"
FROM v_power_per_day
ORDER BY system_id, day;
结果将有与源读数中的完整天数相同的行数，因此我们只能展示部分作为示例。图4.3展示了窗口的大小以及包含的行数。
命名窗口
窗口定义可以相当复杂，正如我们在讨论带有范围的窗口时所了解的那样。它们可以包括分区、排序和窗口的实际范围的定义。有时，您对给定窗口上的多个聚合感兴趣。重复窗口定义会是一项繁琐的任务。
对于我们的领域——测量光伏系统的发电量——我们可以使用分位数来创建报告，该报告实质上同时考虑了季节和天气，通过在每个月份的7天窗口内计算分位数。有时，宽泛的月度平均值可能足够了，但是图表只能代表一个相对平滑的随月份变化的曲线。在一周内，由于天气的变化，发电量的波动较大。离群值和异常值更容易通过分位数捕获和表示。结果可以轻松用于创建移动箱形图，例如。
我们需要三个聚合函数（最小值、最大值和分位数）来缓存异常值和计算分位数，并且我们不想每次都重新定义窗口。我们基本上从列表4.17中采用定义，并将阅读的月份添加到分区中。否则，窗口定义保持不变。我们将定义移动到FROM子句之后，并将其命名为seven_days。可以从任意数量的聚合函数中引用它，具体取决于需要的数量。
SELECT system_id,
day,
min(kWh) OVER seven_days AS "7-day min", -- #1
quantile(kWh, [0.25, 0.5, 0.75]) -- #2
OVER seven_days AS "kWh 7-day quartile",
max(kWh) OVER seven_days AS "7-day max"
FROM v_power_per_day
WINDOW seven_days AS (
PARTITION BY system_id, month(day)
ORDER BY day ASC
RANGE BETWEEN INTERVAL 3 Days PRECEDING
AND INTERVAL 3 Days FOLLOWING
)
ORDER BY system_id, day;
结果现在展示了一个结构化的列类型，"kWh 7-day quartile"：
┌──────────┬────────────┬───────────┬──────────────────────────┬──────────┐
│ system_id│ day │ 7-day min │ kWh 7-day quartile │ 7-day max│
│ int32 │ date │ double │ double[] │ double │
├──────────┼────────────┼───────────┼──────────────────────────┼──────────┤
│ 10 │ 2019-01-01 │ 2.19 │ [2.19, 5.37, 5.55] │ 5.72 │
│ 10 │ 2019-01-02 │ 2.19 │ [4.62, 5.37, 5.55] │ 5.72 │
│ 10 │ 2019-01-03 │ 2.19 │ [3.69, 4.62, 5.55] │ 5.72 │
│ 10 │ 2019-01-04 │ 2.19 │ [3.69, 5.37, 5.72] │ 5.81 │
│ 10 │ 2019-01-05 │ 3.69 │ [4.62, 5.37, 5.72] │ 5.81 │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ 1200 │ 2020-06-22 │ 107.68 │ [149.11, 191.61, 214.68] │ 279.8 │
│ 1200 │ 2020-06-23 │ 0.0 │ [107.68, 191.61, 214.68] │ 279.8 │
│ 1200 │ 2020-06-24 │ 0.0 │ [190.91, 191.61, 214.68] │ 279.8 │
│ 1200 │ 2020-06-25 │ 0.0 │ [191.61, 203.06, 214.68] │ 279.8 │
│ 1200 │ 2020-06-26 │ 0.0 │ [0.0, 203.06, 214.68] │ 279.8 │
├──────────┴────────────┴───────────┴──────────────────────────┴──────────┤
│1587 rows (10 shown) 5 columns │
└─────────────────────────────────────────────────────────────────────────┘
所有聚合函数都可以作为窗口函数使用，我们已经学过了。这包括复杂的统计函数，例如在一个组中计算精确的分位数（quantile和quantile_disc），或者插值分位数（quantile_cont），就像上面展示的一样。这些函数的实现已经针对窗口化进行了优化，我们可以放心使用，而不用担心性能问题。在查询多个聚合时，请使用命名窗口。
访问分区中的前后行
我们已经讨论了排名，并且将在稍后的第48节“使用ASOF连接”中看到计算累计总和的示例，但我们还没有使用在分区内部跳转到前后行的能力。因此，让我们来看看如何计算变化，而现在最好的例子可能就是价格了。
在第3章中，我们创建了一个名为prices的表，存储了德国用于向电网反馈能源的每千瓦时的价格（以分为单位）。随着可再生能源的促进逐渐减少，这些可再生能源的销售价格已经降低。您现在想知道可再生能源补偿金额随时间的变化情况。要计算差异，您需要第n行的价格值，并将其与第n-1行的值进行比较。由于表的行是独立处理的，基本上是逐行处理的，因此如果没有窗口，这是不可能的。但是，如果在任何可排序的列上跨越一个窗口，您就可以使用lag()和lead()来访问当前窗口之外的行。这使您可以选择昨天的价格，然后与今天的价格进行比较。
lag函数将为您提供分区内当前行之前的行中表达式的值，如果没有则返回NULL。这适用于分区中的第一行。lead的行为正好相反（对于分区中的最后一行返回NULL）。在DuckDB中，这两个函数有几种重载方式，允许指定要延迟或提前多少行以及默认窗口。否则，当NULL值不可行时，使用coalesce也是一个选择。
提示：coalesce函数将返回其第一个非NULL参数。
查询4.19将使用lag()计算在新规定出台时价格的差异：
SELECT valid_from,
value,
lag(value) OVER validity AS "Previous value",
value - lag(value, 1, value) OVER validity AS Change
FROM prices
WHERE date_part('year', valid_from) = 2019
WINDOW validity AS (ORDER BY valid_from)
ORDER BY valid_from;
正如您所看到的，在2019年，每个新时期的价格都显著下降了：
┌────────────┬───────────────┬─────────────────┬──────────────┐
│ valid_from │ value │ Previous value │ Change │
│ date │ decimal(5,2) │ decimal(5,2) │ decimal(6,2) │
├────────────┼───────────────┼─────────────────┼──────────────┤
│ 2019-01-01 │ 11.47 │ │ 0.00 │
│ 2019-02-01 │ 11.35 │ 11.47 │ -0.12 │
│ 2019-03-01 │ 11.23 │ 11.35 │ -0.12 │
│ 2019-04-01 │ 11.11 │ 11.23 │ -0.12 │
│ ... │ ... │ ... │ ... │
│ 2019-09-01 │ 10.33 │ 10.48 │ -0.15 │
│ 2019-10-01 │ 10.18 │ 10.33 │ -0.15 │
│ 2019-11-01 │ 10.08 │ 10.18 │ -0.10 │
│ 2019-12-01 │ 9.97 │ 10.08 │ -0.11 │
└────────────┴───────────────┴─────────────────┴──────────────┘
如果我们想要计算2019年价格的总变化，我们必须使用一个公共表达式（CTE），因为我们不能在聚合函数内部嵌套窗口函数调用。一个可能的解决方案如下：
WITH changes AS (
SELECT value - lag(value, 1, value) OVER (ORDER BY valid_from) AS v
FROM prices
WHERE date_part('year', valid_from) = 2019
ORDER BY valid_from
)
SELECT sum(changes.v) AS total_change
FROM changes;
在2019年，德国私人生产的可再生能源补偿每千瓦时减少了1.50欧分。
条件和过滤条件不在 WHERE 子句中
在标准的 WHERE 子句中无法对计算的聚合或窗口函数的结果进行过滤。这样的过滤是为了回答以下类似问题而必要的：
- 选择具有聚合值超过值 x 的组。为此，您必须使用 HAVING 子句。
- 需要在一定日期范围内选择超过特定值的数据。在这种情况下，必须使用 QUALIFY 子句。
此外，您可能需要使用 FILTER 子句将值过滤掉，以防止其进入聚合函数。
Table 4.1: Filtering Clauses and Where to Use Them
| Clause | Where to Use | Effect |
|---|---|---|
| WHERE | After FROM clause | Filters rows based on column values |
| HAVING | After GROUP BY | Filters rows based on aggregates computed for a group |
| QUALIFY | After FROM clause referring to any window expression | Filters rows based on anything that is computed in that window |
| FILTER | After any aggregate function | Filters the values that are passed to the aggregate |
使用 HAVING 子句
“请给我所有产量超过 900 千瓦时的日期！” 在第三章，您了解了 WHERE 子句以及 GROUP BY 的工作原理，您尝试将它们结合起来，就像这样：
SELECT system_id,
date_trunc('day', read_on) AS day,
round(sum(power) / 4 / 1000, 2) AS kWh
FROM readings
WHERE kWh >= 900
GROUP BY ALL;
在 DuckDB 0.8.1 中，它会给出如下错误："Error: Binder Error: Referenced column 'kWh' not found in FROM clause!"，其他版本或数据库可能在措辞上更清晰。其含义是：在应用 WHERE 子句时，尚未知道计算列 "kWh"，并且在那一点上无法知道（与 day 不同，day 也是一个计算列）。在 WHERE 子句中选择行，或者换句话说，在其他地方过滤行，会修改首先被聚合的行。因此，你需要另一个在聚合之后应用的子句：HAVING 子句。它用于在完成所有选定行的聚合后提供过滤条件。
回到最初的任务：你只需要将条件从 WHERE 子句移出，放入 GROUP BY 后面的 HAVING 子句中：
SELECT system_id,
date_trunc('day', read_on) AS day,
round(sum(power) / 4 / 1000, 2) AS kWh
FROM readings
GROUP BY ALL
HAVING kWh >= 900
ORDER BY kWh DESC;
现在结果在经过总和聚合后进行了过滤：
┌───────────┬────────────┬────────┐
│ system_id │ day │ kWh │
│ int32 │ date │ double │
├───────────┼────────────┼────────┤
│ 34 │ 2020-05-12 │ 960.03 │
│ 34 │ 2020-06-08 │ 935.33 │
│ 34 │ 2020-05-23 │ 924.08 │
│ 34 │ 2019-06-09 │ 915.4 │
│ 34 │ 2020-06-06 │ 914.98 │
│ 34 │ 2020-05-20 │ 912.65 │
│ 34 │ 2019-05-01 │ 912.6 │
│ 34 │ 2020-06-16 │ 911.93 │
│ 34 │ 2020-06-07 │ 911.73 │
│ 34 │ 2020-05-18 │ 907.98 │
│ 34 │ 2019-04-10 │ 907.63 │
│ 34 │ 2019-06-22 │ 906.78 │
│ 34 │ 2020-05-19 │ 906.4 │
└───────────┴────────────┴────────┘
使用 QUALIFY 子句
假设您只想返回窗口函数的结果匹配某个过滤器的行。您不能将该过滤器添加到 WHERE 子句中，因为那样会过滤掉包含在窗口中的行，并且您需要使用窗口函数的结果。但是，您也不能使用 HAVING，因为窗口函数在聚合之前进行评估。因此，QUALIFY 允许您对窗口函数的结果进行过滤。
当我们介绍窗口函数时，我们不得不使用 CTE 来过滤结果。通过使用 QUALIFY，我们可以更简单、更清晰地重写查询，仍然获得前三个最高排名的值。
SELECT
dense_rank() OVER (ORDER BY power DESC) AS rnk,
*
FROM readings
QUALIFY rnk <= 3;
回到我们在 4.17 中使用移动窗口的示例。7 天的平均产值是光伏电网效率的一个良好指标，我们可能会询问哪些日期达到了某个特定的阈值。我们只想要结果，其中 7 天窗口内的平均值高于 875 千瓦时，因此将其放入 QUALIFY 子句中。QUALIFY 子句可以通过名称引用窗口函数。
SELECT system_id,
day,
avg(kWh) OVER (
PARTITION BY system_id
ORDER BY day ASC
RANGE BETWEEN INTERVAL 3 Days PRECEDING AND INTERVAL 3 Days FOLLOWING
) AS "kWh 7-day moving average"
FROM v_power_per_day
QUALIFY "kWh 7-day moving average" > 875 -- #1
ORDER BY system_id, day;
根据示例数据，我们找到了三个日期，它们代表了光伏发电在西半球的典型“好日子”：
┌───────────┬────────────┬──────────────────────────┐
│ system_id │ day │ kWh 7-day moving average │
│ int32 │ date │ double │
├───────────┼────────────┼──────────────────────────┤
│ 34 │ 2020-05-21 │ 887.4628571428572 │
│ 34 │ 2020-05-22 │ 884.7342857142858 │
│ 34 │ 2020-06-09 │ 882.4628571428572 │
└───────────┴────────────┴──────────────────────────┘
使用FILTER子句
有时候你想计算一组数值的聚合值、平均值或计数，却意识到有一些行你不想包括在内。你可以添加到过滤子句，但在一个复杂的查询中，你可能需要保留那些行来计算其他字段。例如，假设你有时会得到一些坏的读数，这些读数显示为负值。你想计算传感器的总读数和平均读数。如果你在WHERE子句中过滤掉坏的读数，你就无法计算出总读数。但如果你只是对所有的读数做平均，那么你将包含一些坏的、负的数值。
为了解决这种问题，你可以使用FILTER表达式作为聚合的一部分。
回到第41节，我们需要处理不一致的传感器读数，实际上我们遇到了将空值纳入平均值的问题，这很可能不是我们想要的。我们可以将空值从平均值中过滤掉，而不是将其限制为零，像这样：
INSERT INTO readings(system_id, read_on, power)
SELECT
any_value(SiteId),
time_bucket(INTERVAL '15 Minutes', CAST("Date-Time" AS timestamp)) AS read_on,
coalesce(avg(ac_power) FILTER (ac_power IS NOT NULL AND ac_power >= 0), 0) AS power -- #1
FROM read_csv_auto('https://developer.nrel.gov/api/pvdaq/v3/data_file?api_key=DEMO_KEY&system_id=10&year=2019')
GROUP BY read_on
ORDER BY read_on;
你可能会想为什么我们使用了coalesce函数：如果所有的数据都被过滤掉，那么没有任何数据进入聚合函数，整个表达式就变成了null。这意味着如果你过滤掉了聚合函数的所有输入，那么该值就会变成NULL，这将违反我们读数表的约束。像往常一样，在这里没有一种绝对正确的方式，无论你是更倾向于清单4.1还是4.24中的解决方案。在这种情况下，我们稍微倾向于基于FILTER的解决方案，结合使用coalesce，因为这样做的意图稍微更清晰一些。
PIVOT语句
在一个查询中可以有多个聚合，并且它们都可以分别进行过滤。这可以帮助你回答这样一个任务：“我想要一个按系统和年份划分的能源生产报告，年份应该是列！”对每个系统进行生产总量的聚合很容易，对每年的生产总量进行聚合也很容易。同时按照这两个键进行分组也不难，像这样的语句SELECT system_id, year(day), sum(kWh) FROM v_power_per_day GROUP BY ALL ORDER BY system_id;就可以胜任，并返回以下结果：
┌───────────┬─────────────┬────────────────────┐
│ system_id │ year("day") │ sum(kWh) │
│ int32 │ int64 │ double │
├───────────┼─────────────┼────────────────────┤
│ 10 │ 2019 │ 1549.280000000001 │
│ 10 │ 2020 │ 677.1900000000003 │
│ 34 │ 2019 │ 205742.59999999992 │
│ 34 │ 2020 │ 101033.75000000001 │
│ 1200 │ 2019 │ 62012.109999999986 │
│ 1200 │ 2020 │ 30709.329999999998 │
└───────────┴─────────────┴────────────────────┘
虽然我们按系统和年份对数据进行了分组，但每个系统的年份出现在行中，而不是列中。我们希望有3行2列，包含值2019和2020，就像在电子表格程序中找到上述数据一样。重新组织这样的表格的过程称为数据透视，DuckDB 提供了几种可能性来执行此操作，其中一种是使用多个、带过滤条件的聚合。我们不是只有一个 sum 聚合，而是定义了多个，并过滤掉我们不想在特定列中使用的每个值，最终得到以下语句：
SELECT system_id,
sum(kWh) FILTER (WHERE year(day) = 2019) AS 'kWh in 2019',
sum(kWh) FILTER (WHERE year(day) = 2020) AS 'kWh in 2020'
FROM v_power_per_day
GROUP BY system_id;
总和的值相等，但年份现在是列，不再是单独的分组，您虚构的老板现在可以像在电子表格程序中一样查看数据。
┌───────────┬────────────────────┬────────────────────┐
│ system_id │ kWh in 2019 │ kWh in 2020 │
│ int32 │ double │ double │
├───────────┼────────────────────┼────────────────────┤
│ 10 │ 1549.280000000001 │ 677.1900000000003 │
│ 34 │ 205742.59999999992 │ 101033.75000000001 │
│ 1200 │ 62012.109999999986 │ 30709.329999999998 │
└───────────┴────────────────────┴────────────────────┘
有一个缺点：这些列本质上是硬编码的，每次添加一年都需要重新检查该查询。如果你确定你所需的列集是恒定的，或者你发现自己针对其他可能不支持任何其他形式的数据透视的数据库，静态方法可能是你的正确解决方案。
要解决这个问题，可以使用 DuckDB 的 PIVOT 子句。DuckDB 中的 PIVOT 子句允许根据任意表达式动态地进行数据透视。
PIVOT (FROM v_power_per_day) -- #1
ON year(day) -- #2
USING sum(kWh); -- #3
结果与我们在 4.25 中静态构建的完全相符：年份作为列，系统作为行，系统和年份的交叉点是该系统在该年产生的电力总和。
┌───────────┬────────────────────┬────────────────────┐
│ system_id │ 2019 │ 2020 │
│ int32 │ double │ double │
├───────────┼────────────────────┼────────────────────┤
│ 10 │ 1549.280000000001 │ 677.1900000000003 │
│ 34 │ 205742.59999999992 │ 101033.75000000001 │
│ 1200 │ 62012.109999999986 │ 30709.329999999998 │
└───────────┴────────────────────┴────────────────────┘
如果您正在使用“单元格”值的聚合函数，则不在ON子句中的所有列都将用作聚合的分组键。但是，并不需要使用聚合函数。PIVOT v_power_per_day ON day 将产生1382行和545列的结果！为什么会这样呢？v_power_per_day 包含1382个不同的(system_id, kWh)值，这构成了行。系统被要求使用day而不是 year(day) 来创建一个列，而有记录的日期却有543天。另外两列是system_id和kWh列。单元格中的内容是什么？有许多许多个零和一些个一。如果没有使用USING子句，DuckDB将在没有具体值的日期填充单元格为零，并在具有值的日期填充为一。因此，如果您实际上对所有日期的表格视图感兴趣，您可能希望在这种情况下使用第一个聚合函数：
PIVOT (
FROM v_power_per_day
WHERE day BETWEEN '2020-05-30' AND '2020-06-02'
)
ON DAY USING first(kWh);
请注意，我们有意选择了仅选择几天而不是尝试打印几百列。上述查询在日期上对此结果进行了透视：
┌───────────┬────────────┬───────┐
│ system_id │ day │ kWh │
│ int32 │ date │ double│
├───────────┼────────────┼───────┤
│ 1200 │ 2020-05-30 │ 280.4 │
│ 1200 │ 2020-05-31 │ 282.25│
│ 1200 │ 2020-06-01 │ 288.29│
│ 1200 │ 2020-06-02 │ 152.83│
│ ... │ ... │ ... │
│ ... │ ... │ ... │
│ ... │ ... │ ... │
│ 10 │ 2020-05-30 │ 4.24 │
│ 10 │ 2020-05-31 │ 3.78 │
│ 10 │ 2020-06-01 │ 4.47 │
│ 10 │ 2020-06-02 │ 5.09 │
├───────────┴────────────┴───────┤
│12 rows (8 shown) 3 columns│
└────────────────────────────────┘
转换成表格视图，任何电子表格专家都会感到满意：
┌───────────┬────────────┬────────────┬────────────┬────────────┐
│ system_id │ 2020-05-30 │ 2020-05-31 │ 2020-06-01 │ 2020-06-02 │
│ int32 │ double │ double │ double │ double │
├───────────┼────────────┼────────────┼────────────┼────────────┤
│ 10 │ 4.24 │ 3.78 │ 4.47 │ 5.09 │
│ 34 │ 732.5 │ 790.33 │ 796.55 │ 629.17 │
│ 1200 │ 280.4 │ 282.25 │ 288.29 │ 152.83 │
└───────────┴────────────┴────────────┴────────────┴────────────┘
以上所有查询都使用了专有的DuckDB变体PIVOT。DuckDB的语法使编写数据透视语句更加简单且不容易出错，因为它完全消除了对应该在其上进行数据透视的行的静态枚举。DuckDB还支持更标准的SQL形式的PIVOT。然而，对于PIVOT子句的支持在不同的数据库系统中存在很大差异，其他可能的目标数据库不太可能具有完全相同的标准风格。因此，在这种情况下，我们更愿意使用专有的语法，这样更容易阅读，而不是希望SQL更具可移植性。
在DuckDB中，完全可以在USING子句中计算多个聚合，并使用多个列进行数据透视。我们可以利用这一点，不仅计算每年的总产量（即所有天数的总和），还可以添加两个额外的列来突出显示最佳日期：
PIVOT v_power_per_day
ON year(day)
USING round(sum(kWh)) AS total, max(kWh) AS best_day;
我们对总量进行了四舍五入，以使结果更易读：
┌───────────┬────────────┬───────────────┬────────────┬───────────────┐
│ system_id │ 2019_total │ 2019_best_day │ 2020_total │ 2020_best_day │
│ int32 │ double │ double │ double │ double │
├───────────┼────────────┼───────────────┼────────────┼───────────────┤
│ 10 │ 1549.0 │ 7.47 │ 677.0 │ 6.97 │
│ 34 │ 205743.0 │ 915.4 │ 101034.0 │ 960.03 │
│ 1200 │ 62012.0 │ 337.29 │ 30709.0 │ 343.43 │
└───────────┴────────────┴───────────────┴────────────┴───────────────┘
使用 ASOF JOIN (近似匹配连接)
假设您销售一种全天价格波动剧烈的产品。您可以定期预测价格，例如每 15 分钟一次，但这是您能做到的最好的程度。然而，客户可以随时要求购买您的产品。
这可能会导致销售数据与价格数据并不完全匹配的情况。清单 4.27 通过一个示例演示了这一点。它创建了两个临时表 (CTE)：
- 一个包含随机一天一小时内价格的虚构价格表，每 15 分钟一个价格，共 4 条记录。
- 一个包含全天销售情况的销售表，共 12 条记录。
然后，查询以一种简单的方式将这些表连接在一起。但是，您最终只会得到 4 个结果，而不是为每个 12 次销售找到对应价格。这是因为连接未考虑销售时间和价格预测之间的时间差。
WITH prices AS (
SELECT range AS valid_at,
random()*10 AS price
FROM range(
'2023-01-01 01:00:00'::timestamp,
'2023-01-01 02:00:00'::timestamp, INTERVAL '15 minutes')
),
sales AS (
SELECT range AS sold_at,
random()*10 AS num
FROM range(
'2023-01-01 01:00:00'::timestamp,
'2023-01-01 02:00:00'::timestamp, INTERVAL '5 minutes')
)
SELECT sold_at, valid_at AS 'with_price_at', round(num * price,2) as price
FROM sales
JOIN prices ON prices.valid_at = sales.sold_at;
销售情况不佳，正如这个结果清楚地表明的那样，并在图4.5中表示：
┌─────────────────────┬─────────────────────┬────────┐
│ sold_at │ with_price_at │ price │
│ timestamp │ timestamp │ double │
├─────────────────────┼─────────────────────┼────────┤
│ 2023-01-01 01:00:00 │ 2023-01-01 01:00:00 │ 21.17 │
│ 2023-01-01 01:15:00 │ 2023-01-01 01:15:00 │ 12.97 │
│ 2023-01-01 01:30:00 │ 2023-01-01 01:30:00 │ 44.61 │
│ 2023-01-01 01:45:00 │ 2023-01-01 01:45:00 │ 9.45 │
└─────────────────────┴─────────────────────┴────────┘
进入ASOF JOIN：ASOF JOIN（即“as of”）是一种连接子句，它基于不等式进行连接，在连接列不完全相等的情况下选择一个“足够好”的值。回到列表4.27，我们必须更改两个内容：将JOIN关键字替换为ASOF JOIN，并提供一个不等式运算符。下面的不等式条件prices.valid_at <= sales.sold_at意味着所有在销售点之前或在销售点处有效的价格都可以用来计算总价格。
WITH prices AS (
SELECT range AS valid_at,
random()*10 AS price
FROM range(
'2023-01-01 01:00:00'::timestamp,
'2023-01-01 02:00:00'::timestamp, INTERVAL '15 minutes')
),
sales AS (
SELECT range AS sold_at,
random()*10 AS num
FROM range(
'2023-01-01 01:00:00'::timestamp,
'2023-01-01 02:00:00'::timestamp, INTERVAL '5 minutes')
)
SELECT sold_at, valid_at AS 'with_price_at', round(num * price,2) AS price
FROM sales
ASOF JOIN prices -- #1
ON prices.valid_at <= sales.sold_at; -- #2
请注意 DuckDB 如何选择与销售时间最接近的价格，如下所示。此外，我们现在确实获得了预期的 12 行结果：
┌─────────────────────┬─────────────────────┬────────┐
│ sold_at │ with_price_at │ price │
│ timestamp │ timestamp │ double │
├─────────────────────┼─────────────────────┼────────┤
│ 2023-01-01 01:00:00 │ 2023-01-01 01:00:00 │ 1.59 │
│ 2023-01-01 01:05:00 │ 2023-01-01 01:00:00 │ 3.56 │
│ 2023-01-01 01:10:00 │ 2023-01-01 01:00:00 │ 2.71 │
│ 2023-01-01 01:15:00 │ 2023-01-01 01:15:00 │ 29.12 │
│ 2023-01-01 01:20:00 │ 2023-01-01 01:15:00 │ 14.92 │
│ 2023-01-01 01:25:00 │ 2023-01-01 01:15:00 │ 4.83 │
│ 2023-01-01 01:30:00 │ 2023-01-01 01:30:00 │ 2.84 │
│ 2023-01-01 01:35:00 │ 2023-01-01 01:30:00 │ 3.84 │
│ 2023-01-01 01:40:00 │ 2023-01-01 01:30:00 │ 4.95 │
│ 2023-01-01 01:45:00 │ 2023-01-01 01:45:00 │ 23.1 │
│ 2023-01-01 01:50:00 │ 2023-01-01 01:45:00 │ 30.07 │
│ 2023-01-01 01:55:00 │ 2023-01-01 01:45:00 │ 11.6 │
└─────────────────────┴─────────────────────┴────────┘
ASOF JOIN经常用于处理时间序列数据，例如股票报价、价格或物联网传感器数据。在我们的示例中，它可以用来将不断变化的销售价格与系统读数进行连接，以计算任意时间点的价格。下面的最后一个示例再次使用我们的光伏示例数据，应用相同的逻辑来选择有效的价格。然后演示了ASOF JOIN可以与本章学习的其他构造一起使用，例如使用窗口来累积在销售期间以不同价格计算的累积运行总收益：
SELECT power.day,
power.kWh,
prices.value AS 'ct/kWh',
ROUND(SUM(prices.value * power.kWh)
OVER (ORDER BY power.day ASC) / 100, 2) AS 'Accumulated earnings in EUR'
FROM v_power_per_day power
ASOF JOIN prices
ON prices.valid_from <= power.day
WHERE system_id = 34
ORDER BY day;
结果显示了日期、产生的千瓦时数量、当天每千瓦时的 ct 价格，以及产生的电力和价格的乘积的累积总和：
┌────────────┬──────────┬───────────┬─────────────────────────────┐
│ day │ kWh │ ct/kWh │ Accumulated earnings in EUR │
│ date │ double │ decimal │ double │
├────────────┼──────────┼───────────┼─────────────────────────────┤
│ 2019-01-01 │ 471.4 │ 11.47 │ 54.07 │
│ 2019-01-02 │ 458.58 │ 11.47 │ 106.67 │
│ 2019-01-03 │ 443.65 │ 11.47 │ 157.56 │
│ 2019-01-04 │ 445.03 │ 11.47 │ 208.6 │
│ ... │ ... │ ... │ ... │
│ 2020-06-23 │ 798.85 │ 9.17 │ 31371.86 │
│ 2020-06-24 │ 741.15 │ 9.17 │ 31439.83 │
│ 2020-06-25 │ 762.6 │ 9.17 │ 31509.76 │
│ 2020-06-26 │ 11.98 │ 9.17 │ 31510.86 │
├────────────┴──────────┴───────────┴─────────────────────────────┤
│ 543 rows (8 shown) 4 columns│
└─────────────────────────────────────────────────────────────────┘
DuckDB 被定位为一个涵盖广泛用例的 OLAP 数据库。处理时间序列数据当然就是其中之一，而 ASOF JOIN 则是其中的一部分。
无论涉及哪个领域（从我们示例中的传感器读数到病人心率监测和股市波动），在某个时间记录的值通常会通过与特定关键值进行连接来丰富，这些关键值在一段时间内是有效的。支持 ASOF 可以实现所有时间戳对齐并不完美的情况。
使用表函数
大多数 SQL 函数接受参数并返回单个值。然而，表函数不同于普通函数，它们不仅返回单个值，还会返回一系列行。因此，表函数可以在任何可以出现表的地方使用。
根据函数的不同，它们可以访问外部资源（例如文件或 URL）并将它们转换为标准 SQL 语句的一部分的关系。DuckDB 不是唯一支持表函数概念的关系型数据库，但它提供了一组令人印象深刻的表函数，可以满足多种用例。
您可以使用以下语句获取 DuckDB 安装中所有表函数的列表，该语句使用了名为 duckdb_functions() 的表函数：
SELECT DISTINCT ON(function_name) function_name
FROM duckdb_functions() -- #1
WHERE function_type = 'table'
ORDER BY function_name;
这章的例子以及我们之前的数据导入过程中已经广泛使用了 read_csv*、read_parquet 等函数。额外的扩展（例如空间扩展）会增加读取外部资源并生成关系数据的表函数列表。
range(start, stop) 和 generate_series(start, stop) 是非常好用的表函数。这两个函数都可以创建一个介于 start 和 stop 之间的数值列表。start 参数是包含式的。对于 range 函数，stop 参数是不包含的，而对于 generate_series 则是包含的。这两个函数都提供重载版本，带有第三个参数 step 用于定义步长，默认为 1。还存在仅接受 stop 参数且默认 start 为 0 的变体。虽然可以直接像普通函数那样使用它们，但以类似于访问表格的方式进行查询时会更加强大。
如果你需要 1 到 5 之间的数字列表，并且不想硬编码它们，你可以使用 SELECT generate_series(1, 5); 数字是有用的，但这些函数也适用于时间数据。但是，在使用时间数据时，请注意需要同时指定起始和结束参数，因为对于任何一个参数都没有合理的默认值。让我们将其用于实际用途。我们示例数据中的读数在 2020 年中旬结束。基于这些数据的报告如果是为整整一年的话，会过早结束，如下所示：
SELECT strftime(day, '%Y-%m') AS month, avg(kwh)
FROM v_power_per_day WHERE year(day) = 2020
GROUP BY ALL ORDER BY month;
结果将如下输出所示：
┌─────────┬────────────────────┐
│ month │ avg(kwh) │
│ varchar │ double │
├─────────┼────────────────────┤
│ 2020-01 │ 222.13169014084497 │
│ 2020-02 │ 133.52356321839076 │
│ 2020-03 │ 207.86670454545438 │
│ 2020-04 │ 309.7838888888888 │
│ 2020-05 │ 349.5753763440861 │
│ 2020-06 │ 337.80820512820515 │
└─────────┴────────────────────┘
如果你被要求创建一个图表，你可能会发现自己处于这样一种情况，需要考虑如何表示未来的几个月。以下是使用range()函数覆盖整个一年并将缺失值表示为0的一种方法：
WITH full_year AS (
SELECT generate_series AS day
FROM generate_series(
'2020-01-01'::date,
'2020-12-31'::date, INTERVAL '1 day'
)
)
SELECT strftime(full_year.day, '%Y-%m') AS month,
avg(kWh) FILTER (WHERE kWh IS NOT NULL) AS actual
FROM full_year
LEFT OUTER JOIN v_power_per_day per_day ON per_day.day = full_year.day
GROUP BY ALL
ORDER BY month;
现在的结果是一个完整一年的报告，但遗憾的是，2020年6月之后的数值缺失了：
┌─────────┬────────────────────┐
│ month │ actual │
│ varchar │ double │
├─────────┼────────────────────┤
│ 2020-01 │ 222.13169014084508 │
│ 2020-02 │ 133.52356321839076 │
│ 2020-03 │ 207.86670454545455 │
│ 2020-04 │ 309.7838888888888 │
│ 2020-05 │ 349.57537634408607 │
│ 2020-06 │ 337.80820512820515 │
│ 2020-07 │ │
│ 2020-08 │ │
│ 2020-09 │ │
│ 2020-10 │ │
│ 2020-11 │ │
│ 2020-12 │ │
└─────────┴────────────────────┘
将这个想法进一步推广，可以使用前一年同一月的值来预测生产值。为此，您需要再次加入v_power_per_day，使用一年的偏移量：
WITH full_year AS (
SELECT generate_series AS day
FROM generate_series(
'2020-01-01'::date,
'2020-12-31'::date, INTERVAL '1 day'
)
)
SELECT strftime(full_year.day, '%Y-%m') AS month,
avg(present.kWh) FILTER (WHERE present.kWh IS NOT NULL) AS actual,
avg(past.kWh) FILTER (WHERE past.kWh IS NOT NULL) AS forecast
FROM full_year
LEFT OUTER JOIN v_power_per_day present ON present.day = full_year.day
LEFT OUTER JOIN v_power_per_day past ON past.day = full_year.day - INTERVAL '1 year'
GROUP BY ALL
ORDER BY month;
结果更加令人愉悦，并且偶然地提供了今年与去年的比较，基本上是免费的。
┌─────────┬────────────────────┬────────────────────┐
│ month │ actual │ forecast │
│ varchar │ double │ double │
├─────────┼────────────────────┼────────────────────┤
│ 2020-01 │ 222.13169014084505 │ 161.59319248826304 │
│ 2020-02 │ 133.5235632183909 │ 111.07298850574716 │
│ 2020-03 │ 207.8667045454546 │ 150.65231060606064 │
│ 2020-04 │ 309.78388888888895 │ 316.1782222222224 │
│ 2020-05 │ 349.57537634408595 │ 325.36881720430125 │
│ 2020-06 │ 337.8082051282051 │ 351.60691056910514 │
│ 2020-07 │ │ 334.32311827956994 │
│ 2020-08 │ │ 314.928817204301 │
│ 2020-09 │ │ 289.6049999999999 │
│ 2020-10 │ │ 253.82935483870958 │
│ 2020-11 │ │ 191.3843333333334 │
│ 2020-12 │ │ 164.88628205128202 │
└─────────┴────────────────────┴────────────────────┘
使用LATERAL连接
在第43节中，我们学习了相关和不相关子查询。清单4.5演示了如何将不相关子查询一次与外部查询连接。从性能的角度来看，这可能是有益的，因为子查询只需要评估一次，然后针对其他表的每一行执行连接。
然而，有时候，您确切地希望为外部查询的每个值评估内部查询。这就是LATERAL JOIN发挥作用的地方。您可以将其视为for循环的内部块，而外部查询则是控制结构。
解开数组、展开数据等任务可以使用LATERAL来处理。假设您对太阳的强度感兴趣，以及一天中某些时段内到达您所在地的太阳能量有多少。Open Meteo提供了一个免费的API，提供广泛的天气数据，包括所谓的全球水平辐照度（GHI）。这是地面水平面上收到的来自上方的全部短波辐射总量。这个值对光伏安装特别重要，以W/m²为单位测量。他们的API生成一个包含两个单独数组的JSON对象，一个是时间戳，一个是所选值。后一个数组是感兴趣的数组，我们希望为一些给定的事实检索特定的值。
{
"latitude": 50.78,
"longitude": 6.0799994,
"utc_offset_seconds": 7200,
"timezone": "Europe/Berlin",
"timezone_abbreviation": "CEST",
"elevation": 178.0,
"hourly_units": {
"time": "iso8601",
"shortwave_radiation_instant": "W/m²"
},
"hourly": {
"time": [
"2023-08-26T00:00",
"2023-08-26T01:00",
"2023-08-26T02:00",
"2023-08-26T03:00",
"2023-08-26T04:00",
"2023-08-26T05:00"
],
"shortwave_radiation_instant": [
0.0,
0.0,
0.0,
0.0,
0.0,
9.1
]
}
}
该段落描述了获取用于示例的 JSON 数据的两种方法：
- 代码仓库中的 JSON 文件：您可以从本书的代码仓库中获取该 JSON 文件。文件位于
ch04/ghi_past_and_future.json路径下。 - 在线 API 获取数据：您可以使用以下 URL 从开放气象数据 API 获取最新数据： api.open-meteo.com/v1/forecast…
乍一看，使用 SQL 从该数组中挑选出上午、中午和傍晚时段的数据似乎是一项艰巨的任务。让我们看看 LATERAL 函数如何解决这个问题。
我们在第一章已经了解到 DuckDB 可以处理 JSON 数据，有关细节将在第五章进一步讲解。现在，只要知道您可以像从 FROM 子句中的任何其他表一样，从 JSON 文件中进行选择就足够了。
下面的查询生成一系列包含 7 天的日期，然后将其与 8 点、13 点和 19 点（下午 7 点）的时间进行连接以创建索引。这些索引是日期编号 * 24 加上一天中所需的小时数，从而在 JSON 数组中找到相应的值。该索引是子查询的 LATERAL 驱动器：
INSTALL json;
LOAD json;
WITH days AS (
SELECT generate_series AS value FROM generate_series(7)
),
hours AS (
SELECT unnest([8, 13, 18]) AS value
),
indexes AS (
SELECT days.value * 24 + hours.value AS i
FROM days, hours
)
SELECT date_trunc('day', now()) - INTERVAL '7 days' +
INTERVAL (indexes.i || ' hours') AS ts, -- #1
ghi.v AS "GHI in W/m^2"
FROM indexes
CROSS JOIN LATERAL (
SELECT hourly.shortwave_radiation_instant[i+1] -- #2
AS v
FROM 'code/ch04/ghi_past_and_future.json' -- #3
) AS ghi
ORDER BY ts;
2023 年 8 月末亚琛的天气状况如下，对于光伏发电来说并不是一个好月份：
┌──────────────────────────┬──────────────┐
│ ts │ GHI in W/m^2 │
│ timestamp with time zone │ double │
├──────────────────────────┼──────────────┤
│ 2023-08-26 08:00:00+02 │ 36.0 │
│ 2023-08-26 13:00:00+02 │ 490.7 │
│ 2023-08-26 18:00:00+02 │ 2.3 │
│ 2023-08-27 08:00:00+02 │ 243.4 │
│ 2023-08-27 13:00:00+02 │ 124.3 │
│ · │ · │
│ · │ · │
│ · │ · │
│ 2023-09-01 13:00:00+02 │ 392.0 │
│ 2023-09-01 18:00:00+02 │ 0.0 │
│ 2023-09-02 08:00:00+02 │ 451.0 │
│ 2023-09-02 13:00:00+02 │ 265.0 │
│ 2023-09-02 18:00:00+02 │ 0.0 │
└──────────────────────────┴──────────────┘
子查询可以为驱动的外部表的每一行产生零行、一行或多行。在上面的示例中，它为每个外部行产生了一行。如果子查询产生了多行，则外部行的值将重复，类似于CROSS JOIN的作用。如果子查询没有产生任何值，则连接也不会产生值。在这种情况下，我们必须同样应用OUTER JOIN。此时，仅使用LATERAL关键字是不够的，我们必须像这样使用完整的JOIN语法。以下查询是人为的且价值较小，仅用于演示语法。这两个查询都会生成从1到4的一系列值，外部的步长为一，内部的步长为两。我们在ON子句中比较这两个值。
SELECT i, j
FROM generate_series(1, 4) t(i)
LEFT OUTER JOIN LATERAL (
SELECT * FROM generate_series(1, 4, 2) t(j)
) sq ON sq.j = i -- #1
ORDER BY i;
这个查询的结果如下所示：
┌───────┬───────┐
│ i │ j │
│ int64 │ int64 │
├───────┼───────┤
│ 1 │ 1 │
│ 2 │ │
│ 3 │ 3 │
│ 4 │ │
└───────┴───────┘
在第4.8节中的价格问题也可以用子查询和LATERAL JOIN来解决。实质上，子查询必须返回价格表中有效期与销售日期最接近的行。为了使其正常工作，我们不能使用普通的连接，因为子查询必须针对每个传入的日期产生不同的值。因此，通常作为连接一部分的日期列必须移动到子查询内部。因此，加入的子查询现在成为了相关的，或者说是与外部查询进行了横向连接。下面示例中的相关性是价格的有效期与记录电力生产的日期之间的对比。
SELECT power.day, power.kWh,
prices.value AS "EUR/kWh"
FROM v_power_per_day power
LEFT JOIN LATERAL ( -- #1
SELECT *
FROM prices
WHERE prices.valid_from <= power.day -- #2
ORDER BY valid_from DESC LIMIT 1 -- #3
) AS prices ON true
WHERE system_id = 34
ORDER BY day;
对于 DuckDB 中与时间序列相关的计算，我们肯定会使用 ASOF JOIN。当你需要考虑可移植性时，LATERAL 是一个吸引人的选择，你可能会发现支持 LATERAL 而不是 ASOF JOIN 的数据库更多。在你想要将数据集展开以生成更多行的场景中使用 LATERAL。
总结
-
SQL 标准自1992年（SQL-92）最后一次重大修订以来已经发生了很大变化，DuckDB 支持广泛的现代 SQL，包括 CTEs（SQL:1999）、窗口函数（SQL:2003）、列表聚合（SQL:2016）等。
-
分组集允许对多个组进行聚合计算，可以深入到不同级别的细节；ROLLUP 和 CUBE 可用于生成子组或组合分组键。
-
DuckDB 完全支持窗口函数，包括命名窗口和范围，可用于计算累积总数、排名等用例。
-
所有聚合函数，包括统计计算和插值，都针对窗口上下文进行了优化。
-
HAVING 和 QUALIFY 可用于在计算聚合和窗口后选择数据，FILTER 防止不需要的数据进入聚合。
-
DuckDB 包含 ASOF join，适用于涉及时间序列数据的用例。
-
DuckDB 还支持 LATERAL join，有助于展开数据并在一定程度上模拟循环。
-
结果可以使用简化的、DuckDB 特定的 PIVOT 语句或更静态的标准 SQL 方法进行透视。
转载自:https://juejin.cn/post/7353515388255027250