問題描述

問題分為兩部分.如何檢查我的數據庫中缺少哪些工作日，如果缺少某些工作日，則添加它們并用最近日期的值填充該行.

The problem splits into two parts. How to check which working days are missing from my database, if some are missing then add them and fill the row with the values from the closest date.

第一部分，檢查并找到日期.我應該使用下面示例中的間隙方法嗎?

First part, check and find the days. Should i use a gap approach like in the example below?

SELECT t1.col1 AS startOfGap, MIN(t2.col1) AS endOfGap  
   FROM  
   (SELECT col1 = theDate + 1  FROM sampleDates tbl1  
      WHERE NOT EXISTS(SELECT * FROM sampleDates tbl2  
                      WHERE tbl2.theDate = tbl1.theDate + 1) 
      AND theDate <> (SELECT MAX(theDate) FROM sampleDates)) t1 
   INNER JOIN  
   (SELECT col1 = theDate - 1  FROM sampleDates tbl1  
      WHERE NOT EXISTS(SELECT * FROM sampleDates tbl2  
                      WHERE tbl1.theDate = tbl2.theDate + 1) 
      AND theDate <> (SELECT MIN(theDate) FROM sampleDates)) t2  
   ON t1.col1 <= t2.col1 
   GROUP BY t1.col1; 

然后我需要查看哪個日期與我丟失的日期最接近，并用最近的值填充新插入的日期(丟失的日期).前段時間，我想出了一些方法來從一行中獲得最接近的值，但這次我需要調整它以檢查向下和向上.

Then i need to see which is the closest date to the one i was missing and fill the new inserted date (the one which was missing) with the values from the closest. Some time ago, I came up with something to get the closest value from a row, but this time i need to adapt it to check both down and upwards.

SELECT
t,A, C,Y,
COALESCE(Y, 
            (SELECT TOP (1) Y  
            FROM tableT  AS p2 
            WHERE
                  p2.Y IS NOT NULL 
                  AND p2.[t] <= p.[t] and p.C = p2.C
 ORDER BY p2.[t] DESC)) as 'YNew'
FROM tableT AS p
order by c, t

如何將它們合二為一?

謝謝

預期結果

  Date          1mA 
20.12.2012    0.152
21.12.2012    0.181 
22 weekend so it's skipped (they are skipped automatically)  
23 weekend -,- 
24 missing  
25 missing 
26 missing
27.12.2012    0.173
28.12.2012    0.342


  Date          1mA 
20.12.2012    0.152
21.12.2012    0.181 
22 weekend so it's skipped (they are skipped automatically)  
23 weekend    0.181
24 missing    0.181
25 missing    0.181
26 missing    0.173
27.12.2012    0.173
28.12.2012    0.342

因此，24,25,26 甚至不存在空值.他們根本不在那里.

So, 24,25,26 are not even there with null values. They are simply not there.

編輯 2:為了取最接近的值，讓我們考慮一下我一直在上面看的場景.所以當它丟失時總是返回 1.

EDIT 2: For taking the closest value, let's consider the scenario in which i'm always looking above. So always going back 1 when it's missing.

Date          1mA 
    20.12.2012    0.152
    21.12.2012    0.181 
    22 weekend so it's skipped (they are skipped automatically)  
    23 weekend    0.181
    24 missing    0.181
    25 missing    0.181
    26 missing    0.181 
    27.12.2012    0.173
    28.12.2012    0.342

推薦答案

對于這些類型的查詢，您可以通過創建包含您需要測試的每個日期的日歷表獲得顯著的性能優勢.(如果您熟悉術語維度表"，這只是一個用于枚舉每個感興趣日期的表格.)

For these types of query you gain significant performance benefits from creating a calendar table containing every date you'll ever need to test. (If you're familiar with the term "dimension tables", this is just one such table to enumerate every date of interest.)

此外，整個查詢可以變得非常簡單.

Also, the query as a whole can become significantly simpler.

SELECT
   cal.calendar_date   AS data_date,
   CASE WHEN prev_data.gap <= next_data.gap
        THEN prev_data.data_value
        ELSE COALESCE(next_data.data_value, prev_data.data_value)
   END
       AS data_value
FROM
    calendar   AS cal
OUTER APPLY
(
    SELECT TOP(1)
        data_date,
        data_value,
        DATEDIFF(DAY, data_date, cal.calendar_date)   AS gap
    FROM
        data_table
    WHERE
        data_date <= cal.calendar_date
    ORDER BY
        data_date DESC
)
   prev_data
OUTER APPLY
(
    SELECT TOP(1)
        data_date,
        data_value,
        DATEDIFF(DAY, cal.calendar_date, data_date)   AS gap
    FROM
        data_table
    WHERE
        data_date >  cal.calendar_date
    ORDER BY
        data_date ASC
)
   next_data
WHERE
   cal.calendar_date BETWEEN '2015-01-01' AND '2015-12-31'
;

編輯以不同的要求回復您的評論

EDIT Reply to your comment with a different requirement

始終獲得上面的值"更容易，并且將這些值插入到表中也很容易...

To always get "the value above" is easier, and to insert those values in to a table is easy enough...

INSERT INTO
    data_table
SELECT
   cal.calendar_date,
   prev_data.data_value
FROM
    calendar   AS cal
CROSS APPLY
(
    SELECT TOP(1)
        data_date,
        data_value
    FROM
        data_table
    WHERE
        data_date <= cal.calendar_date
    ORDER BY
        data_date DESC
)
   prev_data
WHERE
       cal.calendar_date BETWEEN '2015-01-01' AND '2015-12-31'
   AND cal.calendar_date <> prev_data.data_date
;

注意:您可以添加 WHERE prev_data.gap >0 到上面更大的查詢，只獲取沒有數據的日期.

Note: You could add WHERE prev_data.gap > 0 to the bigger query above to only get dates that don't already have data.

這篇關于找到工作日的缺失條目，并用最近日期的值填充該行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！