SQL中LAG与LEAD函数的正确使用方法咨询

你的代码是完全正确的，这正是LEAD窗口函数处理时间序列分组数据的标准用法。

为什么你的写法合理： #

• 通过PARTITION BY patient_name将数据按患者拆分，确保只在同一患者的记录范围内获取后续测量数据，避免跨患者的错误关联
• ORDER BY year指定了记录的排序逻辑，保证后续值是按时间顺序的下一次测量结果，符合业务逻辑
• 分别用LEAD(year)和LEAD(location)提取下一次测量的年份与地点，计算间隔年数和地点变化的逻辑清晰直接
• 用WHERE next_year IS NOT NULL过滤掉每个患者的最后一条记录（无后续测量的记录），完全匹配需求中"每两次测量之间变化"的要求

可选优化建议 #

你可以将重复的窗口定义合并，让代码更简洁易维护：

WITH next_measurements AS (
    SELECT 
        patient_name,
        year as start_year,
        gender,
        patient_weight as start_weight,
        location as start_location,
        LEAD(year) OVER w as next_year,
        LEAD(location) OVER w as next_location
    FROM sample_data
    WINDOW w AS (PARTITION BY patient_name ORDER BY year) -- 统一窗口定义
)
SELECT 
    patient_name,
    start_year,
    gender,
    start_weight,
    (next_year - start_year) as years_until_next,
    LOWER(start_location) || '-' || LOWER(next_location) as location_change
FROM next_measurements
WHERE next_year IS NOT NULL
ORDER BY patient_name, start_year;

原数据表结构与数据 #

CREATE TABLE SAMPLE_DATA (
    patient_name VARCHAR(50),
    year INTEGER,
    gender CHAR(1),
    patient_weight DECIMAL(5,2),
    location VARCHAR(20)
);

INSERT INTO SAMPLE_DATA (patient_name, year, gender, patient_weight, location) VALUES
    ('Sarah', 2010, 'F', 65.00, 'hospital'),
    ('Sarah', 2012, 'F', 66.00, 'home'),
    ('Sarah', 2013, 'F', 67.00, 'hospital'),
    ('Michael', 2011, 'M', 78.00, 'hospital'),
    ('Michael', 2013, 'M', 76.00, 'home'),
    ('Michael', 2015, 'M', 77.00, 'hospital'),
    ('James', 2010, 'M', 82.00, 'home'),
    ('James', 2014, 'M', 80.00, 'hospital'),
    ('Emma', 2012, 'F', 70.00, 'hospital'),
    ('Emma', 2013, 'F', 71.00, 'home'),
    ('Emma', 2015, 'F', 71.00, 'hospital'),
    ('Robert', 2011, 'M', 88.00, 'hospital'),
    ('Robert', 2014, 'M', 85.00, 'home'),
    ('Maria', 2010, 'F', 63.00, 'hospital'),
    ('Maria', 2012, 'F', 64.00, 'home'),
    ('Maria', 2015, 'F', 64.00, 'hospital');

原表数据展示 #

patient_name | year | gender | patient_weight | location
-------------|------|--------|----------------|----------
Sarah        | 2010 | F      | 65.00         | hospital
Sarah        | 2012 | F      | 66.00         | home
Sarah        | 2013 | F      | 67.00         | hospital
Michael      | 2011 | M      | 78.00         | hospital
Michael      | 2013 | M      | 76.00         | home
Michael      | 2015 | M      | 77.00         | hospital
James        | 2010 | M      | 82.00         | home
James        | 2014 | M      | 80.00         | hospital
Emma         | 2012 | F      | 70.00         | hospital
Emma         | 2013 | F      | 71.00         | home
Emma         | 2015 | F      | 71.00         | hospital
Robert       | 2011 | M      | 88.00         | hospital
Robert       | 2014 | M      | 85.00         | home
Maria        | 2010 | F      | 63.00         | hospital
Maria        | 2012 | F      | 64.00         | home
Maria        | 2015 | F      | 64.00         | hospital

期望结果 #

patient_name | start_year | gender | start_weight | years_until_next | location_change    
-------------|------------|---------|--------------|------------------|-------------------
Sarah        | 2010       | F       | 65.00       | 2               | hospital-home     
Sarah        | 2012       | F       | 66.00       | 1               | home-hospital     
Michael      | 2011       | M       | 78.00       | 2               | hospital-home     
Michael      | 2013       | M       | 76.00       | 2               | home-hospital     
James        | 2010       | M       | 82.00       | 4               | home-hospital     
Emma         | 2012       | F       | 70.00       | 1               | hospital-home     
Emma         | 2013       | F       | 71.00       | 2               | home-hospital     
Robert       | 2011       | M       | 88.00       | 3               | hospital-home     
Maria        | 2010       | F       | 63.00       | 2               | hospital-home     
Maria        | 2012       | F       | 64.00       | 3               | home-hospital

你的实现代码 #

WITH next_measurements AS (
    SELECT 
        patient_name,
        year as start_year,
        gender,
        patient_weight as start_weight,
        location as start_location,
        LEAD(year) OVER (
            PARTITION BY patient_name 
            ORDER BY year
        ) as next_year,
        LEAD(location) OVER (
            PARTITION BY patient_name 
            ORDER BY year
        ) as next_location
    FROM sample_data
)
SELECT 
    patient_name,
    start_year,
    gender,
    start_weight,
    (next_year - start_year) as years_until_next,
    LOWER(start_location) || '-' || LOWER(next_location) as location_change
FROM next_measurements
WHERE next_year IS NOT NULL
ORDER BY patient_name, start_year;

执行结果 #

patient_name start_year gender start_weight years_until_next location_change
         Emma       2012      F           70                1   hospital-home
         Emma       2013      F           71                2   home-hospital
        James       2010      M           82                4   home-hospital
        Maria       2010      F           63                2   hospital-home
        Maria       2012      F           64                3   home-hospital
      Michael       2011      M           78                2   hospital-home
      Michael       2013      M           76                2   home-hospital
       Robert       2011      M           88                3   hospital-home
        Sarah       2010      F           65                2   hospital-home
        Sarah       2012      F           66                1   home-hospital

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