大数据

duckdb 基础语法学习

Posted by

demo01.sql

CREATE TABLE grades (grade INTEGER, course VARCHAR);
INSERT INTO grades VALUES (7, 'Math'), (9, 'Math'), (8, 'CS');

SELECT min(grade) FROM grades;

-- 通过在 WHERE 子句中使用标量子查询，我们可以找出该成绩是针对哪门课程获得的
SELECT 
	course 
FROM grades
WHERE grade = (SELECT min(grade) FROM grades)
;

-- ARRAY 子查询
SELECT ARRAY(SELECT grade FROM grades) AS all_grades;

-- ALL 量词指定：当比较运算符左侧的表达式与比较运算符右侧子查询中的每个值进行逐项比较，且结果全部为 true 时，整个比较表达式的结果才为 true。
SELECT 6 <= ALL (SELECT grade FROM grades) AS adequate;
SELECT 8 >= ALL (SELECT grade FROM grades) AS adequate;

-- ANY 量词指定：当至少有一个逐项比较的结果为 true 时，整个比较表达式的结果为 true。
SELECT 7 >= ANY (SELECT grade FROM grades) AS fail;

-- 量词 SOME 可以代替 ANY 使用：ANY 和 SOME 是可以互换的。
SELECT 7 >= SOME (SELECT grade FROM grades) AS fail;

-- EXISTS 运算符用于检测子查询内是否存在任何行。当子查询返回一条或多条记录时，它返回 true，否则返回 false
SELECT EXISTS(FROM grades WHERE course='Math') AS math_grades_present;

-- NOT EXISTS 运算符用于检测子查询内是否没有任何行。当子查询返回空结果时，它返回 true，否则返回 false。
SELECT NOT EXISTS(FROM grades WHERE course='Math') AS math_grades_present;


CREATE TABLE Person (id BIGINT, name VARCHAR);
CREATE TABLE interest (PersonId BIGINT, topic VARCHAR);

INSERT INTO Person VALUES (1, 'Jane'), (2, 'Joe');
INSERT INTO interest VALUES (2, 'Music');

-- 筛选没有填写兴趣的人员
SELECT *
FROM Person
WHERE NOT EXISTS (FROM interest WHERE interest.PersonId = Person.id);

-- IN 运算符用于检查左侧表达式是否包含在子查询定义的集合或右侧（RHS）的一组表达式中。如果表达式存在于右侧，则 IN 运算符返回 true
SELECT 'Math' IN (SELECT course FROM grades) AS math_grades_present;

-- 相关子查询 查找每门课程的最低成绩
SELECT *
FROM grades grades_parent
WHERE grade = (
	SELECT min(grade)
	FROM grades
	WHERE grades.course = grades_parent.course
);

-- 将子查询的每一行作为结构体（Struct）返回
SELECT t
FROM (
	SELECT unnest(generate_series(1, 5)) AS x, 'hello' as y
) t;

-- SELECT * 但是排除y列、z列
SELECT st.* EXCLUDE(y, z) FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS st);

聚合函数

-- 聚合函数

-- 生成包含 amount 列总和的单行结果
SELECT sum(amount)
FROM sales;

-- 按每个唯一区域生成一行，包含每个分组的 amount 总和
SELECT region, sum(amount)
FROM sales
GROUP BY region;

-- 仅返回 amount 总和大于 100 的区域
SELECT region
FROM sales
GROUP BY region 
HAVING sum(amount) > 100;

-- 返回 region 列中唯一值的数量
SELECT count(DISTINCT region)
FROM sales;

-- 返回两个值：amount 的总和，以及使用 FILTER 子句排除区域为 north 的行后的 amount 总和
SELECT sum(amount), sum(amount) FILTER (region != 'north')
FROM sales;

-- 按 amount 列的顺序返回所有区域的列表
SELECT list(region ORDER BY amount DESC)
FROM sales;

-- 使用 first() 聚合函数返回第一笔销售的金额
SELECT first(amount ORDER BY date ASC)
FROM sales;

-- 聚合函数中的 ORDER BY 子句
CREATE TABLE tbl AS
SELECT s FROM range(1, 4) r(s);

SELECT string_agg(s, ', ' ORDER BY s DESC) AS countdown
FROM tbl

-- 除 list (array_agg)、first (arbitrary) 和 last 外，所有通用聚合函数都会忽略 NULL。
-- 要从 list 中排除 NULL，可以使用 FILTER 子句。要从 first 中忽略 NULL，可以使用 any_value 聚合。


-- 示例开始
CREATE TABLE sales (
    id INTEGER,
    product VARCHAR,
    category VARCHAR,
    price DECIMAL(10,2),
    quantity INTEGER,
    sale_date DATE,
    flags INTEGER,
    returned BOOLEAN
);

INSERT INTO sales VALUES
(1, 'Laptop'  , 'Electronics', 999.99, 1, '2025-01-10', 1, false),
(2, 'Mouse'   , 'Electronics', 19.99,  3, '2025-01-11', 2, false),
(3, 'Keyboard', 'Electronics', 49.99,  2, '2025-01-11', 3, true ),
(4, 'Desk'    , 'Furniture'  , 199.99, 1, '2025-01-12', 0, false),
(5, 'Chair'   , 'Furniture'  , 89.99,  2, '2025-01-13', 1, false),
(6, 'Monitor' , 'Electronics', 249.99, 1, '2025-01-14', 3, true ),
(7, 'Laptop'  , 'Electronics', 1099.99,1, '2025-01-15', 1, false),
(8, 'Mouse'   , 'Electronics', 19.99,  5, '2025-01-16', 2, false),
(9, 'Desk'    , 'Furniture'  , 249.99, 1, '2025-01-17', 1, true );


-- 通用聚合函数
-- COUNT计数
SELECT count(*) FROM sales;
-- 非空值计算 prices没有null
SELECT count(price) FROM sales;
-- 不重复的产品个数
SELECT COUNT(DISTINCT product) FROM sales;

-- SUM 求和
-- 所有订单总金额
SELECT SUM(price * quantity) AS total_revenue
FROM sales;
-- 总数量
SELECT SUM(quantity) FROM sales;

-- AVG 平均值
SELECT AVG(price) FROM sales;

-- 最大值最小值
SELECT MIN(price) as cheapest, MAX(price) AS most_expensive FROM sales;

-- 有序聚合函数（需要指定ORDER BY）
-- FIRST / LAST – 按顺序取第一个/最后一个值
-- 按销售日期最早/最晚的产品名
SELECT 
	FIRST(product ORDER BY sale_date) as first_sold,
	LAST(product ORDER BY sale_date) as last_sold
FROM sales;

-- ARG_MIN / ARG_MAX – 返回使某列最值的另一列
-- 最便宜的产品名
SELECT ARG_MIN(product, price) as cheapest_product
FROM sales;
-- 最贵的产品名
SELECT ARG_MAX(product, price) as most_expensive_product
FROM sales;

-- 列表与字符串聚合
-- LIST / ARRAY_AGG – 将值收集为数组
-- 将所有产品名收集为数组
SELECT list(product) FROM sales;
-- 去重后的产品名数组
SELECT list(DISTINCT product) FROM sales;

-- STRING_AGG – 字符串拼接
-- 用逗号连接所有产品名
SELECT STRING_AGG(product, ', ') FROM sales;

-- HISTOGRAM – 生成频次直方图 (MAP)
-- {'Laptop':2,'Mouse':2,'Keyboard':1,'Desk':2,'Chair':1,'Monitor':1}
SELECT HISTOGRAM(product) FROM sales;


-- 布尔聚合
-- BOOL_AND / BOOL_OR – 逻辑与/或
-- 是否所有订单都未退货 returned 全为false
SELECT BOOL_AND(returned) FROM sales;
-- 是否存在退货 returned 至少一个为true
ELECT BOOL_OR(returned) FROM sales;

数组函数

-- 创建订单表，goods 字段是整数数组（商品ID列表）
CREATE TABLE orders (
    order_id   INTEGER,
    customer   VARCHAR,
    goods      INTEGER[],      -- 数组列
    tags       VARCHAR[]       -- 字符串数组列（用于演示交集/并集）
);

-- 插入 4 条订单数据
INSERT INTO orders VALUES
(1, 'Alice',   [101, 102, 103],       ['electronics', 'urgent']),
(2, 'Bob',     [102, 104],            ['book', 'gift']),
(3, 'Charlie', [101, 105, 106, 107],  ['urgent', 'fragile']),
(4, 'Diana',   [103, 104, 105],       ['gift']);

SELECT * FROM orders

