基于双表Symbol匹配计算24h/7d/30d周期价格变动

我来帮你完善这个跨表计算价格差值的SQL查询——你的初始思路已经找对了方向，咱们把它扩展到支持24小时、7天、30天三个周期的价格对比：

基础实现方案（兼容多数SQL数据库） #

这个方案用子查询分别获取每个周期的VWAP数据，逻辑清晰，适合快速验证需求：

SELECT 
    al.symbol,
    al.lastPriceUSD AS current_price,
    al.circulatingSupply,
    -- 取24小时窗口内的第一条VWAP（最接近周期起始点的价格）
    (SELECT vw.vwapUSD 
     FROM VWAPUSD vw 
     WHERE vw.symbol = al.symbol 
       AND vw.createdAt >= NOW() - INTERVAL 1 DAY 
     ORDER BY vw.createdAt ASC 
     LIMIT 1) AS vwap_24h_ago,
    -- 取7天窗口内的第一条VWAP
    (SELECT vw.vwapUSD 
     FROM VWAPUSD vw 
     WHERE vw.symbol = al.symbol 
       AND vw.createdAt >= NOW() - INTERVAL 7 DAY 
     ORDER BY vw.createdAt ASC 
     LIMIT 1) AS vwap_7d_ago,
    -- 取30天窗口内的第一条VWAP
    (SELECT vw.vwapUSD 
     FROM VWAPUSD vw 
     WHERE vw.symbol = al.symbol 
       AND vw.createdAt >= NOW() - INTERVAL 30 DAY 
     ORDER BY vw.createdAt ASC 
     LIMIT 1) AS vwap_30d_ago,
    -- 计算各周期价格变化百分比（保留两位小数）
    ROUND(100 * (al.lastPriceUSD - (SELECT vw.vwapUSD FROM VWAPUSD vw WHERE vw.symbol = al.symbol AND vw.createdAt >= NOW() - INTERVAL 1 DAY ORDER BY vw.createdAt ASC LIMIT 1)) / al.lastPriceUSD, 2) AS difference_24h_pct,
    ROUND(100 * (al.lastPriceUSD - (SELECT vw.vwapUSD FROM VWAPUSD vw WHERE vw.symbol = al.symbol AND vw.createdAt >= NOW() - INTERVAL 7 DAY ORDER BY vw.createdAt ASC LIMIT 1)) / al.lastPriceUSD, 2) AS difference_7d_pct,
    ROUND(100 * (al.lastPriceUSD - (SELECT vw.vwapUSD FROM VWAPUSD vw WHERE vw.symbol = al.symbol AND vw.createdAt >= NOW() - INTERVAL 30 DAY ORDER BY vw.createdAt ASC LIMIT 1)) / al.lastPriceUSD, 2) AS difference_30d_pct
FROM assetList al
-- 只保留有对应VWAP历史数据的资产（若要包含无历史数据的资产，改用LEFT JOIN）
JOIN VWAPUSD vw ON al.symbol = vw.symbol
GROUP BY al.symbol, al.lastPriceUSD, al.circulatingSupply
ORDER BY (al.circulatingSupply * al.lastPriceUSD) DESC;

关键细节说明 #

• 周期VWAP获取逻辑：因为VWAPUSD每10分钟更新一次，我们取每个周期起始时间后的第一条记录，这是最接近周期起点的参考价格；如果需要计算周期内的平均VWAP，只需把LIMIT 1替换成AVG(vw.vwapUSD)并去掉ORDER BY即可。
• 差值百分比计算：沿用了你原有的公式，用ROUND()优化可读性，结果表示当前价格相对周期前VWAP的涨跌幅度。
• 性能优化提示：如果你的数据量较大，重复子查询会影响性能，推荐用下面的CTE（公共表表达式）版本，一次性预取所有需要的VWAP数据：

高性能优化版本（PostgreSQL兼容） #

WITH asset_vwaps AS (
    SELECT 
        symbol,
        -- 24小时周期的起始VWAP
        FIRST_VALUE(vwapUSD) OVER (PARTITION BY symbol ORDER BY createdAt ASC) AS vwap_24h_ago,
        -- 7天周期的起始VWAP
        FIRST_VALUE(vwapUSD) OVER (PARTITION BY symbol ORDER BY createdAt ASC) AS vwap_7d_ago,
        -- 30天周期的起始VWAP
        FIRST_VALUE(vwapUSD) OVER (PARTITION BY symbol ORDER BY createdAt ASC) AS vwap_30d_ago
    FROM VWAPUSD
    WHERE createdAt >= NOW() - INTERVAL 30 DAY -- 覆盖最长的30天周期
)
SELECT 
    al.symbol,
    al.lastPriceUSD AS current_price,
    al.circulatingSupply,
    av.vwap_24h_ago,
    av.vwap_7d_ago,
    av.vwap_30d_ago,
    ROUND(100 * (al.lastPriceUSD - av.vwap_24h_ago) / al.lastPriceUSD, 2) AS difference_24h_pct,
    ROUND(100 * (al.lastPriceUSD - av.vwap_7d_ago) / al.lastPriceUSD, 2) AS difference_7d_pct,
    ROUND(100 * (al.lastPriceUSD - av.vwap_30d_ago) / al.lastPriceUSD, 2) AS difference_30d_pct
FROM assetList al
JOIN asset_vwaps av ON al.symbol = av.symbol
ORDER BY (al.circulatingSupply * al.lastPriceUSD) DESC;

这个版本通过CTE提前聚合所有周期的VWAP数据，避免重复查询，在数据量较大时性能提升明显。

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