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