在大約 250 萬行的表中查找缺失的數據間隙
我在一家經濟諮詢公司工作，我們所有的數據都儲存在 SQL Server 11 (2012) 中。我們的數據基本上由一個日期列、一個原始數據列、幾個計算列和一個列組成，該列有一個短程式碼來區分每組數據和下一組數據。
我們有數千個這樣的集合，它們都載入到同一個表中（總共約 250 萬行），然後從查詢中提取並排序。它們通常按日期排序，從第一個月開始，逐月移動，直到最後一個月。每個系列的開始和結束日期從一年到 100 多年不等。
最近，我們遇到了一些隨機數據部分消失的問題。整行剛起來就離開了，這使得找到這些缺失的行有點挑戰，而不需要每個月都梳理一下是否缺失一個月，這對於一個 250 萬行的表來說是一項不可能完成的任務。
我的老闆要求我編寫一個查詢/儲存過程，該過程將查看這個巨大的表並查看哪些集合缺少行以及它們在哪裡。
我一直在嘗試解決這個問題，這個問題略高於我的 SQL 技能，我似乎無法在網路上的任何地方找到任何遇到類似問題的人。我將經歷我已經擁有的東西，也許有人至少可以告訴我我是否朝著正確的方向前進，並可能提供一些關於我應該從這裡去哪裡的見解。
我可以在網上找到的最佳解決方案是使用 CTE 創建一個臨時日期表，然後將它們與原始表進行比較。如果我只是在一個特定數據集中掃描問題，這非常有用，但是我在同一個表中有許多數據集，所有數據集都有不同的開始和結束日期。所以無論如何我還是選擇了它，希望我最終可以擴展它來搜尋它們中的很多。這是我的程式碼：
declare @startDate Date, @endDate Date set @startDate = '2000-01-01' set @endDate = '2016-11-01' ;with GetDates As ( select @startDate as TheDate UNION ALL select DATEADD(MONTH,1, TheDate) from GetDates where DATEADD(MONTH,1, TheDate) <= @endDate ) SELECT TheDate,SHORTCODE,MonthYear From GetDates LEFT OUTER JOIN VWTBL_INDICATOR ON GetDates.TheDate=VWTBL_INDICATOR.MonthYear AND VWTBL_INDICATOR.SHORTCODE='RMI WEST' OPTION(MAXRECURSION 1000)‘RMI West’ 將是標記此特定數據集的簡碼，它缺少從 2004 年 11 月到 2005 年 3 月的數據，當我執行此查詢時，這些數據顯示為空。這幾乎正是我所需要的，但對於我在表中擁有的每個數據集。
如何正確編寫此查詢？我們公司將我們數據庫上的大量工作外包出去，所以我對它的內容並不十分熟悉。我們有一個上傳功能，當我使用它提取數據時，一切都很好。但是當我在一兩週後查看數據時，它缺少隨機日期。他們一直在尋找解決方案，但同時我們需要一種方法來找到這些差距。
我們將始終檢查幾個月。我們的數據幾乎總是從給定年份的 1 月開始，到當月結束，如果數據還沒有出來，則在當月結束。
我有一個短程式碼的元數據表，但是每個短程式碼的預期月數不包括在內，因為到目前為止我們還沒有使用過這些資訊。我可以詢問添加欄位並修改更新腳本以在更新數據時包含它們，如果這對於使其正常工作至關重要。
我工作的公司歷來將其所有數據儲存在大量 Excel 電子表格中。他們一直在將所有這些數據轉換為 SQL。我的工作是通過各種方法檢查它，以確保 SQL Server 上的數據與原始電子表格中的數據匹配。原始數據可通過我們文件系統上的電子表格網路訪問。在更新數據的時候，我們還是使用 Excel 來更新資訊，然後使用我之前提到的上傳功能在 SQL 中載入。一段時間以來，數據基本上是乾淨和匹配的。僅在過去幾個月中，才出現了這種數據差距問題。
無需生成日期。
以下查詢將為您提供一個沒有行的 SHORTCODES 列表：
select SHORTCODE from shortcodes except select SHORTCODE from VWTBL_INDICATOR以下查詢將為您提供每個 SHORTCODE 的 MonthYear 的連續範圍。
select SHORTCODE ,min(MonthYear) as from_MonthYear ,max(MonthYear) as to_MonthYear ,count(*) as months from (SELECT SHORTCODE ,MonthYear ,row_number() over (partition by SHORTCODE order by MonthYear) as rn From VWTBL_INDICATOR ) t group by SHORTCODE ,DATEADD(month,-rn,MonthYear) order by SHORTCODE ,from_MonthYear如果您願意，可以使用以下具有附加資訊層的版本：
- missing_from_MonthYear + to_MonthYear：中間缺失範圍
- 範圍：每個 SHORTCODE 的範圍數（範圍>1 表示中間有間隙）
- range_seq：每個 SHORTCODE 範圍的序號
- is_first：每個 SHORTCODE 的第一個範圍的指示（檢查 from_MonthYear 以查看您是否缺少前面的日期）
- is_last：每個 SHORTCODE 的最後一個範圍的指示（檢查 to_MonthYear 以查看您是否缺少以下日期）
select SHORTCODE ,from_MonthYear as exists_from_MonthYear ,to_MonthYear as exists_to_MonthYear ,dateadd (day,1,to_MonthYear) as missing_from_MonthYear ,dateadd (day,-1,lead (from_MonthYear) over (partition by SHORTCODE order by from_MonthYear)) as missing_to_MonthYear ,count (*) over (partition by SHORTCODE) as ranges ,row_number () over (partition by SHORTCODE order by from_MonthYear) as range_seq ,case from_MonthYear when min(from_MonthYear) over (partition by SHORTCODE) then 1 end as is_first ,case to_MonthYear when max(to_MonthYear) over (partition by SHORTCODE) then 1 end as is_last from (select SHORTCODE ,min(MonthYear) as from_MonthYear ,max(MonthYear) as to_MonthYear ,count(*) as months from (SELECT SHORTCODE ,MonthYear ,row_number() over (partition by SHORTCODE order by MonthYear) as rn From VWTBL_INDICATOR ) t group by SHORTCODE ,DATEADD(month,-rn,MonthYear) ) t order by SHORTCODE ,from_MonthYear