Scribd
Upload
5 views1 page

Materialized View for Ticker Data Analysis

Uploaded by

Luis Aramayo
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
5 views1 page

Materialized View for Ticker Data Analysis

Uploaded by

Luis Aramayo
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

-- VISTA MATERIALIZADA: ticker_base_ba_view

CREATE MATERIALIZED VIEW ticker_base_ba_view AS


WITH ultimas_filas AS (
SELECT DISTINCT ON (symbol)
symbol AS cedear,
date, close_price, var_diaria, var_semanal, var_1M, YoY_1Y, SMA21, SMA50,
SMA200,
open_price, high_price, low_price, volume,
var_3m, var_6m, var_YTD, YoY_2Y, YoY_3Y, YoY_5Y, YoY_10Y
FROM historical_data_ba
ORDER BY symbol, date DESC
)
SELECT
[Link], -- symbol original de NYSE
[Link],
[Link],
u.open_price, u.high_price, u.low_price, u.close_price, [Link],
u.var_diaria, u.var_semanal, u.var_1M, u.YoY_1Y,
u.SMA21, u.SMA50, u.SMA200,
u.var_3m, u.var_6m, u.var_YTD,
u.YoY_2Y, u.YoY_3Y, u.YoY_5Y, u.YoY_10Y
FROM ultimas_filas u
JOIN ticker_info ti ON [Link] = [Link];

-- INDICE
CREATE UNIQUE INDEX idx_ticker_base_ba_view_symbol ON ticker_base_ba_view (symbol);

-- funcion para actualizar la vista

CREATE OR REPLACE FUNCTION refresh_ticker_base_ba_view()


RETURNS void AS $$
BEGIN
REFRESH MATERIALIZED VIEW CONCURRENTLY ticker_base_ba_view;
END;
$$ LANGUAGE plpgsql;

-- prueba
select * from ticker_base_ba;

You might also like