duckdb的pivot支持自动显示列，不必都用IN

〇〇 · 发表于 2023-5-18 15:49

Pivot Statement - DuckDB
CREATE TABLE Cities(Country VARCHAR, Name VARCHAR, Year INT, Population INT);INSERT INTO Cities VALUES ('NL', 'Amsterdam', 2000, 1005);INSERT INTO Cities VALUES ('NL', 'Amsterdam', 2010, 1065);INSERT INTO Cities VALUES ('NL', 'Amsterdam', 2020, 1158);INSERT INTO Cities VALUES ('US', 'Seattle', 2000, 564);INSERT INTO Cities VALUES ('US', 'Seattle', 2010, 608);INSERT INTO Cities VALUES ('US', 'Seattle', 2020, 738);INSERT INTO Cities VALUES ('US', 'New York City', 2000, 8015);INSERT INTO Cities VALUES ('US', 'New York City', 2010, 8175);INSERT INTO Cities VALUES ('US', 'New York City', 2020, 8772);

PIVOT Cities ON Year USING SUM(Population);

CountryName200020102020
NLAmsterdam100510651158
USSeattle564608738
USNew York City801581758772

〇〇 · 发表于 2023-5-18 15:50

用in也是支持的

FROM Cities PIVOT ( SUM(Population) as total, COUNT(Population) as count FOR Year IN (2000, 2010, 2020) Country in ('NL', 'US'));

Name2000_NL_total2000_NL_count2000_US_total2000_US_count2010_NL_total2010_NL_count2010_US_total2010_US_count2020_NL_total2020_NL_count2020_US_total2020_US_count
Amsterdam10051NULL010651NULL011581NULL0
SeattleNULL05641NULL06081NULL07381
New York CityNULL080151NULL081751NULL087721