大数据

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;

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

上一篇 笔记本合盖/关屏不休眠，向日葵24小时在线指南