20230928_09.md

DuckDB 语法糖: Dynamic PIVOT and UNPIVOT 动态行列转换

作者

digoal

日期

2023-09-28

标签

PostgreSQL , PolarDB , duckdb , 语法糖

---

背景

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

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

Dynamic PIVOT and UNPIVOT 动态行列转换

测试数据如下

CREATE TABLE purchases (item VARCHAR, year INT, count INT);  
  
INSERT INTO purchases  
    VALUES ('phasers', 2155, 1035), ('phasers', 2156, 25039), ('phasers', 2157, 95000),  
           ('photon torpedoes', 2155, 255), ('photon torpedoes', 2156, 17899), ('photon torpedoes', 2157, 87492);  
  
FROM purchases;  

item	year	count
phasers	2155	1035
phasers	2156	25039
phasers	2157	95000
photon torpedoes	2155	255
photon torpedoes	2156	17899
photon torpedoes	2157	87492

行转列

CREATE TABLE pivoted_purchases AS  
     PIVOT purchases   
          ON year   
          USING SUM(count)   
          GROUP BY item;  
  
FROM pivoted_purchases;  

item	2155	2156	2157
phasers	1035	25039	95000
photon torpedoes	255	17899	87492

列转行

UNPIVOT pivoted_purchases  
     ON COLUMNS(* EXCLUDE item)  
     INTO  
          NAME year  
          VALUE count;  

item	year	count
phasers	2155	1035
phasers	2156	25039
phasers	2157	95000
photon torpedoes	2155	255
photon torpedoes	2156	17899
photon torpedoes	2157	87492

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

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

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

PostgreSQL 解决方案集合

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

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

About 德哥