create table boll as select
*, (avg(close) over win)-2*sqrt((avg(close*close) over win)-pow((avg(close) over win),2)) as BOLD,
(avg(close) over win) 2*sqrt((avg(close*close) over win)-pow((avg(close) over win),2)) as BOLU
from bhav
window win as (partition by isin order by timestamp rows 19 preceding);
使用上面的查詢，如果當前行之前少于 19 行，是否有辦法NULL從視窗計算回傳win？
uj5u.com熱心網友回復：
您可以COUNT()在您定義的視窗上使用視窗函式來檢查有多少行：
create table boll as
select *,
CASE WHEN COUNT(*) OVER win >= 19 THEN (avg(close) over win) - 2 * sqrt((avg(close*close) over win)-pow((avg(close) over win),2)) END as BOLD,
CASE WHEN COUNT(*) OVER win >= 19 THEN (avg(close) over win) 2*sqrt((avg(close*close) over win)-pow((avg(close) over win),2)) END as BOLU
from bhav
window win as (partition by isin order by timestamp rows 19 preceding);
uj5u.com熱心網友回復：
只需查看 19 個時間戳LAG：
create table boll as
select *,
case when lag(timestamp, 19) over (order by timestamp) is not null then
avg(close) over win) - 2 * sqrt((avg(close*close) over win) - pow((avg(close) over win), 2)
end as BOLD,
case when lag(timestamp, 19) over (order by timestamp) is not null then
avg(close) over win) 2 * sqrt((avg(close*close) over win) - pow((avg(close) over win), 2)
end as BOLU
from bhav
window win as (partition by isin order by timestamp rows 19 preceding);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/311419.html