使用BigQuery计算2020-2021年薪资变动百分比遇问题求助

问题分析与修正方案 #

1. 字段重复问题

原查询使用SELECT *会将两个CTE表的所有字段都返回，即使USING指定的关联字段不会重复，Salary这类非关联字段会因为在两个表中都存在而重复显示。

2. 缺失2020年数据问题

原查询虽然关联了2020年的表，但没有显式选择该表的薪资字段，且FROM子句从2021年表开始，导致结果中只包含2021年的字段值。

修正后的查询语句 #

WITH a_2020 AS (
    SELECT Emp_ID, Address, Name, State, Salary AS salary_2020
    FROM `xx.salary.Sal_2020_2021` 
    WHERE Year = 2020
),
b_2021 AS (
    SELECT Emp_ID, Address, Name, State, Salary AS salary_2021
    FROM `xx.salary.Sal_2020_2021` 
    WHERE Year = 2021
)
SELECT 
    Emp_ID,
    Address,
    Name,
    State,
    salary_2020,
    salary_2021,
    ROUND(((salary_2020 - salary_2021)/salary_2020)*100, 2) AS salary_change_percent
FROM a_2020
JOIN b_2021 USING (Emp_ID, Address, Name, State)

关键优化点 #

• 明确指定需要返回的字段，避免SELECT *带来的冗余重复字段，同时给两年的薪资分别命名为salary_2020和salary_2021，清晰区分
• 显式列出salary_2020和salary_2021字段，确保两年的薪资数据都能在结果中显示
• 使用ROUND函数将百分比结果保留两位小数，提升可读性
• 调整JOIN的起始表为a_2020（内连接顺序不影响结果，仅为逻辑更清晰）

如果需要处理仅在某一年存在记录的员工（比如新入职或离职），可以将JOIN替换为FULL OUTER JOIN，并通过IFNULL函数处理NULL值。

内容的提问来源于stack exchange，提问作者mp.kaur