Mercurial > gemma

comparison schema/reports.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 fbad74acd23f
children c0425c948c14
comparison
equal deleted inserted replaced
5634:677a52ad9486 5640:47ee6ecf94de
39 GROUP BY g.objname,g.day,g.ym,g.cc,g.hm ) 39 GROUP BY g.objname,g.day,g.ym,g.cc,g.hm )
40 SELECT cc, ym AS month, objname, hm, sum(missing) AS daynodata 40 SELECT cc, ym AS month, objname, hm, sum(missing) AS daynodata
41 FROM measure 41 FROM measure
42 GROUP BY cc, ym, objname, hm; 42 GROUP BY cc, ym, objname, hm;
43 43
44 DROP MATERIALIZED VIEW waterway.dqr_bottleneck_stats;
45
44 CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS 46 CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS
45 WITH d AS ( SELECT ym::date 47 WITH d AS ( SELECT ym::date
46 FROM generate_series( '2019-10-01'::date, 48 FROM generate_series( '2019-10-01'::date,
47 now() - interval '1 day', 49 now() - interval '1 day',
48 '1 month'::interval ) ym), 50 '1 month'::interval ) ym),
49 bn AS ( SELECT DISTINCT objnam, responsible_country AS cc, 51 bn AS ( SELECT DISTINCT bottleneck_id ,max(objnam) as objnam, responsible_country AS cc,
50 min ((lower(stretch)).hectometre) AS hm 52 min ((lower(stretch)).hectometre) AS hm
51 FROM waterway.bottlenecks GROUP BY objnam,cc), 53 FROM waterway.bottlenecks
52 bid AS (SELECT objnam, array_agg(distinct(bottleneck_id)) AS ids 54 WHERE validity @> tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)')
53 FROM waterway.bottlenecks GROUP BY objnam) 55 GROUP BY bottleneck_id,cc
54 SELECT bn.cc, bn.hm, d.ym AS month, bid.objnam, 56 )
57 SELECT bn.cc, bn.hm, d.ym AS month, bn.objnam, bn.bottleneck_id,
55 COALESCE(count(distinct(sr.date_info)),0) AS srcnt, 58 COALESCE(count(distinct(sr.date_info)),0) AS srcnt,
56 COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt 59 COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
57 FROM bn, bid 60 FROM bn
58 CROSS JOIN d 61 CROSS JOIN d
59 LEFT OUTER JOIN waterway.sounding_results sr 62 LEFT OUTER JOIN waterway.sounding_results sr
60 ON ARRAY[sr.bottleneck_id] <@ bid.ids 63 ON sr.bottleneck_id = bn.bottleneck_id
61 AND d.ym <= sr.date_info 64 AND d.ym <= sr.date_info
62 AND sr.date_info < (d.ym + interval '1 month') 65 AND sr.date_info < (d.ym + interval '1 month')
63 LEFT OUTER JOIN waterway.fairway_availability fa 66 LEFT OUTER JOIN waterway.fairway_availability fa
64 ON ARRAY[fa.bottleneck_id] <@ bid.ids 67 ON fa.bottleneck_id = bn.bottleneck_id
65 LEFT OUTER JOIN waterway.effective_fairway_availability efa 68 LEFT OUTER JOIN waterway.effective_fairway_availability efa
66 ON fairway_availability_id = fa.id 69 ON fairway_availability_id = fa.id
67 AND d.ym <= efa.measure_date 70 AND d.ym <= efa.measure_date
68 AND efa.measure_date < (d.ym + interval '1 month') 71 AND efa.measure_date < (d.ym + interval '1 month')
69 AND efa.measure_type = 'Measured' 72 AND efa.measure_type = 'Measured'
70 WHERE bid.objnam = bn.objnam 73 GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bn.objnam;;
71 GROUP BY bn.cc, bn.hm, d.ym, bid.objnam;
72 74
73 -- We need a wrapper procedure with owner rights for 75 -- We need a wrapper procedure with owner rights for
74 -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW 76 -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW
75 -- completely replaces the contents of a materialized view. To execute 77 -- completely replaces the contents of a materialized view. To execute
76 -- this command you must be the owner of the materialized view."" 78 -- this command you must be the owner of the materialized view.""