BigQuery跨表按日期取值：每日最大值与最新值查询方案

需求1：按日期分组，获取各列最大值并返回单行 #

直接关联两张表会因多对多关系产生重复行，正确做法是先分别对两张表按日期+标的聚合最大值，再进行关联，避免笛卡尔积。

实现查询 #

WITH aggregated_table1 AS (
  SELECT
    date,
    symbol,
    MAX(fee_rate) AS fee_rate,
    MAX(rebate_rate) AS rebate_rate,
    MAX(short_shares_available) AS short_shares_available
  FROM `db1.table1`
  WHERE symbol = 'AMC'
  GROUP BY date, symbol
),
aggregated_table2 AS (
  SELECT
    date,
    ticker,
    MAX(share_volume) AS share_volume
  FROM `db1.table2`
  WHERE ticker = 'AMC'
  GROUP BY date, ticker
)
SELECT
  a1.date,
  a1.symbol,
  a1.fee_rate,
  a1.rebate_rate,
  a1.short_shares_available,
  a2.share_volume
FROM aggregated_table1 a1
LEFT JOIN aggregated_table2 a2
  ON a1.date = a2.date
  AND a1.symbol = a2.ticker
ORDER BY a1.date DESC
LIMIT 25

说明 #

• 先通过CTE分别对table1和table2按date+symbol/ticker分组，用MAX()聚合各字段最大值
• 再将两个聚合后的结果关联，确保每日仅返回一行数据

需求2：按日期分组，获取各列的最新录入值并返回单行 #

利用tape_time字段判断table2的最新记录；对于table1，若有录入时间字段可替换排序逻辑，以下示例默认取该日期下最后出现的记录。

实现查询 #

WITH latest_table1 AS (
  SELECT
    date,
    symbol,
    fee_rate,
    rebate_rate,
    short_shares_available,
    -- 若table1有录入时间字段，替换为该字段的降序排序
    ROW_NUMBER() OVER (PARTITION BY date, symbol ORDER BY (SELECT NULL) DESC) AS rn
  FROM `db1.table1`
  WHERE symbol = 'AMC'
),
latest_table2 AS (
  SELECT
    date,
    ticker,
    share_volume,
    ROW_NUMBER() OVER (PARTITION BY date, ticker ORDER BY tape_time DESC) AS rn
  FROM `db1.table2`
  WHERE ticker = 'AMC'
)
SELECT
  t1.date,
  t1.symbol,
  t1.fee_rate,
  t1.rebate_rate,
  t1.short_shares_available,
  t2.share_volume
FROM latest_table1 t1
LEFT JOIN latest_table2 t2
  ON t1.date = t2.date
  AND t1.symbol = t2.ticker
WHERE t1.rn = 1 AND t2.rn = 1
ORDER BY t1.date DESC
LIMIT 25

说明 #

• 使用ROW_NUMBER()窗口函数，按date+symbol/ticker分区：
  • table2按tape_time DESC排序，取第一行即为当日最新录入的share_volume
  • 若table1有专门的录入时间字段，将ORDER BY (SELECT NULL) DESC替换为该时间字段的降序排序，即可获取table1当日最新记录
• 过滤rn = 1的行，确保每个日期仅返回一条最新数据

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