DuckDB 技巧 – 第 2 部分

Gabor Szarnyas

2024-10-11 · 5 分钟

要点：我们将继续我们的“DuckDB 技巧”系列，重点介绍用于清理、转换和汇总数据的查询。

概览

本文是 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 的 CSV 读取器加载 schedule.csv 文件，CSV sniffer 会将第一列检测为 VARCHAR 字段。

CREATE TABLE schedule_raw AS
    SELECT * FROM 'https://duckdb.net.cn/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，以便稍后在查询中将其视为时间戳。为了实现这一点，我们可以使用我们刚刚加载的表，并通过使用基于正则表达式的搜索和替换操作来修复有问题的实体，该操作将格式统一为 hours.minutes，后跟 am 或 pm。然后，我们使用 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;

请注意，我们使用点运算符进行函数链式调用以提高可读性。例如，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>。

为了实现这一点，我们首先创建一个表 timeslot_location_combinations，其中包含使用 CROSS JOIN 的所有可能的组合。然后，我们可以使用 LEFT JOIN 将原始表连接到这些组合。最后，我们使用 coalesce 函数将 NULL 值替换为 <empty> 字符串。

CROSS JOIN 子句等同于简单地在 FROM 子句中列出表而不指定连接条件。通过显式地拼写出 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>          │
├─────────────────────┴──────────────┴──────────────────┤
│ 12 rows                                     3 columns │
└───────────────────────────────────────────────────────┘

我们还可以使用 WITH 子句将所有内容放在一个查询中

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 语句定义越来越新的表来完成。

例如，在上面的部分中，我们创建了 schedule_raw、schedule_cleaned 和 schedule_filled。如果由于某种原因，我们想要跳过时间戳的清理步骤，我们必须重新制定计算 schedule_filled 的查询，以使用 schedule_raw 而不是 schedule_cleaned。这可能很繁琐且容易出错，并且会导致大量未使用的临时数据——这些数据可能会被我们忘记更新的查询意外地拾取！

在交互式分析中，最好通过运行 CREATE OR REPLACE 语句使用相同的表名。

CREATE OR REPLACE TABLE table_name AS
    ...
    FROM table_name
    ...;

使用此技巧，我们可以按如下方式运行我们的分析：

CREATE OR REPLACE TABLE schedule AS
    SELECT * FROM 'https://duckdb.net.cn/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 语句将自动替换任何现有表。

计算列的校验和

计算表中每列的校验和通常是有益的，例如，查看两项操作之间列的内容是否已更改。我们可以按如下方式计算 schedule 表的校验和：

SELECT bit_xor(md5_number(COLUMNS(*)::VARCHAR))
FROM schedule;

这里发生了什么？我们首先列出列 (COLUMNS(*)) 并将所有列转换为 VARCHAR 值。然后，我们使用 md5_number 函数计算数字 MD5 哈希值，并使用 bit_xor 聚合函数聚合它们。这会为每列生成一个 HUGEINT (INT128) 值，该值可用于比较表的内容。

如果我们在上面的脚本中运行此查询，我们将得到以下结果：

┌──────────────────────────────────────────┬────────────────────────────────────────┬─────────────────────────────────────────┐
│                 timeslot                 │                location                │                  event                  │
│                  int128                  │                 int128                 │                 int128                  │
├──────────────────────────────────────────┼────────────────────────────────────────┼─────────────────────────────────────────┤
│ -134063647976146309049043791223896883700 │ 85181227364560750048971459330392988815 │ -65014404565339851967879683214612768044 │
└──────────────────────────────────────────┴────────────────────────────────────────┴─────────────────────────────────────────┘

┌────────────────────────────────────────┬────────────────────────────────────────┬─────────────────────────────────────────┐
│                timeslot                │                location                │                  event                  │
│                 int128                 │                 int128                 │                 int128                  │
├────────────────────────────────────────┼────────────────────────────────────────┼─────────────────────────────────────────┤
│ 62901011016747318977469778517845645961 │ 85181227364560750048971459330392988815 │ -65014404565339851967879683214612768044 │
└────────────────────────────────────────┴────────────────────────────────────────┴─────────────────────────────────────────┘

┌──────────────────────────────────────────┬──────────┬──────────────────────────────────────────┐
│                 timeslot                 │ location │                  event                   │
│                  int128                  │  int128  │                  int128                  │
├──────────────────────────────────────────┼──────────┼──────────────────────────────────────────┤
│ -162418013182718436871288818115274808663 │        0 │ -135609337521255080720676586176293337793 │
└──────────────────────────────────────────┴──────────┴──────────────────────────────────────────┘

为校验和查询创建宏

我们可以使用新的 query_table 函数将校验和查询转换为表宏

CREATE MACRO checksum(table_name) AS TABLE
    SELECT bit_xor(md5_number(COLUMNS(*)::VARCHAR))
    FROM query_table(table_name);

这样，我们可以简单地在 schedule 表上调用它，如下所示（也利用了 DuckDB 的 FROM-first 语法）：

FROM checksum('schedule');

┌──────────────────────────────────────────┬────────────────────────────────────────┬─────────────────────────────────────────┐
│                 timeslot                 │                location                │                  event                  │
│                  int128                  │                 int128                 │                 int128                  │
├──────────────────────────────────────────┼────────────────────────────────────────┼─────────────────────────────────────────┤
│ -134063647976146309049043791223896883700 │ 85181227364560750048971459330392988815 │ -65014404565339851967879683214612768044 │
└──────────────────────────────────────────┴────────────────────────────────────────┴─────────────────────────────────────────┘