BigQuery技术问询：如何检查指定日期员工工时表数据是否缺失

原查询的核心问题是未生成日期与员工的全量组合，仅返回了有工时表记录的日期-员工对，同时使用ROW_NUMBER()的逻辑不符合计数需求。以下是修正方案：

修正后的SQL查询 #

方案1：从工时表提取唯一员工（适用于无单独员工表场景） #

SELECT
    D.CALENDAR_DATE,
    EMP.EMPLOYEE,
    COUNT(E.TIMESHEET_DATE) AS NUMBER_OF_TIMESHEETS
FROM
    DIM.CALENDAR D
-- 生成日期与所有员工的全量组合
CROSS JOIN (
    SELECT DISTINCT EMPLOYEE
    FROM EMPLOYEE_TIMESHEETS
) EMP
-- 左连工时表，匹配对应员工的当日记录
LEFT JOIN EMPLOYEE_TIMESHEETS E
    ON E.TIMESHEET_DATE = D.CALENDAR_DATE
    AND E.EMPLOYEE = EMP.EMPLOYEE
WHERE
    D.CALENDAR_DATE BETWEEN '2023-04-01' AND '2023-07-19'
GROUP BY
    D.CALENDAR_DATE,
    EMP.EMPLOYEE
ORDER BY
    EMP.EMPLOYEE,
    D.CALENDAR_DATE ASC;

方案2：使用单独员工表（推荐，覆盖未提交过工时的员工） #

若存在员工维度表（如DIM.EMPLOYEE），优先用该表获取全量员工：

SELECT
    D.CALENDAR_DATE,
    EMP.EMPLOYEE_NAME AS EMPLOYEE,
    COUNT(E.TIMESHEET_DATE) AS NUMBER_OF_TIMESHEETS
FROM
    DIM.CALENDAR D
CROSS JOIN DIM.EMPLOYEE EMP
LEFT JOIN EMPLOYEE_TIMESHEETS E
    ON E.TIMESHEET_DATE = D.CALENDAR_DATE
    AND E.EMPLOYEE_ID = EMP.EMPLOYEE_ID -- 按实际关联字段调整
WHERE
    D.CALENDAR_DATE BETWEEN '2023-04-01' AND '2023-07-19'
GROUP BY
    D.CALENDAR_DATE,
    EMP.EMPLOYEE_NAME,
    EMP.EMPLOYEE_ID
ORDER BY
    EMP.EMPLOYEE_NAME,
    D.CALENDAR_DATE ASC;

关键修正点 #

1. 全量组合生成：通过CROSS JOIN生成指定日期区间内每个日期与每个员工的所有组合，确保无遗漏。
2. 精准关联：左连工时表时同时匹配日期和员工，避免跨员工的记录干扰。
3. 正确计数：用COUNT(E.TIMESHEET_DATE)统计匹配到的工时记录数，无记录时返回0，有记录时返回1（符合工时表仅插入一行的规则）。
4. 原查询问题：原左连仅关联日期未关联员工，无法生成全量组合；ROW_NUMBER()是分区内的行编号逻辑，不适合用于记录计数。

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