MariaDB按时间戳分区取Top30数据的LIMIT替代方案问询

要实现每个时间戳分区仅保留按percent_change_24h降序的前30条，再筛选满足条件的行，你需要利用ROW_NUMBER()窗口函数生成的行号来过滤每个分区的前30条，而非使用全局LIMIT。

正确SQL查询 #

SELECT t.cmc_id, t.symbol,
       t.percent_change_1h, t.percent_change_24h,
       t.cmc_rank, t.market_cap, t.timestamp
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY timestamp ORDER BY percent_change_24h DESC) AS seqnum
    FROM table1.listings
    WHERE timestamp >= '2025-12-23 09:20:00'
      AND cmc_rank IS NOT NULL -- 注意你之前的查询里误写为c_rank，这里修正为cmc_rank
) t
WHERE t.seqnum <= 30 -- 保留每个时间戳分区的前30条数据
  AND t.percent_change_1h > 4
  AND t.percent_change_24h > 2;

逻辑说明 #

1. 内层查询：
   • 按timestamp分区，每个分区内按percent_change_24h降序排列，用ROW_NUMBER()生成每行在所属分区的序号seqnum。
   • 先过滤时间范围和cmc_rank不为空的行，减少后续计算量。
2. 外层查询：
   • 通过seqnum <= 30筛选出每个时间戳分区的前30条数据，和你单时间戳查询里的LIMIT 30逻辑完全对应。
   • 再应用percent_change_1h > 4和percent_change_24h > 2的过滤条件，得到最终结果。

你之前的错误点 #

• 第一个查询未利用seqnum过滤每个分区的前30条，导致所有符合时间范围的行都被保留，结果集过大。
• 第二个查询的LIMIT 30是全局限制，只会返回整个结果集的前30条，而非每个时间戳的前30条。
• 最后尝试的COUNT()窗口函数使用了滑动窗口框架，用于计算滑动范围内的行数，不符合“取每个分区前30条”的需求。

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