-- 数组构造
-- 使用不同写法构造数组
SELECT 
	array_value(1, 2, 3) as arr1,
	list_value(1, 2, 3) as arr2,
	[1, 2, 3] as arr3;

-- 将查询结果聚合成数组
-- 输出: [[101,102,103], [102,104], [101,105,106,107], [103,104,105]]
SELECT array_agg(goods) as all_goods FROM orders;

-- 数组访问与切片
SELECT goods[1] as first_item FROM orders;
-- 提取第2到第3个元素
SELECT goods[2:3] as middles_items FROM orders;


-- 数组基本信息
-- 长度、是否包含元素
SELECT
    array_length(goods)        AS len,
    len(goods)                 AS same_len,
    array_has(goods, 102)      AS has_102
FROM orders;

-- 数组修改（增删改）
-- 连接、追加、前插
SELECT
    array_concat([1,2], [3,4])           AS concat,       -- [1,2,3,4]
    array_append(goods, 999)             AS appended,     -- 尾部追加 999
    array_prepend(888, goods)            AS prepended    -- 头部插入 888
FROM orders;

SELECT
    array_pop_front(goods)  AS pop_front,  -- 去掉第一个元素
    array_pop_back(goods)   AS pop_back    -- 去掉最后一个元素
FROM orders;

-- 数组变换（筛选、映射、展开）
-- 筛选出大于 102 的商品ID
SELECT array_filter(goods, x -> x > 102) AS filtered FROM orders;
-- 给每个ID加上100
SELECT array_transform(goods, x -> x + 100) AS transformed FROM orders;
-- 对数组元素求和
SELECT array_reduce(goods, (acc, x) -> acc + x) AS total FROM orders;

-- 排序与去重
-- 排序、逆序、去重
SELECT 
	array_sort(goods) as sorted,
	array_reverse_sort(goods) as rev_sorted,
	array_distinct(goods) as distinct_vals,
	array_unique(goods) as unique_vals
FROM orders;

-- 数组展平
-- 先将所有订单的商品列表收集为嵌套数组，然后展平
SELECT flatten(array_agg(goods)) as all_items_flat FROM orders;

-- 列表聚合（list_ 前缀函数）
-- 直接对每一行的数组进行统计
SELECT
    list_aggregate(goods, 'sum')          AS sum_list,
    list_sum(goods)                       AS sum_list2,
    list_min(goods)                       AS min_val,
    list_max(goods)                       AS max_val,
    list_avg(goods)                       AS avg_val
FROM orders;

SELECT
    array_intersect(tags, ['urgent', 'gift'])    AS intersect,
    array_has_all(tags, ['book', 'gift'])      AS has_all,   -- 是否包含所有给定元素
    array_has_any(tags, ['fragile', 'book'])     AS has_any,   -- 是否包含任意给定元素
FROM orders;

-- 数组生成与解构
-- 生成 1 到 5 的整数序列
SELECT generate_series(1, 5) as series;
-- 将数组展开为行
SELECT order_id, unnest(goods) as item_id
FROM orders;
-- 数组转字符串
SELECT array_to_string(goods, '-') as str
FROM orders;
-- 字符串转数组
SELECT string_to_array('a,b,c,d', ',') as arr

-- Map 类型相关（关联数组）
-- Map 实际上是键值对数组，通常由两个等长数组组成
-- 构造map
SELECT map(['name', 'age'], ['bob', '20']) AS user_map;

-- 从表中构造（假设我们想映射商品ID到数量）
SELECT map(goods, generate_series(1, len(goods))) as id_to_pos
FROM orders;

-- map函数
WITH m AS (
    SELECT map(['a', 'b', 'c'], [1, 2, 3]) AS mp
)
SELECT
    map_keys(mp)   AS keys,
    map_values(mp) AS vals,
    map_entries(mp) AS entries,         -- [{key=a, value=1}, ...]
    map_extract(mp, 'b') AS b_val      -- 2
FROM m;


-- 按客户统计其订单中所有商品（去重）
SELECT
    customer,
    list_distinct(flatten(list(goods))) AS all_items
FROM orders
GROUP BY customer;

日期函数

-- 创建用户活动日志表
CREATE TABLE user_activity (
    user_id      INTEGER,
    activity_date DATE,          -- 纯日期
    activity_time TIME,          -- 纯时间
    activity_ts  TIMESTAMP,      -- 时间戳
    login_str    VARCHAR         -- 字符串时间，用于转换演示
);

-- 插入 4 条记录，覆盖不同日期和时间
INSERT INTO user_activity VALUES
(1, '2025-03-15', '08:30:00', '2025-03-15 08:30:00', '2025-03-15 08:30:00'),
(2, '2025-03-16', '09:15:30', '2025-03-16 09:15:30', '2025-03-16 09:15:30'),
(3, '2025-03-16', '12:00:00', '2025-03-16 12:00:00', '2025-03-16 12:00:00'),
(4, '2025-04-01', '23:59:59', '2025-04-01 23:59:59', '2025-04-01 23:59:59')
;

SELECT * FROM user_activity;

-- 获取当前日期/时间
SELECT
	CURRENT_DATE as today,
	CURRENT_TIME as now_time,
	CURRENT_TIMESTAMP as now_ts,
	NOW() as same_now_ts
	
-- 提取日期/时间分量
SELECT
    activity_ts,
    YEAR(activity_ts)           AS yr,
    MONTH(activity_ts)          AS mon,
    DAY(activity_ts)            AS dy,
    HOUR(activity_ts)           AS hr,
    MINUTE(activity_ts)         AS min,
    SECOND(activity_ts)         AS sec,
    DAYOFWEEK(activity_ts)      AS dow,        -- 周日=0 或 周一=1？ DuckDB 中周日=0
    DAYOFYEAR(activity_ts)      AS doy,
    QUARTER(activity_ts)        AS qtr,
    WEEK(activity_ts)           AS wk,         -- ISO 周年
    ISODOW(activity_ts)         AS iso_dow,    -- 周一=1, 周日=7
    ERA(activity_ts)            AS era,        -- 'CE' 或 'BCE'
    DECADE(activity_ts)         AS decade,
    CENTURY(activity_ts)        AS century,
    MILLENNIUM(activity_ts)     AS millennium,
    EPOCH(activity_ts)          AS unix_epoch  -- 秒数
FROM user_activity;


-- 使用 date_part 和 extract
SELECT
    activity_ts,
    DATE_PART('year', activity_ts)   AS yr,
    DATE_PART('month', activity_ts)  AS mon,
    DATE_PART('day', activity_ts)    AS dy,
    DATE_PART('hour', activity_ts)   AS hr,
    -- EXTRACT 语法效果一样
    EXTRACT(YEAR FROM activity_ts)   AS yr2,
    EXTRACT(MONTH FROM activity_ts)  AS mon2
FROM user_activity;

-- 日期截断 date_trunc
SELECT
    activity_ts,
    DATE_TRUNC('year', activity_ts)   AS trunc_year,    -- 当年第一天 00:00:00
    DATE_TRUNC('month', activity_ts)  AS trunc_month,
    DATE_TRUNC('day', activity_ts)    AS trunc_day,
    DATE_TRUNC('hour', activity_ts)   AS trunc_hour,
    DATE_TRUNC('minute', activity_ts) AS trunc_minute
FROM user_activity;

-- 日期运算（加减间隔）
SELECT
    activity_date,
    activity_date + INTERVAL 1 DAY             AS tomorrow,
    activity_date - INTERVAL 1 MONTH           AS last_month,
    activity_ts + INTERVAL 3 HOUR              AS three_hours_later,
    activity_ts - INTERVAL '30 minutes'        AS half_hour_ago,
    DATEDIFF('day', activity_date, CURRENT_DATE) AS days_ago,
    DATEDIFF('hour', activity_ts, CURRENT_TIMESTAMP) AS hours_ago
FROM user_activity;

-- 日期差值 datediff 和 age
-- age 返回 INTERVAL 类型
SELECT 
	activity_ts,
	DATEDIFF('day', '2025-01-01'::DATE, activity_ts) as day_since_new_year,
	DATEDIFF('second', activity_ts, NOW()) as seconds_until_now,
	AGE(activity_ts, '2025-01-01'::DATE) as age_interval
FROM user_activity;

-- 最后一天 last_day
SELECT
	activity_date,
	last_day(activity_date) as last_day_of_month
FROM user_activity;

-- 日期/时间构造 make_date, make_time, make_timestamp
SELECT
    MAKE_DATE(2025, 6, 15)                       AS a_date,
    MAKE_TIME(14, 30, 0)                         AS a_time,
    MAKE_TIMESTAMP(2025, 6, 15, 14, 30, 0)       AS a_ts;

