主页 / dba / 问题 / 255827
Accepted
TMilliman
Asked: 2019-12-18 13:35:12 +0800 CST

查找日期不相交的记录

  • 772

考虑下表，

CREATE TABLE temp ( 
    [TabName] VARCHAR(255),
    [ID] VARCHAR(255),
    [AsOfDate] DATE)

  INSERT INTO temp VALUES 
    ('TAB1', 'C103', '2019-05-01'),
    ('TAB1', 'C103', '2019-06-01'),
    ('TAB2', 'C103', '2019-06-01'),
    ('TAB2', 'C103', '2019-07-01'),
    ('TAB1', 'C103', '2019-09-01'),
    ('TAB1', 'C104', '2019-06-01'),
    ('TAB1', 'C104', '2019-08-01')

TabName     ID      AsOfDate      
TAB1        C103     '2019-05-01'            
TAB1        C103     '2019-06-01'                        
TAB2        C103     '2019-06-01'                                    
TAB2        C103     '2019-07-01'     
TAB1        C103     '2019-09-01'
TAB1        C104     '2019-06-01'
TAB1        C104     '2019-08-01'
TAB1        C105     '2019-04-01'
TAB1        C105     '2019-05-01'

我想从表中查找已跳过日期的 ID。在此表中，我想确定 ID C103 和 C104 已跳过日期，因为它们已从“2019-07-01”跳到“2019-09-01”和“2019-06-01”跳到“2019-8” -01'。

我发现之前提出的以下问题Find Missing Dates in Data我相信这为该方法提供了一些潜在的线索，即使用 CTE，但是，我不确定如何将它们应用于整个表中日期不连续的问题.

我们应该在这里查看分区吗？

sql-server

2 个回答

  1. Best Answer

    您可以使用递归 CTE 来实现这一点，并结合两个窗口函数（ROW_NUMBER和RANK）来生成当前日期值和前一个值之间的日期差的运行总计（按标签名和 id 组）。然后，您只选择日期差大于 1 的那些行。

    如果您使用的是 SQL Server 2012 或更高版本，则有一种更简单的方法。使用LAG函数检索先前的值（按选项卡名和 id 分组）并对其执行 DATEDIFF。

    下面包括示例，您可以在此db<>fiddle中看到它们的实际作用。

    滞后示例：

    SELECT
  TabName,
  Id,
  AsOfDate
FROM
(
  SELECT
    TabName,
    Id,
    AsOfDate,
    ISNULL(DATEDIFF(DAY, LAG(AsOfDate) OVER (PARTITION BY TabName, Id ORDER BY AsOfDate), AsOfDate), 0) AS PrevDateDiff
  FROM Temp
) t
WHERE t.PrevDateDiff > 1
ORDER BY tabname, id, asofdate

    递归 CTE 示例：

    ;WITH CTE AS (
  SELECT
    TabName,
    Id,
    AsOfDate,
    RANK() OVER (ORDER BY TabName, Id) AS Grp,
    ROW_NUMBER() OVER (PARTITION BY TabName, Id ORDER BY AsOfDate) AS Rn
  FROM Temp
), CTE2 AS
(
  SELECT c1.TabName,
    c1.Id,
    c1.AsOfDate,
    c1.Grp,
    c1.Rn,
    0 AS DateDiff
  FROM CTE c1
  WHERE c1.Rn = 1
  UNION ALL
  SELECT c1.TabName,
    c1.Id,
    c1.AsOfDate,
    c1.Grp,
    c1.Rn,
    DATEDIFF(DAY, c2.AsOfDate, c1.AsOfDate) AS DateDiff
  FROM CTE c1
  INNER JOIN CTE2 c2 ON c2.Rn = c1.Rn - 1 AND c2.Grp = c1.Grp
  WHERE c1.Rn > 1
)

SELECT
  TabName,
  Id,
  AsOfDate
FROM CTE2
WHERE DateDiff > 1
ORDER BY tabname, id, asofdate
    • 2

  2. 首先创建日历表。

    以您想要的任何方式填充。

    create table CalendarDate(Dates DateTime primary key)
insert into CalendarDate WITH (TABLOCK)  (Dates)
select top (1000000) 
DATEADD(day, ROW_NUMBER()over(order by (select null))-1,'1970-01-01') DT
from sys.objects

    然后，

    CREATE TABLE #RealTable ( 
    [TabName] VARCHAR(255),
    [ID] VARCHAR(255),
    [AsOfDate] DATE)

  INSERT INTO #RealTable VALUES 
    ('TAB1', 'C103', '2019-05-01'),
    ('TAB1', 'C103', '2019-05-02'),
    ('TAB2', 'C103', '2019-05-03'),
    ('TAB2', 'C103', '2019-05-05'),
    ('TAB1', 'C103', '2019-05-06'),
    ('TAB1', 'C104', '2019-06-01'),
    ('TAB1', 'C104', '2019-06-03')


;with CTE as
(
select [ID],max(AsOfDate) MxDT,min(AsOfDate)MinDt 
from  #RealTable
group by [ID]
)
,CTE1 as
(
select cd.Dates,c.id 
from CalendarDate cd,CTE c
where cd.Dates>=MinDt and  cd.Dates<=MxDt
)
select * from CTE1 c
where 
not exists
(select * from #RealTable t 
where c.ID=t.ID and c.Dates=t.AsOfDate )


drop table #RealTable
    • 1

相关问题