如何用SQL实现基于赛事结果序列的交叉统计矩阵？

Hey there! 作为SQL新手能想到用历史比赛序列来做交叉统计分析，这思路已经很到位啦！你提到用CASE语句统计交叉矩阵没达到预期，确实，CASE更适合行内的条件判断，而要生成行转列的交叉统计矩阵，Pivot（或者不同数据库里的类似功能）才是更合适的工具。咱们一步步来解决这个问题：

第一步：先确认基础数据统计 #

假设你已经有了包含HomeTeamSequence（主队最近两场结果）、AwayTeamSequence（客队最近两场结果）和FTR（比赛结果）的数据集，首先可以先通过分组查询拿到每个序列组合下主队获胜（FTR='H'）的次数：

SELECT
  HomeTeamSequence,
  AwayTeamSequence,
  COUNT(*) AS HomeWinCount
FROM your_match_table
WHERE FTR = 'H'
GROUP BY HomeTeamSequence, AwayTeamSequence
ORDER BY HomeTeamSequence, AwayTeamSequence;

这个查询会输出类似这样的结果（比如序列是H=胜、D=平、L=负的组合）：

但这是行式的统计，如果你想要把AwayTeamSequence转成列（形成交叉矩阵），就需要用Pivot了。

第二步：用Pivot实现交叉矩阵 #

不同数据库的Pivot语法略有差异，下面给你两种最常见的实现方式：

1. SQL Server 版本 #

直接用内置的PIVOT函数，需要提前列出所有可能的AwayTeamSequence值（比如两场结果的组合共9种：HH、HD、HL、DH、DD、DL、LH、LD、LL）：

SELECT
  HomeTeamSequence,
  -- 用COALESCE把NULL替换成0，避免空值显示
  COALESCE([HH], 0) AS [Away_HH],
  COALESCE([HD], 0) AS [Away_HD],
  COALESCE([HL], 0) AS [Away_HL],
  COALESCE([DH], 0) AS [Away_DH],
  COALESCE([DD], 0) AS [Away_DD],
  COALESCE([DL], 0) AS [Away_DL],
  COALESCE([LH], 0) AS [Away_LH],
  COALESCE([LD], 0) AS [Away_LD],
  COALESCE([LL], 0) AS [Away_LL]
FROM (
  -- 子查询先拿到分组统计结果
  SELECT
    HomeTeamSequence,
    AwayTeamSequence,
    COUNT(*) AS HomeWinCount
  FROM your_match_table
  WHERE FTR = 'H'
  GROUP BY HomeTeamSequence, AwayTeamSequence
) AS SourceData
-- 对AwayTeamSequence进行行转列
PIVOT (
  SUM(HomeWinCount)  -- 这里用SUM/MAX都可以，因为每个组合只有一个统计值
  FOR AwayTeamSequence IN ([HH], [HD], [HL], [DH], [DD], [DL], [LH], [LD], [LL])
) AS PivotMatrix;

执行后会生成一个二维矩阵，行是主队序列，列是客队序列，单元格值是对应组合下主队获胜的次数。

2. PostgreSQL 版本 #

PostgreSQL没有内置Pivot函数，需要借助tablefunc扩展的crosstab功能：

-- 先确保启用tablefunc扩展（只需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  -- 第一个参数：基础统计查询
  'SELECT HomeTeamSequence, AwayTeamSequence, COUNT(*)
   FROM your_match_table
   WHERE FTR = ''H''
   GROUP BY HomeTeamSequence, AwayTeamSequence
   ORDER BY 1, 2',
  -- 第二个参数：定义要转成列的所有客队序列值
  'SELECT DISTINCT AwayTeamSequence FROM your_match_table ORDER BY 1'
) AS ct (
  -- 定义结果表的列结构
  HomeTeamSequence text,
  "Away_HH" int,
  "Away_HD" int,
  "Away_HL" int,
  "Away_DH" int,
  "Away_DD" int,
  "Away_DL" int,
  "Away_LH" int,
  "Away_LD" int,
  "Away_LL" int
);

第三步：动态生成Pivot列（可选） #

如果你的序列组合很多，手动列出来太麻烦，可以用动态SQL自动生成所有列：

SQL Server 动态SQL示例 #

DECLARE @cols NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

-- 自动获取所有不同的客队序列值，并转成带引号的格式
SELECT @cols = STRING_AGG(QUOTENAME(AwayTeamSequence), ', ')
FROM (SELECT DISTINCT AwayTeamSequence FROM your_match_table) AS SeqValues;

-- 动态构建Pivot查询语句
SET @query = N'
SELECT
  HomeTeamSequence,
  ' + STRING_AGG('COALESCE(' + QUOTENAME(AwayTeamSequence) + ', 0) AS ' + QUOTENAME('Away_' + AwayTeamSequence), ', ') + '
FROM (
  SELECT
    HomeTeamSequence,
    AwayTeamSequence,
    COUNT(*) AS HomeWinCount
  FROM your_match_table
  WHERE FTR = ''H''
  GROUP BY HomeTeamSequence, AwayTeamSequence
) AS SourceData
PIVOT (
  SUM(HomeWinCount)
  FOR AwayTeamSequence IN (' + @cols + ')
) AS PivotMatrix;';

-- 执行动态查询
EXEC sp_executesql @query;

为什么CASE语句没达到预期？ #

你之前尝试的CASE语句大概是这样的逻辑：

SELECT
  HomeTeamSequence,
  CASE WHEN AwayTeamSequence = 'HH' THEN COUNT(*) ELSE 0 END AS HH
FROM your_match_table
WHERE FTR = 'H'
GROUP BY HomeTeamSequence;

这种写法的问题在于，GROUP BY只按HomeTeamSequence分组，CASE里的条件是行级判断，没法同时统计多个AwayTeamSequence的组合，最终只能得到单个列的统计，没法形成完整的交叉矩阵——而Pivot正是专门解决这种行转列场景的工具。

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