-- 字符串与日期/时间的相互转换
SELECT
    login_str,
    STRPTIME(login_str, '%Y-%m-%d %H:%M:%S')   AS parsed_ts_1
FROM user_activity;

-- 日期/时间戳 → 字符串
SELECT 
	activity_ts,
	strftime(activity_ts, '%Y年%m月%d日 %H:%M') as formatted_cn,
	strftime(activity_ts, '%Y-%m-%d')            as date_str
FROM user_activity;

-- 转换为日期或时间类型（Cast）
SELECT 
	activity_ts,
	activity_ts::DATE as to_date,
	activity_ts::TIME as to_time,
	activity_ts::DATETIME as to_datetime,
	CAST(activity_ts as DATE) as cast_date
FROM user_activity;

-- 日期比较与范围查询（WHERE 子句示例）
-- 查询 2025年3月 的活动
SELECT *
FROM user_activity
WHERE activity_date BETWEEN '2025-03-01' AND '2025-03-31';

-- 查询最近 30 天内的活动（假设当前日期为 2025-04-10）
SELECT *
FROM user_activity
WHERE activity_ts >= CURRENT_DATE - INTERVAL 30 DAY;

-- 聚合中的日期使用
-- 按月份统计活跃用户数
SELECT
    DATE_TRUNC('month', activity_ts) AS month, -- 第 1 列
    COUNT(DISTINCT user_id)          AS active_users  -- 第 2 列
FROM user_activity
GROUP BY 1 -- 即 GROUP BY DATE_TRUNC('month', activity_ts)
ORDER BY 1; -- 即 ORDER BY DATE_TRUNC('month', activity_ts)

列表函数

-- 创建包含各种数据类型的测试表
CREATE TABLE full_list_demo (
    id INT,
    int_list INT[],         -- 整数数组
    str_list VARCHAR[],     -- 字符串数组
    bool_list BOOLEAN[],    -- 布尔数组
    float_list DOUBLE[],    -- 浮点数组 (用于向量计算)
    nested_list INT[][]     -- 二维嵌套数组
);

-- 插入测试数据
INSERT INTO full_list_demo VALUES
(1, [3, 3, 9],       ['a', 'b', 'c'],       [true, false], [1.0, 2.0, 3.0], [[1, 2], [3, 4]]),
(2, [1, 2, NULL],    ['x', 'y'],            [false, false],[4.0, 5.0, 6.0], [[5, 6]]),
(3, [10, 20, 30, 40],['aa', 'bb', 'cc'],    [true, true],  [1.0, 0.0],      [[7, 8], [9, 10]]);

SELECT *
FROM full_list_demo;

-- 下标、切片
-- 提取单个元素 
SELECT int_list[3]
FROM full_list_demo
WHERE id = 1;
-- 切片提取 别名 list_extract, array_extract
SELECT int_list[1:2]
FROM full_list_demo
WHERE id = 1;
-- 函数形式切片
SELECT list_slice(int_list, 2, 3)
FROM full_list_demo
WHERE id = 1;

SELECT int_list[1:3:2]
FROM full_list_demo
WHERE id = 1;
-- 链接字符串
SELECT 'a'||'b'

-- 基础列表操作 (增删、转换、生成)
-- 移除最后一个元素
SELECT array_pop_back(int_list)
FROM full_list_demo
WHERE id = 1;
-- 移除第一个元素
SELECT array_pop_front(int_list)
FROM full_list_demo
WHERE id = 1;
-- 在开头添加元素
SELECT array_push_front(int_list, 0)
FROM full_list_demo
WHERE id = 1;
-- 用分隔符连接元素
SELECT array_to_string(int_list, '-')
FROM full_list_demo
WHERE id = 1;
-- 默认用逗号连接
SELECT array_to_string_comma_default(str_list)
FROM full_list_demo
WHERE id = 1;
-- 连接多个值 (跳过 NULL)
SELECT concat('Hello', ' ', 'world');
SELECT concat([1, 2], NULL, [3, 4]);
-- 是否包含元素
SELECT contains(int_list, 3)
FROM full_list_demo
WHERE id = 1;
-- 生成序列 (包含 stop)
SELECT generate_series(2, 5, 3);
-- 返回列表长度 (别名 len)
SELECT length(int_list)
FROM full_list_demo
WHERE id = 1;
-- 在末尾追加元素 (别名 array_push_back)
SELECT list_append(int_list, 99)
FROM full_list_demo
WHERE id = 1;
-- 连接多个列表 (跳过 NULL)
SELECT list_concat([2, 3], [4, 5], int_list)
FROM full_list_demo
WHERE id = 1;
-- 是否包含元素 (别名 list_has)
SELECT list_contains(int_list, 3)
FROM full_list_demo
WHERE id = 1;
-- 去重并移除 NULL
SELECT list_distinct(int_list)
FROM full_list_demo
WHERE id = 1;
-- 提取第 index 个值
SELECT list_extract(int_list, 1) 
FROM full_list_demo
WHERE id = 1;
-- 返回元素索引 (找不到返回 NULL)
SELECT list_position(int_list, 9) 
FROM full_list_demo
WHERE id = 1;
-- 在开头插入元素
SELECT list_prepend(0, int_list) 
FROM full_list_demo
WHERE id = 1;
-- 调整大小，不足用 value 填充
SELECT list_resize(int_list, 5, 0) 
FROM full_list_demo
WHERE id = 1;
-- 反转列表
SELECT list_reverse(int_list)
FROM full_list_demo
WHERE id = 1;
-- 按索引列表选择元素
SELECT list_select(int_list, [1, 3]) 
FROM full_list_demo
WHERE id = 1;
-- 生成序列
SELECT range(1, 5, 3)
-- 列表重复 count 次 生成新的列表
SELECT repeat([1, 2], 3)
-- 将列表展开为多行
SELECT unnest(int_list)
FROM full_list_demo
WHERE id = 1;

-- 排序、集合与高阶函数 
-- 返回升序排序后的原始索引 也就是按照升序 列表中的元素应该排第几个
SELECT list_grade_up([3, 6, 1, 2]);
-- 降序排序
SELECT list_reverse_sort(int_list)
FROM full_list_demo
WHERE id = 1;
-- 升序排序
SELECT list_sort(int_list)
FROM full_list_demo
WHERE id = 1;
-- l1 是否包含 l2 的所有元素
SELECT list_has_all(int_list, [3, 9]) 
FROM full_list_demo
WHERE id = 1;
-- l1 和 l2 是否有交集
SELECT list_has_any(int_list, [3, 10])
FROM full_list_demo
WHERE id = 1;
-- 返回两个列表的交集 (去重)
SELECT list_intersect(int_list, [9, 10])
FROM full_list_demo
WHERE id = 1;
-- 过滤元素
SELECT list_filter(int_list, x -> x > 3) 
FROM full_list_demo
WHERE id = 1;
-- 归约为单个值
SELECT list_reduce(int_list, (x, y) -> x + y) 
FROM full_list_demo
WHERE id = 1;
-- 转换每个元素
SELECT list_transform(int_list, x -> x * 2) 
FROM full_list_demo
WHERE id = 1;
-- 使用布尔掩码过滤
SELECT list_where([1, 2, 3, 4], [true, false, false, true]);
-- 将多个列表打包为 struct 列表
SELECT list_zip([11, 22], [3, 4]);

-- 列表聚合函数
SELECT list_aggregate(int_list, 'min')
FROM full_list_demo
WHERE id = 1;

SELECT list_avg(int_list)
FROM full_list_demo
WHERE id = 1;

SELECT list_bool_and(bool_list)
FROM full_list_demo
WHERE id = 1;

SELECT list_bool_or(bool_list)
FROM full_list_demo
WHERE id = 1;

SELECT list_count(int_list)
FROM full_list_demo
WHERE id = 1;

SELECT list_first(int_list)
FROM full_list_demo
WHERE id = 1;

SELECT list_histogram(int_list)
FROM full_list_demo
WHERE id = 1;

SELECT list_last(int_list)
FROM full_list_demo
WHERE id = 1;

SELECT list_max(int_list)
FROM full_list_demo
WHERE id = 1;

SELECT list_min(int_list)
FROM full_list_demo
WHERE id = 1;

SELECT list_sum(int_list)
FROM full_list_demo
WHERE id = 1;
-- 计算唯一元素数量
SELECT list_unique(int_list)
FROM full_list_demo
WHERE id = 1;

集合操作

CREATE TABLE list_set_demo (
    id INT,
    list_a INT[],
    list_b INT[]
);

