Mercurial > gemma
annotate schema/updates/1452/01.report_views.sql @ 5624:29d729607ac1
Added tag v5.7 for changeset ff0af91a7f62
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Thu, 22 Dec 2022 18:42:14 +0100 |
parents | 90fc90148ef8 |
children |
rev | line source |
---|---|
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 -- 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
|
2 CREATE MATERIALIZED VIEW waterway.dqr_gauge_stats AS |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
3 WITH d AS ( SELECT ym::date, d::date |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
4 FROM generate_series( '2019-10-01'::date, |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
5 now() - interval '1 day', |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
6 '1 month'::interval ) ym, |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
7 generate_series( ym, |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 ( ym + interval '1 month' |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
9 - interval '1 day'), |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
10 '1 day'::interval ) d ), |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
11 g AS ( SELECT d.ym, d.d AS day, (location).country_code AS cc, |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
12 objname, array_agg(distinct(location)) AS locations |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 FROM waterway.gauges,d |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
14 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
|
15 measure AS ( |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
16 SELECT g.ym, g.cc, g.objname, g.day, |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
17 CASE WHEN count(measure_date) = 0 |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
18 THEN 1 ELSE 0 END AS missing |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 FROM g |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 LEFT OUTER JOIN waterway.gauge_measurements gm |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 ON ARRAY[location] <@ g.locations |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
22 AND g.day <= measure_date |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
23 AND measure_date < (g.day + interval '1 day') |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
24 GROUP BY g.objname,g.day,g.ym,g.cc ) |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
25 SELECT cc, ym AS month, objname, sum(missing) AS daynodata |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
26 FROM measure |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
27 GROUP BY cc, ym, objname; |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
28 |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
30 WITH d AS ( SELECT ym::date |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
31 FROM generate_series( '2019-10-01'::date, |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
32 now() - interval '1 day', |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
33 '1 month'::interval ) ym), |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
34 bn AS ( SELECT DISTINCT objnam, responsible_country AS cc |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
35 FROM waterway.bottlenecks ), |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
36 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
|
37 FROM waterway.bottlenecks GROUP BY objnam) |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
38 SELECT bn.cc, d.ym AS month, bid.objnam, |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
39 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
|
40 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
|
41 FROM bn, bid |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
42 CROSS JOIN d |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
43 LEFT OUTER JOIN waterway.sounding_results sr |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
44 ON ARRAY[sr.bottleneck_id] <@ bid.ids |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
45 AND d.ym <= sr.date_info |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
46 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
|
47 LEFT OUTER JOIN waterway.fairway_availability fa |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
48 ON ARRAY[fa.bottleneck_id] <@ bid.ids |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
49 LEFT OUTER JOIN waterway.effective_fairway_availability efa |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
50 ON fairway_availability_id = fa.id |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
51 AND d.ym <= efa.measure_date |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
52 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
|
53 AND efa.measure_type = 'Measured' |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
54 WHERE bid.objnam = bn.objnam |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
55 GROUP BY bn.cc, d.ym, bid.objnam; |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
56 |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
57 -- Refresh access rights! |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
58 GRANT SELECT on ALL tables in schema waterway TO waterway_user; |