如何编写SQL查询按日期取每个pid最近3次发送记录并统计点击量

你可以借助窗口函数对分组内的发送记录排序后过滤统计，通用写法如下（适配MySQL8.0+、PostgreSQL、SQL Server、Hive等支持窗口函数的主流数据库）：

-- 生成新表"Last 3 sends_clickCount"
CREATE TABLE `Last 3 sends_clickCount` AS
SELECT
  email,
  pid,
  NULLIF(COUNT(click_date), 0) AS clickCount
FROM (
  -- 对每个email+pid组合的发送记录按发送时间倒序排名
  SELECT
    email,
    pid,
    click_date,
    ROW_NUMBER() OVER (PARTITION BY email, pid ORDER BY sent_date DESC) AS rn
  FROM 你的原表名 -- 替换为实际的邮件发送数据表名
) t
WHERE rn <= 3 -- 仅保留每个分组最新的3条记录
GROUP BY email, pid;

逻辑说明 #

• 内层子查询通过ROW_NUMBER()窗口函数，按email和pid分组，每组内按sent_date倒序排序生成排名，确保最新的发送记录排名靠前
• 外层过滤仅保留每组排名前3的记录，用COUNT(click_date)统计非空点击数，该函数会自动忽略click_date为null的记录，不需要额外判断
• NULLIF(COUNT(click_date), 0)用来把统计结果为0的情况转换为null，匹配你给出的预期输出要求
• 如果同一用户同一产品存在多条相同sent_date的发送记录，且你需要保留所有并列的最新记录，可以把ROW_NUMBER()替换为RANK()或者DENSE_RANK()

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