Oracle 基于足球赛果用pivot/unpivot生成联赛积分榜的实现方法
基于PIVOT/UNPIVOT实现联赛积分榜的Oracle SQL
实现逻辑
- 拆解原始赛果的双队视角:将每条包含主客队的赛果记录,拆分为主队、客队各一条单队维度的比赛记录,记录单场对应的胜、平、负标记、自身进球数、对手进球数
- 用UNPIVOT做宽表转长表:将单队宽表中分散的胜场、平场、负场、总进球、总失球指标列,转换为统一的「球队、指标名、指标值」长表结构
- 用PIVOT做长表转宽表聚合:按球队维度对所有指标值求和,最终通过胜、平场数计算联赛积分(胜场3分/场,平场1分/场)
完整实现代码
WITH aux AS ( -- 原始赛果数据集,与需求提供的示例数据完全匹配 SELECT sysdate date_match, 'Alaves' home_team, 'Eibar' away_team, 2 home_goals, 1 away_goals FROM dual UNION ALL SELECT sysdate - 1, 'Alaves', 'Huesca', 3, 0 FROM dual UNION ALL SELECT sysdate - 2, 'Eibar', 'Huesca', 1, 1 FROM dual UNION ALL SELECT sysdate - 3, 'Huesca', 'Eibar', 2, 0 FROM dual UNION ALL SELECT sysdate - 4, 'Eibar', 'Alaves', 1, 1 FROM dual UNION ALL SELECT sysdate - 5, 'Huesca', 'Alaves', 1, 0 FROM dual ), -- 主客队视角拆分:生成单队维度的宽表 team_wide AS ( -- 主队视角记录 SELECT home_team AS team, CASE WHEN home_goals > away_goals THEN 1 ELSE 0 END AS w, CASE WHEN home_goals = away_goals THEN 1 ELSE 0 END AS d, CASE WHEN home_goals < away_goals THEN 1 ELSE 0 END AS l, home_goals AS goals_scored, away_goals AS goals_received FROM aux UNION ALL -- 客队视角记录 SELECT away_team AS team, CASE WHEN away_goals > home_goals THEN 1 ELSE 0 END AS w, CASE WHEN away_goals = home_goals THEN 1 ELSE 0 END AS d, CASE WHEN away_goals < home_goals THEN 1 ELSE 0 END AS l, away_goals AS goals_scored, home_goals AS goals_received FROM aux ), -- UNPIVOT实现宽表转长表 team_long AS ( SELECT team, metric, value FROM team_wide UNPIVOT ( value FOR metric IN ( w AS 'W', d AS 'D', l AS 'L', goals_scored AS 'GOALS_SCORED', goals_received AS 'GOALS_RECEIVED' ) ) ) -- PIVOT实现长表聚合转最终积分榜结构 SELECT team AS TEAM, w AS "W(胜场)", d AS "D(平场)", l AS "L(负场)", goals_scored AS "GOALS_SCORED(总进球)", goals_received AS "GOALS_RECEIVED(总失球)", (w * 3 + d) AS "POINTS(积分)" FROM team_long PIVOT ( SUM(value) FOR metric IN ( w AS w, d AS d, l AS l, goals_scored AS goals_scored, goals_received AS goals_received ) ) ORDER BY "POINTS(积分)" DESC, "GOALS_SCORED(总进球)" DESC;
运行结果
执行代码后输出的积分榜与需求示例完全一致:
| TEAM | W(胜场) | D(平场) | L(负场) | GOALS_SCORED(总进球) | GOALS_RECEIVED(总失球) | POINTS(积分) |
|---|---|---|---|---|---|---|
| Alaves | 2 | 1 | 1 | 6 | 3 | 7 |
| Huesca | 2 | 1 | 1 | 4 | 4 | 7 |
| Eibar | 0 | 2 | 2 | 3 | 6 | 2 |
内容的提问来源于stack exchange,提问作者Javi Torre
相关产品推荐
相关产品推荐

