查找连续开始/结束列的第一个匹配项

3zwjbxry  于 2021-06-23  发布在  Mysql
关注(0)|答案(2)|浏览(332)

我有一张表，里面有工作日期。每次发生变化时，都会添加一个新行—工资变化是最常见的变化。所以新的一行等于此人最后一次约会加上一（1）。如果我的工资在2014年4月1日发生变化，我的前一行将结束 TO 日期 2013-03-31 我的新一排就要开始了 FROM 日期 2014-04-01 .
我只想知道我的工作日期，不想知道由于变动而产生的日期。看看这张table：

SSN         FROM        TO
----------------------------------
0987654321  2011-01-01  2011-12-31
0987654321  2012-01-01  2012-12-31
1234567890  2012-01-01  2012-12-31
0987654321  2013-01-01  2013-12-31
1234567890  2013-01-01  2013-06-30
0987654321  2014-01-01  2014-08-31
1234567890  2016-01-01  2016-12-31
1234567890  2017-01-01  2017-12-31
1234567890  2018-01-01  null

我想要的输出：

SSN         FROM        TO
----------------------------------
0987654321  2011-01-01  2014-08-31
1234567890  2012-01-01  2013-06-30
1234567890  2016-01-01  null

我想我可以创造一个比 TO :

SELECT 
    SSN, TO, FROM, DATEADD(DAY, 1, TO) AS NEW 
FROM 
    table

但我不知道我该怎么匹配 NEW 与 TO 在不同的行上。也许 WHERE NOT EXISTS 或者别的什么？我做不到。
然后我想我可以 LAG 但默认情况下，表中的前一行与下一行不相关，因此无法使用 ORDER BY 在子查询中。我不允许，不知道为什么（t-sql？）。
仅供参考，我不能 CREATE TABLE 或者 INSERT INTO TABLE 等等，我也不能声明变量。我们将得到一个模块，允许所有这些，但现在我没有这些特权。
更新：第一个答案实际上是正确的，但我注意到另一个字段干扰了它。一个ssn可以包含多个id，因此id也必须拆分。这是我table上的实际数据。

CREATE TABLE Samples
    (
     SSN varchar(10), 
     ID varchar(4),
     FromDate Date, 
     ToDate Date
    );

INSERT INTO Samples
(SSN, ID, FromDate, ToDate)
VALUES
( '6612140000', '1000', '2005-01-01', '2005-03-31' ),
( '6612140000', '1000', '2005-04-01', '2005-09-30' ),
( '6612140000', '1000', '2005-10-01', '2006-03-31' ), 
( '6612140000', '2000', '2005-10-01', '2006-04-30' ),
( '6612140000', '1000', '2006-04-01', '2007-03-31' ),
( '6612140000', '1000', '2007-04-01', '2008-03-31' ),
( '6612140000', '1000', '2008-04-01', '2009-03-31' ),
( '6612140000', '1000', '2009-04-01', '2010-03-31' ),
( '6612140000', '1000', '2010-04-01', '2010-11-30' ),
( '6612140000', '1000', '2010-12-01', '2011-03-31' ),
( '6612140000', '1000', '2011-04-01', '2011-08-21' ),
( '6612140000', '1000', '2011-08-22', '2011-11-13' ),
( '6612140000', '1000', '2011-11-14', '2011-11-30' ),
( '6612140000', '1000', '2011-12-01', '2012-01-31' ),
( '6612140000', '1000', '2016-07-01', '2017-03-31' ),
( '6612140000', '1000', '2017-04-01', '2017-11-30' ),
( '6612140000', '1000', '2017-12-01', '2018-03-31' ),
( '6612140000', '1000', '2018-04-01', null ),
( '7605140000', '1000', '2013-11-01', '2013-11-30' ),
( '7605140000', '1000', '2013-12-01', '2013-12-31' ),
( '7605140000', '1000', '2014-01-01', '2014-03-31' ),
( '7605140000', '1000', '2014-04-01', '2014-12-31' ),
( '7605140000', '1000', '2015-05-01', '2015-05-31' ),
( '7605140000', '1000', '2015-06-01', '2015-09-30' ),
( '7605140000', '1000', '2015-10-01', '2015-10-31' ),
( '7605140000', '1000', '2016-01-25', '2016-07-24' ),
( '7605140000', '1000', '2016-07-25', '2016-08-31' ),
( '7605140000', '1000', '2016-09-01', '2017-03-31' ),
( '7605140000', '1000', '2017-04-01', '2017-11-30' ),
( '7605140000', '1000', '2017-12-01', null );

