如何按用户分组获取最新n条邮箱记录并聚合为数组？

错误原因分析 #

你的代码存在几个关键问题：

• 窗口函数分区错误：PARTITION BY user, email会将每个user+email组合单独划分分区，每个分区仅一条记录，导致所有行的row_num都是1，无法筛选出前2个邮箱。
• 无效分组字段：GROUP BY uuid, user中的uuid字段在样本数据中不存在，属于语法错误。
• 冗余聚合函数：窗口函数中使用MAX(session_time)是多余的，未分组的窗口函数可直接用session_time排序。

针对单条邮箱记录的解决方案 #

如果每个user+email组合仅对应一条记录，可直接按user分区，按session_time降序排序列出行号，再筛选前2条聚合：

WITH
  sample_data AS (
  SELECT
    '2023-06-12 10:00:00' AS session_time,
    '1' AS user,
    'example@example.com' AS email
  UNION ALL
  SELECT
    '2023-06-12 11:00:00' AS session_time,
    '2' AS user,
    'example@example.com' AS email
  UNION ALL
  SELECT
    '2023-06-12 12:00:00' AS session_time,
    '3' AS user,
    'example@example.com' AS email
  UNION ALL
  SELECT
    '2023-06-12 13:00:00' AS session_time,
    '3' AS user,
    'example2@example.com' AS email
  UNION ALL
  SELECT
    '2023-06-12 14:00:00' AS session_time,
    '3' AS user,
    'example3@example.com' AS email
),
ranked_emails AS (
  SELECT
    user,
    email,
    -- 按用户分区，会话时间降序分配行号
    ROW_NUMBER() OVER(PARTITION BY user ORDER BY session_time DESC) AS row_num
  FROM sample_data
)
SELECT
  user,
  ARRAY_AGG(email ORDER BY row_num) AS emails
FROM ranked_emails
WHERE row_num <= 2
GROUP BY user;

执行后，用户3会得到['example3@example.com', 'example2@example.com']两个最新邮箱，符合预期。

针对重复邮箱记录的解决方案 #

如果同一个用户的同一个邮箱存在多条会话记录，需先保留每个邮箱的最新会话时间，再排序取前2个：

WITH
  sample_data AS (
  SELECT
    '2023-06-12 10:00:00' AS session_time,
    '1' AS user,
    'example@example.com' AS email
  UNION ALL
  SELECT
    '2023-06-12 11:00:00' AS session_time,
    '2' AS user,
    'example@example.com' AS email
  UNION ALL
  SELECT
    '2023-06-12 12:00:00' AS session_time,
    '3' AS user,
    'example@example.com' AS email
  UNION ALL
  SELECT
    '2023-06-12 13:00:00' AS session_time,
    '3' AS user,
    'example@example.com' AS email
  UNION ALL
  SELECT
    '2023-06-12 14:00:00' AS session_time,
    '3' AS user,
    'example2@example.com' AS email
  UNION ALL
  SELECT
    '2023-06-12 15:00:00' AS session_time,
    '3' AS user,
    'example3@example.com' AS email
),
latest_email_per_user AS (
  SELECT
    user,
    email,
    MAX(session_time) AS latest_session_time
  FROM sample_data
  GROUP BY user, email
),
ranked_emails AS (
  SELECT
    user,
    email,
    ROW_NUMBER() OVER(PARTITION BY user ORDER BY latest_session_time DESC) AS row_num
  FROM latest_email_per_user
)
SELECT
  user,
  ARRAY_AGG(email ORDER BY row_num) AS emails
FROM ranked_emails
WHERE row_num <= 2
GROUP BY user;

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