INSERT INTO list_set_demo VALUES
(1, [1, 2, 3, 4], [3, 4, 5, 6]),
(2, [1, 2],       [3, 4]),
(3, [1, 2, 2, 3], [2, 3, 3]); -- 包含重复元素，测试去重逻辑

SELECT 
    id, 
    list_intersect(list_a, list_b) AS intersection_result
FROM list_set_demo;

SELECT 
    id, 
    -- 先拼接，再去重
    list_distinct(list_concat(list_a, list_b)) AS union_result
FROM list_set_demo;

SELECT 
    id, 
    -- 过滤出 list_a 中，那些“不包含在 list_b 中”的元素
    list_filter(list_a, x -> NOT list_contains(list_b, x)) AS except_result
FROM list_set_demo;

map对象函数

-- 1. 创建包含 MAP 类型的测试表
CREATE TABLE map_demo (
    id INT,
    user_info MAP(VARCHAR, VARCHAR), -- 用户属性 (字符串 -> 字符串)
    scores MAP(VARCHAR, INT),        -- 考试成绩 (科目 -> 分数)
    tags MAP(VARCHAR, BOOLEAN)       -- 标签状态 (标签名 -> 是否激活)
);

-- 2. 插入测试数据
-- 注意：DuckDB 中可以使用 {'k1': 'v1', 'k2': 'v2'} 语法直接创建 Map
INSERT INTO map_demo VALUES
(1, {'name': 'Alice', 'age': '30', 'city': 'NY'}, {'math': 90, 'english': 85}, {'vip': true, 'active': true}),
(2, {'name': 'Bob', 'age': '25'}, {'math': 80, 'physics': 95}, {'vip': false, 'active': true}),
(3, {'name': 'Charlie', 'city': 'LA'}, {'english': 70}, {'active': false});

SELECT *
FROM map_demo;

-- 创建与基础访问
-- 从两个列表（keys 和 values）创建一个 Map。
SELECT map(['a', 'b'], [1, 2]);
-- 创建一个空的 Map。
SELECT map()
-- 根据 key 提取 value。如果 key 不存在，返回 NULL。
SELECT scores['math']
FROM map_demo
WHERE id=1;
-- 等同于 map[key]。key 不存在返回 NULL。
SELECT element_at(scores, 'math') 
FROM map_demo
WHERE id=1;
-- 根据 key 提取 value，返回的是一个包含单元素的 List。如果 key 不存在，返回空 List []（注意：不是 NULL！）。
SELECT map_extract(scores, 'math') 
FROM map_demo
WHERE id=1;
-- 提取键、值与键值对
-- 返回 Map 中所有 key 组成的 List。
SELECT map_keys(user_info) 
FROM map_demo
WHERE id=1;
-- 返回 Map 中所有 value 组成的 List。
SELECT map_values(user_info) 
FROM map_demo
WHERE id=1;
-- 返回包含所有键值对的 List。每个键值对是一个 Struct（包含 key 和 value 字段）
SELECT map_entries(user_info)
FROM map_demo
WHERE id=1;
-- 转换与重建
-- 从包含 Struct（必须有 key 和 value 字段）的 List 创建 Map。
SELECT map_from_entries([{'key': 'x', 'value': 1}, {'key': 'y', 'value': 2}]);
-- 返回 Map 中键值对的数量（即 Map 的长度）。
SELECT cardinality(user_info)
FROM map_demo
WHERE id=1;
-- 将多个 Map 合并为一个。如果存在重复的 key
SELECT map_concat(scores, map(['art', 'math'], [100, 0])) FROM map_demo WHERE id=1;

-- 将每个人的成绩展开成单独的行
SELECT
	id, 
	entry.key as subject,
	entry.value as score
	-- map_entries 将scores变成 struct 列表
	-- unnest 将列表变成多行 整体看作表 别名 t struct对象列 别名 entry 
	-- map_demo 和 t表cross join 默认就是map_demo的一行对应自己的scores拆解的表数据
FROM map_demo, unnest(map_entries(scores)) as t(entry)
WHERE id <= 2
;

-- 场景：只保留分数 >= 85 的科目
-- 思路：先找出 >= 85 的科目名（keys），再用这些 keys 重建 Map
-- 直接过滤 Map，保留 value >= 85 的键值对
WITH filtered_data AS (
    SELECT 
        id, 
        scores, -- 关键：必须保留原始的 scores 列
        list_filter(map_keys(scores), k -> scores[k] >= 85) AS valid_keys
    FROM map_demo
)
SELECT 
    id, 
    map_from_entries(
    	-- 构造一个map key来自valid_keys， value从scores中获取
        list_transform(valid_keys, k -> {'key': k, 'value': scores[k]})
    ) AS high_scores
FROM filtered_data
WHERE id = 1;

-- 场景：统计每个人有多少个标签是 true
SELECT 
    id, 
    list_sum(list_transform(map_values(tags), v -> CASE WHEN v THEN 1 ELSE 0 END)) AS active_tag_count 
FROM map_demo;

SIMILAR TO 运算符根据其模式是否匹配给定字符串返回 true 或 false。它类似于 LIKE，区别在于它使用正则表达式来解释模式。

SELECT 'abc' SIMILAR TO 'abc';       -- true
SELECT 'abc' SIMILAR TO 'a';         -- false
SELECT 'abc' SIMILAR TO '.*(b|d).*'; -- true
SELECT 'abc' SIMILAR TO '(b|c).*';   -- false
SELECT 'abc' NOT SIMILAR TO 'abc';   -- false

正则函数

CREATE TABLE regex_full_demo (
    id INT,
    text_content VARCHAR,
    log_data VARCHAR
);

INSERT INTO regex_full_demo VALUES
(1, 'Order ID: ORD-2023-001, Price: $99.5', '[INFO] User alice logged in'),
(2, 'Contact: bob@test.com, Phone: 13800138000', '[ERROR] Disk full on /dev/sda1'),
(3, 'Date: 2024-07-29, Code: A1B2C3', '[WARN] Memory usage at 90%');

SELECT *
FROM regex_full_demo;

-- 全量正则函数示例
-- 提取订单号中的年份部分 (第1个捕获组)
SELECT
	id,
	regexp_extract(text_content, 'ORD-(\d{4})-\d+', 1 ) as order_year
FROM regex_full_demo
WHERE id = 1
;

-- 不指定 group (默认为 0，返回整个匹配项)
SELECT
	id,
	regexp_extract(text_content, 'ORD-\d{4}-\d+') AS full_order_id 
FROM regex_full_demo
WHERE id = 1
;

-- 返回一个结构体（Struct），字段名为 name_list 中的名称。
SELECT
	id,
	regexp_extract(text_content, '(\d{4})-(\d{2})-(\d{2})', ['y', 'm', 'd']) 
FROM regex_full_demo
WHERE id = 3;

-- 访问结构体中的特定字段
SELECT
	id,
	regexp_extract(text_content, '(\d{4})-(\d{2})-(\d{2})', ['y', 'm', 'd'])['m'] as month_only
FROM regex_full_demo
WHERE id = 3
;

-- 返回所有匹配项组成的 List。
-- 提取文本中所有的数字序列
SELECT
	id,
	regexp_extract_all(text_content, '\d+') as all_numbers
FROM regex_full_demo
WHERE id = 1
;

-- 示例：提取所有邮箱地址 (假设 text_content 中有多个邮箱)
-- 注意：这里为了演示，我们手动构造一个含多个邮箱的字符串
SELECT regexp_extract_all('a@b.com and c@d.org', '(\w+@\w+.\w+)', 1) as emails;

-- 整个字符串必须完全匹配正则才返回 
SELECT
	id,
	regexp_full_match(text_content, '^\d+$') as is_pure_number
FROM regex_full_demo
WHERE id = 1
;

-- 示例：判断日志级别格式是否标准 [WORD]
SELECT 
    id,
    regexp_full_match(log_data, '^\[\w+\] .+$') AS is_valid_log_format 
FROM regex_full_demo WHERE id = 1;

-- 只要字符串中包含匹配项就返回 true (部分匹配)
SELECT
	id,
	regexp_matches(log_data, 'ERROR') as has_error
FROM regex_full_demo
;

-- 示例：查找包含邮箱格式的行
SELECT 
    id, 
    regexp_matches(text_content, '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}') AS has_email 
FROM regex_full_demo;

-- 替换匹配项。默认只替换第一个，加 'g' 选项替换全部
SELECT 
    id,
    regexp_replace(text_content, '(1\d{2})\d{4}(\d{4})', '\1****\2') AS masked_phone 
FROM regex_full_demo WHERE id = 2
;

-- 示例：全局替换 (添加 'g' 选项)，将所有数字替换为 '#'
SELECT
	id,
	regexp_replace(text_content, '\d', '#', 'g') as no_number
