Mercurial > gemma
view schema/updates/1452/01.report_views.sql @ 5477:a3d46cee9f69
merge
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Tue, 17 Aug 2021 16:52:15 +0200 |
parents | 90fc90148ef8 |
children |
line wrap: on
line source
-- Materialized Views with statistical data for data quality reports CREATE MATERIALIZED VIEW waterway.dqr_gauge_stats AS WITH d AS ( SELECT ym::date, d::date FROM generate_series( '2019-10-01'::date, now() - interval '1 day', '1 month'::interval ) ym, generate_series( ym, ( ym + interval '1 month' - interval '1 day'), '1 day'::interval ) d ), g AS ( SELECT d.ym, d.d AS day, (location).country_code AS cc, objname, array_agg(distinct(location)) AS locations FROM waterway.gauges,d GROUP BY objname,d.d,(location).country_code,d.ym ), measure AS ( SELECT g.ym, g.cc, g.objname, g.day, CASE WHEN count(measure_date) = 0 THEN 1 ELSE 0 END AS missing FROM g LEFT OUTER JOIN waterway.gauge_measurements gm ON ARRAY[location] <@ g.locations AND g.day <= measure_date AND measure_date < (g.day + interval '1 day') GROUP BY g.objname,g.day,g.ym,g.cc ) SELECT cc, ym AS month, objname, sum(missing) AS daynodata FROM measure GROUP BY cc, ym, objname; CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS WITH d AS ( SELECT ym::date FROM generate_series( '2019-10-01'::date, now() - interval '1 day', '1 month'::interval ) ym), bn AS ( SELECT DISTINCT objnam, responsible_country AS cc FROM waterway.bottlenecks ), bid AS (SELECT objnam, array_agg(distinct(bottleneck_id)) AS ids FROM waterway.bottlenecks GROUP BY objnam) SELECT bn.cc, d.ym AS month, bid.objnam, COALESCE(count(distinct(sr.date_info)),0) AS srcnt, COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt FROM bn, bid CROSS JOIN d LEFT OUTER JOIN waterway.sounding_results sr ON ARRAY[sr.bottleneck_id] <@ bid.ids AND d.ym <= sr.date_info AND sr.date_info < (d.ym + interval '1 month') LEFT OUTER JOIN waterway.fairway_availability fa ON ARRAY[fa.bottleneck_id] <@ bid.ids LEFT OUTER JOIN waterway.effective_fairway_availability efa ON fairway_availability_id = fa.id AND d.ym <= efa.measure_date AND efa.measure_date < (d.ym + interval '1 month') AND efa.measure_type = 'Measured' WHERE bid.objnam = bn.objnam GROUP BY bn.cc, d.ym, bid.objnam; -- Refresh access rights! GRANT SELECT on ALL tables in schema waterway TO waterway_user;