Mercurial > gemma

annotate schema/updates/1454/01.add_hm_to_report_views.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 fbad74acd23f
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5401
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
1 -- Materialized Views with statistical data for data quality reports
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
2 DROP MATERIALIZED VIEW waterway.dqr_gauge_stats;
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
3
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
4 CREATE MATERIALIZED VIEW waterway.dqr_gauge_stats AS
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
5 WITH d AS ( SELECT ym::date, d::date
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
6 FROM generate_series( '2019-10-01'::date,
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
7 now() - interval '1 day',
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
8 '1 month'::interval ) ym,
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
9 generate_series( ym,
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
10 ( ym + interval '1 month'
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
11 - interval '1 day'),
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
12 '1 day'::interval ) d ),
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
13 g AS ( SELECT d.ym, d.d AS day, (location).country_code AS cc,
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
14 min((location).hectometre) AS hm,
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
15 objname, array_agg(distinct(location)) AS locations
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
16 FROM waterway.gauges,d
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
17 GROUP BY objname,d.d,(location).country_code,d.ym ),
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
18 measure AS (
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
19 SELECT g.ym, g.cc, g.hm, g.objname, g.day,
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
20 CASE WHEN count(measure_date) = 0
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
21 THEN 1 ELSE 0 END AS missing
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
22 FROM g
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
23 LEFT OUTER JOIN waterway.gauge_measurements gm
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
24 ON ARRAY[location] <@ g.locations
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
25 AND g.day <= measure_date
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
26 AND measure_date < (g.day + interval '1 day')
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
27 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:
diff changeset
28 SELECT cc, ym AS month, objname, hm, sum(missing) AS daynodata
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
29 FROM measure
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
30 GROUP BY cc, ym, objname, hm;
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
31
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
32 DROP MATERIALIZED VIEW waterway.dqr_bottleneck_stats;
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
33
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
34 CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
35 WITH d AS ( SELECT ym::date
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
36 FROM generate_series( '2019-10-01'::date,
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
37 now() - interval '1 day',
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
38 '1 month'::interval ) ym),
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
39 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:
diff changeset
40 min ((lower(stretch)).hectometre) AS hm
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
41 FROM waterway.bottlenecks GROUP BY objnam,cc),
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
42 bid AS (SELECT objnam, array_agg(distinct(bottleneck_id)) AS ids
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
43 FROM waterway.bottlenecks GROUP BY objnam)
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
44 SELECT bn.cc, bn.hm, d.ym AS month, bid.objnam,
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
45 COALESCE(count(distinct(sr.date_info)),0) AS srcnt,
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
46 COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
47 FROM bn, bid
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
48 CROSS JOIN d
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
49 LEFT OUTER JOIN waterway.sounding_results sr
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
50 ON ARRAY[sr.bottleneck_id] <@ bid.ids
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
51 AND d.ym <= sr.date_info
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
52 AND sr.date_info < (d.ym + interval '1 month')
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
53 LEFT OUTER JOIN waterway.fairway_availability fa
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
54 ON ARRAY[fa.bottleneck_id] <@ bid.ids
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
55 LEFT OUTER JOIN waterway.effective_fairway_availability efa
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
56 ON fairway_availability_id = fa.id
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
57 AND d.ym <= efa.measure_date
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
58 AND efa.measure_date < (d.ym + interval '1 month')
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
59 AND efa.measure_type = 'Measured'
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
60 WHERE bid.objnam = bn.objnam
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
61 GROUP BY bn.cc, bn.hm, d.ym, bid.objnam;
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
62
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
63 -- Refresh access rights!
fbad74acd23f Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
64 GRANT SELECT on ALL tables in schema waterway TO waterway_user;