嵌套SQL查询实现：如何从每个目标日志中返回1条符合条件的样本

方案1：窗口函数法（适配绝大多数支持标准SQL的数据库，如BigQuery、Spark SQL、PostgreSQL等） #

该方案逻辑清晰性能优异，是优先推荐的实现方式：

WITH rocket_logs AS (
  -- 子查询1：获取所有ROCKET类型的日志名称列表
  SELECT  
    runs.name as log_name
  FROM project.runs.latest_runs  
  WHERE object_type = "ROCKET"  
  GROUP BY object_type, log_name
),
ranked_events AS (
  -- 子查询2：给每个日志下符合条件的事件按组编号
  SELECT  
    e.object.path_meters as pos,  
    e.object_speed as speed,  
    e.log.run as run_name,
    -- 按日志名称分组编号，可修改ORDER BY规则自定义取数逻辑（比如按速度倒序取最大速度样本）
    ROW_NUMBER() OVER (PARTITION BY e.log.run ORDER BY e.object.path_meters) AS rn
  FROM project.events.last30days e
  INNER JOIN rocket_logs rl ON e.log.run = rl.log_name
  WHERE e.object.speed > 0.0
)
-- 取每个日志的第一条符合条件的样本
SELECT pos, speed, run_name
FROM ranked_events
WHERE rn = 1

方案2：LATERAL JOIN关联法（适配支持横向子查询的数据库，如PostgreSQL、Spark 3.0+） #

如果不需要对样本做额外排序处理，该写法更简洁：

SELECT t.*
FROM (
  SELECT runs.name as log_name
  FROM project.runs.latest_runs  
  WHERE object_type = "ROCKET"  
  GROUP BY object_type, log_name
) rl
LEFT JOIN LATERAL (
  SELECT  
    object.path_meters as pos,  
    object_speed as speed,  
    log.run as run_name
  FROM project.events.last30days e
  WHERE e.log.run = rl.log_name
    AND e.object.speed > 0.0
  LIMIT 1
) t ON TRUE
-- 过滤掉没有符合条件样本的日志
WHERE t.run_name IS NOT NULL

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