梧桐数据库之查询特定日期的套餐价格

一、背景说明

随着市场竞争的加剧和技术的发展，运营商需要频繁调整其服务套餐的价格以吸引客户或应对成本变化。假设某移动运营商决定对其提供的各种服务套餐（如流量包、语音通话包等）进行价格调整，并记录了每次价格调整的具体日期和新的价格。现在，管理层希望了解2024年9月1日当天所有服务套餐的有效价格是多少，以便分析当时的市场策略和收益情况。如果某个服务套餐在此之前从未调整过价格，则默认该套餐的初始价格为10元。

二、表结构说明

为了存储这些信息，我们将创建一个名为 ServicePackages 的表来保存每个服务套餐的价格变动历史。以下是建表语句：

CREATE TABLE ServicePackages (
  package_id INT,          -- 服务套餐ID
  new_price INT,           -- 新价格
  change_date DATE,        -- 价格变更日期
  PRIMARY KEY (package_id, change_date)  -- 主键：(套餐ID, 变更日期)
);

三、表数据插入

接下来，我们插入一些示例数据到 ServicePackages 表中，模拟几个不同套餐在不同时期的价格变化情况。

INSERT INTO ServicePackages (package_id, new_price, change_date) VALUES
(1, 15, '2024-07-01'),  -- 套餐1于2024-07-01调整至15元
(2, 10, '2024-01-01'),  -- 套餐2于年初保持原价10元
(1, 20, '2024-08-01'),  -- 套餐1再次于2024-08-01调价至20元
(3, 12, '2024-06-15');  -- 套餐3于2024-06-15首次定价为12元

四、实现思路分解

本题的关键点在找到 2024-09-01 前所有有改动的产品及其最新价格和没有没有修改过价格的产品。

1、先找到所有的产品

2、再找到所有 2024-09-01 前有修改的产品和他们最新的价格

3、使用 left join 将两个查询联合，如果产品没有价格，说明没有修改过，设置为 10；如果有价格，设置为最新的价格

五、SQL代码实现

-- 查询在指定日期前所有套餐的最新价格
SELECT
  p.package_id AS '套餐ID',  -- 显示套餐编号
  COALESCE(m.new_price, 10) AS '价格'  -- 若存在对应记录则显示新价格，否则显示默认价格10
FROM
  (SELECT DISTINCT package_id FROM ServicePackages) p  -- 获取所有套餐ID
  LEFT JOIN  -- 左连接确保即使没有变更记录也能返回结果
  (
       SELECT 
         package_id,
         new_price
       FROM
         ServicePackages
       WHERE 
         (package_id, change_date) in 
             (
               SELECT 
                 package_id,
                 MAX(change_date)  -- 找出每个套餐最近一次的价格变更日期
               FROM
                 ServicePackages
               WHERE
                 change_date <= '2024-09-01'  -- 只考虑截止到指定日期之前的变更
               GROUP BY
                 package_id
              )
   ) m
  ON p.package_id = m.package_id  -- 根据package_id关联两个子查询

以上SQL脚本实现了题目要求的功能转换，并适用于移动运营商的服务套餐价格分析场景。

执行结果如下：