問題描述
問題分為兩部分.如何檢查我的數據庫中缺少哪些工作日,如果缺少某些工作日,則添加它們并用最近日期的值填充該行.
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模板網!