|
|
很快，我将不得不在几天内准备一份商品价格清单。粒度为1天。在商品销售的日子里，我将以平均价格获得当天的平均价格。有时没有销售，所以我非常适合使用足够的近似值通过上一次和下一次销售，价格从一个线性变化到另一个线性变化。# H# b3 v: @2 R+ ^: @0 J7 D/ [! Z# m
想象一下原始数据：
; S$ C. p/ w; jItem Date PriceBread 2000-01-01 10Bread 2000-01-02 9.5Bread 2000-01-04 9.1Sugar 2000-01-01 100Sugar 2000-01-11 150我可以到这里：
7 V& c/ S" M6 T1 nItem Date PriceBread 2000-01-01 10Bread 2000-01-02 9.5Bread 2000-01-03 NULLBread 2000-01-04 9.1Sugar 2000-01-01 100Sugar 2000-01-02 NULLSugar 2000-01-03 NULLSugar 2000-01-04 NULLSugar 2000-01-05 NULLSugar 2000-01-06 NULLSugar 2000-01-07 NULLSugar 2000-01-08 NULLSugar 2000-01-09 NULLSugar 2000-01-10 NULLSugar 2000-01-11 150我想去的地方是:
0 Y1 b' o- W) t! c% c4 FItem Date PriceBread 2000-01-01 10Bread 2000-01-02 9.5Bread 2000-01-03 9.3 --being 9.5 ((9.1 - 9.5 / 2) * 1)Bread 2000-01-04 9.1Sugar 2000-01-01 100Sugar 2000-01-02 105 --being - 100 / 10* 1)Sugar 2000-01-03 110 --being - 100 / 10* 2)Sugar 2000-01-04 115Sugar 2000-01-05 120Sugar 2000-01-06 125Sugar 2000-01-07 130Sugar 2000-01-08 135Sugar 2000-01-09 140Sugar 2000-01-10 145 --being - 100 / 10* 9)Sugar 2000-01-11 150到目前为止，我尝试了什么？只想想；我正计划做类似的事情：
1 C6 k; ^4 _2 u$ |8 ]) r提取原始数据
" K; G8 S$ k' ^+ D" x添加数字/日历表填充稀疏数据
% Q5 J i& V; O$ G; `; pLAST_VALUE()(或第一个？)开始/跟随(具有nulls-last order子句)从原始数据中获取第一个非空before_date，beforeing_date，previous_price和following_price+ }- H$ T4 c! E1 H9 _7 t/ \/ `: ]
DATEDIFF假日期和foreign_date获得天数(实际上是跨越差距的距离)gap_progress）和差距距离（following_date-previous_date） w9 q$ f0 c& ~* f
获取公式的下一个价格，上一个价格和间隔距离（preceding_price （（next_price-previous_price）/ gap_distance）* gap_progress）然而，我想知道是否有一个更简单的方法，因为我有数百万个项目日，而且它似乎不那么有效。4 ], e/ D" [. x! |" f' @8 W# ?7 q
我发现了很多这样的问题例子，其中最后一行或下一行的数据被逐一抹去以填补空白，但我不记得看到过这种尝试过渡。该技术可以通过向前涂抹、复制最新值和向后涂抹来双重应用：" r _1 G+ s7 }0 ^9 L0 E- m) e
Item Date DateFwd DateBak PriceF PriceBBread 2000-01-01 2000-01-01 2000-01-01 10 10Bread 2000-01-02 2000-01-02 2000-01-02 9.5 9.5Bread 2000-01-03 2000-01-02 2000-01-04 9.5 9.1Bread 2000-01-04 2000-01-04 2000-01-04 9.1 9.1Sugar 2000-01-01 2000-01-01 2000-01-01 100 10000Sugar 2000-01-02 2000-01-01 2000-01-11 100 150Sugar 2000-01-03 2000-01-01 2000-01-11 100 150Sugar 2000-01-04 2000-01-01 2000-01-11 100 150Sugar 2000-01-05 2000-01-01 2000-01-11 100 150Sugar 2000-01-06 2000-01-01 2000-01-11 100 150Sugar 2000-01-07 2000-01-01 2000-01-11 100 150Sugar 2000-01-08 2000-01-01 2000-01-11 100 150Sugar 2000-01-09 2000-01-01 2000-01-11 100 150Sugar 2000-01-10 2000-01-01 2000-01-11 100 150Sugar 2000-01-11 2000-01-11 2000-01-11 150 150这些可能为公式提供必要的数据 (preceding_price ((next_price -preceding_price)/gap_distance) * gap_progress)：5 T) T2 }/ }" E2 H2 q7 f' \6 C6 x
gap_distance = DATEDIFF（day，DateFwd，DateBak）3 Y2 K: e" w' n& k' L6 D
gap_progress = DATEDIFF(天，日期，DateFwd）
5 k; g6 a8 G8 [& D( ]5 snext_price = PriceB# }8 p3 }7 y* ?' a8 H, s0 C
previous_price = PriceF？
8 S4 r! }+ [$ A- @这是我知道可以获取的数据的DDL(结合日历表的原始数据)3 |# {7 L, k3 _0 |6 j: F
CREATE TABLE Data([I] varchar(5),[D] date,[P] DECIMAL(10,5));INSERT Data([I],[D],[P])VALUES('Bread','2000-01-(10)，Bread','2000-01-02',9.5),('Bread','2000-01-04',9.1),('Sugar','2000-01-(100)，Sugar','2000-01-11',150);CREATE TABLE Cal([D] DATE);INSERT Cal VALUES('2000-01-01'),('2000-01-02'),('2000-01-03'),('2000-01-04'),('2000-01-05'),('2000-01-06'),('2000-01-07'),('2000-01-08'),('2000-01-09'),('2000-01-10'),('2000-01-11');SELECT d.i as [item],c.d as [date],d.p as [price] FROMcal c LEFT JOIN data d ON c.d = d.d 3 Q& q/ b: h+ Y7 r4 q
解决方案:
( D% T0 ?6 r, ?" Y$ y2 E 一口气就能轻松产生缺口和价格: X- d: ]7 D6 R+ A; H- E) o6 h$ D
所以我从你的原始数据开始
- M0 q$ S0 {6 g+ M2 ]9 T8 rCREATE TABLE t ([I] varchar(5),[D] date,[P] DECIMAL(10,2));INSERT INTO t ([I],[D],[P])VALUES Bread','2000-01- Bread','2000-01-02 00:00:00，9.5、Bread','2000-01-04 00:00:00，9. Sugar','2000-01- Sugar','2000-01-150；with-- number is a tally table. here i use recursive cte to generate 100 numbersnumber as( select n = 0 union all select n = n 1 from number where n < 99),0)drop table t 0)drop table t |
|
技术问答
125 人阅读
|
0 人回复
|
2023-09-12