FROM regex_full_demo
WHERE id = 1
;


-- 按正则分割，返回 List
-- 按非字母字符分割单词
SELECT
	id,
	regexp_split_to_array(text_content, '[^a-zA-Z]+') as words_list
FROM regex_full_demo
WHERE id = 1
;

-- 按正则分割，返回多行 
-- 将一行日志拆分成多行单词
SELECT 
    id, 
    word 
FROM regex_full_demo, 
     UNNEST(regexp_split_to_array(log_data, '\s+')) AS t(word)
WHERE id = 1;

结构体函数

-- 1. 创建包含 STRUCT 类型的测试表
-- 注意：所有 STRUCT 字段都必须定义具体的字段名
CREATE TABLE struct_demo (
    id INT,
    user_info STRUCT(name VARCHAR, age INT, city VARCHAR),       -- 命名结构体：用户信息
    coordinates STRUCT(x DOUBLE, y DOUBLE),                      -- 命名结构体：坐标
    status_record STRUCT(status_msg VARCHAR, code INT, is_ok BOOLEAN) -- 命名结构体：状态记录
);

-- 2. 插入测试数据
-- 可以使用 {'key': value} 语法，也可以使用 row() 函数（DuckDB 会自动映射到对应字段）
INSERT INTO struct_demo VALUES
(1, {'name': 'Alice', 'age': 30, 'city': 'NY'}, {'x': 10.5, 'y': 20.1}, row('Status_OK', 200, true)),
(2, {'name': 'Bob', 'age': 25, 'city': 'LA'}, {'x': 5.2, 'y': 8.8}, row('Status_Err', 404, false)),
(3, {'name': 'Charlie', 'age': 35, 'city': 'SF'}, {'x': 0.0, 'y': 0.0}, row('Status_Warn', 500, true));


SELECT *
FROM struct_demo;


-- 访问与提取
-- 点记法：提取命名字段
SELECT
	user_info.name
FROM struct_demo
WHERE id = 1
;
-- 括号记法：提取命名字段
SELECT
	user_info['name']
FROM struct_demo
WHERE id = 1
;
-- 函数提取：通过字段名提取
SELECT struct_extract(user_info, 'name') 
FROM struct_demo 
WHERE id=1;

-- 创建与合并
-- 创建未命名结构体（元组）
SELECT row('Test', 123, false);
-- 创建命名结构体
SELECT struct_pack(i := 4, s := 'hello');
-- 合并多个结构体为一个
SELECT struct_concat(struct_pack(a:=1), struct_pack(b:=2));

-- 修改与更新
-- 向现有结构体添加新字段
SELECT
	struct_insert(user_info, email:='a@b.com')
FROM struct_demo
WHERE id = 1
;
-- 更新已有字段或添加新字段
SELECT
	struct_update(user_info, age:=31, vip:=true)
FROM struct_demo
WHERE id = 1
;

SELECT struct_pack(a:=1, b:=2, c:=3)

-- 搜索与定位
-- 检查 STRUCT 是否包含指定的条目。
SELECT struct_contains(row(1, 2, 3), 2);
SELECT struct_position(row(1, 2, 3), 2);

-- 动态更新结构体中的某个字段
-- 将所有错误状态码（>=400）标记为 is_ok = false
SELECT 
	id,
	struct_update(
		status_record,
		is_ok := CASE WHEN status_record.code >= 400 THEN false ELSE true END
	) AS cleaned_status
FROM struct_demo
;

-- 将多个列打包成一个 Struct
-- 需要将相关的几个列打包，方便后续传递给 Python 或存入 JSON 字段
SELECT 
	id,
	struct_pack(
		name := user_info.name,
		coord_x := coordinates.x,
		coord_y := coordinates.y
	) as user_location_struct
FROM struct_demo
;

-- 从 JSON 字符串解析为 Struct
SELECT CAST('{"name": "Dave", "age": 40}' AS STRUCT(name VARCHAR, age INT)) AS parsed_user;

字符串函数

CREATE TABLE string_full_demo (
    id INT,
    raw_text VARCHAR,           -- 普通文本
    complex_emoji VARCHAR,      -- 复杂 Emoji 文本
    file_path VARCHAR,          -- 文件路径
    messy_data VARCHAR,         -- 杂乱数据
    url_str VARCHAR             -- URL 字符串
);

INSERT INTO string_full_demo VALUES
(1, 'Hello DuckDB 🦆', '🤦🏼‍♂️ High Five', '/usr/local/bin/duckdb.exe', '  Name: Alice ; Age: 30  ', 'https://duckdb.org/docs?lang=zh'),
(2, 'Data Engineering is fun!', 'Complex: 👨‍👩‍👧‍👦', 'C:\Windows\System32\cmd.exe', 'ID=1001;Status=Active', 'path/to/file.csv'),
(3, 'mühleisen', 'Simple: 😀', '/var/log/syslog', 'Key1:Val1|Key2:Val2', 'hello%20world');

SELECT *
FROM string_full_demo;

-- 基础提取与切片
-- 提取单个字符
SELECT 'abc'[2];
-- 切片提取
SELECT 'abc'[2:3];
-- 取左侧 n 个字符
SELECT left('abc', 2);
-- 取右侧 n 个字符
SELECT right('abc', 2);
-- 传统子串提取
SELECT substring('abc', 2, 3);
-- 字符数
SELECT length('abc');
-- 字节数 (UTF-8)  Emoji
SELECT length('abc');

-- 搜索、匹配与替换
-- 是否包含子串
SELECT contains('abc', 'bc');
-- 是否以...开头
SELECT starts_with('abc', 'ab');
-- 是否以...结尾
SELECT suffix('abc', 'bc');
-- 返回子串位置
SELECT instr('abc', 'b');
-- 全局替换
SELECT replace('abc', 'b', 'x');
-- 字符映射替换
SELECT translate('abc', 'ac', 'xz');
-- 从 string 中去除变音符号。
SELECT strip_accents('mühleisen');

-- 连接、填充与格式化
SELECT 'a' || 'b';
-- 连接多个值 (跳过NULL)
SELECT concat('a', null, 1, 'b');
-- 带分隔符连接
SELECT concat_ws(', ', 'a', 'b', 1);
-- 格式化字符串
SELECT format('User {} is {}', 'Alice', 30);
-- 左填充
SELECT lpad('99', 5, '0');
-- 生成文本条形图
SELECT bar(5, 0, 20, 10)
-- 人类可读字节大小
SELECT format_bytes(16000);

-- 分割、路径与 URL 解析
-- 按分隔符分割为 List
SELECT string_split('a,b,c', ',');
-- 取分割后的第 idx 部分
SELECT split_part('a,b,c', ',', 2);
-- 提取文件名 (可去扩展名)
SELECT parse_filename('a.txt', true)
-- 提取目录路径
SELECT parse_dirpath('/home/a.txt')
-- 返回路径组件列表
SELECT parse_path('/home/a/b/c.txt')
-- URL 编码
SELECT url_encode('hello world')
-- URL 解码
SELECT url_decode('hello%20world')
-- md5
SELECT md5('abc')

-- 文本相似度
SELECT jaro_similarity('abc', 'abcccccccccc');
SELECT jaccard('a', 'abccccccccccdddd');
SELECT levenshtein('a', 'abccccccccccdddd');

-- 利用 split_part 和 trim 从非结构化字符串中提取数据。
SELECT 
	id,
	trim(split_part(split_part(messy_data, 'Name:', 2), ';', 1)) as clean_name,
	trim(split_part(split_part(messy_data, 'Age:', 2), ' ', 1)) as clean_age
FROM string_full_demo
WHERE id = 1;

-- 自动识别 Windows 或 Unix 路径并提取文件名。
SELECT 
    id,
    file_path,
    parse_filename(file_path, true, 'both_slash') AS pure_filename
FROM string_full_demo;

窗口函数

-- 1. 创建测试表
CREATE TABLE window_demo (
    id INT,
    plant VARCHAR,        -- 电厂/部门
    event_date DATE,      -- 日期 (用于 RANGE/GROUPS 演示)
    generation DOUBLE,    -- 发电量/数值
    operator VARCHAR,     -- 操作员 (用于 DISTINCT 演示)
    status VARCHAR        -- 状态 (包含 NULL，用于 IGNORE NULLS 演示)
);

-- 2. 插入精心构造的测试数据
INSERT INTO window_demo VALUES
-- Plant A: 连续日期，有 NULL 值
(1, 'Plant_A', '2023-01-01', 100.0, 'Alice', 'OK'),
(2, 'Plant_A', '2023-01-02', 150.0, 'Bob',   NULL),   -- NULL 值
(3, 'Plant_A', '2023-01-03', 120.0, 'Alice', 'OK'),
(4, 'Plant_A', '2023-01-04', NULL,  'Charlie','WARN'), -- NULL 发电量
(5, 'Plant_A', '2023-01-05', 180.0, 'Alice', 'OK'),

