Mercurial > gemma

annotate schema/updates/1454/01.add_hm_to_report_views.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +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;