SQL Server中基于其他行与列为表新增Route、Date列的方法

核心判定逻辑 #

同一张客票的分段行程满足两个规则：

• 同个出行人维度下，行程按时间排序后，上一段的目的地 = 当前段的出发地（行程连续）
• 相邻两段的出行日期间隔不超过设定的中转阈值（样例中Sam在马尔代夫停留数日，间隔远超中转时长，自动判定为新客票）
  基于这个逻辑给同一张客票的所有分段打统一分组标记，再取每组首尾信息填充字段即可。

注意：原始表中from是SQL Server保留关键字，所有查询中引用该字段必须加[]转义，避免语法错误。

实现步骤 #

1. 测试表与样例数据（和给出的场景完全对齐） #

-- 建表
CREATE TABLE TravelRecord (
    name VARCHAR(50),
    [from] VARCHAR(50),
    [to] VARCHAR(50),
    traveling_date DATE
);
-- 插入样例数据
INSERT INTO TravelRecord VALUES
('Mike','London','Paris','2022-01-05'),
('Mike','Paris','Barcelona','2022-01-05'),
('Sam','Cairo','Riyadh','2022-03-06'),
('Sam','Riyadh','Dubai','2022-03-06'),
('Sam','Dubai','Maldives','2022-03-07'),
('Sam','Maldives','Riyadh','2022-03-13'),
('Sam','Riyadh','Cairo','2022-03-13');

2. 核心计算SQL #

WITH Step1 AS (
    -- 按出行人、出行日期排序，标记每段是否为新客票首段
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY name ORDER BY traveling_date) AS rn,
        -- 取上一段的目的地、上一段的出行日期
        LAG([to]) OVER(PARTITION BY name ORDER BY traveling_date) AS prev_to,
        LAG(traveling_date) OVER(PARTITION BY name ORDER BY traveling_date) AS prev_date
    FROM TravelRecord
),
Step2 AS (
    SELECT 
        *,
        -- 累计求和生成客票分组ID：每遇到一个新客票首段，分组ID+1
        SUM(CASE 
                WHEN rn = 1 THEN 1 -- 第一条行程必然是首段
                -- 上一段目的地和当前出发地不匹配 或 间隔超过24小时（中转阈值可自行调整） 判定为新客票首段
                WHEN prev_to != [from] OR DATEDIFF(HOUR, prev_date, traveling_date) > 24 THEN 1
                ELSE 0 
            END) OVER(PARTITION BY name ORDER BY rn) AS ticket_group
    FROM Step1
),
Step3 AS (
    SELECT 
        *,
        -- 取同组首段的出发地、出行日期
        FIRST_VALUE([from]) OVER(PARTITION BY name, ticket_group ORDER BY rn) AS ticket_start,
        FIRST_VALUE(traveling_date) OVER(PARTITION BY name, ticket_group ORDER BY rn) AS ticket_date,
        -- 取同组尾段的目的地
        LAST_VALUE([to]) OVER(PARTITION BY name, ticket_group ORDER BY rn ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ticket_end,
        -- 标记同组内的第一条行（需要填充字段的行）
        ROW_NUMBER() OVER(PARTITION BY name, ticket_group ORDER BY rn) AS group_rn
    FROM Step2
)
-- 最终结果输出 新增Route和Date字段
SELECT 
    name,
    [from],
    [to],
    traveling_date,
    -- 仅组内第一行填充路线，其余留空
    CASE WHEN group_rn = 1 THEN CONCAT(ticket_start, '-', ticket_end) ELSE '' END AS Route,
    -- 仅组内第一行填充日期，格式为 日/月份缩写/年，其余留空
    CASE WHEN group_rn = 1 THEN FORMAT(ticket_date, 'd/MMM/yyyy', 'en-gb') ELSE '' END AS Date
FROM Step3
ORDER BY name, rn;

结果验证 #

执行上述SQL返回的结果完全匹配需求：

• Mike的London→Paris段：Route为London-Barcelona，Date为5/Jan/2022，后续Paris→Barcelona段两个新增字段为空
• Sam的Cairo→Riyadh段：Route为Cairo-Maldives，Date为6/Mar/2022，后续两段去程中转段字段为空
• Sam的Maldives→Riyadh段：Route为Maldives-Cairo，Date为13/Mar/2022，后续Riyadh→Cairo段字段为空

可调参数说明 #

• 中转间隔阈值：当前设置为相邻两段间隔超过24小时判定为新客票，你可以根据业务规则调整DATEDIFF(HOUR, prev_date, traveling_date) > 24里的数值，比如改成48就是间隔超过2天算新客票
• 日期格式：如果需要调整Date字段的显示格式，修改FORMAT函数里的格式参数即可

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