MySQL中按医生、患者分组计算平均购买间隔的实现方法

核心思路 #

要计算相邻购买记录的平均间隔，核心是先对同组（同医生+同患者）的购买天数升序排序，再取每条记录的上一条购买天数做差，最后对差值求平均即可。MySQL 8.0及以上版本可直接使用窗口函数LAG()实现上一行取值，逻辑简洁易维护；5.x版本可通过用户变量模拟窗口函数实现相同效果。

测试数据准备 #

首先基于描述的表结构和示例数据构建测试表：

CREATE TABLE `purchase_record` (
  `doctor` varchar(32) NOT NULL COMMENT '医生姓名',
  `patient_of_doctor` varchar(32) NOT NULL COMMENT '所属患者',
  `patient_bought_item_from_doctor_x_days_ago` int NOT NULL COMMENT 'X天前购买'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `purchase_record` VALUES
('Aaron','Jeff',10),
('Aaron','Jeff',20),
('Jess','Jason',50),
('Jess','Jason',20),
('Jess','Jason',30),
('Aaron','Stu',90),
('Aaron','Stu',70),
('Aaron','Stu',110),
('Aaron','Stu',105);

MySQL 8.0+ 实现（推荐） #

使用CTE+LAG()窗口函数的写法逻辑清晰，可直接得到预期结果：

WITH sorted_purchase AS (
  SELECT
    doctor,
    patient_of_doctor,
    patient_bought_item_from_doctor_x_days_ago,
    -- 按医生、患者分区，按购买天数升序取上一条记录的购买天数
    LAG(patient_bought_item_from_doctor_x_days_ago) OVER (
      PARTITION BY doctor, patient_of_doctor
      ORDER BY patient_bought_item_from_doctor_x_days_ago
    ) AS prev_day
  FROM purchase_record
)
SELECT
  doctor,
  patient_of_doctor,
  ROUND(AVG(patient_bought_item_from_doctor_x_days_ago - prev_day), 2) AS avg_buy_interval
FROM sorted_purchase
-- 过滤每组第一条无前置购买记录的数据
WHERE prev_day IS NOT NULL
GROUP BY doctor, patient_of_doctor;

执行结果 #

和需求给出的预期计算值完全一致：

MySQL 5.x 兼容实现 #

5.x版本不支持窗口函数和CTE，可通过用户变量在排序后的数据集上记录上一条记录的分组和天数，计算间隔后再聚合：

SELECT
  doctor,
  patient_of_doctor,
  ROUND(AVG(day_gap), 2) AS avg_buy_interval
FROM (
  SELECT
    doctor,
    patient_of_doctor,
    -- 同组内计算当前记录与上一条记录的天数差
    IF(
      @group_key = CONCAT(doctor, '|', patient_of_doctor),
      patient_bought_item_from_doctor_x_days_ago - @last_day,
      NULL
    ) AS day_gap,
    -- 更新变量，记录当前分组标识和当前购买天数
    @group_key := CONCAT(doctor, '|', patient_of_doctor),
    @last_day := patient_bought_item_from_doctor_x_days_ago
  FROM (
    -- 必须先对数据按分组、购买天数升序排序，保证相邻记录顺序正确
    SELECT * FROM purchase_record
    ORDER BY doctor, patient_of_doctor, patient_bought_item_from_doctor_x_days_ago
  ) AS ordered_data,
  -- 初始化用户变量
  (SELECT @group_key := '', @last_day := NULL) AS var_init
) AS gap_calculation
WHERE day_gap IS NOT NULL
GROUP BY doctor, patient_of_doctor;

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