SQL 中的透视问题

Question

我无法解决 SQL 中枢轴函数的问题。我想保留 uid 列，但结果中每个 uid 只有一行 + sc_2015、sc_2016、sc_2017 等列(在原始表中，每月每年有一行 uid 行 + 分数列。

SELECT * FROM 
(
SELECT DISTINCT
t1.uid,
t2.score_year AS yr,
--averaging scores across 12-months per year per uid:
CAST(AVG(t2.score) OVER (PARTITION BY t1.uid,t2.score_year) AS DECIMAL(4,3)) AS avg_annual_score
FROM
table1 t1
INNER JOIN table2 t2
ON t1.uid
=
t2.uid
WHERE t2.score_year IN (
'2015',
'2016',
'2017',
'2018',
'2019')
)
PIVOT (
MAX(avg_annual_score) sc FOR yr IN (
'2015',
'2016',
'2017',
'2018',
'2019')
);

Answer 1

我建议放弃PIVOT运算符，而是在这里只使用老式的透视查询：

SELECT
t1.uid,
AVG(CASE WHEN t2.score_year = 2015 THEN t2.score END) AS avg_score_2015,
AVG(CASE WHEN t2.score_year = 2016 THEN t2.score END) AS avg_score_2016
AVG(CASE WHEN t2.score_year = 2017 THEN t2.score END) AS avg_score_2017,
AVG(CASE WHEN t2.score_year = 2018 THEN t2.score END) AS avg_score_2018
FROM table1 t1
LEFT JOIN table2 t2
ON t1.uid = t2.uid
GROUP BY
t1.uid;

我建议不使用PIVOT的原因是，在许多情况下，上述版本实际上会表现得更好。 恕我直言，代码更容易阅读和维护。

编辑：

可能写入PIVOT版本如下所示：

SELECT uid, [2015], [2016], [2017], [2018]
FROM
(
SELECT t1.uid, t2.score, t2.score_year
FROM table1 t1
LEFT JOIN table2 t2
ON t1.uid = t2.uid
WHERE t2.score_year BETWEEN 2015 AND 2018
) AS source
PIVOT
(
AVG(score)
FOR score_year IN ([2015], [2016], [2017], [2018])
) AS pivotal;