Mercurial > gemma
view schema/reports.sql @ 5736:55892008ec96 default tip
Fixed a bunch of corner cases in WG import.
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Wed, 29 May 2024 19:02:42 +0200 |
parents | 2a1f36ab6e91 |
children |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2021 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Sascha Wilde <sascha.wilde@intevation.de> -- 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, min((location).hectometre) AS hm, 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.hm, 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,g.hm ) SELECT cc, ym AS month, objname, hm, sum(missing) AS daynodata FROM measure GROUP BY cc, ym, objname, hm; 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 bottleneck_id ,max(objnam) as objnam, responsible_country AS cc, min ((lower(stretch)).hectometre) AS hm FROM waterway.bottlenecks GROUP BY bottleneck_id,cc ), bnn AS ( SELECT DISTINCT ON (bottleneck_id) bottleneck_id, objnam, validity FROM waterway.bottlenecks b ORDER BY bottleneck_id, validity DESC ) SELECT bn.cc, bn.hm, d.ym AS month, bnn.objnam, bn.bottleneck_id, COALESCE(count(distinct(sr.date_info)),0) AS srcnt, COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt FROM bn CROSS JOIN d JOIN bnn ON bnn.bottleneck_id = bn.bottleneck_id LEFT OUTER JOIN waterway.sounding_results sr ON sr.bottleneck_id = bn.bottleneck_id AND d.ym <= sr.date_info AND sr.date_info < (d.ym + interval '1 month') LEFT OUTER JOIN waterway.fairway_availability fa ON fa.bottleneck_id = bn.bottleneck_id 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' GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bnn.objnam; -- We need a wrapper procedure with owner rights for -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW -- completely replaces the contents of a materialized view. To execute -- this command you must be the owner of the materialized view."" CREATE OR REPLACE PROCEDURE sys_admin.update_dqr_stats() LANGUAGE plpgsql AS $$ BEGIN EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_bottleneck_stats'; EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_gauge_stats'; END; $$ SECURITY DEFINER; GRANT EXECUTE ON PROCEDURE sys_admin.update_dqr_stats() TO sys_admin; -- Config update statement INSERT INTO sys_admin.stats_updates VALUES ('Data quality report', 'CALL sys_admin.update_dqr_stats();');