Mercurial > gemma

comparison schema/reports.sql @ 5420:851c14d57680 marking-single-beam

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged default into marking-single-beam branch.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Thu, 08 Jul 2021 00:14:58 +0200
parents fbad74acd23f
children 47ee6ecf94de
comparison
equal deleted inserted replaced
5419:202715173935 5420:851c14d57680
21 generate_series( ym, 21 generate_series( ym,
22 ( ym + interval '1 month' 22 ( ym + interval '1 month'
23 - interval '1 day'), 23 - interval '1 day'),
24 '1 day'::interval ) d ), 24 '1 day'::interval ) d ),
25 g AS ( SELECT d.ym, d.d AS day, (location).country_code AS cc, 25 g AS ( SELECT d.ym, d.d AS day, (location).country_code AS cc,
26 min((location).hectometre) AS hm,
26 objname, array_agg(distinct(location)) AS locations 27 objname, array_agg(distinct(location)) AS locations
27 FROM waterway.gauges,d 28 FROM waterway.gauges,d
28 GROUP BY objname,d.d,(location).country_code,d.ym ), 29 GROUP BY objname,d.d,(location).country_code,d.ym ),
29 measure AS ( 30 measure AS (
30 SELECT g.ym, g.cc, g.objname, g.day, 31 SELECT g.ym, g.cc, g.hm, g.objname, g.day,
31 CASE WHEN count(measure_date) = 0 32 CASE WHEN count(measure_date) = 0
32 THEN 1 ELSE 0 END AS missing 33 THEN 1 ELSE 0 END AS missing
33 FROM g 34 FROM g
34 LEFT OUTER JOIN waterway.gauge_measurements gm 35 LEFT OUTER JOIN waterway.gauge_measurements gm
35 ON ARRAY[location] <@ g.locations 36 ON ARRAY[location] <@ g.locations
36 AND g.day <= measure_date 37 AND g.day <= measure_date
37 AND measure_date < (g.day + interval '1 day') 38 AND measure_date < (g.day + interval '1 day')
38 GROUP BY g.objname,g.day,g.ym,g.cc ) 39 GROUP BY g.objname,g.day,g.ym,g.cc,g.hm )
39 SELECT cc, ym AS month, objname, sum(missing) AS daynodata 40 SELECT cc, ym AS month, objname, hm, sum(missing) AS daynodata
40 FROM measure 41 FROM measure
41 GROUP BY cc, ym, objname; 42 GROUP BY cc, ym, objname, hm;
42 43
43 CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS 44 CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS
44 WITH d AS ( SELECT ym::date 45 WITH d AS ( SELECT ym::date
45 FROM generate_series( '2019-10-01'::date, 46 FROM generate_series( '2019-10-01'::date,
46 now() - interval '1 day', 47 now() - interval '1 day',
47 '1 month'::interval ) ym), 48 '1 month'::interval ) ym),
48 bn AS ( SELECT DISTINCT objnam, responsible_country AS cc 49 bn AS ( SELECT DISTINCT objnam, responsible_country AS cc,
49 FROM waterway.bottlenecks ), 50 min ((lower(stretch)).hectometre) AS hm
51 FROM waterway.bottlenecks GROUP BY objnam,cc),
50 bid AS (SELECT objnam, array_agg(distinct(bottleneck_id)) AS ids 52 bid AS (SELECT objnam, array_agg(distinct(bottleneck_id)) AS ids
51 FROM waterway.bottlenecks GROUP BY objnam) 53 FROM waterway.bottlenecks GROUP BY objnam)
52 SELECT bn.cc, d.ym AS month, bid.objnam, 54 SELECT bn.cc, bn.hm, d.ym AS month, bid.objnam,
53 COALESCE(count(distinct(sr.date_info)),0) AS srcnt, 55 COALESCE(count(distinct(sr.date_info)),0) AS srcnt,
54 COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt 56 COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
55 FROM bn, bid 57 FROM bn, bid
56 CROSS JOIN d 58 CROSS JOIN d
57 LEFT OUTER JOIN waterway.sounding_results sr 59 LEFT OUTER JOIN waterway.sounding_results sr
64 ON fairway_availability_id = fa.id 66 ON fairway_availability_id = fa.id
65 AND d.ym <= efa.measure_date 67 AND d.ym <= efa.measure_date
66 AND efa.measure_date < (d.ym + interval '1 month') 68 AND efa.measure_date < (d.ym + interval '1 month')
67 AND efa.measure_type = 'Measured' 69 AND efa.measure_type = 'Measured'
68 WHERE bid.objnam = bn.objnam 70 WHERE bid.objnam = bn.objnam
69 GROUP BY bn.cc, d.ym, bid.objnam; 71 GROUP BY bn.cc, bn.hm, d.ym, bid.objnam;
70 72
71 -- We need a wrapper procedure with owner rights for 73 -- We need a wrapper procedure with owner rights for
72 -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW 74 -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW
73 -- completely replaces the contents of a materialized view. To execute 75 -- completely replaces the contents of a materialized view. To execute
74 -- this command you must be the owner of the materialized view."" 76 -- this command you must be the owner of the materialized view.""