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