求助：基于通知发送日期前后30天的时间范围数据提取

看起来你的核心需求是：关联每条通知的发送日期（created_at），并统计该日期前后30天时间范围内的用户epaper_opens数据，但当前的查询因为几个逻辑问题没达到预期，我来帮你拆解和修正：

原查询的核心问题 #

1. 字段别名冲突：你把CAST(created_at AS date)别名为Date，但关联表analytics.fcts_customer_activity_agg里的日期字段也叫Date（从WHERE条件能看出来），数据库无法区分这两个Date，导致时间过滤逻辑混乱。
2. 左连接被“降级”为内连接：你把时间区间条件放在WHERE子句中，这会过滤掉所有fcts_customer_activity_agg表中没有匹配数据的通知记录——因为左连接后不匹配的行Date字段为NULL，会被WHERE条件直接排除，这可能不是你想要的（你应该想保留所有通知，即使对应用户在30天内没有打开行为）。
3. 聚合维度可能不符合需求：按state, epaper_opens, Date分组，会把相同状态、相同打开数、相同发送日期的通知合并，但你可能更需要按发送日期和状态聚合，统计该窗口内的总打开数。

修正后的查询方案 #

方案1：保留所有通知，关联前后30天的打开数据（按原分组逻辑调整）

SELECT 
  n.state,
  COALESCE(a.epaper_opens, 0) AS epaper_opens,
  CAST(n.created_at AS DATE) AS notification_sent_date,
  COUNT(n.created_at) AS sent_notifications
FROM analytics.fct_notifications n
LEFT JOIN analytics.fcts_customer_activity_agg a
  ON n.customer_id = a.subscription_id
  -- 将时间条件移至JOIN子句，确保左连接保留所有通知
  AND a.`Date` BETWEEN DATE_SUB(CAST(n.created_at AS DATE), INTERVAL 30 DAY)
                    AND DATE_ADD(CAST(n.created_at AS DATE), INTERVAL 30 DAY)
GROUP BY n.state, COALESCE(a.epaper_opens, 0), CAST(n.created_at AS DATE)
ORDER BY notification_sent_date DESC;

方案2：按发送日期聚合，统计30天窗口内的总打开数（更符合常见需求）

如果你想统计每个通知发送日期+状态对应的前后30天总打开数，而不是按单个打开数值分组，用这个版本：

SELECT 
  n.state,
  SUM(COALESCE(a.epaper_opens, 0)) AS total_epaper_opens_in_30d_window,
  CAST(n.created_at AS DATE) AS notification_sent_date,
  COUNT(n.created_at) AS sent_notifications
FROM analytics.fct_notifications n
LEFT JOIN analytics.fcts_customer_activity_agg a
  ON n.customer_id = a.subscription_id
  AND a.`Date` BETWEEN DATE_SUB(CAST(n.created_at AS DATE), INTERVAL 30 DAY)
                    AND DATE_ADD(CAST(n.created_at AS DATE), INTERVAL 30 DAY)
GROUP BY n.state, CAST(n.created_at AS DATE)
ORDER BY notification_sent_date DESC;

关键修改说明 #

• 表别名与字段区分：给两张表加了n和a的别名，明确字段归属，彻底解决Date字段的歧义问题。
• 时间条件移至JOIN子句：确保左连接的特性被保留——即使用户在30天内没有打开行为，通知记录依然会被保留，此时epaper_opens用COALESCE填充为0，避免NULL值影响统计。
• 聚合逻辑优化：方案2调整了聚合方式，更贴合“分析发送日期前后30天区间数据”的核心需求，统计每个日期窗口内的总打开数。

额外注意事项 #

• 如果你的数据库是PostgreSQL，需要把DATE_SUB/DATE_ADD换成PostgreSQL的语法：CAST(n.created_at AS DATE) - INTERVAL '30 days'和CAST(n.created_at AS DATE) + INTERVAL '30 days'。
• 确认created_at字段确实是通知的实际发送日期，而不是通知记录的创建日期，否则时间窗口会计算错误。

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