时间序列等间距降采样至指定点数的SQL优化方案问询

背景 #

我有一个约7.5万条数据的数据库，存储与国家关联的时间序列，表结构如下：
| country | value | date |
通过FastAPI向前端提供数据时，受限于前端图表库最多支持300个点的限制，需要将每个国家的时间序列降采样为包含首尾点的300个等间距点。

现有实现与优化尝试 #

初始方案：PERCENT_RANK窗口函数实现 #

最初采用窗口函数计算百分比排名来分组采样：

WITH
    E AS (
        SELECT *,
        PERCENT_RANK() OVER w as rw
        FROM my_table
        WINDOW w AS (PARTITION BY country ORDER BY date)
    )
SELECT *, FLOOR(300*rw) as sel FROM E GROUP BY E.country, sel;

优化后方案：基于日期区间的分组采样 #

参考思路后写出新SQL，实际运行速度有明显提升：

SELECT country, FLOOR(300*(TO_DAYS(date) - x.da) /(x.dd - x.da)) as g, date, value
    FROM table
    JOIN ( SELECT TO_DAYS(MIN(date)) AS da, TO_DAYS(MAX(date)) as dd, country as cc
              FROM table GROUP BY country
         ) AS x
         ON table.country = x.cc
    GROUP BY country, g;

疑问与解答 #

1. 新方案是否为最优实现？ #

新方案已经是更高效的选择，相比初始方案有这些核心优势：

• 避免全量排序开销：初始方案的PERCENT_RANK需要对每个国家的所有数据做窗口排序，而新方案仅需一次GROUP BY country预计算首尾日期，聚合操作的开销远低于窗口排序。
• 分组逻辑更高效：基于日期区间计算的分组编号g是数值型，数据库分组时的处理速度比窗口函数生成的百分比排名更快。
• 天然保证首尾点：第一条数据的(TO_DAYS(date)-da)/(dd-da)为0，对应g=0；最后一条数据该值为1，对应g=299，分组后必然包含这两个关键节点。

进一步优化建议

• 添加联合索引：给(country, date)建立联合索引，预计算首尾日期和主查询关联时都能直接走索引，避免全表扫描。
• 处理边界异常：如果某个国家的所有数据日期相同（dd-da=0），会触发除以0错误，可添加判断：CASE WHEN x.dd = x.da THEN 0 ELSE FLOOR(300*(TO_DAYS(date)-x.da)/(x.dd-x.da)) END as g。
• 明确聚合逻辑：当前GROUP BY country, g后，date和value取的是分组内默认第一条数据，若需要特定取值（如分组内最早日期、平均值），建议显式指定聚合函数，比如MIN(date) as sampled_date, AVG(value) as sampled_value，避免结果不确定性。

2. 数据库分区的影响 #

分区对性能的影响取决于分区键的选择：

• 按country分区：预计算首尾日期和主查询时，数据库仅需扫描对应国家的分区，无需遍历全表，数据量越大性能提升越明显。
• 按其他字段（如date）分区：对country的聚合和分组没有显著收益，甚至可能因跨分区查询导致性能下降，因此分区键需匹配查询场景。

3. 小数据量示例验证（14条→5个点） #

假设某国家有14条按日期排序的数据，首尾日期差为D，5个点对应4个区间，每个区间跨度为D/4：

• 第1条数据：(date-da)/D=0 → g=0
• 第4条左右数据：(date-da)/D≈0.25 → g=1
• 第7条左右数据：(date-da)/D≈0.5 → g=2
• 第10条左右数据：(date-da)/D≈0.75 → g=3
• 第14条数据：(date-da)/D=1 → g=4
  分组后正好得到包含首尾的5个等间距点，逻辑完全正确。

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