sql - 在 SQL Server 2014 中用日期划分行

Question

我在 SQL 方面遇到问题。我有下表:

declare @t table (START_DATE datetime,
                  END_DATE datetime, 
                  GROSS_SALES_PRICE decimal(10,2)
                 );

insert into @t 
values ('2014-08-06 00:00:00.000', '2014-10-06 23:59:59.000', 29.99),
       ('2014-09-06 00:00:00.000', '2014-09-09 23:59:59.000', 32.99),
       ('2014-09-10 00:00:00.000', '2014-09-30 23:59:59.000', 32.99),
       ('2014-10-07 00:00:00.000', '2049-12-31 23:59:59.000', 34.99)

我想分隔重叠的日期。例如，我的第一行是 START_DATE 2014-08-06 和 END_DATE 2014-10-06。我们可以看到第二行和第三行的日期都在第一行的这段时间内。

所以我想将它们分开:

declare @t2 table (START_DATE datetime,
                   END_DATE datetime, 
                   GROSS_SALES_PRICE decimal(10,2)
                  );

insert into @t2 
values ('2014-08-06 00:00:00.000', '2014-09-05 23:59:59.000', 29.99),
       ('2014-09-06 00:00:00.000', '2014-09-09 23:59:59.000', 32.99),
       ('2014-09-10 00:00:00.000', '2014-09-30 23:59:59.000', 32.99),
       ('2014-10-01 00:00:00.000', '2014-10-06 23:59:59.000', 29.99),
       ('2014-10-07 00:00:00.000', '2049-12-31 23:59:59.000', 34.99)

所以第二行和第三行保持不变。第一行应包含新的 END_DATE。我们还有新的一排。 GROSS_SALES_PRICE 应保持内部期间的状态。感谢帮助。我正在使用 SQL Server 2014

Answer 1

日历/日期表可以简化这一点，但我们也可以使用 common table expression 查询来生成临时日期表。 。

从那里，我们可以将其作为间隙和岛屿式问题来解决。使用日期表并使用 outer apply() 获取 start_date 和 gross_sales_price 的最新值，我们可以识别要重新设置的组:使用两个 row_number() 进行聚合s。第一个仅按日期排序，减去另一个按我们作为最新start_date的值分区并按日期排序。

然后，您可以将公共(public)表表达式 src 的结果转储到临时表并使用它进行插入/删除，或者您可以使用 使用 merge源代码。

/* -- dates --*/
declare @fromdate datetime, @thrudate datetime;
select  @fromdate = min(start_date), @thrudate = max(end_date) from #t;
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(datetime,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
    , [End_Date]=convert(datetime,dateadd(millisecond,-3,dateadd(day,row_number() over(order by (select 1)),@fromdate)))
  from n as deka cross join n as hecto cross join n as kilo
                 cross join n as tenK cross join n as hundredK
   order by [Date]
)
/* -- islands -- */
, cte as (
select 
    start_date = d.date
  , end_date   = d.end_date
  , x.gross_sales_price
  , grp = row_number() over (order by d.date)
        - row_number() over (partition by x.start_date order by d.date)
from dates d
  outer apply (
    select top 1 l.start_date, l.gross_sales_price
    from #t l
    where d.date >= l.start_date
      and d.date <= l.end_date
    order by l.start_date desc
    ) x
)
/* -- aggregated islands -- */
, src as (
select 
    start_date = min(start_date)
  , end_date   = max(end_date)  
  , gross_sales_price
from cte
group by gross_sales_price, grp
)
/* -- merge -- */
merge #t with (holdlock) as target
using src as source
  on target.start_date = source.start_date
 and target.end_date   = source.end_date
 and target.gross_sales_price = source.gross_sales_price
when not matched by target 
  then insert (start_date, end_date, gross_sales_price)
    values (start_date, end_date, gross_sales_price)
when not matched by source 
  then delete
output $action, inserted.*, deleted.*;
/* -- results -- */
select 
    start_date
  , end_date  
  , gross_sales_price
from #t 
order by start_date

rextester 演示:http://rextester.com/MFXCQQ90933

合并输出(您不需要输出它，只是为了演示而显示):

+---------+---------------------+---------------------+-------------------+---------------------+---------------------+-------------------+
| $action |     START_DATE      |      END_DATE       | GROSS_SALES_PRICE |     START_DATE      |      END_DATE       | GROSS_SALES_PRICE |
+---------+---------------------+---------------------+-------------------+---------------------+---------------------+-------------------+
| INSERT  | 2014-10-01 00:00:00 | 2014-10-06 23:59:59 | 29.99             | NULL                | NULL                | NULL              |
| INSERT  | 2014-08-06 00:00:00 | 2014-09-05 23:59:59 | 29.99             | NULL                | NULL                | NULL              |
| DELETE  | NULL                | NULL                | NULL              | 2014-08-06 00:00:00 | 2014-10-06 23:59:59 | 29.99             |
+---------+---------------------+---------------------+-------------------+---------------------+---------------------+-------------------+

结果:

+-------------------------+-------------------------+-------------------+
|       start_date        |        end_date         | gross_sales_price |
+-------------------------+-------------------------+-------------------+
| 2014-08-06 00:00:00.000 | 2014-09-05 23:59:59.997 | 29.99             |
| 2014-09-06 00:00:00.000 | 2014-09-09 23:59:59.997 | 32.99             |
| 2014-09-10 00:00:00.000 | 2014-09-30 23:59:59.997 | 32.99             |
| 2014-10-01 00:00:00.000 | 2014-10-06 23:59:59.997 | 29.99             |
| 2014-10-07 00:00:00.000 | 2049-12-31 23:59:59.997 | 34.99             |
+-------------------------+-------------------------+-------------------+

日历和数字表引用:

• Generate a set or sequence without loops 2- Aaron Bertrand
• Creating a Date Table/Dimension in SQL Server 2008 - David Stein
• Calendar Tables - Why You Need One - David Stein
• Creating a date dimension or calendar table in SQL Server - Aaron Bertrand

合并引用:

• Use Caution with SQL Server''s MERGE Statement - Aaron Bertrand
• UPSERT Race Condition With Merge - Dan Guzman
• An Interesting MERGE Bug - Paul White
• Can I optimize this merge statement - Aaron Bertrand
• If you are using indexed views and MERGE, please read this! - Aaron Bertrand
• The Case of the Blocking Merge Statement (LCK_M_RS_U locks) - Kendra Little
• Writing t-sql merge statements the right way - David Stein