答案中的代码，我尝试添加 ID 场到，运气不好：

with

  FromDates as (
    -- All of the   FromDates   for each   SSN   for which there is not
    --   a contiguous preceding period.
    select SO.SSN, SO.ID, SO.FromDate, SO.ToDate,
      Row_Number() over ( partition by SO.SSN order by SO.FromDate ) as RN
      from Samples as SO
      where not exists (
        select 42 from Samples as SI where SI.SSN = SO.SSN and SI.ID = SO.ID and
          SI.ToDate = DateAdd( day, -1, SO.FromDate ) ) ),

  ToDates as (
    -- All of the   ToDates   for each   SSN   for which there is not
    --   a contiguous following period.
    select SSN, ID, FromDate, ToDate, Row_Number() over ( partition by SSN order by FromDate ) as RN
      from Samples as SO
      where not exists (
        select 42 from Samples as SI where SI.SSN = SO.SSN and SI.ID = SO.ID and
          SI.FromDate = DateAdd( day, 1, SO.ToDate ) ) ),

  Ranges as (
    -- Pair the   FromDate   and   ToDate   entries for each   SSN .
    select F.SSN, F.ID, F.FromDate, T.ToDate
      from FromDates as F inner join
        ToDates as T on T.SSN = F.SSN and T.ID = F.ID and T.RN = F.RN ) 

-- Use any ONE of the following   select   statements to see what is going on:
-- select * from FromDates
--  select * from ToDates
  select * from Ranges 
  -- where SSN = '6612140000'
  order by SSN, ID, FromDate

退货：

SSN         ID      FromDate    ToDate
6612140000  1000    2016-07-01  (null)
7605140000  1000    2013-11-01  2014-12-31
7605140000  1000    2014-03-01  2014-12-31
7605140000  1000    2015-05-01  2015-10-31
7605140000  1000    2015-05-01  2015-10-31
7605140000  1000    2016-01-25  (null)
mysqlsql-servertsql

2条答案

按热度按时间
mrphzbgm

mrphzbgm1#

下面的示例根据您的数据组装孤岛。通过改变最后的 select 如果语句被启用/注解，则可以看到流程中的中间结果。
更新：更改了cte中的日期比较，以便它们可以从上的索引中获益 SSN, FromDate 以及 SSN, ToDate .

-- Sample data.
declare @Samples table ( SSN VarChar(10), FromDate Date, ToDate Date );
insert into @Samples ( SSN, FromDate, ToDate ) values
  ( '0987654321', '2011-01-01', '2011-12-31' ),
  ( '0987654321', '2012-01-01', '2012-12-31' ),
  ( '1234567890', '2012-01-01', '2012-12-31' ),
  ( '0987654321', '2013-01-01', '2013-12-31' ),
  ( '1234567890', '2013-01-01', '2013-06-30' ),
  ( '0987654321', '2014-01-01', '2014-08-31' ),
  ( '1234567890', '2016-01-01', '2016-12-31' ),
  ( '1234567890', '2017-01-01', '2017-12-31' ),
  ( '1234567890', '2018-01-01', null );
select *
  from @Samples;

-- Sample data made a little easier to read.
select *,
  case when exists (
    select 42 from @Samples as SI where SI.SSN = S.SSN and
      DateDiff( day, S.ToDate, SI.FromDate ) = 1 ) then 1 else 0 end as Continued
  from @Samples as S
  order by SSN, FromDate;

