Mercurial > gemma

annotate schema/reports.sql @ 5666:37c2354a6024 clickable-links

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Render links only to known bottlenecks
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 05 Dec 2023 15:34:31 +0100
parents 2a1f36ab6e91
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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),
5640
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents: 5401
diff changeset
49 bn AS ( SELECT DISTINCT bottleneck_id ,max(objnam) as objnam, responsible_country AS cc,
5401
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
5640
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents: 5401
diff changeset
51 FROM waterway.bottlenecks
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents: 5401
diff changeset
52 GROUP BY bottleneck_id,cc
5647
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
53 ),
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
54 bnn AS ( SELECT DISTINCT ON (bottleneck_id) bottleneck_id, objnam, validity
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
55 FROM waterway.bottlenecks b
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
56 ORDER BY bottleneck_id,
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
57 validity DESC )
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
58 SELECT bn.cc, bn.hm, d.ym AS month, bnn.objnam, bn.bottleneck_id,
5390
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
59 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
60 COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
5640
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents: 5401
diff changeset
61 FROM bn
5390
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
62 CROSS JOIN d
5647
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
63 JOIN bnn
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
64 ON bnn.bottleneck_id = bn.bottleneck_id
5390
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
65 LEFT OUTER JOIN waterway.sounding_results sr
5640
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents: 5401
diff changeset
66 ON sr.bottleneck_id = bn.bottleneck_id
5390
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
67 AND d.ym <= sr.date_info
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
68 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
69 LEFT OUTER JOIN waterway.fairway_availability fa
5640
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents: 5401
diff changeset
70 ON fa.bottleneck_id = bn.bottleneck_id
5390
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
71 LEFT OUTER JOIN waterway.effective_fairway_availability efa
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
72 ON fairway_availability_id = fa.id
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
73 AND d.ym <= efa.measure_date
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
74 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
75 AND efa.measure_type = 'Measured'
5647
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
76 GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bnn.objnam;
5390
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
77
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
78 -- 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
79 -- 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
80 -- 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
81 -- 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
82
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 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
84 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
85 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
86 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
87 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
88 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
89 $$ 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
90
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 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
92
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
93 -- 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
94 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
95 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
96 'CALL sys_admin.update_dqr_stats();');