問題描述
我正在嘗試找到一種方法，將每月百分比變化應用于預測定價.我在 excel 中設置了我的問題，使其更清楚一些.我使用的是 SQL Server 2017.
I'm trying to find a way to apply monthly percentage changes to forecast pricing. I set my problem up in excel to make it a bit more clear. I'm using SQL Server 2017.
我們會說 18 年 9 月 1 日之前的所有月份都是歷史月份，而 2018 年 9 月 1 日及以后的月份都是預測月份.我需要使用...計算預測價格(樣本數據上的黃色陰影)
We'll say all months before 9/1/18 are historical and 9/1/18 and beyond are forecasts. I need to calculate the forecast price (shaded in yellow on the sample data) using...
Forecast Price = (Previous Row Forecast Price * Pct Change) + Previous Row Forecast Price
需要說明的是，我的數據中尚不存在黃色陰影價格.這就是我試圖讓我的查詢計算.由于這是每月百分比變化，每一行都依賴于前一行并且超出了單個 ROW_NUMBER/PARTITION 解決方案，因為我們必須使用之前計算出的價格.顯然，excel 中的簡單順序計算在這里有點困難.知道如何在 SQL 中創建預測價格列嗎?
Just to be clear, the yellow shaded prices do not exist in my data yet. That is what I am trying to have my query calculate. Since this is monthly percentage change, each row depends on the row before and goes beyond a single ROW_NUMBER/PARTITION solution because we have to use the previous calculated price. Clearly what is an easy sequential calculation in excel is a bit more difficult here. Any idea how to create forecasted price column in SQL?
推薦答案
您需要使用遞歸 CTE.這是查看前一行計算值的一種更簡單的方法:
You need to use a recursive CTE. That is one of the easier ways to look at the value of a calculated value from previous row:
DECLARE @t TABLE(Date DATE, ID VARCHAR(10), Price DECIMAL(10, 2), PctChange DECIMAL(10, 2));
INSERT INTO @t VALUES
('2018-01-01', 'ABC', 100, NULL),
('2018-01-02', 'ABC', 150, 50.00),
('2018-01-03', 'ABC', 130, -13.33),
('2018-01-04', 'ABC', 120, -07.69),
('2018-01-05', 'ABC', 110, -08.33),
('2018-01-06', 'ABC', 120, 9.09),
('2018-01-07', 'ABC', 120, 0.00),
('2018-01-08', 'ABC', 100, -16.67),
('2018-01-09', 'ABC', NULL, -07.21),
('2018-01-10', 'ABC', NULL, 1.31),
('2018-01-11', 'ABC', NULL, 6.38),
('2018-01-12', 'ABC', NULL, -30.00),
('2019-01-01', 'ABC', NULL, 14.29),
('2019-01-02', 'ABC', NULL, 5.27);
WITH ncte AS (
-- number the rows sequentially without gaps
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) AS rn
FROM @t
), rcte AS (
-- find first row in each group
SELECT *, Price AS ForecastedPrice
FROM ncte AS base
WHERE rn = 1
UNION ALL
-- find next row for each group from prev rows
SELECT curr.*, CAST(prev.ForecastedPrice * (1 + curr.PctChange / 100) AS DECIMAL(10, 2))
FROM ncte AS curr
INNER JOIN rcte AS prev ON curr.ID = prev.ID AND curr.rn = prev.rn + 1
)
SELECT *
FROM rcte
ORDER BY ID, rn
結果:
| Date | ID | Price | PctChange | rn | ForecastedPrice |
|------------|-----|--------|-----------|----|-----------------|
| 2018-01-01 | ABC | 100.00 | NULL | 1 | 100.00 |
| 2018-01-02 | ABC | 150.00 | 50.00 | 2 | 150.00 |
| 2018-01-03 | ABC | 130.00 | -13.33 | 3 | 130.01 |
| 2018-01-04 | ABC | 120.00 | -7.69 | 4 | 120.01 |
| 2018-01-05 | ABC | 110.00 | -8.33 | 5 | 110.01 |
| 2018-01-06 | ABC | 120.00 | 9.09 | 6 | 120.01 |
| 2018-01-07 | ABC | 120.00 | 0.00 | 7 | 120.01 |
| 2018-01-08 | ABC | 100.00 | -16.67 | 8 | 100.00 |
| 2018-01-09 | ABC | NULL | -7.21 | 9 | 92.79 |
| 2018-01-10 | ABC | NULL | 1.31 | 10 | 94.01 |
| 2018-01-11 | ABC | NULL | 6.38 | 11 | 100.01 |
| 2018-01-12 | ABC | NULL | -30.00 | 12 | 70.01 |
| 2019-01-01 | ABC | NULL | 14.29 | 13 | 80.01 |
| 2019-01-02 | ABC | NULL | 5.27 | 14 | 84.23 |
DB Fiddle 演示
這篇關于根據前一行內的計算值創建計算值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！