-- Plant B: 日期有跳跃 (1号、3号、4号)，且3号有两条记录 (用于 GROUPS 演示)
(6, 'Plant_B', '2023-01-01', 200.0, 'David', 'OK'),
(7, 'Plant_B', '2023-01-03', 220.0, 'Eve',   'OK'),   -- 跳跃了2号
(8, 'Plant_B', '2023-01-03', 240.0, 'David', 'WARN'), -- 同一天第二条记录
(9, 'Plant_B', '2023-01-04', 210.0, 'Eve',   NULL);   -- NULL 值

SELECT *
FROM window_demo;

-- 基础排名与编号
SELECT plant, event_date, generation,
	-- 强制唯一编号
	ROW_NUMBER() OVER (PARTITION BY plant ORDER BY event_date) as row_num,
	-- 有间隙排名
	RANK() OVER (PARTITION BY plant ORDER BY operator ASC) as rank_val,
	-- 无间隙排名
	DENSE_RANK() OVER (PARTITION BY plant ORDER BY operator ASC) as dense_rank_val,
	-- 均匀分桶 (分2个桶)
	NTILE(2) OVER (PARTITION BY plant ORDER BY generation) as ntile_group,
	-- 累积分布 (0~1)
	CUME_DIST() OVER (PARTITION BY plant ORDER BY generation) as cume_dist_val,
	-- 百分比排名 (0~1)
	PERCENT_RANK() OVER (PARTITION BY plant ORDER BY generation) as pct_rank_val
FROM window_demo
WHERE plant = 'Plant_A';

