RedShift/PostgreSQL如何获取结账时点对应历史登录与结账记录

实现方案 #

核心思路 #

要为每笔结账记录匹配其发生时间之前对应用户的最近成功/失败登录时间，你可以从两种常用实现中选择，均兼容Redshift和PostgreSQL环境：

方案1：LATERAL JOIN（性能更优，适合大数量级场景） #

你已经通过窗口函数生成了包含lastGoodCheckout、lastFailedCheckout的结账中间表，此处假设该中间表名为checkouts_enriched，直接关联即可：

SELECT
  c.checkout_id,
  c.checkout_time AS checkout,
  c.user_id,
  lg.login AS lastGoodLogin,
  lf.login AS lastFailedLogin,
  c.lastGoodCheckout,
  c.lastFailedCheckout
FROM checkouts_enriched c
-- 匹配最近成功登录记录
LEFT JOIN LATERAL (
  SELECT login
  FROM login_attempts
  WHERE user_id = c.user_id
    AND success = 1
    AND login < c.checkout_time
  ORDER BY login DESC
  LIMIT 1
) lg ON TRUE
-- 匹配最近失败登录记录
LEFT JOIN LATERAL (
  SELECT login
  FROM login_attempts
  WHERE user_id = c.user_id
    AND success = 0
    AND login < c.checkout_time
  ORDER BY login DESC
  LIMIT 1
) lf ON TRUE
ORDER BY c.checkout_id;

该方案每次关联只会取满足时间条件的最新一条登录记录，不需要全量扫描所有匹配的登录数据，登录数据量越大性能优势越明显。

方案2：条件聚合（写法更简洁，适合中小数据量场景） #

WITH login_time_agg AS (
  SELECT
    c.checkout_id,
    MAX(CASE WHEN la.success = 1 THEN la.login END) AS lastGoodLogin,
    MAX(CASE WHEN la.success = 0 THEN la.login END) AS lastFailedLogin
  FROM checkouts_enriched c
  LEFT JOIN login_attempts la
    ON la.user_id = c.user_id
    AND la.login < c.checkout_time
  GROUP BY c.checkout_id
)
SELECT
  c.checkout_id,
  c.checkout_time AS checkout,
  c.user_id,
  la.lastGoodLogin,
  la.lastFailedLogin,
  c.lastGoodCheckout,
  c.lastFailedCheckout
FROM checkouts_enriched c
INNER JOIN login_time_agg la ON c.checkout_id = la.checkout_id
ORDER BY c.checkout_id;

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