如何在SQL中透视然后聚合

Question

如何从该表中获取：

header2<1>

键	header1	日期
xxxx	A	C
xxxx	A	D	2
xxxx	B	C	3
xxxx	B	D	4

D-Shih的答案可以写得更像雪花：

SELECT 
key,
max(iff(header1 = 'A', date, null)) AS first_date_A,
max(iff(header1 = 'B', date, null)) AS first_date_B
FROM (
SELECT *
FROM fake_data
QUALIFY Row_number() over(partition by header1, key order by date) = 1
) tt
GROUP BY key

其具有给定的数据：

WITH fake_data AS (
SELECT * FROM VALUES
('xxxx','A','C',1),
('xxxx','A','D',2),
('xxxx','B','C',3),
('xxxx','B','D',4)
t(key, header1, header2, date)
)

并且由于ROW_NUMBER而适用于A/B值，但不给你C/D值，并且如果你提到"；这要困难得多"；但在您想要的输出中没有提到该列，我想您也想要它。

SELECT DISTINCT
key
,first_value(iff(header1 = 'A', date, null)) ignore nulls over (partition by key order by date) as first_date_a
,first_value(iff(header1 = 'B', date, null)) ignore nulls over (partition by key order by date) as first_date_b
,first_value(iff(header2 = 'C', date, null)) ignore nulls over (partition by key order by date) as first_date_c
,first_value(iff(header2 = 'D', date, null)) ignore nulls over (partition by key order by date) as first_date_d
FROM fake_data;

给出：

KEY
xxxx	1	3	1	2

您可以尝试将Row_number窗口函数与聚合条件函数一起使用。

SELECT key,
max(case when header1 = 'A' then date end) first_date_A,
max(case when header1 = 'B' then date end) first_date_B
FROM (
SELECT *,Row_number() over(partition by header1,key order by date) rn
FROM t1
) tt
WHERE rn = 1
GROUP BY key