Mercurial > gemma

changeset 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 677a52ad9486
children c0425c948c14
files schema/reports.sql schema/updates/1470/01.update_report.sql schema/version.sql
diffstat 3 files changed, 43 insertions(+), 11 deletions(-) [+]
line wrap: on
line diff
--- a/schema/reports.sql	Thu Jun 01 11:23:19 2023 +0200
+++ b/schema/reports.sql	Thu Jun 01 12:24:18 2023 +0200
@@ -41,34 +41,36 @@
     FROM measure
     GROUP BY cc, ym, objname, hm;
 
+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 objnam, responsible_country AS cc,
+     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 objnam,cc),
-     bid AS (SELECT objnam, array_agg(distinct(bottleneck_id)) AS ids
-               FROM waterway.bottlenecks GROUP BY objnam)
-  SELECT bn.cc, bn.hm, d.ym AS month, bid.objnam,
+               FROM waterway.bottlenecks
+               WHERE validity @> tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)')
+               GROUP BY bottleneck_id,cc
+               )
+  SELECT bn.cc, bn.hm, d.ym AS month, bn.objnam, bn.bottleneck_id,
          COALESCE(count(distinct(sr.date_info)),0) AS srcnt,
          COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
-    FROM bn, bid
+    FROM bn
     CROSS JOIN d
     LEFT OUTER JOIN waterway.sounding_results sr
-      ON ARRAY[sr.bottleneck_id] <@ bid.ids
+      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 ARRAY[fa.bottleneck_id] <@ bid.ids
+      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'
-    WHERE bid.objnam = bn.objnam
-    GROUP BY bn.cc, bn.hm, d.ym, bid.objnam;
+    GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bn.objnam;;
 
 -- We need a wrapper procedure with owner rights for
 -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1470/01.update_report.sql	Thu Jun 01 12:24:18 2023 +0200
@@ -0,0 +1,30 @@
+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
+               WHERE validity @> tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)')
+               GROUP BY bottleneck_id,cc
+               )
+  SELECT bn.cc, bn.hm, d.ym AS month, bn.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
+    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, bn.objnam;
\ No newline at end of file
--- a/schema/version.sql	Thu Jun 01 11:23:19 2023 +0200
+++ b/schema/version.sql	Thu Jun 01 12:24:18 2023 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1468);
+INSERT INTO gemma_schema_version(version) VALUES (1470);