Mercurial > gemma
annotate schema/reports.sql @ 5625:39c1698fb0ff
Bumped dev version
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Thu, 22 Dec 2022 18:43:46 +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();'); |