DuckDB: 强大的动态列选择能力

DuckDB 1.1版本有几个特性使动态列选择变得更好。如果你duckdb版本较低，可以从官网地址下载最新版本进行学习，本文将深入探讨这些特性。包括动态选择列、排除列、基于动态列过滤数据等。

数据准备

为了演示动态列选择，我们需要有很多列的数据集，最好是包含很多数值的数据集。Kaggle提供了FIFA 22 complete player 数据集。我下载了一些CSV文件，并在DuckDB命令行中创建名为players的表：

create table players AS
select *
FROM read_csv('players_*.csv', ignore_errors=true);

验证结果：

D create table players AS select * FROM read_csv('players_*.csv', ignore_errors=true);
100% ▕████████████████████████████████████████████████████████████▏
D select count(*) from main.players;
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│       142079 │
└──────────────┘

让我们通过查询DESCRIBE播放器的输出来快速了解一下我们将使用哪些列，这本身就是一个非常酷的DuckDB特性。

.mode jsonlines
FROM (DESCRIBE players)
SELECT
  MAP {column_name: column_type}
    .array_agg() AS col_info;

输出结果：

D .mode jsonlines
D from (desc players) select map{column_name: column_type}.array_agg() as col_info;
{"col_info":"[{sofifa_id=BIGINT}, {player_url=VARCHAR}, {short_name=VARCHAR}, {long_name=VARCHAR}, {player_positions=VARCHAR}, {overall=BIGINT}, {potential=BIGINT}, {value_eur=DOUBLE}, {wage_eur=DOUBLE}, {age=BIGINT}, {dob=DATE}, {height_cm=BIGINT}, {weight_kg=BIGINT}, {club_team_id=DOUBLE}, {club_name=VARCHAR}, {league_name=VARCHAR}, {league_level=BIGINT}, {club_position=VARCHAR}, {club_jersey_number=BIGINT}, {club_loaned_from=VARCHAR}, {club_joined=DATE}, {club_contract_valid_until=BIGINT}, {nationality_id=BIGINT}, {nationality_name=VARCHAR}, {nation_team_id=DOUBLE}, {nation_position=VARCHAR}, {nation_jersey_number=BIGINT}, {preferred_foot=VARCHAR}, {weak_foot=BIGINT}, {skill_moves=BIGINT}, {international_reputation=BIGINT}, {work_rate=VARCHAR}, {body_type=VARCHAR}, {real_face=VARCHAR}, {release_clause_eur=VARCHAR}, {player_tags=VARCHAR}, {player_traits=VARCHAR}, {pace=BIGINT}, {shooting=BIGINT}, {passing=BIGINT}, {dribbling=BIGINT}, {defending=BIGINT}, {physic=BIGINT}, {attacking_crossing=BIGINT}, {attacking_finishing=BIGINT}, {attacking_heading_accuracy=BIGINT}, {attacking_short_passing=BIGINT}, {attacking_volleys=BIGINT}, {skill_dribbling=BIGINT}, {skill_curve=BIGINT}, {skill_fk_accuracy=BIGINT}, {skill_long_passing=BIGINT}, {skill_ball_control=BIGINT}, {movement_acceleration=BIGINT}, {movement_sprint_speed=BIGINT}, {movement_agility=BIGINT}, {movement_reactions=BIGINT}, {movement_balance=BIGINT}, {power_shot_power=BIGINT}, {power_jumping=BIGINT}, {power_stamina=BIGINT}, {power_strength=BIGINT}, {power_long_shots=BIGINT}, {mentality_aggression=BIGINT}, {mentality_interceptions=BIGINT}, {mentality_positioning=BIGINT}, {mentality_vision=BIGINT}, {mentality_penalties=BIGINT}, {mentality_composure=VARCHAR}, {defending_marking_awareness=BIGINT}, {defending_standing_tackle=BIGINT}, {defending_sliding_tackle=BIGINT}, {goalkeeping_diving=BIGINT}, {goalkeeping_handling=BIGINT}, {goalkeeping_kicking=BIGINT}, {goalkeeping_positioning=BIGINT}, {goalkeeping_reflexes=BIGINT}, {goalkeeping_speed=BIGINT}, {ls=VARCHAR}, {st=VARCHAR}, {rs=VARCHAR}, {lw=VARCHAR}, {lf=VARCHAR}, {cf=VARCHAR}, {rf=VARCHAR}, {rw=VARCHAR}, {lam=VARCHAR}, {cam=VARCHAR}, {ram=VARCHAR}, {lm=VARCHAR}, {lcm=VARCHAR}, {cm=VARCHAR}, {rcm=VARCHAR}, {rm=VARCHAR}, {lwb=VARCHAR}, {ldm=VARCHAR}, {cdm=VARCHAR}, {rdm=VARCHAR}, {rwb=VARCHAR}, {lb=VARCHAR}, {lcb=VARCHAR}, {cb=VARCHAR}, {rcb=VARCHAR}, {rb=VARCHAR}, {gk=VARCHAR}, {player_face_url=VARCHAR}, {club_logo_url=VARCHAR}, {club_flag_url=VARCHAR}, {nation_logo_url=VARCHAR}, {nation_flag_url=VARCHAR}]"}

使用columns()选择列

是时候做一些查询了。我们将从使用COLUMNS（）开始，它返回表中的所有列，与下面的方法相同：

select COLUMNS(*) FROM players LIMIT 1;

直接用 * 星号 结果一样：

D select * from players limit 1;
{
  "sofifa_id":158023,"player_url":"https://sofifa.com/player/158023/lionel-messi/150002","short_name":"L. Messi","long_name":"Lionel Andr茅s Messi Cuccittini","player_positions":"CF","overall":93,"potential":95,"value_eur":100500000.0,"wage_eur":550000.0,"age":27,"dob":"1987-06-24","height_cm":169,"weight_kg":67,"club_team_id":241.0,"club_name":"FC Barcelona","league_name":"Spain Primera Division","league_level":1,"club_position":"CF","club_jersey_number":10,"club_loaned_from":null,"club_joined":"2004-07-01","club_contract_valid_until":2018,"nationality_id":52,"nationality_name":"Argentina","nation_team_id":1369.0,"nation_position":"CF","nation_jersey_number":10,"preferred_foot":"Left","weak_foot":3,"skill