如何在Snowflake中实现Databricks SQL多聚合函数PIVOT逻辑

Snowflake 原生的PIVOT语法仅支持单个聚合函数，要实现Databricks SQL中同时用多个聚合做PIVOT的效果，可参考以下两种实现方式：

首先修正测试表代码：原建表语句缺少age字段，且日期格式需要适配Snowflake标准，修正后的测试数据构建语句如下：

CREATE OR REPLACE TABLE person (id INT, name STRING, date date, class INT, address STRING, age INT);
INSERT INTO person VALUES
    (100, 'John', '2021-01-30', 1, 'Street 1', 25),
    (200, 'Mary', '2021-01-20', 1, 'Street 2', 22),
    (300, 'Mike', '2021-01-21', 3, 'Street 3', 30),
    (100, 'John', '2021-05-15', 4, 'Street 4', 25);

方案一：条件聚合实现（推荐） #

逻辑直观，执行性能更稳定，返回结果与原Databricks查询完全一致：

SELECT 
  id,
  class,
  address,
  SUM(CASE WHEN name = 'John' THEN age END) AS john_a,
  MAX(CASE WHEN name = 'John' THEN date END) AS john_c,
  SUM(CASE WHEN name = 'Mike' THEN age END) AS mike_a,
  MAX(CASE WHEN name = 'Mike' THEN date END) AS mike_c
FROM person
GROUP BY id, class, address;

方案二：嵌套PIVOT实现 #

如果需要沿用PIVOT语法，可以拆分两次PIVOT分别处理不同的聚合函数：

SELECT * 
FROM person
PIVOT (SUM(age) FOR name IN ('John' AS john_a, 'Mike' AS mike_a))
PIVOT (MAX(date) FOR name IN ('John' AS john_c, 'Mike' AS mike_c));

内容的提问来源于stack exchange，提问作者Parth Desai