Mercurial > gemma

comparison schema/updates/1470/01.update_report.sql @ 5640:47ee6ecf94de DQR adjustments

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
update reports.sql. Migration of materialized view
author Thomas Junk <thomas.junk@intevation.de>
date Thu, 01 Jun 2023 12:24:18 +0200
parents
children 6fe970d800ec
comparison
equal deleted inserted replaced
5634:677a52ad9486 5640:47ee6ecf94de
1 DROP MATERIALIZED VIEW waterway.dqr_bottleneck_stats;
2
3 CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS
4 WITH d AS ( SELECT ym::date
5 FROM generate_series( '2019-10-01'::date,
6 now() - interval '1 day',
7 '1 month'::interval ) ym),
8 bn AS ( SELECT DISTINCT bottleneck_id ,max(objnam) as objnam, responsible_country AS cc,
9 min ((lower(stretch)).hectometre) AS hm
10 FROM waterway.bottlenecks
11 WHERE validity @> tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)')
12 GROUP BY bottleneck_id,cc
13 )
14 SELECT bn.cc, bn.hm, d.ym AS month, bn.objnam, bn.bottleneck_id,
15 COALESCE(count(distinct(sr.date_info)),0) AS srcnt,
16 COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
17 FROM bn
18 CROSS JOIN d
19 LEFT OUTER JOIN waterway.sounding_results sr
20 ON sr.bottleneck_id = bn.bottleneck_id
21 AND d.ym <= sr.date_info
22 AND sr.date_info < (d.ym + interval '1 month')
23 LEFT OUTER JOIN waterway.fairway_availability fa
24 ON fa.bottleneck_id = bn.bottleneck_id
25 LEFT OUTER JOIN waterway.effective_fairway_availability efa
26 ON fairway_availability_id = fa.id
27 AND d.ym <= efa.measure_date
28 AND efa.measure_date < (d.ym + interval '1 month')
29 AND efa.measure_type = 'Measured'
30 GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bn.objnam;