Athena SQL合并数据集：生成全ID全日期存在状态视图

问题背景 #

• 数据集1（所有ID集合）：

id

123
234
456

• 数据集2（记录各日期存在的ID）：

id   date
  
123  02-04-2023
123  03-04-2023
123  03-05-2023

需求 #

生成包含所有ID与所有日期的全组合的结果集，新增is_present列标记该ID在对应日期是否存在于数据集2，示例如下：

id    date         is_present 

123   02-04-2023      true
123   02-05-2023      false
...

123   03-04-2023      true
123   03-05-2023      true
123   03-06-2023      false  
...
234   <all dates>     false
456   <all dates>     false

现有代码问题 #

原有SQL代码仅生成无日期的false记录，无法实现需求：

CREATE OR REPLACE VIEW "test_view" AS 
SELECT
  t1.id
, (CASE WHEN (t2.id IS NULL) THEN false ELSE true END) is_present
, t2.day
, t2.month
, t2.year
FROM
  (dataset t1
LEFT JOIN dataset t2 ON (t1.id = t2.id))

解决方案 #

要实现全组合，核心是先生成所有ID和所有日期的笛卡尔积，再关联数据集2标记存在状态。

完整SQL代码 #

CREATE OR REPLACE VIEW "test_view" AS
WITH all_dates AS (
    -- 提取数据集2中的所有唯一日期
    SELECT DISTINCT date FROM dataset2
),
all_ids AS (
    -- 提取数据集1中的所有ID
    SELECT id FROM dataset1
),
id_date_cartesian AS (
    -- 生成所有ID与所有日期的笛卡尔积
    SELECT a.id, d.date
    FROM all_ids a
    CROSS JOIN all_dates d
)
SELECT
    ic.id,
    ic.date,
    -- 标记是否存在：关联到数据集2则为true，否则false
    CASE WHEN t2.id IS NOT NULL THEN true ELSE false END AS is_present
FROM id_date_cartesian ic
LEFT JOIN dataset2 t2
    ON ic.id = t2.id AND ic.date = t2.date;

代码说明 #

1. all_dates CTE：从数据集2中提取所有不重复的日期，确保覆盖所有需要的日期维度。
2. all_ids CTE：从数据集1中获取完整的ID列表。
3. id_date_cartesian CTE：通过CROSS JOIN生成ID和日期的全组合，这是实现需求的关键步骤——原有代码缺少这一步，导致无法得到所有日期的组合。
4. 最终关联：将笛卡尔积结果左连接数据集2，通过CASE语句判断该ID+日期组合是否存在，生成is_present标记。

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