-- Process the data.
with
  FromDates as (
    -- All of the   FromDates   for each   SSN   for which there is not
    --   a contiguous preceding period.
    select SO.SSN, SO.FromDate, SO.ToDate,
      Row_Number() over ( partition by SO.SSN order by SO.FromDate ) as RN
      from @Samples as SO
      where not exists (
        select 42 from @Samples as SI where SI.SSN = SO.SSN and
          SI.ToDate = DateAdd( day, -1, SO.FromDate ) ) ),
  ToDates as (
    -- All of the   ToDates   for each   SSN   for which there is not
    --   a contiguous following period.
    select SSN, FromDate, ToDate, Row_Number() over ( partition by SSN order by FromDate ) as RN
      from @Samples as SO
      where not exists (
        select 42 from @Samples as SI where SI.SSN = SO.SSN and
          SI.FromDate = DateAdd( day, 1, SO.ToDate ) ) ),
  Ranges as (
    -- Pair the   FromDate   and   ToDate   entries for each   SSN .
    select F.SSN, F.FromDate, T.ToDate
      from FromDates as F inner join
        ToDates as T on T.SSN = F.SSN and T.RN = F.RN )
  -- Use any ONE of the following   select   statements to see what is going on:
--  select * from FromDates order by SSN, FromDate;
--  select * from ToDates order by SSN, FromDate;
  select * from Ranges order by SSN, FromDate;

当然，如果真的有 Id 内的值 SSN 如果要单独处理，答案会变成这样：

-- Sample data.
declare @Samples as Table ( SSN VarChar(10), Id VarChar(4), FromDate Date, ToDate Date );
insert into @Samples ( SSN, ID, FromDate, ToDate ) values
    ( '6612140000', '1000', '2005-01-01', '2005-03-31' ),
    ( '6612140000', '1000', '2005-04-01', '2005-09-30' ),
    ( '6612140000', '1000', '2005-10-01', '2006-03-31' ), 
    ( '6612140000', '2000', '2005-10-01', '2006-04-30' ),
    ( '6612140000', '1000', '2006-04-01', '2007-03-31' ),
    ( '6612140000', '1000', '2007-04-01', '2008-03-31' ),
    ( '6612140000', '1000', '2008-04-01', '2009-03-31' ),
    ( '6612140000', '1000', '2009-04-01', '2010-03-31' ),
    ( '6612140000', '1000', '2010-04-01', '2010-11-30' ),
    ( '6612140000', '1000', '2010-12-01', '2011-03-31' ),
    ( '6612140000', '1000', '2011-04-01', '2011-08-21' ),
    ( '6612140000', '1000', '2011-08-22', '2011-11-13' ),
    ( '6612140000', '1000', '2011-11-14', '2011-11-30' ),
    ( '6612140000', '1000', '2011-12-01', '2012-01-31' ),
    ( '6612140000', '1000', '2016-07-01', '2017-03-31' ),
    ( '6612140000', '1000', '2017-04-01', '2017-11-30' ),
    ( '6612140000', '1000', '2017-12-01', '2018-03-31' ),
    ( '6612140000', '1000', '2018-04-01', null ),
    ( '7605140000', '1000', '2013-11-01', '2013-11-30' ),
    ( '7605140000', '1000', '2013-12-01', '2013-12-31' ),
    ( '7605140000', '1000', '2014-01-01', '2014-03-31' ),
    ( '7605140000', '1000', '2014-03-01', '2014-12-31' ),
    ( '7605140000', '1000', '2014-04-01', '2014-12-31' ),
    ( '7605140000', '1000', '2015-05-01', '2015-05-31' ),
--  ( '7605140000', '1000', '2015-05-01', '2015-05-31' ), -- Duplicate row?!
    ( '7605140000', '1000', '2015-06-01', '2015-09-30' ),
--  ( '7605140000', '1000', '2015-06-01', '2015-09-30' ), -- Duplicate row?!
    ( '7605140000', '1000', '2015-10-01', '2015-10-31' ),
