71 lines
2.4 KiB
SQL
71 lines
2.4 KiB
SQL
CREATE TABLE mesure_gauge ( -- TODO ; revoir procédures d'injection des données pour faire la différence entre gauge et compteur
|
|
"date" TIMESTAMPTZ,
|
|
id_captation INT NOT NULL,
|
|
valeur float4 NOT NULL
|
|
) WITH (
|
|
tsdb.hypertable,
|
|
timescaledb.segmentby = 'id_captation',
|
|
timescaledb.orderby='date DESC'
|
|
);
|
|
|
|
INSERT INTO mesure_gauge ("date", id_captation, valeur)
|
|
SELECT m."date", m.id_captation, m.valeur
|
|
FROM mesure m
|
|
WHERE m.id_captation IN (
|
|
SELECT c.id_captation
|
|
FROM captation c
|
|
WHERE c."type" = 'G' -- TODO : prévoir cette info dans captation
|
|
);
|
|
|
|
SELECT add_retention_policy('mesure_gauge', INTERVAL '6 months'); -- on ne garde les données brutes que 6 mois
|
|
|
|
CREATE MATERIALIZED VIEW mesure_gauge_heure -- valeurs par heure
|
|
WITH (timescaledb.continuous) AS
|
|
SELECT
|
|
id_captation,
|
|
time_bucket(INTERVAL '1 HOUR', mg."date") AS "date",
|
|
time_weight('linear', mg."date", mg.valeur) AS valeur_temporelle,
|
|
MAX(mg.valeur),
|
|
MIN(mg.valeur)
|
|
FROM mesure_gauge mg
|
|
GROUP BY 1, 2;
|
|
|
|
SELECT add_continuous_aggregate_policy( -- On met à jour les données tt les h en relisant les données brutes entre 1 mois et 1 jour
|
|
'mesure_gauge_heure',
|
|
start_offset => INTERVAL '1 month',
|
|
end_offset => INTERVAL '1 day',
|
|
schedule_interval => INTERVAL '1 hour'
|
|
);
|
|
|
|
SELECT add_retention_policy('mesure_gauge_heure', INTERVAL '5 years'); -- on ne garde les données par h que 5 ans
|
|
|
|
CREATE MATERIALIZED VIEW mesure_gauge_jour -- valeurs par jour
|
|
WITH (timescaledb.continuous) AS
|
|
SELECT
|
|
id_captation,
|
|
time_bucket(INTERVAL '1 DAY', mg."date") AS "date",
|
|
rollup(valeur_temporelle) AS valeur_temporelle,
|
|
MAX(mg.max),
|
|
MIN(mg.min)
|
|
FROM mesure_gauge_heure mg
|
|
GROUP BY 1, 2;
|
|
|
|
SELECT add_continuous_aggregate_policy( -- On met à jour les données tt les h en relisant les données brutes entre 1 mois et 1 jour
|
|
'mesure_gauge_jour',
|
|
start_offset => INTERVAL '1 month',
|
|
end_offset => INTERVAL '1 day',
|
|
schedule_interval => INTERVAL '1 hour'
|
|
);
|
|
|
|
SELECT add_retention_policy('mesure_gauge_jour', INTERVAL '20 years'); -- on ne garde les données par h que 20 ans
|
|
|
|
-- ex. select
|
|
select
|
|
mgh.id_captation,
|
|
time_bucket(INTERVAL '1 DAY', mgh."date") AS "date", -- 1 jour et pas seulement une heure
|
|
average(rollup(valeur_temporelle)) AS moyenne -- rollup pour avour le concaténation des valeur_temporelle
|
|
from mesure_gauge_heure mgh
|
|
where mgh.id_captation = 59 -- puissance PAC
|
|
group by 1, 2
|
|
order by "date" desc
|
|
limit 1000; |