SQL按Name分组统计匹配计数生成YES/NO标识问题求解

• 运行环境：GCP平台SQL开发，现有Users表，包含Name、B、C、same、different字段，样例数据共3行：
  • Arun：B值为1234-5678、C值为1234、same=1、different=0
  • Tara：B值为6789 - 7654、C值为6789、same=1、different=0
  • Arun：B值为4567、C值为4324、same=0、different=1
• 基础比对规则：提取B列以-分隔的首段值（需处理分隔符前后空格）与C列比较，相等则same赋值1、different赋值0，不等则different赋值1、same赋值0
• 统计需求：按Name维度统计每个用户的same、different总计数，计数满足阈值（样例阈值为≥1）时，YES列赋值1、NO列赋值1，否则赋值0。预期输出包含Name、B、C、count(same)、count(different)、yes、no七个字段，结果为：
  • Arun：count(same)=1、count(different)=1、yes=1、no=1
  • Tara：count(same)=1、count(different)=0、yes=1、no=0
• 原有编写SQL的核心问题：
  1. 函数不兼容：GCP BigQuery不支持MySQL专属的SUBSTRING_INDEX函数
  2. 语法错误：IF函数括号位置错位，判断阈值>1和样例规则不匹配
  3. 分组逻辑错误：将B、C加入GROUP BY会导致按单行明细分组，无法得到用户维度的汇总值
  4. 格式兼容问题：未处理B字段中-前后存在空格的场景，会导致值匹配失败

适配GCP BigQuery语法，使用窗口函数保留明细行同时展示用户维度聚合结果：

SELECT
    Name,
    B,
    C,
    -- 行级比对逻辑，处理分隔符前后空格、类型不一致问题
    CASE WHEN TRIM(SPLIT(B, '-')[OFFSET(0)]) = CAST(C AS STRING) THEN 1 ELSE 0 END AS same,
    CASE WHEN TRIM(SPLIT(B, '-')[OFFSET(0)]) != CAST(C AS STRING) THEN 1 ELSE 0 END AS different,
    -- 按Name维度统计总计数，窗口函数保留行级明细
    SUM(CASE WHEN TRIM(SPLIT(B, '-')[OFFSET(0)]) = CAST(C AS STRING) THEN 1 ELSE 0 END) OVER (PARTITION BY Name) AS `count(same)`,
    SUM(CASE WHEN TRIM(SPLIT(B, '-')[OFFSET(0)]) != CAST(C AS STRING) THEN 1 ELSE 0 END) OVER (PARTITION BY Name) AS `count(different)`,
    -- 阈值判断，样例规则为计数≥1即标记为1
    IF(SUM(CASE WHEN TRIM(SPLIT(B, '-')[OFFSET(0)]) = CAST(C AS STRING) THEN 1 ELSE 0 END) OVER (PARTITION BY Name) >= 1, 1, 0) AS yes,
    IF(SUM(CASE WHEN TRIM(SPLIT(B, '-')[OFFSET(0)]) != CAST(C AS STRING) THEN 1 ELSE 0 END) OVER (PARTITION BY Name) >= 1, 1, 0) AS no
FROM
    Users

• 无-分隔符的B字段会直接取整值参与比对，兼容4567这类无分隔符的格式
• 窗口函数按Name分区计算聚合值，不需要把明细字段加入分组，既保留每行B、C的原始值，又能展示对应Name维度的统计结果
• 增加TRIM和类型转换逻辑，覆盖分隔符带空格、字段类型不匹配的边界场景
• 运行后Arun的两条记录都会展示count(same)=1、count(different)=1、yes=1、no=1，Tara的记录展示count(same)=1、count(different)=0、yes=1、no=0，完全匹配预期结果。

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