久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

找到工作日的缺失條目,并用最近日期的值填充

find the missing entries for the working days and fill the row with the values from the closest date(找到工作日的缺失條目,并用最近日期的值填充該行)
本文介紹了找到工作日的缺失條目,并用最近日期的值填充該行的處理方法,對大家解決問題具有一定的參考價值,需要的朋友們下面隨著小編來一起學習吧!

問題描述

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

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模板網!

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯系我們刪除處理,感謝您的支持!

相關文檔推薦

Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個子標記轉換為具有多個分隔符的單列-SQL Server (3))
How can I create a view from more than one table?(如何從多個表創建視圖?)
Create calculated value based on calculated value inside previous row(根據前一行內的計算值創建計算值)
How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何將表格的前兩列堆疊成一列,但也僅將第三列與第一列配對?) - IT屋-程序員軟件開發技
Recursive t-sql query(遞歸 t-sql 查詢)
Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱轉換為日期/月份編號(問題和答案的組合))
主站蜘蛛池模板: 日韩在线观看一区二区三区 | 97色在线观看免费视频 | www日本在线播放 | 一区二区伦理电影 | 国产精品成人av | 欧洲色 | 日韩中文字幕在线观看 | 91在线观看视频 | 国产97视频在线观看 | 亚洲成人第一页 | 性做久久久久久免费观看欧美 | 成人免费黄色 | 久久国产精品无码网站 | 一区二区精品在线 | 免费观看av网站 | 国产一区二 | 国产片侵犯亲女视频播放 | 国产精品久久777777 | 欧美日韩电影一区二区 | 精品一区二区三区免费视频 | 久久国产精品网站 | 国产精品二区三区在线观看 | 国产精品美女久久久久久免费 | 亚洲第一网站 | 99精品免费在线观看 | 色网在线播放 | 国产精品亚洲一区二区三区在线观看 | 久草热线| 国产综合久久久久久鬼色 | 欧美综合色 | 亚洲区视频| 国产精品久久av | 91在线资源 | 欧美在线观看免费观看视频 | 中文字幕免费视频 | 成人免费av在线 | 精品毛片 | 日韩精品一区二区三区中文字幕 | 久久国产精品一区 | 国产精品成人在线 | 91国内视频在线 |