基于时间维度筛选最优价格的查询语句开发需求

我现在遇到一个SQL查询开发的问题，想请教怎么实现：我需要写一个查询，找出指定时间段内最划算的、和时间挂钩的价格，但同时绝对不能排除那些在这个时间段里完全没有价格数据的对象。

因为价格的起止周期是完全灵活的，没法用常规的“大于/小于”这类简单范围查询来处理。我自己初步琢磨的思路是：先找出价格区间和目标时间段重叠部分的时间跨度最小的记录，要是有多个这样的重叠跨度相同的记录，就选其中价格最低的那个。

价格表结构及现有数据 #

下面是我的价格表（部分数据）：

解决方案思路及SQL示例 #

结合需求，我整理了一个可行的方案，用CTE和窗口函数来实现，这里以PostgreSQL为例（不同数据库的日期函数可能需要微调）：

首先假设咱们有一个对象表objects，里面存了所有需要保留的oid（毕竟要包含没有价格的对象）。核心逻辑是先计算每个价格区间和目标时间段的重叠时长，再给每个对象的价格记录排序，选出最优的那条。

-- 定义目标时间段，这里可以替换成实际参数
WITH params AS (
    SELECT '2024-06-01'::DATE AS target_start, '2024-09-01'::DATE AS target_end
),
price_overlaps AS (
    SELECT
        o.oid,
        p.id,
        p.start AS price_start,
        p.end AS price_end,
        p.price,
        -- 计算和目标时间段的重叠天数，没有重叠的话会是0，但后续过滤掉了不重叠的记录
        GREATEST(0, 
            DATE_PART('day', LEAST(p.end, params.target_end) - GREATEST(p.start, params.target_start))
        ) AS overlap_days
    FROM objects o
    CROSS JOIN params
    LEFT JOIN prices p 
        ON o.oid = p.oid
        -- 只保留和目标时间段有重叠的价格记录
        AND p.start <= params.target_end
        AND p.end >= params.target_start
),
ranked_prices AS (
    SELECT
        oid,
        id,
        price_start,
        price_end,
        price,
        overlap_days,
        -- 按重叠天数升序（越小越优先），再按价格升序（越低越优先）给每个对象的记录排名
        ROW_NUMBER() OVER (
            PARTITION BY oid 
            ORDER BY overlap_days ASC, price ASC
        ) AS rn
    FROM price_overlaps
)
SELECT
    oid,
    id,
    price_start,
    price_end,
    price,
    overlap_days
FROM ranked_prices
WHERE rn = 1
ORDER BY oid;

逻辑解释 #

1. params CTE：先定义目标时间段，方便后续修改参数。
2. price_overlaps CTE：关联对象表和价格表，计算每个价格区间和目标时间段的重叠天数。没有价格的对象，对应的价格相关字段都会是NULL，这样就保留了这些对象。
3. ranked_prices CTE：用ROW_NUMBER()窗口函数按oid分组，先按重叠天数从小到大排序，再按价格从小到大排序，给每个组内的记录标上排名。
4. 最后筛选出每个组排名第一的记录，就是每个对象的最优价格（或者没有价格的NULL记录）。

如果是MySQL数据库，日期计算部分可以换成DATEDIFF(LEAST(p.end, params.target_end), GREATEST(p.start, params.target_start)) + 1（注意天数计算的差异，MySQL的DATEDIFF是结束减开始，返回天数差，加1才是实际天数），窗口函数的用法是一致的。

备注：内容来源于stack exchange，提问作者maidan