使用SQL FULL JOIN合并日托考勤数据时出现冗余列问题

问题根源 #

你用FULL JOIN并选择*时，两个表都包含Student_Id、First_Name、Last_Name列，BigQuery会自动给右表的重复列加上_1后缀，导致冗余；同时SELECT *会先输出左表所有列（9月考勤+用户信息），再输出右表所有列（用户信息+10月考勤），所以9月最后一列后会先出现重复的用户列，再显示10月考勤。另外，用First_Name作为连接键风险高，同名学生会导致匹配错误。

解决方案1：明确指定列并合并用户信息 #

用Student_Id作为唯一连接键，通过COALESCE合并两边的用户信息（避免某一侧无数据时出现空值），再按日期顺序列出考勤列：

SELECT
  -- 合并用户信息，优先取9月数据，无则用10月的
  COALESCE(Sep.Student_Id, Oct.Student_Id) AS Student_Id,
  COALESCE(Sep.First_Name, Oct.First_Name) AS First_Name,
  COALESCE(Sep.Last_Name, Oct.Last_Name) AS Last_Name,
  -- 按顺序列出9月所有考勤列
  Sep.`09/01/2022`,
  Sep.`09/02/2022`,
  -- ... 省略中间9月日期列
  Sep.`09/30/2022`,
  -- 按顺序列出10月所有考勤列
  Oct.`10/01/2022`,
  Oct.`10/02/2022`,
  -- ... 省略中间10月日期列
  Oct.`10/31/2022`
FROM
  `ready-set-grow-analysis.RSG_Analysis.Sep_Att` AS Sep
FULL JOIN
  `ready-set-grow-analysis.RSG_Analysis.Oct_Att` AS Oct
ON
  Sep.Student_Id = Oct.Student_Id -- 用唯一标识Student_Id连接，避免同名错误

解决方案2：用UNPIVOT+PIVOT灵活合并（适合多月份扩展） #

先把宽表转成窄表（每行对应一个学生的单日考勤），合并后再转回宽表，自动按日期顺序排列：

WITH combined_attendance AS (
  -- 转换9月数据为窄表格式
  SELECT
    Student_Id,
    First_Name,
    Last_Name,
    DATE(PARSE_DATE('%m/%d/%Y', date_col)) AS attendance_date,
    attendance
  FROM
    `ready-set-grow-analysis.RSG_Analysis.Sep_Att`
  UNPIVOT (
    attendance FOR date_col IN (
      `09/01/2022`,
      `09/02/2022`,
      -- ... 省略其他9月日期列
      `09/30/2022`
    )
  )
  UNION ALL
  -- 转换10月数据为窄表格式并合并
  SELECT
    Student_Id,
    First_Name,
    Last_Name,
    DATE(PARSE_DATE('%m/%d/%Y', date_col)) AS attendance_date,
    attendance
  FROM
    `ready-set-grow-analysis.RSG_Analysis.Oct_Att`
  UNPIVOT (
    attendance FOR date_col IN (
      `10/01/2022`,
      `10/02/2022`,
      -- ... 省略其他10月日期列
      `10/31/2022`
    )
  )
)
-- 转回宽表，按日期顺序排列列
SELECT
  Student_Id,
  First_Name,
  Last_Name,
  `2022-09-01`, `2022-09-02`, ..., `2022-09-30`,
  `2022-10-01`, `2022-10-02`, ..., `2022-10-31`
FROM
  combined_attendance
PIVOT (
  MAX(attendance) FOR attendance_date IN (
    DATE('2022-09-01'), DATE('2022-09-02'), ..., DATE('2022-09-30'),
    DATE('2022-10-01'), DATE('2022-10-02'), ..., DATE('2022-10-31')
  )
)

这种方法后续加新月份时只需扩展UNPIVOT部分，无需调整整体结构，列顺序会自动按日期从9月到10月平滑过渡。

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