如何用SQL查询代币近30天每日EOD价格（无需临时表）

你当前使用的内连接查询逻辑，本质是单日期维度下，按代币分组取时间戳最大的一条价格记录，要扩展到近30天范围不需要临时表，只需要把日期维度加入分组规则即可，以下两种写法都可以直接在你的PostgreSQL环境运行：

写法1：PostgreSQL原生最优写法（性能最高） #

利用PG专属的DISTINCT ON语法，仅需单次扫描表即可完成查询，比多层join、子查询关联的效率高很多，适合数据量大的场景：

SELECT
  tokenid AS "Token",
  price AS "Price",
  source_exchange AS "Source",
  datetime AS "MaxDate"
FROM token_prices
WHERE 
  -- 过滤近30天数据，用范围匹配而非字段函数计算，命中时间索引提升查询速度
  datetime >= CURRENT_DATE - INTERVAL '30 days'
  AND datetime < CURRENT_DATE + INTERVAL '1 day'
-- 核心规则：按「代币+自然日」维度去重
DISTINCT ON (tokenid, datetime::date)
ORDER BY
  tokenid,
  datetime::date,
  datetime DESC; -- 同组内按时间倒序，第一条即为当日最晚更新的收盘价格

写法2：通用窗口函数写法（全SQL方言兼容） #

如果需要迁移到其他不支持DISTINCT ON的数据库，可以用ROW_NUMBER()窗口函数实现，逻辑完全一致：

WITH daily_rank AS (
  SELECT
    tokenid AS "Token",
    price AS "Price",
    source_exchange AS "Source",
    datetime AS "MaxDate",
    -- 按代币、日期分组，组内按时间倒序编号
    ROW_NUMBER() OVER (
      PARTITION BY tokenid, datetime::date
      ORDER BY datetime DESC
    ) AS record_rank
  FROM token_prices
  WHERE
    datetime >= CURRENT_DATE - INTERVAL '30 days'
    AND datetime < CURRENT_DATE + INTERVAL '1 day'
)
SELECT "Token", "Price", "Source", "MaxDate"
FROM daily_rank
WHERE record_rank = 1 -- 只取每组排名第一（时间最晚）的记录
-- 结果排序：最新日期在前，同日期下按代币排序，和你给出的预期输出格式匹配
ORDER BY "MaxDate" DESC, "Token";

注意事项 #

• 如果遇到同一代币在同一毫秒级时间戳下，存在多个交易所上报数据的极端场景，可以在排序规则末尾加优先级字段，比如ORDER BY datetime DESC, source_exchange，保证每个代币每日固定返回1条记录，不会出现重复。
• 时间过滤条件不要写成datetime::date >= (CURRENT_DATE - 30)，这种写法会导致数据库无法使用datetime字段上的B树索引，数据量超过百万级后查询速度会明显下降。

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