20230928_03.md

DuckDB 语法糖: Dynamic column selection 支持通配符,exclude,replace,lambda 等动态列选择|值替换|列选择

作者

digoal

日期

2023-09-28

标签

PostgreSQL , PolarDB , duckdb , 语法糖

---

背景

duckdb增加了很多SQL语法糖, 目标是要让用户可以随心所欲的写SQL.

https://duckdb.org/2023/08/23/even-friendlier-sql.html

Dynamic column selection 支持通配符,exclude等动态列选择

加载一些测试数据:

INSTALL httpfs;  
LOAD httpfs;  
CREATE TABLE trek_facts AS  
    SELECT * FROM 'https://raw.githubusercontent.com/Alex-Monahan/example_datasets/main/Star_Trek-Season_1.csv';  
  
DESCRIBE trek_facts;  

结构如下:

column_name	column_type	null	key	default	extra
season_num	BIGINT	YES	NULL	NULL	NULL
episode_num	BIGINT	YES	NULL	NULL	NULL
aired_date	DATE	YES	NULL	NULL	NULL
cnt_kirk_hookups	BIGINT	YES	NULL	NULL	NULL
cnt_downed_redshirts	BIGINT	YES	NULL	NULL	NULL
bool_aliens_almost_took_over_planet	BIGINT	YES	NULL	NULL	NULL
bool_aliens_almost_took_over_enterprise	BIGINT	YES	NULL	NULL	NULL
cnt_vulcan_nerve_pinch	BIGINT	YES	NULL	NULL	NULL
cnt_warp_speed_orders	BIGINT	YES	NULL	NULL	NULL
highest_warp_speed_issued	BIGINT	YES	NULL	NULL	NULL
bool_hand_phasers_fired	BIGINT	YES	NULL	NULL	NULL
bool_ship_phasers_fired	BIGINT	YES	NULL	NULL	NULL
bool_ship_photon_torpedos_fired	BIGINT	YES	NULL	NULL	NULL
cnt_transporter_pax	BIGINT	YES	NULL	NULL	NULL
cnt_damn_it_jim_quote	BIGINT	YES	NULL	NULL	NULL
cnt_im_givin_her_all_shes_got_quote	BIGINT	YES	NULL	NULL	NULL
cnt_highly_illogical_quote	BIGINT	YES	NULL	NULL	NULL
bool_enterprise_saved_the_day	BIGINT	YES	NULL	NULL	NULL

使用columns(正则)

SELECT  
    episode_num,  
    COLUMNS('.*warp.*')  
FROM trek_facts;  

episode_num	cnt_warp_speed_orders	highest_warp_speed_issued
0	1	1
…	…	…
27	1	1

columns可以和其他函数结合使用

SELECT  
    MAX(COLUMNS('.*warp.*'))  
FROM trek_facts;  

max(trek_facts.cnt_warp_speed_orders)	max(trek_facts.highest_warp_speed_issued)
5	8

可以放在where中进行计算, 匹配多列相当于and

SELECT  
    episode_num,  
    COLUMNS('.*warp.*')  
FROM trek_facts  
WHERE  
    COLUMNS('.*warp.*') >= 2;  
    -- cnt_warp_speed_orders >= 2   
    -- AND   
    -- highest_warp_speed_issued >= 2  

episode_num	cnt_warp_speed_orders	highest_warp_speed_issued
14	3	7
17	2	7
18	2	8
29	2	8

COLUMNS() with EXCLUDE and REPLACE

-- 排除 season_num 列名.  
  
SELECT  
    MAX(COLUMNS(* EXCLUDE season_num))  
FROM trek_facts;  

替换列

-- 使用aired_date::timestamp 替换 aired_date   
SELECT  
    MAX(COLUMNS(* REPLACE aired_date::timestamp AS aired_date))  
FROM trek_facts;  

COLUMNS() with lambda functions

-- 列名符合like ...  
  
SELECT  
    episode_num,  
    COLUMNS(col -> col LIKE '%warp%')  
FROM trek_facts  
WHERE  
    COLUMNS(col -> col LIKE '%warp%') >= 2;  

episode_num	cnt_warp_speed_orders	highest_warp_speed_issued
14	3	7
17	2	7
18	2	8
29	2	8

期望 PostgreSQL|开源PolarDB 增加什么功能?

PolarDB 云原生分布式开源数据库

PolarDB 学习图谱: 训练营、培训认证、在线互动实验、解决方案、内核开发公开课、生态合作、写心得拿奖品

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.

购买PolarDB云服务折扣活动进行中, 55元起

About 德哥