代码之家  ›  专栏  ›  技术社区  ›  Manav Kotian

将不同日期范围中的类似对象分组,以获取SQL Server中的最小和最大日期

  •  0
  • Manav Kotian  · 技术社区  · 7 年前

    我有一张桌子:

    account   | onln_status |   browse status | beg_date |  end_date
    ----------+-------------+-----------------+----------+-------------
    123456789 | On          |   Y             | 1/1/2018 |  2/1/2018
    123456789 | On          |   N             | 2/2/2018 |  4/1/2018
    123456789 | On          |   Y             | 4/2/2018 |  5/1/2018
    123456789 | Off         |   N             | 5/2/2018 |  7/1/2018
    123456789 | Off         |   Y             | 7/2/2018 |  8/1/2018
    123456789 | On          |   Y             | 8/2/2018 |  10/1/2018
    123456789 | On          |   N             | 10/2/2018|  11/1/2018
    

    并需要结果显示:

    account   | onln_status |   beg_date |  end_date
    ----------+-------------+------------+------------
    123456789 | On          |   1/1/2018 |  5/1/2018
    123456789 | Off         |   5/2/2018 |  8/1/2018
    123456789 | On          |   8/2/2018 |  11/1/2018
    

    起初,我使用min(beg-date)和max(end-date),但在这种情况下不起作用:

    select 
        omsid, onln_status, min(beg_date), max(end_date)
    from 
        table
    group by 
        omsid, onln_status
    

    我还尝试在以前的online\u状态发生变化时获取唯一的号码,但无法将其添加到号码上:

     select 
         *, 
         case 
            when onln_status <> lag(onln_status) over (partition by account order by beg_date)
               then 1 
              else 0
         end as status_change
     from 
         table
    
    1 回复  |  直到 7 年前
        1
  •  1
  •   Thom A    7 年前

    这应该可以让你继续:

    CREATE TABLE Account (AccountID bigint,
                          onln_status varchar(3),
                          BrowseStatus char(1),
                          Beg_date date,
                          End_Date date);
    GO
    
    INSERT INTO Account
    SELECT A, O, B, CONVERT(date,S,101), CONVERT(date,E,101)
    FROM (
        VALUES (123456789,'On','Y','1/1/2018','2/1/2018'),
               (123456789,'On','N','2/2/2018','4/1/2018'),
               (123456789,'On','Y','4/2/2018','5/1/2018'),
               (123456789,'Off','N','5/2/2018','7/1/2018'),
               (123456789,'Off','Y','7/2/2018','8/1/2018'),
               (123456789,'On','Y','8/2/2018','10/1/2018'),
               (123456789,'On','N','10/2/2018','11/1/2018')) V(A, O, B, S, E);
    GO
    
    WITH Grps AS(
        SELECT *,
               ROW_NUMBER() OVER (ORDER BY Beg_date) - --You may need to add a PARTITION here (I.e. on AccountID)
               ROW_NUMBER() OVER (PARTITION BY onln_status ORDER BY Beg_date) AS Grp --You may need to add a PARTITION here (I.e. on AccountID)
        FROM Account)
    SELECT AccountID,
           onln_status,
           MIN(beg_date) AS beg_date,
           MAX(End_date) AS End_Date
    FROM Grps
    GROUP BY AccountID,
              onln_status,
              Grp;
    
    GO
    DROP TABLE Account;
    

    请注意我对 ROW_NUMBER() 虽然您可能需要添加更多分区。