我需要搜索所有的值,找到丢失的一个,替换空值,并添加适当的日期,它的新值.
Date | Price |
---|---|
3/5/2020 | 40 |
5/5/2020 | null |
7/5/2020 | 30 |
30/5/2020 | null |
1/6/2020 | null |
3/6/2020 | null |
5/6/2020 | 55 |
Date | Price | PriceDate |
---|---|---|
3/5/2020 | 40 | null |
5/5/2020 | 40 | 3/5/2020 |
7/5/2020 | 30 | null |
30/5/2020 | 30 | 7/5/2020 |
1/6/2020 | 55 | 5/6/2020 |
3/6/2020 | 55 | 5/6/2020 |
5/6/2020 | 55 | null |
重点是-对于5月,我需要替换下一个值,对于6月,我需要从6月5日开始取值,并替换前几天的值. 在这两种情况下,我还需要添加包含Date From值的附加列.
我的问题是:如何从以前的日期获取值-在本例中是6月并替换它们,以及如何在该日期(PriceDate)列中添加额外的值?
我也做了一些更改,能够获得我想要的值,但我想知道如何获得这个日期的正确日期(请参见第二个表-作为输出):
https://sqlfiddle.com/sql-server/online-compiler个
到目前为止,我所做的是:
CREATE TABLE Sale (
DatePrice DATE,
Price FLOAT
);
INSERT INTO Sale (DatePrice, Price) VALUES
('2020-05-02', NULL),
('2020-05-03', 40.00),
('2020-05-05', NULL),
('2020-05-06', NULL),
('2020-05-07', 30.00),
('2020-05-30', NULL),
('2020-06-01', NULL),
('2020-06-03', NULL),
('2020-06-05', 55.00),
('2020-06-06', 60.00),
('2020-06-09', NULL),
('2020-07-02', 72.00),
('2020-07-04', NULL),
('2020-07-06', NULL),
('2020-07-07', NULL),
('2020-07-19', 80.00);
IF OBJECT_ID('tempdb..#FilledPrices') IS NOT NULL DROP TABLE #FilledPrices;
SELECT
DatePrice,
Price,
YEAR(DatePrice) AS Year,
MONTH(DatePrice) AS Month,
NULL AS FilledPrice
INTO #FilledPrices
FROM Sale;
WITH FirstPrices AS (
SELECT
Year,
Month,
MIN(DatePrice) AS FirstDate
FROM #FilledPrices
WHERE Price IS NOT NULL
GROUP BY Year, Month
)
UPDATE fp
SET fp.FilledPrice = s.Price
FROM #FilledPrices fp
INNER JOIN FirstPrices f ON fp.Year = f.Year AND fp.Month = f.Month
INNER JOIN Sale s ON s.DatePrice = f.FirstDate
WHERE fp.Price IS NULL AND fp.DatePrice <= f.FirstDate;
UPDATE fp
SET fp.FilledPrice = (
SELECT TOP 1 Price
FROM #FilledPrices
WHERE Year = fp.Year AND Month = fp.Month AND DatePrice < fp.DatePrice AND Price IS NOT NULL
ORDER BY DatePrice DESC
)
FROM #FilledPrices fp
WHERE fp.Price IS NULL AND fp.FilledPrice IS NULL;
UPDATE #FilledPrices
SET FilledPrice = Price
WHERE Price IS NOT NULL;
SELECT
DatePrice,
COALESCE(FilledPrice, Price) AS Price
FROM #FilledPrices
ORDER BY DatePrice;
我在本例中使用的是临时表.First Off-我在第一个数据记录之前的特定月份搜索空值.如果出现这种情况,请将空值替换为First Met值.在第二次迭代中,我搜索所有值,如果找到空值,则将其替换为前一条记录中的值.我的结果是,这个方法运行得很好:
DatePrice | Price |
---|---|
2020-05-02 | 40.0 |
2020-05-03 | 40.0 |
2020-05-05 | 40.0 |
2020-05-06 | 40.0 |
2020-05-07 | 30.0 |
2020-05-30 | 30.0 |
2020-06-01 | 55.0 |
2020-06-03 | 55.0 |
2020-06-05 | 55.0 |
2020-06-06 | 60.0 |
2020-06-09 | 60.0 |
2020-07-02 | 72.0 |
2020-07-04 | 72.0 |
2020-07-06 | 72.0 |
2020-07-07 | 72.0 |
2020-07-19 | 80.0 |
我需要做的最后一件事是使用PriceDate创建额外的表,并为具有空值的记录填充日期,这些记录的日期是我们从记录中获取的值,对于没有更改的记录,将日期值保留为空值-请参见示例作为POST中的第二个表作为示例.