高效范围Max/Min查询结构推荐及零件价格波动SQL查询需求

一、范围Max/Min查询的高效数据结构推荐 #

范围极值查询是很多业务场景的核心需求，不同的数据结构适配不同的使用场景，给你列几个最实用的选项：

• 线段树（Segment Tree）：
  绝对是动态数据集的首选——支持O(log n)的查询和更新操作。如果你的数据需要频繁修改（比如实时更新零件价格），同时还要快速查询任意区间的最值，线段树的灵活性和效率都拉满。

• 稀疏表（Sparse Table）：
  专为静态数据集优化，预处理时间O(n log n)，查询时间直接O(1)，空间开销是O(n log n)。如果是处理历史归档的价格数据这类基本不会变动的数据集，用它查询最值几乎是瞬时的，非常高效。

• Fenwick树（二叉索引树）：
  虽然常用来做前缀和，但也能扩展到前缀最值查询，进而推导任意区间的最值。它的优点是实现简单、空间占用小（O(n)），不过相比线段树，处理非前缀区间的灵活性稍弱，适合偏向前缀相关的范围查询场景。

• 平衡二叉搜索树（红黑树/AVL树）：
  要是你需要维护一个有序的数据集，同时支持插入、删除和范围最值查询，平衡BST能做到各类操作都是O(log n)。比如动态维护零件价格的有序集合，随时查询某个价格区间内的最值，这个就很合适。

二、MySQL中查询指定日期范围涨跌最大零件的实现 #

先假设你的PartPrices表结构是：part_id（零件ID）、price_date（聚合日期）、price（当日零件价格）。下面给你两种实现方案，适配不同的MySQL版本：

方案1：用窗口函数（MySQL 8.0+推荐） #

窗口函数能很简洁地获取每个零件的起止价格，然后计算涨跌幅：

WITH PartPriceRange AS (
    SELECT
        part_id,
        -- 获取指定日期范围内该零件的最早价格
        FIRST_VALUE(price) OVER (PARTITION BY part_id ORDER BY price_date ASC) AS start_price,
        -- 获取指定日期范围内该零件的最晚价格，注意窗口范围要设为全区间
        LAST_VALUE(price) OVER (
            PARTITION BY part_id 
            ORDER BY price_date ASC 
            RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS end_price
    FROM PartPrices
    WHERE price_date BETWEEN '2024-01-01' AND '2024-03-31' -- 替换成你的目标日期范围
),
PriceChange AS (
    SELECT
        part_id,
        start_price,
        end_price,
        -- 计算涨跌幅，保留2位小数，避免除以0错误
        ROUND((end_price - start_price)/NULLIF(start_price, 0) * 100, 2) AS price_change_pct
    FROM PartPriceRange
    GROUP BY part_id, start_price, end_price -- 去重，每个零件只留一条记录
)
-- 合并最大涨幅和最大跌幅的结果
SELECT
    '最大涨幅' AS change_type,
    part_id,
    start_price,
    end_price,
    price_change_pct AS change_pct
FROM PriceChange
WHERE price_change_pct = (SELECT MAX(price_change_pct) FROM PriceChange)
UNION ALL
SELECT
    '最大跌幅' AS change_type,
    part_id,
    start_price,
    end_price,
    price_change_pct AS change_pct
FROM PriceChange
WHERE price_change_pct = (SELECT MIN(price_change_pct) FROM PriceChange);

方案2：兼容MySQL 5.x版本（无窗口函数） #

如果你的MySQL版本较低，用子查询来获取起止价格：

-- 先获取每个零件的起止价格
SELECT
    '最大涨幅' AS change_type,
    part_id,
    start_price,
    end_price,
    ROUND((end_price - start_price)/NULLIF(start_price, 0) * 100, 2) AS change_pct
FROM (
    SELECT
        p.part_id,
        (SELECT price FROM PartPrices WHERE part_id = p.part_id AND price_date BETWEEN '2024-01-01' AND '2024-03-31' ORDER BY price_date ASC LIMIT 1) AS start_price,
        (SELECT price FROM PartPrices WHERE part_id = p.part_id AND price_date BETWEEN '2024-01-01' AND '2024-03-31' ORDER BY price_date DESC LIMIT 1) AS end_price
    FROM (SELECT DISTINCT part_id FROM PartPrices WHERE price_date BETWEEN '2024-01-01' AND '2024-03-31') p
) AS PartPriceRange
WHERE (end_price - start_price)/NULLIF(start_price, 0) = (
    SELECT MAX((end_price - start_price)/NULLIF(start_price, 0))
    FROM (
        SELECT
            p.part_id,
            (SELECT price FROM PartPrices WHERE part_id = p.part_id AND price_date BETWEEN '2024-01-01' AND '2024-03-31' ORDER BY price_date ASC LIMIT 1) AS start_price,
            (SELECT price FROM PartPrices WHERE part_id = p.part_id AND price_date BETWEEN '2024-01-01' AND '2024-03-31' ORDER BY price_date DESC LIMIT 1) AS end_price
        FROM (SELECT DISTINCT part_id FROM PartPrices WHERE price_date BETWEEN '2024-01-01' AND '2024-03-31') p
    ) AS temp
)
UNION ALL
SELECT
    '最大跌幅' AS change_type,
    part_id,
    start_price,
    end_price,
    ROUND((end_price - start_price)/NULLIF(start_price, 0) * 100, 2) AS change_pct
FROM (
    SELECT
        p.part_id,
        (SELECT price FROM PartPrices WHERE part_id = p.part_id AND price_date BETWEEN '2024-01-01' AND '2024-03-31' ORDER BY price_date ASC LIMIT 1) AS start_price,
        (SELECT price FROM PartPrices WHERE part_id = p.part_id AND price_date BETWEEN '2024-01-01' AND '2024-03-31' ORDER BY price_date DESC LIMIT 1) AS end_price
    FROM (SELECT DISTINCT part_id FROM PartPrices WHERE price_date BETWEEN '2024-01-01' AND '2024-03-31') p
) AS PartPriceRange
WHERE (end_price - start_price)/NULLIF(start_price, 0) = (
    SELECT MIN((end_price - start_price)/NULLIF(start_price, 0))
    FROM (
        SELECT
            p.part_id,
            (SELECT price FROM PartPrices WHERE part_id = p.part_id AND price_date BETWEEN '2024-01-01' AND '2024-03-31' ORDER BY price_date ASC LIMIT 1) AS start_price,
            (SELECT price FROM PartPrices WHERE part_id = p.part_id AND price_date BETWEEN '2024-01-01' AND '2024-03-31' ORDER BY price_date DESC LIMIT 1) AS end_price
        FROM (SELECT DISTINCT part_id FROM PartPrices WHERE price_date BETWEEN '2024-01-01' AND '2024-03-31') p
    ) AS temp
);

额外提示 #

• 记得替换SQL中的日期范围为你实际需要的区间；
• 用NULLIF(start_price, 0)是为了避免起始价格为0时触发除以0的错误，这种情况下涨跌幅会返回NULL，你可以根据业务需求调整处理逻辑；
• 如果有多个零件同时达到最大涨幅/跌幅，这个查询会返回所有符合条件的零件。

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