Mercurial > gemma
changeset 5647:2a1f36ab6e91 DQR adjustments
updated report
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Tue, 20 Jun 2023 12:04:52 +0200 |
parents | 88fdf143daf1 |
children | e8aa69040bec |
files | schema/reports.sql schema/updates/1470/01.update_report.sql |
diffstat | 2 files changed, 18 insertions(+), 8 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/reports.sql Mon Jun 19 17:21:45 2023 +0200 +++ b/schema/reports.sql Tue Jun 20 12:04:52 2023 +0200 @@ -49,14 +49,19 @@ bn AS ( SELECT DISTINCT bottleneck_id ,max(objnam) as objnam, responsible_country AS cc, min ((lower(stretch)).hectometre) AS hm FROM waterway.bottlenecks - WHERE validity @> tstzrange(now() - interval '1 hour', now(), '[)') GROUP BY bottleneck_id,cc - ) - SELECT bn.cc, bn.hm, d.ym AS month, bn.objnam, bn.bottleneck_id, + ), + 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 @@ -68,7 +73,7 @@ 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, bn.objnam; + GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bnn.objnam; -- We need a wrapper procedure with owner rights for -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW
--- a/schema/updates/1470/01.update_report.sql Mon Jun 19 17:21:45 2023 +0200 +++ b/schema/updates/1470/01.update_report.sql Tue Jun 20 12:04:52 2023 +0200 @@ -8,14 +8,19 @@ bn AS ( SELECT DISTINCT bottleneck_id ,max(objnam) as objnam, responsible_country AS cc, min ((lower(stretch)).hectometre) AS hm FROM waterway.bottlenecks - WHERE validity @> tstzrange(now() - interval '1 hour', now(), '[)') GROUP BY bottleneck_id,cc - ) - SELECT bn.cc, bn.hm, d.ym AS month, bn.objnam, bn.bottleneck_id, + ), + 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 @@ -27,7 +32,7 @@ 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, bn.objnam; + 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; \ No newline at end of file