Mercurial > gemma
view schema/updates/1470/01.update_report.sql @ 5677:a57be8bfb6ea sr-v2
More suited file names.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Sun, 11 Feb 2024 09:51:21 +0100 |
parents | 2a1f36ab6e91 |
children |
line wrap: on
line source
DROP MATERIALIZED VIEW waterway.dqr_bottleneck_stats; CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS WITH d AS ( SELECT ym::date FROM generate_series( '2019-10-01'::date, now() - interval '1 day', '1 month'::interval ) ym), bn AS ( SELECT DISTINCT bottleneck_id ,max(objnam) as objnam, responsible_country AS cc, min ((lower(stretch)).hectometre) AS hm FROM waterway.bottlenecks GROUP BY bottleneck_id,cc ), bnn AS ( SELECT DISTINCT ON (bottleneck_id) bottleneck_id, objnam, validity FROM waterway.bottlenecks b ORDER BY bottleneck_id, validity DESC ) SELECT bn.cc, bn.hm, d.ym AS month, bnn.objnam, bn.bottleneck_id, COALESCE(count(distinct(sr.date_info)),0) AS srcnt, COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt FROM bn CROSS JOIN d JOIN bnn ON bnn.bottleneck_id = bn.bottleneck_id LEFT OUTER JOIN waterway.sounding_results sr ON sr.bottleneck_id = bn.bottleneck_id AND d.ym <= sr.date_info AND sr.date_info < (d.ym + interval '1 month') LEFT OUTER JOIN waterway.fairway_availability fa ON fa.bottleneck_id = bn.bottleneck_id LEFT OUTER JOIN waterway.effective_fairway_availability efa ON fairway_availability_id = fa.id AND d.ym <= efa.measure_date AND efa.measure_date < (d.ym + interval '1 month') AND efa.measure_type = 'Measured' GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bnn.objnam; -- Refresh access rights! GRANT SELECT on ALL tables in schema waterway TO waterway_user;