gemma: schema/reports.sql annotate

annotate schema/reports.sql @ 5560:f2204f91d286

Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.

author	Sascha L. Teichmann <sascha.teichmann@intevation.de>
date	Wed, 09 Feb 2022 18:34:40 +0100
parents	fbad74acd23f
children	47ee6ecf94de

rev	line source
5390 90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	1 -- This is Free Software under GNU Affero General Public License v >= 3.0
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	2 -- without warranty, see README.md and license for details.
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	3
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	4 -- SPDX-License-Identifier: AGPL-3.0-or-later
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	5 -- License-Filename: LICENSES/AGPL-3.0.txt
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	6
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	7 -- Copyright (C) 2021 by via donau
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	9 -- Software engineering by Intevation GmbH
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	10
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	11 -- Author(s):
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	12 -- * Sascha Wilde <sascha.wilde@intevation.de>
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	13
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	14
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	15 -- Materialized Views with statistical data for data quality reports
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	16 CREATE MATERIALIZED VIEW waterway.dqr_gauge_stats AS
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	17 WITH d AS ( SELECT ym::date, d::date
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	18 FROM generate_series( '2019-10-01'::date,
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	19 now() - interval '1 day',
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	20 '1 month'::interval ) ym,
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	21 generate_series( ym,
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	22 ( ym + interval '1 month'
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	23 - interval '1 day'),
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	24 '1 day'::interval ) d ),
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	25 g AS ( SELECT d.ym, d.d AS day, (location).country_code AS cc,
5401 fbad74acd23f Sort BN and WG in reports by river hectometre. Sascha Wilde <wilde@sha-bang.de> parents: 5391 diff changeset	26 min((location).hectometre) AS hm,
5390 90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	27 objname, array_agg(distinct(location)) AS locations
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	28 FROM waterway.gauges,d
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	29 GROUP BY objname,d.d,(location).country_code,d.ym ),
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	30 measure AS (
5401 fbad74acd23f Sort BN and WG in reports by river hectometre. Sascha Wilde <wilde@sha-bang.de> parents: 5391 diff changeset	31 SELECT g.ym, g.cc, g.hm, g.objname, g.day,
5390 90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	32 CASE WHEN count(measure_date) = 0
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	33 THEN 1 ELSE 0 END AS missing
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	34 FROM g
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	35 LEFT OUTER JOIN waterway.gauge_measurements gm
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	36 ON ARRAY[location] <@ g.locations
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	37 AND g.day <= measure_date
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	38 AND measure_date < (g.day + interval '1 day')
5401 fbad74acd23f Sort BN and WG in reports by river hectometre. Sascha Wilde <wilde@sha-bang.de> parents: 5391 diff changeset	39 GROUP BY g.objname,g.day,g.ym,g.cc,g.hm )
fbad74acd23f Sort BN and WG in reports by river hectometre. Sascha Wilde <wilde@sha-bang.de> parents: 5391 diff changeset	40 SELECT cc, ym AS month, objname, hm, sum(missing) AS daynodata
5390 90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	41 FROM measure
5401 fbad74acd23f Sort BN and WG in reports by river hectometre. Sascha Wilde <wilde@sha-bang.de> parents: 5391 diff changeset	42 GROUP BY cc, ym, objname, hm;
5390 90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	43
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	44 CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	45 WITH d AS ( SELECT ym::date
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	46 FROM generate_series( '2019-10-01'::date,
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	47 now() - interval '1 day',
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	48 '1 month'::interval ) ym),
5401 fbad74acd23f Sort BN and WG in reports by river hectometre. Sascha Wilde <wilde@sha-bang.de> parents: 5391 diff changeset	49 bn AS ( SELECT DISTINCT objnam, responsible_country AS cc,
fbad74acd23f Sort BN and WG in reports by river hectometre. Sascha Wilde <wilde@sha-bang.de> parents: 5391 diff changeset	50 min ((lower(stretch)).hectometre) AS hm
fbad74acd23f Sort BN and WG in reports by river hectometre. Sascha Wilde <wilde@sha-bang.de> parents: 5391 diff changeset	51 FROM waterway.bottlenecks GROUP BY objnam,cc),
5390 90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	52 bid AS (SELECT objnam, array_agg(distinct(bottleneck_id)) AS ids
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	53 FROM waterway.bottlenecks GROUP BY objnam)
5401 fbad74acd23f Sort BN and WG in reports by river hectometre. Sascha Wilde <wilde@sha-bang.de> parents: 5391 diff changeset	54 SELECT bn.cc, bn.hm, d.ym AS month, bid.objnam,
5390 90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	55 COALESCE(count(distinct(sr.date_info)),0) AS srcnt,
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	56 COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	57 FROM bn, bid
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	58 CROSS JOIN d
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	59 LEFT OUTER JOIN waterway.sounding_results sr
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	60 ON ARRAY[sr.bottleneck_id] <@ bid.ids
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	61 AND d.ym <= sr.date_info
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	62 AND sr.date_info < (d.ym + interval '1 month')
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	63 LEFT OUTER JOIN waterway.fairway_availability fa
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	64 ON ARRAY[fa.bottleneck_id] <@ bid.ids
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	65 LEFT OUTER JOIN waterway.effective_fairway_availability efa
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	66 ON fairway_availability_id = fa.id
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	67 AND d.ym <= efa.measure_date
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	68 AND efa.measure_date < (d.ym + interval '1 month')
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	69 AND efa.measure_type = 'Measured'
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	70 WHERE bid.objnam = bn.objnam
5401 fbad74acd23f Sort BN and WG in reports by river hectometre. Sascha Wilde <wilde@sha-bang.de> parents: 5391 diff changeset	71 GROUP BY bn.cc, bn.hm, d.ym, bid.objnam;
5390 90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	72
5391 1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	73 -- We need a wrapper procedure with owner rights for
5390 90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	74 -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	75 -- completely replaces the contents of a materialized view. To execute
90fc90148ef8 Added materialized views based data quality report Sascha Wilde <wilde@intevation.de> parents: diff changeset	76 -- this command you must be the owner of the materialized view.""
5391 1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	77
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	78 CREATE OR REPLACE PROCEDURE sys_admin.update_dqr_stats()
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	79 LANGUAGE plpgsql AS $$
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	80 BEGIN
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	81 EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_bottleneck_stats';
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	82 EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_gauge_stats';
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	83 END;
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	84 $$ SECURITY DEFINER;
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	85
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	86 GRANT EXECUTE ON PROCEDURE sys_admin.update_dqr_stats() TO sys_admin;
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	87
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	88 -- Config update statement
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	89 INSERT INTO sys_admin.stats_updates
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	90 VALUES ('Data quality report',
1dd63a2405bc Fixed remaining TODO for refreshing materialized views within a procedure with owner rights. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5390 diff changeset	91 'CALL sys_admin.update_dqr_stats();');

Mercurial > gemma

annotate schema/reports.sql @ 5560:f2204f91d286