DuckDB 技巧 - 第二部分
alitrack约 7 分钟DuckDB 技巧 - 第二部分
作者: Gabor Szarnyas
原文:https://duckdb.org/2024/10/11/duckdb-tricks-part-2.html
继续探索 DuckDB 技巧系列，重点讲解如何通过 SQL 查询实现数据清理、转换与汇总。
概览
本篇是 DuckDB 技巧系列 的最新一篇，将继续展示 DuckDB 中的一些实用 SQL 技巧。以下是本次内容的重点：
| 操作 | SQL 指令 |
| 修复 CSV 文件中的时间戳 | regexp_replace 和 strptime |
| 填充缺失值 | CROSS JOIN，LEFT JOIN 和 coalesce |
| 重复的数据转换步骤 | CREATE OR REPLACE TABLE t AS … FROM t … |
| 计算列的校验和 | bit_xor(md5_number(COLUMNS(*)::VARCHAR)) |
| 为校验和查询创建宏 | CREATE MACRO checksum(tbl) AS TABLE … |
数据集
在示例数据中，我们使用 schedule.csv 文件。这是一个包含会议日程的手写 CSV 文件，包含时间段、地点和安排的活动信息。
timeslot,location,event
2024-10-10 9am,room Mallard,Keynote
2024-10-10 10.30am,room Mallard,Customer stories
2024-10-10 10.30am,room Fusca,Deep dive 1
2024-10-10 12.30pm,main hall,Lunch
2024-10-10 2pm,room Fusca,Deep dive 2修复 CSV 文件中的时间戳
实际操作中，输入的 CSV 文件经常会出现各种格式问题，比如不规范的时间戳 2024-10-10 9am。因此，如果直接用 DuckDB 读取 schedule.csv 文件，CSV 自动解析器会将时间戳列识别为 VARCHAR（字符串）字段：
CREATE TABLE schedule_raw AS
SELECT * FROM 'https://duckdb.org/data/schedule.csv';SELECT * FROM schedule_raw;
┌────────────────────┬──────────────┬──────────────────┐
│ timeslot │ location │ event │
│ varchar │ varchar │ varchar │
├────────────────────┼──────────────┼──────────────────┤
│ 2024-10-10 9am │ room Mallard │ Keynote │
│ 2024-10-10 10.30am │ room Mallard │ Customer stories │
│ 2024-10-10 10.30am │ room Fusca │ Deep dive 1 │
│ 2024-10-10 12.30pm │ main hall │ Lunch │
│ 2024-10-10 2pm │ room Fusca │ Deep dive 2 │
└────────────────────┴──────────────┴──────────────────┘为了便于后续查询，我们希望将 timeslot 列的类型改为 TIMESTAMP。可以在加载的原始表上使用正则表达式 regexp_replace 来统一格式，将 9am 等时间表示规范化为 09.00am 的格式，然后用 strptime 函数将这些字符串转换为时间戳格式。在 strptime 中，%p 标记会自动识别并处理 am 或 pm：
CREATE TABLE schedule_cleaned AS
SELECT
timeslot
.regexp_replace(' (\d+)(am|pm)$', ' \1.00\2')
.strptime('%Y-%m-%d %H.%M%p') AS timeslot,
location,
event
FROM schedule_raw;FROM schedule_cleaned;
这里我们用到 点操作符[1] 进行函数链式调用，使代码更简洁。例如，regexp_replace(string, pattern, replacement) 被写作 string.regexp_replace(pattern, replacement)。生成的表如下：
┌─────────────────────┬──────────────┬──────────────────┐
│ timeslot │ location │ event │
│ timestamp │ varchar │ varchar │
├─────────────────────┼──────────────┼──────────────────┤
│ 2024-10-10 09:00:00 │ room Mallard │ Keynote │
│ 2024-10-10 10:30:00 │ room Mallard │ Customer stories │
│ 2024-10-10 10:30:00 │ room Fusca │ Deep dive 1 │
│ 2024-10-10 12:30:00 │ main hall │ Lunch │
│ 2024-10-10 14:00:00 │ room Fusca │ Deep dive 2 │
└─────────────────────┴──────────────┴──────────────────┘填充缺失值
接下来，我们希望创建一个完整的时间表，即每个时间段和地点都在表中占据一行。对于那些没有安排活动的组合，我们希望填入 <empty> 字符串表示为空。
首先，用 CROSS JOIN 创建一个包含所有可能的时间段和地点组合的表 timeslot_location_combinations，然后通过 LEFT JOIN 将这些组合与原始表关联，最后使用 coalesce 函数[2] 将 NULL 值替换为 <empty>。
CROSS JOIN是笛卡尔积运算，将两个表中的每一行都组合在一起，生成大量数据。在大数据量表上执行时，CROSS JOIN操作计算量非常大，最好只在必要时使用。
CREATE TABLE timeslot_location_combinations AS
SELECT timeslot, location
FROM (SELECT DISTINCT timeslot FROM schedule_cleaned)
CROSS JOIN (SELECT DISTINCT location FROM schedule_cleaned);CREATE TABLE schedule_filled AS
SELECT timeslot, location, coalesce(event, '<empty>') AS event
FROM timeslot_location_combinations
LEFT JOIN schedule_cleaned
USING (timeslot, location)
ORDER BY ALL;
SELECT * FROM schedule_filled;
结果如下：
┌─────────────────────┬──────────────┬──────────────────┐
│ timeslot │ location │ event │
│ timestamp │ varchar │ varchar │
├─────────────────────┼──────────────┼──────────────────┤
│ 2024-10-10 09:00:00 │ main hall │ <empty> │
│ 2024-10-10 09:00:00 │ room Fusca │ <empty> │
│ 2024-10-10 09:00:00 │ room Mallard │ Keynote │
│ 2024-10-10 10:30:00 │ main hall │ <empty> │
│ 2024-10-10 10:30:00 │ room Fusca │ Deep dive 1 │
│ 2024-10-10 10:30:00 │ room Mallard │ Customer stories │
│ 2024-10-10 12:30:00 │ main hall │ Lunch │
│ 2024-10-10 12:30:00 │ room Fusca │ <empty> │
│ 2024-10-10 12:30:00 │ room Mallard │ <empty> │
│ 2024-10-10 14:00:00 │ main hall │ <empty> │
│ 2024-10-10 14:00:00 │ room Fusca │ Deep dive 2 │
│ 2024-10-10 14:00:00 │ room Mallard │ <empty> │
└─────────────────────┴──────────────┴──────────────────┘可以用 WITH 子句[3] 将上述步骤合并为一个查询：
WITH timeslot_location_combinations AS (
SELECT timeslot, location
FROM (SELECT DISTINCT timeslot FROM schedule_cleaned)
CROSS JOIN (SELECT DISTINCT location FROM schedule_cleaned)
)
SELECT timeslot, location, coalesce(event, '<empty>') AS event
FROM timeslot_location_combinations
LEFT JOIN schedule_cleaned
USING (timeslot, location)
ORDER BY ALL;重复的数据转换步骤
数据清理和转换通常需要多个步骤，将数据逐步整理成适合分析的格式。这些步骤可以通过不断定义新的表来实现（比如使用 CREATE TABLE … AS SELECT 语句[4]），但这种方式会产生许多临时表，并容易出现引用错误。
通过使用 CREATE OR REPLACE 语句[5]，我们可以每次使用同一个表名，简化数据转换步骤，避免产生多余的临时数据：
CREATE OR REPLACE TABLE schedule AS
SELECT * FROM 'https://duckdb.org/data/schedule.csv';CREATE OR REPLACE TABLE schedule AS
SELECT
timeslot
.regexp_replace(' (\d+)(am|pm)$', ' \1.00\2')
.strptime('%Y-%m-%d %H.%M%p') AS timeslot,
location,
event
FROM schedule;
CREATE OR REPLACE TABLE schedule AS
WITH timeslot_location_combinations AS (
SELECT timeslot, location
FROM (SELECT DISTINCT timeslot FROM schedule)
CROSS JOIN (SELECT DISTINCT location FROM schedule)
)
SELECT timeslot, location, coalesce(event, '<empty>') AS event
FROM timeslot_location_combinations
LEFT JOIN schedule
USING (timeslot, location)
ORDER BY ALL;
SELECT * FROM schedule;
这样，我们可以跳过不需要的步骤，并直接运行下一步。CREATE OR REPLACE 还可让脚本从头运行，自动替换已有表。
计算列的校验和
为了查看列的内容是否在两次操作间发生变化，我们可以计算表中每一列的校验和，方法如下：
SELECT bit_xor(md5_number(COLUMNS(*)::VARCHAR))
FROM schedule;在这里，我们使用 COLUMNS(*)[6] 列出所有列，并将其转换为 VARCHAR 类型，然后使用 md5_number 函数[7] 计算数值 MD5 哈希，并通过 bit_xor 函数[8] 聚合。这会生成每列的一个 HUGEINT（INT128）值，可用于内容比较。
运行该查询后，可得到以下结果：
┌──────────────────────────────────────────┬──────────┬──────────────────────────────────────────┐
│ timeslot │ location │ event │
│ int128 │ int128 │ int128 │
├──────────────────────────────────────────┼──────────┼──────────────────────────────────────────┤
│ -162418013182718436871288818115274808663 │ 0 │ -135609337521255080720676586176293337793 │
└──────────────────────────────────────────┴──────────┴──────────────────────────────────────────┘为校验和查询创建宏
可以用 DuckDB 的 query_table 函数[9]，将校验和查询封装为 表宏[10]：
CREATE MACRO checksum(table_name) AS TABLE
SELECT bit_xor(md5_number(COLUMNS(*)::VARCHAR))
FROM query_table(table_name);调用时使用 DuckDB 的 FROM-first 语法[11]，在 schedule 表上简单执行以下代码即可：
FROM checksum('schedule');┌──────────────────────────────────────────┬──────────┬──────────────────────────────────────────┐
│ timeslot │ location │ event │
│ int128 │ int128 │ int128 │
├──────────────────────────────────────────┼──────────┼──────────────────────────────────────────┤
│ -162418013182718436871288818115274808663 │ 0 │ -135609337521255080720676586176293337793 │
└──────────────────────────────────────────┴──────────┴──────────────────────────────────────────┘结束语
今天的内容就到这里！我们将很快带来更多 DuckDB 的小技巧和案例分享。如果你有想分享的技巧，请通过社交媒体联系 DuckDB 团队，或者提交到 DuckDB Snippets 网站[12]（由 MotherDuck 团队维护）。
引用链接
[1] 点操作符: https://duckdb.org/docs/sql/functions/overview#function-chaining-via-the-dot-operator[2] coalesce 函数: https://duckdb.org/docs/sql/functions/utility#coalesceexpr-[3] WITH 子句: https://duckdb.org/docs/sql/query_syntax/with[4] CREATE TABLE … AS SELECT 语句: https://duckdb.org/docs/sql/statements/create_table#create-table--as-select-ctas[5] CREATE OR REPLACE 语句: https://duckdb.org/docs/sql/statements/create_table#create-or-replace[6] COLUMNS(*): https://duckdb.org/docs/sql/expressions/star#columns-expression[7] md5_number 函数: https://duckdb.org/docs/sql/functions/utility#md5_numberstring[8] bit_xor 函数: https://duckdb.org/docs/sql/functions/aggregates#bit_xorarg[9] query_table 函数: https://duckdb.org/docs/guides/sql_features/query_and_query_table_functions[10] 表宏: https://duckdb.org/docs/sql/statements/create_macro#table-macros[11] FROM-first 语法: https://duckdb.org/docs/sql/query_syntax/from[12] DuckDB Snippets 网站: https://duckdbsnippets.com/