--  ( '7605140000', '1000', '2015-10-01', '2015-10-31' ), -- Duplicate row?!
    ( '7605140000', '1000', '2016-01-25', '2016-07-24' ),
    ( '7605140000', '1000', '2016-07-25', '2016-08-31' ),
    ( '7605140000', '1000', '2016-09-01', '2017-03-31' ),
    ( '7605140000', '1000', '2017-04-01', '2017-11-30' ),
    ( '7605140000', '1000', '2017-12-01', null );
select *
  from @Samples;

-- Sample data made a little easier to read.
select *,
  case when exists (
    select 42 from @Samples as SI where SI.SSN = S.SSN and SI.Id = S.Id and
      DateDiff( day, S.ToDate, SI.FromDate ) = 1 ) then 1 else 0 end as Continued
  from @Samples as S
  order by SSN, Id, FromDate;

-- Process the data.
with
  FromDates as (
    -- All of the   FromDates   for each   SSN   for which there is not
    --   a contiguous preceding period.
    select SO.SSN, SO.Id, SO.FromDate, SO.ToDate,
      Row_Number() over ( partition by SO.SSN, SO.Id order by SO.FromDate ) as RN
      from @Samples as SO
      where not exists (
        select 42 from @Samples as SI where SI.SSN = SO.SSN and SI.Id = SO.Id and
          SI.ToDate = DateAdd( day, -1, SO.FromDate ) ) ),
  ToDates as (
    -- All of the   ToDates   for each   SSN   for which there is not
    --   a contiguous following period.
    select SO.SSN, SO.Id, SO.FromDate, SO.ToDate,
      Row_Number() over ( partition by SSN, SO.Id order by FromDate ) as RN
      from @Samples as SO
      where not exists (
        select 42 from @Samples as SI where SI.SSN = SO.SSN and SI.Id = SO.Id and
          SI.FromDate = DateAdd( day, 1, SO.ToDate ) ) ),
  Ranges as (
    -- Pair the   FromDate   and   ToDate   entries for each   SSN .
    select F.SSN, F.Id, F.FromDate, T.ToDate
      from FromDates as F inner join
        ToDates as T on T.SSN = F.SSN and T.Id = F.Id and T.RN = F.RN )
  -- Use any ONE of the following   select   statements to see what is going on:
--  select * from FromDates order by SSN, Id, FromDate;
--  select * from ToDates order by SSN, Id, FromDate;
  select * from Ranges order by SSN, Id, FromDate;
赞(0）分享  回复(0）举报  2021-06-23
p4tfgftt

p4tfgftt2#

这是一个缺口和孤岛问题，标准解决方案基于嵌套的分析函数：

1:将每行与前一行进行比较，并在新组启动时将其标记为1。

2:计算标志上的累计和，为每组行分配一个数字。

他说：现在你可以对这些小组做任何你想做的事情。

-- data must be correct, i.e. a Slowly Changing Dimension without gaps or overlapping periods
with calcFlag as
 (
   select SSN, Id, FromDate, ToDate,
      -- new group starts when the previous end date
      -- is not the current start date -1
      case when lag(ToDate)
                over (partition by SSN, Id
                      order by FromDate ) = DateAdd( day, -1, FromDate )
           then 0
           else 1
      end as flag
   from samples
 ),
calcGroup as 
 (
   select SSN, Id, FromDate, ToDate, flag,
      -- Cumulative Sum to dynamically assign group number
      sum(flag)
      over ( partition by SSN, Id 
             order by FromDate 
             rows unbounded preceding ) as grp#
   from calcFlag
 )
select SSN, Id, 
   min(FromDate), 
   -- either max date or NULL 
   nullif(max(coalesce(ToDate, '9999-12-31')), '9999-12-31')
from calcGroup
group by SSN, Id, grp# -- include dynamically calculated group number
order by SSN, Id, min(FromDate)
;
赞(0）分享  回复(0）举报  2021-06-23
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com