sqlite - 如何 MELT SQL 表？

Question

我有下面的 SQL 表，我们称之为teams_score，它显示了一场比赛的 2 名球员的球队及其得分。每次他们以 Player1 或 Player2 出现在团队中时，我都需要观察每个球员的得分。我认为这就像融化。

Player1,         Player2,          TeamScore
"Johnny Onspot", "Pat Magroin",     95
"Pepe Roni",     "Cole Kutz",       78
"Ben Dover",     "Ibin Yerkinoff",  76
"Johnny Onspot", "Frumunda Mabalz", 69
"Sal Lami",      "Cole Kutz",       65
"Pat Magroin",   "Frumunda Mabalz", 63

我想找出平均得分最高的前 3 名选手。

例如，“Pat Magroin”出现在 2 支队伍中，得分分别为 95 和 63，平均得分为 79

所以我需要如下所示显示表格“player_score”，然后按平均得分汇总。如何获得下面显示的表格 player_score？

Player,            Score
"Ben Dover",       76
"Cole Kutz",       78
"Cole Kutz",       65
"Frumunda Mabalz", 69
"Frumunda Mabalz", 63
"Ibin Yerkinoff",  76
"Johnny Onspot",   95
"Johnny Onspot",   69
"Pat Magroin",     95
"Pat Magroin",     63
"Pepe Roni",       78
"Sal Lami",        65

获得 player_score 后，我应该能够运行查询(如下所示)以获取表 result_table，如下所示。

SELECT Player, AVG(Score) AS Avg_Score FROM player_score
GROUP BY Player
ORDER BY Avg_Score DESC
LIMIT 3;

Player,            Avg_Score
"Johnny Onspot",   82
"Pat Magroin",     79
"Pepe Roni",       78

Answer 1

SELECT *
FROM (
    SELECT Player1 AS Player, TeamScore as Score
    FROM teams_score

    UNION ALL

    SELECT Player2 AS Player, TeamScore as Score
    FROM teams_score
)

应该给

Player                  Score
"Johnny Onspot",        95
"Pat Magroin",          95
"Pepe Roni",            78
"Cole Kutz",            78
"Ben Dover",            76
"Ibin Yerkinoff",       76
"Johnny Onspot",        69
"Frumunda Mabalz",      69
"Sal Lami",             65
"Cole Kutz",            65
"Pat Magroin",          63
"Frumunda Mabalz",      63

注意使用 UNION ALL(而不是仅仅使用 UNION)来确保包含重复行。这样可以正确计算平均值。

从这里开始，你应该可以做你想做的事了

WITH
    ...above code
AS player_score
SELECT Player, AVG(Score) AS Avg_Score
FROM player_score
GROUP BY Player
ORDER BY Avg_Score DESC
LIMIT 3;