如何将指定SQL查询转换为Google Sheets单公式实现多值统计

我有一个包含三列的表格：player_a、player_b、result（对应Google Sheets的A、B、C列，数据从A3开始），其中player_a和player_b是锦标赛玩家的标准化字符串，result的值为'W'或'L'。想要生成包含player_a、player_b、num wins、num losses、winrate的汇总表。对应的SQL查询如下：

SELECT 
  player_a, 
  player_b, 
  num_wins, num_loss, 
  (num_wins*100/(num_wins+num_loss)) as winrate
FROM (
SELECT 
  player_a, 
  player_b, 
  count(case when result = 'W' THEN 1 END) as num_wins, 
  count(case when result = 'L' THEN 1 END) as num_loss
 FROM `scores` 
 GROUP BY player_a, player_b) as grouped_scores;

尝试用Google Sheets的QUERY函数时，发现它不支持COUNT(CASE...)语法，目前通过拆分步骤实现，想找单个公式/查询完成所有计算的方法。

解决方案 #

方法1：用LET+BYROW+COUNTIFS实现一键生成 #

通过LET定义变量简化结构，先获取所有玩家组合，再批量计算胜场、负场和胜率：

=LET(
    grouped, QUERY(Sheet1!A3:C, "SELECT A, B GROUP BY A, B", 0),
    playerA, INDEX(grouped,,1),
    playerB, INDEX(grouped,,2),
    numWins, BYROW(playerA&"|"&playerB, LAMBDA(x, COUNTIFS(Sheet1!A3:A, INDEX(SPLIT(x,"|"),1), Sheet1!B3:B, INDEX(SPLIT(x,"|"),2), Sheet1!C3:C, "W"))),
    numLosses, BYROW(playerA&"|"&playerB, LAMBDA(x, COUNTIFS(Sheet1!A3:A, INDEX(SPLIT(x,"|"),1), Sheet1!B3:B, INDEX(SPLIT(x,"|"),2), Sheet1!C3:C, "L"))),
    winRate, IFERROR(numWins/(numWins+numLosses)*100, 0),
    VSTACK({"player_a","player_b","num wins","num losses","winrate"}, HSTACK(grouped, numWins, numLosses, winRate))
)

说明：

• LET函数统一管理变量，避免重复引用数据
• BYROW遍历每个玩家组合，用COUNTIFS精准统计对应胜/负场数
• VSTACK和HSTACK合并表头与数据列，直接生成完整汇总表

方法2：用QUERY的PIVOT功能实现分组统计 #

利用PIVOT将result的'W'/'L'转成列，自动统计数量后补全胜率：

=LET(
    pivotData, QUERY(Sheet1!A3:C, "SELECT A, B, COUNT(C) WHERE C IS NOT NULL GROUP BY A, B PIVOT C", 0),
    playerA, INDEX(pivotData,,1),
    playerB, INDEX(pivotData,,2),
    numWins, IFERROR(INDEX(pivotData,,3), 0),
    numLosses, IFERROR(INDEX(pivotData,,4), 0),
    winRate, IFERROR(numWins/(numWins+numLosses)*100, 0),
    VSTACK({"player_a","player_b","num wins","num losses","winrate"}, HSTACK(playerA, playerB, numWins, numLosses, winRate))
)

说明：

• PIVOT C会自动将'W'和'L'转为两列，统计每组的对应次数
• IFERROR处理无胜场/负场的情况，默认填充0
• 最后合并所有列并添加标准表头

方法3：兼容旧版本的SUMPRODUCT实现 #

如果你的Google Sheets版本不支持BYROW，可以用SUMPRODUCT的数组形式替代：

=LET(
    baseData, QUERY({
        Sheet1!A3:B,
        ARRAYFORMULA(SUMPRODUCT((Sheet1!A3:A=TRANSPOSE(Sheet1!A3:A))*(Sheet1!B3:B=TRANSPOSE(Sheet1!B3:B))*(Sheet1!C3:C="W"))),
        ARRAYFORMULA(SUMPRODUCT((Sheet1!A3:A=TRANSPOSE(Sheet1!A3:A))*(Sheet1!B3:B=TRANSPOSE(Sheet1!B3:B))*(Sheet1!C3:C="L")))
    }, "SELECT Col1, Col2, MAX(Col3), MAX(Col4) GROUP BY Col1, Col2 LABEL MAX(Col3) 'num wins', MAX(Col4) 'num losses'", 0),
    winRate, IFERROR(INDEX(baseData,,3)/(INDEX(baseData,,3)+INDEX(baseData,,4))*100, 0),
    VSTACK({"player_a","player_b","num wins","num losses","winrate"}, HSTACK(baseData, winRate))
)

说明：

• 用TRANSPOSE配合SUMPRODUCT实现数组层面的条件统计
• 再通过QUERY分组去重，得到唯一玩家组合的胜/负场数
• 最后计算并合并胜率列

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