create or replace view `primal-monument-358918.Blog.daily_indicators` as
WITH ceny AS
( SELECT Stock,Date,Open,High,Low,Close,Volume,
AVG(Close) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7,
AVG(Close) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma30,
AVG(Close) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 99 PRECEDING AND CURRENT ROW) AS ma100,
AVG(Close) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS ma200,
SUM(CASE WHEN Close > Open THEN Close - Open ELSE 0 END) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) AS gain,
SUM(CASE WHEN Open > Close THEN Open - Close ELSE NULL END) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) AS loss,
MAX(High) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) AS highest,
MIN(Low) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) AS lowest,
(AVG(Close) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) - AVG(Close) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW)) AS macd
FROM `primal-monument-358918.Blog.ceny_gieldowe`),
wskazniki AS (
SELECT stock,Close,volume,date,ma7,ma30,ma100,ma200,(100 - (100 / (1 + (gain / loss)))) AS rsi,Macd,
(CASE WHEN STDDEV_POP(Close) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) = 0 THEN NULL ELSE ((Close - AVG(Close) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW)) / STDDEV_POP(Close) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW)) END)
AS bollinger_band
FROM ceny)
SELECT
Stock,Close,volume,date,ma7,ma30,ma100,ma200,rsi,macd,bollinger_band,
ABS(ma200 - Close) ma200_reach,
ABS(ma100 - Close) ma100_reach,
ABS(ma30 - Close) ma30_reach,
ABS(ma7 - Close) ma7_reach,
FROM wskazniki
Hybrid Poplar Sp. z o.o.
VAT ID: PL5213892597
ul. Ksawerów 3,
02-656 Warszawa
Copyright © 2023 bigglo | Wszelkie prawa zastrzeżone.
Hybrid Poplar Sp. z o.o.
VAT ID: PL5213892597
ul. Ksawerów 3,
02-656 Warszawa
Copyright © 2023 bigglo.pl