查询账户在日期区间内的产品往复切换记录

表结构及测试数据 #

CREATE TABLE AccountBalanceAndProduct
(
    [ID] [int] NOT NULL IDENTITY(1, 1),
    [EffectiveDate] [date] NULL,
    [AccountID] [varchar] (20) NULL,
    [Product] [varchar] (20)  
)

INSERT INTO AccountBalanceAndProduct (EffectiveDate, Accountid, Product)
VALUES 
    ('2024-07-10', 'Acc1', 'ABA'),
    ('2024-07-11', 'Acc1', 'ABA'),
    ('2024-07-12', 'Acc1', 'ABB'),
    ('2024-07-12', 'Acc1', 'ABA'),
    ('2024-07-13', 'Acc1', 'ABB'),
    ('2024-07-14', 'Acc1', 'ABA'),
    ('2024-07-15', 'Acc1', 'ABC'),
    ('2024-07-16', 'Acc1', 'ABC'),
    ('2024-07-17', 'Acc1', 'ABA'),
    ('2024-07-10', 'Acc2', 'ABA'),
    ('2024-07-11', 'Acc2', 'ABA'),
    ('2024-07-12', 'Acc2', 'ABB'),
    ('2024-07-13', 'Acc2', 'ABB'),
    ('2024-07-14', 'Acc2', 'ABA'),
    ('2024-07-15', 'Acc2', 'ABC'),
    ('2024-07-16', 'Acc2', 'ABC'),
    ('2024-07-17', 'Acc2', 'ABA')

需求说明 #

需要查询账户的产品切换记录，包含切换前后的产品编码、起止日期，以及切换时的累计切换次数。现有SQL无法处理账户在不同产品间往复切换的场景。

现有问题SQL #

;WITH ProductSwitch AS
(
    SELECT
        a.id,
        a.Accountid,
        a.Product,
        a.EffectiveDate[DateSwitchedTo],
        ROW_NUMBER() OVER (PARTITION BY AccountID ORDER BY a.id ASC) [RowNumber]
    FROM 
        (SELECT
             MIN(bal.ID) [id],
             bal.AccountId,
             bal.Product,
             MIN(bal.EffectiveDate) [EffectiveDate]
         FROM 
             AccountBalanceAndProduct bal
         WHERE 
             1 = 1
         GROUP BY 
             bal.Product, bal.Accountid) a
)
SELECT 
    a.AccountID,
    a.Product[ProductFrom],
    a.DateSwitchedTo[FromDate],
    b.Product[ProductTo],
    b.DateSwitchedTo[ToDate],
    b.RowNumber - 1 [TotalNumberOfSwitchesAtTimeOfSwitch]
FROM 
    (SELECT 
         asw.AccountID,
         asw.Product,
         asw.DateSwitchedTo,
         asw.RowNumber
     FROM  
         ProductSwitch asw) a
JOIN 
    (SELECT 
         asw.AccountID,
         asw.Product,
         asw.DateSwitchedTo,
         asw.RowNumber
     FROM
         ProductSwitch asw
     WHERE 
         asw.RowNumber > 1) b ON a.RowNumber = b.RowNumber - 1 
                              AND a.AccountID = b.AccountID

预期结果 #

CREATE TABLE ExpectedResults
(
    AccountID  [varchar](20) NULL,
    ProductFrom  [varchar](20) NULL,
    FromDate [date] NULL,
    ProductTo [varchar](20) NULL,
    ToDate [date] NULL,
    TotalNumberOfSwitchesAtTimeOfSwitch int null
)

INSERT INTO ExpectedResults (AccountID, ProductFrom, FromDate, 
                             ProductTo, ToDate, TotalNumberOfSwitchesAtTimeOfSwitch)
VALUES 
  ('Acc1',  'ABA'   ,'2024-07-10'   ,'ABB'  ,'2024-07-12',  1),
   ('Acc1', 'ABB'   ,'2024-07-12'   ,'ABA'  ,'2024-07-14',  2),
   ('Acc1', 'ABA'   ,'2024-07-14'   ,'ABB'  ,'2024-07-16',  3),
    ('Acc1',    'ABA'   ,'2024-07-16'   ,'ABC'  ,'2024-07-17',  4),
    ('Acc2',    'ABA'   ,'2024-07-10'   ,'ABB'  ,'2024-07-12',  1),
   ('Acc2', 'ABB'   ,'2024-07-12'   ,'ABA'  ,'2024-07-14',  2),
   ('Acc2', 'ABA'   ,'2024-07-14'   ,'ABB'  ,'2024-07-16',  3),
    ('Acc2',    'ABA'   ,'2024-07-16'   ,'ABC'  ,'2024-07-17',  4)

;WITH OrderedRecords AS (
    SELECT 
        AccountID,
        Product,
        EffectiveDate,
        -- 标记连续相同产品的分组
        ROW_NUMBER() OVER (PARTITION BY AccountID ORDER BY ID) - 
        ROW_NUMBER() OVER (PARTITION BY AccountID, Product ORDER BY ID) AS GroupID
    FROM AccountBalanceAndProduct
),
ProductPeriods AS (
    SELECT 
        AccountID,
        Product,
        MIN(EffectiveDate) AS StartDate,
        -- 按时间顺序为每个账户的产品周期编号
        ROW_NUMBER() OVER (PARTITION BY AccountID ORDER BY MIN(ID)) AS PeriodSeq
    FROM OrderedRecords
    GROUP BY AccountID, Product, GroupID
)
SELECT 
    p1.AccountID,
    p1.Product AS ProductFrom,
    p1.StartDate AS FromDate,
    p2.Product AS ProductTo,
    p2.StartDate AS ToDate,
    p2.PeriodSeq - 1 AS TotalNumberOfSwitchesAtTimeOfSwitch
FROM ProductPeriods p1
JOIN ProductPeriods p2 
    ON p1.AccountID = p2.AccountID 
    AND p1.PeriodSeq = p2.PeriodSeq - 1

优化思路 #

1. 识别连续相同产品周期：通过两个ROW_NUMBER()的差值，将同一个账户连续使用的相同产品归为一组，解决往复切换场景下的分组问题。
2. 提取产品周期的起始日期：对每个分组取最早的日期作为该产品周期的开始时间，并按时间顺序为每个账户的产品周期分配序号。
3. 关联相邻周期生成切换记录：将当前周期与下一个周期关联，得到切换前后的产品信息、日期，同时用周期序号的差值计算累计切换次数。

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