Mercurial > gemma
changeset 5401:fbad74acd23f
Sort BN and WG in reports by river hectometre.
| author | Sascha Wilde <wilde@sha-bang.de> |
|---|---|
| date | Wed, 07 Jul 2021 20:11:17 +0200 |
| parents | 983d6efc04e9 |
| children | 851c14d57680 5be842692d87 25d0d3159376 |
| files | report-templates/data-quality-report.yaml schema/reports.sql schema/updates/1454/01.add_hm_to_report_views.sql schema/version.sql |
| diffstat | 4 files changed, 81 insertions(+), 15 deletions(-) [+] |
line wrap: on
line diff
--- a/report-templates/data-quality-report.yaml Wed Jul 07 18:41:53 2021 +0200 +++ b/report-templates/data-quality-report.yaml Wed Jul 07 20:11:17 2021 +0200 @@ -17,9 +17,9 @@ # BN names - type: select statement: > - SELECT DISTINCT objnam AS bnnam + SELECT DISTINCT objnam AS bnnam, hm FROM waterway.dqr_bottleneck_stats - WHERE cc = {{ cc }} ORDER BY objnam; + WHERE cc = {{ cc }} ORDER BY hm, objnam; actions: - type: assign name: last_row @@ -50,7 +50,7 @@ SELECT objnam, srcnt, fwacnt FROM waterway.dqr_bottleneck_stats WHERE cc = {{ cc }} AND month = {{ d }} - ORDER BY objnam; + ORDER BY hm, objnam; actions: - type: assign name: last_row @@ -73,8 +73,8 @@ # Gauges names - type: select statement: > - SELECT DISTINCT objname AS gnam FROM waterway.dqr_gauge_stats - WHERE cc = {{ cc }} ORDER BY objname; + SELECT DISTINCT objname AS gnam, hm FROM waterway.dqr_gauge_stats + WHERE cc = {{ cc }} ORDER BY hm, objname; actions: - type: assign name: last_row @@ -105,7 +105,7 @@ SELECT objname, daynodata FROM waterway.dqr_gauge_stats WHERE cc = {{ cc }} AND month = {{ d }} - ORDER BY objname; + ORDER BY hm, objname; actions: - type: assign name: last_row
--- a/schema/reports.sql Wed Jul 07 18:41:53 2021 +0200 +++ b/schema/reports.sql Wed Jul 07 20:11:17 2021 +0200 @@ -23,11 +23,12 @@ - interval '1 day'), '1 day'::interval ) d ), g AS ( SELECT d.ym, d.d AS day, (location).country_code AS cc, + min((location).hectometre) AS hm, objname, array_agg(distinct(location)) AS locations FROM waterway.gauges,d GROUP BY objname,d.d,(location).country_code,d.ym ), measure AS ( - SELECT g.ym, g.cc, g.objname, g.day, + SELECT g.ym, g.cc, g.hm, g.objname, g.day, CASE WHEN count(measure_date) = 0 THEN 1 ELSE 0 END AS missing FROM g @@ -35,21 +36,22 @@ ON ARRAY[location] <@ g.locations AND g.day <= measure_date AND measure_date < (g.day + interval '1 day') - GROUP BY g.objname,g.day,g.ym,g.cc ) - SELECT cc, ym AS month, objname, sum(missing) AS daynodata + GROUP BY g.objname,g.day,g.ym,g.cc,g.hm ) + SELECT cc, ym AS month, objname, hm, sum(missing) AS daynodata FROM measure - GROUP BY cc, ym, objname; + GROUP BY cc, ym, objname, hm; 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 - FROM waterway.bottlenecks ), + bn AS ( SELECT DISTINCT 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, d.ym AS month, bid.objnam, + SELECT bn.cc, bn.hm, d.ym AS month, bid.objnam, COALESCE(count(distinct(sr.date_info)),0) AS srcnt, COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt FROM bn, bid @@ -66,7 +68,7 @@ AND efa.measure_date < (d.ym + interval '1 month') AND efa.measure_type = 'Measured' WHERE bid.objnam = bn.objnam - GROUP BY bn.cc, d.ym, bid.objnam; + GROUP BY bn.cc, bn.hm, d.ym, bid.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/1454/01.add_hm_to_report_views.sql Wed Jul 07 20:11:17 2021 +0200 @@ -0,0 +1,64 @@ +-- Materialized Views with statistical data for data quality reports +DROP MATERIALIZED VIEW waterway.dqr_gauge_stats; + +CREATE MATERIALIZED VIEW waterway.dqr_gauge_stats AS +WITH d AS ( SELECT ym::date, d::date + FROM generate_series( '2019-10-01'::date, + now() - interval '1 day', + '1 month'::interval ) ym, + generate_series( ym, + ( ym + interval '1 month' + - interval '1 day'), + '1 day'::interval ) d ), + g AS ( SELECT d.ym, d.d AS day, (location).country_code AS cc, + min((location).hectometre) AS hm, + objname, array_agg(distinct(location)) AS locations + FROM waterway.gauges,d + GROUP BY objname,d.d,(location).country_code,d.ym ), + measure AS ( + SELECT g.ym, g.cc, g.hm, g.objname, g.day, + CASE WHEN count(measure_date) = 0 + THEN 1 ELSE 0 END AS missing + FROM g + LEFT OUTER JOIN waterway.gauge_measurements gm + ON ARRAY[location] <@ g.locations + AND g.day <= measure_date + AND measure_date < (g.day + interval '1 day') + GROUP BY g.objname,g.day,g.ym,g.cc,g.hm ) + SELECT cc, ym AS month, objname, hm, sum(missing) AS daynodata + 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, + 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, + COALESCE(count(distinct(sr.date_info)),0) AS srcnt, + COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt + FROM bn, bid + CROSS JOIN d + LEFT OUTER JOIN waterway.sounding_results sr + ON ARRAY[sr.bottleneck_id] <@ bid.ids + 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 + 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; + +-- Refresh access rights! +GRANT SELECT on ALL tables in schema waterway TO waterway_user;