如何用BigQuery SQL查询用户未登录的缺失日期

我有一个用于记录用户登录信息的BigQuery表，表结构及数据如下：

需要编写SQL查询，获取**指定日期范围（如2019-04-05至2019-04-08）**内未登录的用户，并列出他们未登录的日期，期望输出格式如下：

[
  {
    "user_id": 1,
    "user_name": "john doe",
    "dates_not_signed_in": ["2019-04-07", "2019-04-08"]
  },
  {
    "user_id": 2,
    "user_name": "bob bobson",
    "dates_not_signed_in": ["2019-04-06", "2019-04-07"]
  },
  {
    "user_id": 4,
    "user_name": "amy face",
    "dates_not_signed_in": ["2019-04-05", "2019-04-06", "2019-04-07", "2019-04-08"]
  }
]

我知道可以通过SELECT user_id FROM table获取所有用户ID，但不清楚如何扩展该查询以实现上述需求。

核心思路是生成指定日期范围内的所有日期，和所有用户做交叉连接，筛选出用户未登录的日期后按用户聚合，以下是适配BigQuery的完整SQL：

WITH date_range AS (
  -- 生成目标日期范围内的所有日期
  SELECT date
  FROM UNNEST(GENERATE_DATE_ARRAY('2019-04-05', '2019-04-08', INTERVAL 1 DAY)) AS date
),
distinct_users AS (
  -- 获取表中所有唯一用户的ID和姓名
  SELECT DISTINCT user_id, user_name
  FROM your_table_name -- 替换为你的实际表名
),
user_dates AS (
  -- 交叉连接用户与日期范围，得到每个用户在目标区间内的所有日期组合
  SELECT du.user_id, du.user_name, dr.date
  FROM distinct_users du
  CROSS JOIN date_range dr
),
missing_dates AS (
  -- 左连接登录表，筛选出用户未登录的日期（登录记录为空的行）
  SELECT ud.user_id, ud.user_name, ud.date
  FROM user_dates ud
  LEFT JOIN your_table_name t
    ON ud.user_id = t.user_id
    AND ud.date = t.user_sign_in_date
  WHERE t.user_sign_in_date IS NULL
)
-- 按用户聚合，将未登录日期整理为有序数组，并输出JSON格式结果
SELECT 
  FORMAT_JSON_ARRAY(
    TO_JSON_STRING(
      STRUCT(user_id, user_name, ARRAY_AGG(date ORDER BY date) AS dates_not_signed_in)
    )
  ) AS result
FROM missing_dates
GROUP BY user_id, user_name
-- 仅保留存在未登录日期的用户（若需包含全勤用户可删除此条件）
HAVING ARRAY_LENGTH(ARRAY_AGG(date)) > 0

关键步骤说明 #

1. date_range：利用BigQuery的GENERATE_DATE_ARRAY函数快速生成指定区间内的所有日期，无需手动枚举。
2. distinct_users：去重获取唯一用户列表，避免重复处理同一用户的多条登录记录。
3. user_dates：交叉连接用户与日期范围，构建出每个用户在目标区间内的所有日期基准数据。
4. missing_dates：通过左连接原登录表，筛选出没有对应登录记录的日期，即为用户未登录的日期。
5. 最终聚合：用ARRAY_AGG将未登录日期整理为有序数组，再通过TO_JSON_STRING和FORMAT_JSON_ARRAY生成符合需求的JSON输出。

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