-- 偏移与取值
-- 注意 IGNORE NULLS 的强大作用。
SELECT 
    plant, event_date, generation, status,
    -- 获取上一行的发电量
    LAG(generation, 1) OVER w AS prev_gen,
    -- 获取下一行的发电量，如果没有则填 0
    LEAD(generation, 1, 0) OVER w AS next_gen,
    
    -- 获取窗口内第一个非 NULL 的 status (IGNORE NULLS 生效)
    FIRST_VALUE(status IGNORE NULLS) OVER w AS first_valid_status,
    
    -- 获取窗口内最后一个值 (注意：必须加 UNBOUNDED FOLLOWING 否则永远返回当前行)
    LAST_VALUE(generation) OVER (
        PARTITION BY plant ORDER BY event_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_gen_overall,
    
    -- 获取排序后第 2 个非 NULL 的 generation
    NTH_VALUE(generation, 2 IGNORE NULLS) OVER (
        PARTITION BY plant ORDER BY event_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_valid_gen
FROM window_demo
WINDOW w AS (PARTITION BY plant ORDER BY event_date);

-- 在窗口内计算去重后的数量或列表。
SELECT
	plant, event_date, operator,
	-- 计算到当前时间位置，出现过的不重复操作员数量
	COUNT(DISTINCT operator) OVER (PARTITION BY plant ORDER BY event_date) as cnt1,
	-- 将到当前日期为止的不重复操作员聚合成一个List
	LIST(DISTINCT operator) OVER (PARTITION BY plant ORDER BY event_date) as cnt2
FROM window_demo;

SELECT 
    plant, event_date, status,
    MODE(status ORDER BY event_date DESC) OVER (
        PARTITION BY plant ORDER BY event_date
    ) AS latest_modal_status
FROM window_demo;

-- ROWS 框架 (按物理行数)
-- 严格数行数，不管日期的实际跨度。
SELECT 
	plant, event_date, generation,
	-- 当前行 + 前1行 + 后1行 共3行的总合
	SUM(generation) OVER (
		PARTITION BY plant ORDER BY event_date
		ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	) as rows_sum_3
FROM window_demo
;

-- RANGE 框架 (按物理值/时间跨度)
-- 按 ORDER BY 列的实际值（如日期差）来计算，无视物理行数。
SELECT
	plant, event_date, generation,
	-- 当前日期前后1天内的发电量总和（注意Plant_B 1月3日有两条记录，都会被包含）
	SUM(generation) OVER (
		PARTITION BY plant ORDER BY event_date
		RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND INTERVAL 1 DAY FOLLOWING
	) as range_sum_3days
FROM window_demo;

-- GROUPS 框架 (按逻辑分组)
-- 将 ORDER BY 值相同的行视为“一组”。
SELECT
	plant, event_date, generation,
	-- 当前组 + 前1组 + 后1组
	-- 于 Plant_B，1月3号的两条记录属于“同一组”，它们会一起被包含在窗口中。
	SUM(generation) OVER (
		PARTITION BY plant ORDER BY event_date
		GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	) as group_sum
FROM window_demo;

-- 计算“除自己之外”的周围数据，常用于异常检测
SELECT 
    plant, event_date, generation,
    -- 计算前后 1 天内的平均值，但 EXCLUDE 掉当前行 (即不包含自己的值)
    AVG(generation) OVER (
        PARTITION BY plant ORDER BY event_date
        RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING
        EXCLUDE CURRENT ROW
    ) AS avg_others_3days
FROM window_demo;

-- WINDOW 子句 (复用窗口定义)
-- 避免重复书写冗长的 OVER (...)，且 DuckDB 会合并相同窗口定义的计算，大幅提升性能
SELECT 
    plant, event_date, generation,
    MIN(generation) OVER w7 AS min_7day,
    AVG(generation) OVER w7 AS avg_7day,
    MAX(generation) OVER w7 AS max_7day,
    MIN(generation) OVER w3 AS min_3day,
    AVG(generation) OVER w3 AS avg_3day
FROM window_demo
WINDOW 
    -- 定义 7天移动窗口 (前后各3天)
    w7 AS (
        PARTITION BY plant ORDER BY event_date
        RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '3' DAY FOLLOWING
    ),
    -- 定义 3天移动窗口 (前后各1天)
    w3 AS (
        PARTITION BY plant ORDER BY event_date
        RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING
    )
ORDER BY plant, event_date;

-- QUALIFY 子句 (直接过滤窗口结果)
-- 告别繁琐的子查询或 CTE！QUALIFY 专门用于在窗口函数计算后直接过滤。
-- 场景：只保留每个电厂每天发电量最高的那条记录
SELECT 
    plant, event_date, generation, operator
FROM window_demo
QUALIFY ROW_NUMBER() OVER (PARTITION BY plant, event_date ORDER BY generation DESC) = 1;
-- 结果：Plant_B 在 1月3号 只会保留 generation=240 的那条记录

-- 场景：计算每个电厂 3天移动窗口内的箱线图数据 (最小值、四分位数、最大值)
SELECT 
    plant, event_date,
    MIN(generation) OVER w AS min_val,
    -- quantile_cont 返回一个 List，包含 25%, 50%, 75% 分位数
    QUANTILE_CONT(generation, [0.25, 0.5, 0.75]) OVER w AS iqr_list,
    MAX(generation) OVER w AS max_val
FROM window_demo
WINDOW w AS (
    PARTITION BY plant ORDER BY event_date
    RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING
)
ORDER BY plant, event_date;

窗口示例：

筛选用户登录日志中连续三天登录的用户

-- 1. 创建会员登录记录表
CREATE TABLE user_login_log (
    log_id INT,
    user_id INT,
    login_time TIMESTAMP -- 使用 TIMESTAMP 模拟真实的带时分秒的登录时间
);

-- 2. 插入测试数据 (假设当前日期是 2023-10-10 左右)
INSERT INTO user_login_log VALUES
-- User 101: 连续 4 天 (10-01 到 10-04) -> 命中
(1, 101, '2023-10-01 08:00:00'),
(2, 101, '2023-10-02 09:00:00'),
(3, 101, '2023-10-03 10:00:00'),
(4, 101, '2023-10-04 11:00:00'),

-- User 102: 间隔登录 (10-01, 10-03, 10-05) -> 不命中
(5, 102, '2023-10-01 08:00:00'),
(6, 102, '2023-10-03 09:00:00'),
(7, 102, '2023-10-05 10:00:00'),

-- User 103: 10-01 登录了 3 次，10-02 登录 1 次，10-03 登录 1 次 (去重后连续3天) -> 命中
(8,  103, '2023-10-01 08:00:00'),
(9,  103, '2023-10-01 12:00:00'),
(10, 103, '2023-10-01 20:00:00'),
(11, 103, '2023-10-02 09:00:00'),
(12, 103, '2023-10-03 10:00:00'),

-- User 104: 只连续了 2 天 (10-01, 10-02) -> 不命中
(13, 104, '2023-10-01 08:00:00'),
(14, 104, '2023-10-02 09:00:00'),

-- User 105: 10-02, 10-03, 10-04 连续，然后断开，10-06, 10-07 连续 -> 命中 (有一段连续3天)
(15, 105, '2023-10-02 08:00:00'),
(16, 105, '2023-10-03 09:00:00'),
(17, 105, '2023-10-04 10:00:00'),
(18, 105, '2023-10-06 11:00:00'),
(19, 105, '2023-10-07 12:00:00');


-- 方法1

SELECT
	-- 去重输出用户id
	t2.user_id
FROM (
	-- 添加一列记录 当前日期前2行的登录日期是哪天
	SELECT 
		user_id, 
		login_date,
		LAG(t1.login_date , 2) OVER (PARTITION BY user_id ORDER BY t1.login_date) as pre2_date
	FROM (
		-- 用户单日登录去重保留一条数据
		SELECT 
			user_id,
			cast(login_time as DATE) as login_date
		FROM user_login_log
		GROUP BY 1, 2
	) t1
) t2
-- 如果当前日期和前2天日期之差是2表示是连续三天登录
WHERE t2.login_date - t2.pre2_date = 2
GROUP BY user_id
;

-- 方法2

-- 用户去重
SELECT DISTINCT t2.user_id
FROM (
	-- RANGE 计算日期维度前2天及当天 共三天数据条数
	SELECT 
		t1.user_id,
		t1.login_date,
		COUNT(t1.login_date) OVER (
			PARTITION BY t1.user_id ORDER BY t1.login_date
			RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW
		) cnt_days
	FROM (
		-- 单日去重 每天1条
		SELECT DISTINCT user_id, login_time::DATE as login_date
		FROM user_login_log
	) t1
) t2
-- 筛选3天都有登录记录的
WHERE t2.cnt_days = 3

示例2：有规律的组织层级查找

-- 1. 创建用户码值表
CREATE TABLE user_codes (
    user_id VARCHAR,
    code VARCHAR
);
-- 2. 插入测试数据
INSERT INTO user_codes VALUES
('u1', '省级'),
('u2', '市级'),
('u3', '区级'),
('u4', '市级'), -- 用于测试同码值多用户
('u5', '区级');

WITH code_level as (
	SELECT '省级' as code, ['省级'] as ancestors
	UNION ALL
	SELECT '市级' as code, ['市级', '省级'] as ancestors
	UNION ALL
	SELECT '区级' as code, ['区级', '市级', '省级'] as ancestors
)
SELECT t1.user_id, unnest(t2.ancestors) as ext_code
FROM user_codes t1 
INNER JOIN code_level t2 ON t1.code = t2.code
ORDER BY t1.user_id, t2.ancestors
;

-- 输出
u1	省级
u2	市级
u2	省级
u3	区级
u3	市级
u3	省级
u4	市级
u4	省级
u5	区级
u5	市级
u5	省级
CREATE TABLE tb02 (org_code VARCHAR);
INSERT INTO tb02 VALUES 
('000002000008'),
('000002000008000001'),
('000002000008000002'),
('000002000008000001000001'),
('000002000008000002000001');

SELECT org_code as current_code, 
	-- 使用当前的组织编码 每6位生成一个切片 org_code[2: 6 * n]，炸裂出所有父级
	unnest(list_transform(
	generate_series(2, len(org_code) // 6),
	level -> org_code[1: level * 6]
)) as parent_code
FROM tb02
ORDER BY org_code;

-- 输出
000002000008	000002000008
000002000008000001	000002000008
000002000008000001	000002000008000001
000002000008000001000001	000002000008
000002000008000001000001	000002000008000001
000002000008000001000001	000002000008000001000001
000002000008000002	000002000008
000002000008000002	000002000008000002
000002000008000002000001	000002000008
000002000008000002000001	000002000008000002
000002000008000002000001	000002000008000002000001

-- 另一种情况：只保留严格的“子-父”关系，除了最顶层那个没有父级的节点需要保留自身
SELECT org_code as current_code, 
	-- 使用当前的组织编码 每6位生成一个切片 org_code[2: 6 * n]，炸裂出所有父级
	unnest(list_transform(
	-- 除了最顶级父级可以是本身，其他不能父级是本身 所以org_code[2: 6 * (n - 1)]
	generate_series(
            2, 
            IF(len(org_code) // 6 == 2, 2, len(org_code) // 6 - 1)
        ),
	level -> org_code[1: level * 6]
)) as parent_code
FROM tb02
ORDER BY org_code

-- 输出
000002000008	000002000008
000002000008000001	000002000008
000002000008000001000001	000002000008
000002000008000001000001	000002000008000001
000002000008000002	000002000008
000002000008000002000001	000002000008
000002000008000002000001	000002000008000002

-- 去除unnest炸裂函数
SELECT org_code as current_code, 
	-- 使用当前的组织编码 每6位生成一个切片 org_code[2: 6 * n]，炸裂出所有父级
	list_transform(
	-- 除了最顶级父级可以是本身，其他不能父级是本身 所以org_code[2: 6 * (n - 1)]
	generate_series(
            2, 
            IF(len(org_code) // 6 == 2, 2, len(org_code) // 6 - 1)
        ),
	level -> org_code[1: level * 6]
) as parent_code
FROM tb02
ORDER BY org_code

-- 输出
000002000008	{'000002000008'}
000002000008000001	{'000002000008'}
000002000008000001000001	{'000002000008','000002000008000001'}
000002000008000002	{'000002000008'}
000002000008000002000001	{'000002000008','000002000008000002'}

json函数

-- 1. 创建测试表
CREATE TABLE json_demo (
    id INT,
    raw_json VARCHAR,      -- 模拟从外部读取的原始 JSON 字符串
    typed_json JSON        -- DuckDB 原生的 JSON 类型
);

-- 2. 插入各种结构的测试数据
INSERT INTO json_demo VALUES
-- 简单对象
(1, '{"name": "Alice", "age": 30, "city": "NY"}', 
    '{"name": "Alice", "age": 30, "city": "NY"}'),

-- 嵌套对象与数组
(2, '{"user": {"name": "Bob", "address": {"city": "LA"}}, "tags": ["sql", "data"]}', 
    '{"user": {"name": "Bob", "address": {"city": "LA"}}, "tags": ["sql", "data"]}'),

-- 数组内嵌对象
(3, '{"projects": [{"name": "P1", "status": "done"}, {"name": "P2", "status": "pending"}]}', 
    '{"projects": [{"name": "P1", "status": "done"}, {"name": "P2", "status": "pending"}]}'),

-- 包含 null 和缺失字段
(4, '{"name": "Charlie", "age": null}', 
    '{"name": "Charlie", "age": null}'),

-- 脏数据/非标准 JSON (用于测试 valid)
(5, '{invalid json string}', 
    NULL);

SELECT *
FROM json_demo
;

-- JSON 创建与类型转换
-- 将 VARCHAR 解析并转换为原生 JSON 类型。
SELECT json('{"name": "bob", "age": 20}');
-- 将任意表达式、行或表转换为 JSON。
SELECT to_json({'name': 'bob', 'age': 20});
-- 标准 SQL 类型转换。
SELECT 
	CAST(raw_json AS JSON) 
FROM json_demo 
WHERE id=1;

SELECT 
	raw_json::JSON 
FROM json_demo 
WHERE id=1;

-- JSON 提取 
-- 提取 JSON 片段，返回 JSON 类型。
SELECT 
	json_extract(typed_json, '$.tags') 
FROM json_demo
WHERE id = 2
;
-- 提取 JSON 片段，返回 VARCHAR 标量。
SELECT 
	json_extract_string(typed_json, '$.user.name')
FROM json_demo
WHERE id = 2
;

-- -> 操作符 等价于 json_extract，返回 JSON。
SELECT 
	typed_json -> '$.tags' 
FROM json_demo
WHERE id = 2
;

-- ->> 操作符 等价于 json_extract_string，返回 VARCHAR。
SELECT 
	typed_json ->> '$.user.name'
FROM json_demo
WHERE id = 2
;

-- 提取数组第一个元素：$.tags[0]
-- 提取数组所有元素的某个属性：$.projects[*].name (返回 ["P1", "P2"])


--  JSON 结构与属性检查
-- 返回 JSON 对象顶层的所有 key (List)。
SELECT json_keys(typed_json)
FROM json_demo
WHERE id = 1
;

-- 返回 JSON 数组的长度。如果不是数组则报错/返回NULL。
SELECT json_array_length(typed_json -> '$.tags')
FROM json_demo
WHERE id = 2
;

-- 返回 JSON 值的类型 (OBJECT, ARRAY, INTEGER, VARCHAR, NULL 等)。
SELECT json_type(typed_json -> '$.age')
FROM json_demo
WHERE id = 1
;

-- 判断 JSON 是否包含目标 JSON 片段。
SELECT
	json_contains(typed_json, '"sql"')
FROM json_demo
WHERE id = 2
;

-- 验证字符串是否为合法的 JSON。
SELECT id, json_valid(raw_json) FROM json_demo;

-- 将多行聚合为一个 JSON 数组。
SELECT json_group_array(name)
FROM (
	SELECT 'a' as name
	UNION
	select 'b'
)

CREATE TABLE example1 (k VARCHAR, v INTEGER);
INSERT INTO example1 VALUES ('duck', 42), ('goose', 7);
SELECT * FROM example1;
-- 第一个字段值是key 第二个字段值是value
SELECT json_group_object(k, v) FROM example1;

-- 推断 JSON 数组中所有元素的结构，返回 STRUCT 定义。
SELECT json_group_structure(typed_json -> 'projects')
FROM json_demo where id = 3;

-- 将 j2 合并到 j1 中（RFC 7396 标准），j2 覆盖 j1 的同名 key。
SELECT json_merge_patch('{"a":1, "b":2}', '{"b":3, "c":4}');
-- 将 JSON 格式化为带缩进的可读字符串。
SELECT json_pretty(typed_json) FROM json_demo WHERE id=1;

-- 将 JSON 数组炸裂成多行（最常用）
-- 利用 json_extract 提取数组，然后用 UNNEST 炸裂。
SELECT 
    id,
    unnest(cast(typed_json -> 'tags' as VARCHAR[]))  AS single_tag
FROM json_demo
WHERE id = 2;

SELECT 
	id,
	project.name,
	project.status
FROM json_demo, unnest(cast(typed_json -> 'projects' as STRUCT(name VARCHAR, status VARCHAR)[])) as t(project)
WHERE id = 3;



-- 自动推断 JSON 文件结构，直接当作表查询
SELECT * FROM read_json_auto('data.json');


WITH parsed_data as (
	SELECT 
		id,
		CAST(typed_json AS STRUCT(
			projects STRUCT(name VARCHAR, status VARCHAR)[]
		)) as parsed
	FROM json_demo
	WHERE id = 3
)
SELECT id, project.name, project.status
FROM parsed_data, unnest(parsed.projects) as t(project);

CREATE TABLE json_adv_demo (
    id INT,
    j JSON
);

INSERT INTO json_adv_demo VALUES
-- 标准数据
(1, '{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
-- 缺失字段 (没有 coolness)，多出字段 (hair)
(2, '{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}'),
-- 脏数据：类型不匹配 (family 是数字，coolness 是字符串)
(3, '{"family": 123, "coolness": "not_a_number"}');

SELECT *
FROM json_adv_demo;

-- 根据指定的 structure 转换 json。如果类型转换失败或缺失字段，不会报错，而是返回 NULL
-- 场景 1：只提取部分字段，缺失的字段自动变为 NULL
SELECT 
    id,
    json_transform(j, '{"family": "VARCHAR", "coolness": "DOUBLE"}') AS partial_struct
FROM json_adv_demo;

-- 结果：
-- id=1: {'family': anatidae, 'coolness': 42.42}
-- id=2: {'family': canidae, 'coolness': NULL}      <-- 缺失 coolness，变为 NULL
-- id=3: {'family': 123, 'coolness': NULL}          <-- coolness 无法转为 DOUBLE，变为 NULL

-- 转换为包含 LIST 的复杂 STRUCT
SELECT
	id,
	json_transform(j, '{"family": "VARCHAR", "species": ["VARCHAR"], "hair": "BOOLEAN"}') as full_struct
FROM json_adv_demo

-- JSON 表函数
-- json_each(json [, path]) (浅层遍历)
-- 只遍历 JSON 的*顶层（第一层）数组或对象，为每个元素返回一行。
-- 遍历 JSON 的顶层 Key-Value
SELECT
	e.id,
	je.key,
	je.value,
	je.type,
	je.fullkey
FROM json_adv_demo e, json_each(e.j) as je
WHERE e.id = 1;

-- 结果：
-- id | key      | value               | type    | fullkey
-- 1  | family   | "anatidae"          | VARCHAR | $.family
-- 1  | species  | ["duck","goose"]    | ARRAY   | $.species
-- 1  | coolness | 42.42               | DOUBLE  | $.coolness

-- 定 path，从特定层级开始遍历（横向连接 Lateral Join）
SELECT
	e.id,
	je.key,
	je.value,
	je.type
FROM json_adv_demo e, json_each(e.j, '$.species') as je
WHERE e.id = 1
;

-- json_tree(json [, path]) (深层递归遍历)
-- 以深度优先的方式递归遍历 JSON 的*所有层级，为结构中的每一个节点（包括对象、数组、标量）返回一行。*
-- 深度优先遍历整个 JSON 树
-- 场景：提取 JSON 中所有的 "Key-Value" 叶子节点
SELECT 
    e.id,
    -- 过滤出非对象、非数组的节点（即真正的值）
    je.fullkey AS json_path,
    je.atom AS leaf_value  -- atom 字段专门用于获取标量值
FROM json_adv_demo AS e, 
     json_tree(e.j) AS je
WHERE 
    e.id = 1 
    AND je.type NOT IN ('OBJECT', 'ARRAY'); -- 排除容器节点

行转列、列转行

-- 1. 创建宽表（列很多，行很少，典型的“不规整”数据）
CREATE TABLE sales_wide (
    id INT,
    name VARCHAR,
    q1_sales INT,
    q2_sales INT,
    q3_sales INT,
    q4_sales INT
);

INSERT INTO sales_wide VALUES
(1, 'Alice', 100, 150, 200, 250),
(2, 'Bob', 120, 130, NULL, 280); -- 注意 Bob 的 q3 是 NULL

SELECT *
FROM sales_wide;

-- 2. 创建长表（标准的二维关系表，适合存储和计算）
CREATE TABLE scores_long (
    id INT,
    name VARCHAR,
    subject VARCHAR,
    score INT
);

INSERT INTO scores_long VALUES
(1, 'Alice', 'Math', 90),
(1, 'Alice', 'English', 85),
(2, 'Bob', 'Math', 80),
(2, 'Bob', 'English', 95),
(2, 'Bob', 'Physics', 90);

SELECT *
FROM scores_long
;

-- UNPIVOT（列转行 / 宽表转长表）
-- 把 q1_sales, q2_sales 这种横向排列的指标，折叠成标准的“季度”和“销售额”两列，方便后续做时间序列分析。
SELECT *
FROM sales_wide
UNPIVOT (
	-- 定义折叠后的“值”列名
	(sales_amount)
	-- 定义折叠后的“名称”列名以及要折叠的原列
	FOR quarter IN (q1_sales, q2_sales, q3_sales, q4_sales)
);

-- PIVOT（行转列 / 长表转宽表）
SELECT * FROM scores_long
PIVOT (
    -- 1. 聚合函数及目标值列 也就是 行变列 每列的规则 因为可能存在 一样字段 比如 同一用户 两条数学成绩记录，那转成列的时候需要指定 列对应使用哪个字段的值
	-- 当前对科目列进行行转列 那科目具体记录 科目名称就变成了列名，列名下还要存具体的值 同一用户多条数据最后变成了一个用户 多个科目列 也就是进行了聚合，可以指定 每个科目对应值的操作 当前SUM(score)
	-- 一般行转列 是对用户分组 然后 SUM(if(score = 'Math', score, 0))
    SUM(score) 
    -- 2. 要展开的维度列，以及展开后具体的列名（必须用单引号）
    FOR subject IN ('Math', 'English', 'Physics')
)
ORDER BY id;


-- 先 PIVOT 转宽表，再 UNPIVOT 转回长表
SELECT * FROM (
	-- 行转列 指定哪些字段值 转成列名 以及 列名下的值 怎么来的
    SELECT * FROM scores_long
    PIVOT (SUM(score) FOR subject IN ('Math', 'English', 'Physics'))
)
UNPIVOT (
	-- 列转行 指定 列名变成字段值 ，以及 行时候列名对应的值 转换后 变成的字段名
    (score) FOR subject IN ('Math', 'English', 'Physics')
)
ORDER BY id, subject;
下一篇
MiniMax H3 提示词模板
上一篇 笔记本合盖/关屏不休眠，向日葵24小时在线指南