Mercurial > gemma

annotate schema/updates/1470/01.update_report.sql @ 5736:55892008ec96 default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed a bunch of corner cases in WG import.
author Sascha Wilde <wilde@sha-bang.de>
date Wed, 29 May 2024 19:02:42 +0200
parents 2a1f36ab6e91
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5640
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
1 DROP MATERIALIZED VIEW waterway.dqr_bottleneck_stats;
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
2
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
3 CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
4 WITH d AS ( SELECT ym::date
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
5 FROM generate_series( '2019-10-01'::date,
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
6 now() - interval '1 day',
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
7 '1 month'::interval ) ym),
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
8 bn AS ( SELECT DISTINCT bottleneck_id ,max(objnam) as objnam, responsible_country AS cc,
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
9 min ((lower(stretch)).hectometre) AS hm
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
10 FROM waterway.bottlenecks
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
11 GROUP BY bottleneck_id,cc
5647
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
12 ),
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
13 bnn AS ( SELECT DISTINCT ON (bottleneck_id) bottleneck_id, objnam, validity
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
14 FROM waterway.bottlenecks b
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
15 ORDER BY bottleneck_id,
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
16 validity DESC )
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
17 SELECT bn.cc, bn.hm, d.ym AS month, bnn.objnam, bn.bottleneck_id,
5640
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
18 COALESCE(count(distinct(sr.date_info)),0) AS srcnt,
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
19 COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
20 FROM bn
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
21 CROSS JOIN d
5647
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
22 JOIN bnn
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
23 ON bnn.bottleneck_id = bn.bottleneck_id
5640
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
24 LEFT OUTER JOIN waterway.sounding_results sr
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
25 ON sr.bottleneck_id = bn.bottleneck_id
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
26 AND d.ym <= sr.date_info
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
27 AND sr.date_info < (d.ym + interval '1 month')
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
28 LEFT OUTER JOIN waterway.fairway_availability fa
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
29 ON fa.bottleneck_id = bn.bottleneck_id
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
30 LEFT OUTER JOIN waterway.effective_fairway_availability efa
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
31 ON fairway_availability_id = fa.id
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
32 AND d.ym <= efa.measure_date
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
33 AND efa.measure_date < (d.ym + interval '1 month')
47ee6ecf94de update reports.sql. Migration of materialized view
Thomas Junk <thomas.junk@intevation.de>
parents:
diff changeset
34 AND efa.measure_type = 'Measured'
5647
2a1f36ab6e91 updated report
Thomas Junk <thomas.junk@intevation.de>
parents: 5646
diff changeset
35 GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bnn.objnam;
5645
6fe970d800ec Adjust access rights
Thomas Junk <thomas.junk@intevation.de>
parents: 5640
diff changeset
36
6fe970d800ec Adjust access rights
Thomas Junk <thomas.junk@intevation.de>
parents: 5640
diff changeset
37 -- Refresh access rights!
6fe970d800ec Adjust access rights
Thomas Junk <thomas.junk@intevation.de>
parents: 5640
diff changeset
38 GRANT SELECT on ALL tables in schema waterway TO waterway_user;