Mercurial > gemma
annotate report-templates/data-quality-report.yaml @ 5487:e8dc7a7aec41
Merged
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Fri, 20 Aug 2021 13:01:49 +0200 |
parents | 5be842692d87 |
children | a726a92ea5c9 |
rev | line source |
---|---|
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 actions: |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
2 # One sheet per CC |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
3 - type: select |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
4 statement: > |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
5 SELECT cc FROM |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
6 (VALUES ('AT'), ('SK'), ('HU'), ('HR'), ('RS'), ('BG'), ('RO') ) |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
7 AS t (cc); |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 actions: |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
9 - type: sheet |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
10 source: CCTmpl |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
11 destination: "{{ cc }}" |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
12 vars: [last_row, column_number, offset] |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 actions: |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
14 # Header: |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
15 - type: copy |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
16 location: [A1,A5] |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
17 # BN names |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
18 - type: select |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 statement: > |
5401
fbad74acd23f
Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
5392
diff
changeset
|
20 SELECT DISTINCT objnam AS bnnam, hm |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 FROM waterway.dqr_bottleneck_stats |
5401
fbad74acd23f
Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
5392
diff
changeset
|
22 WHERE cc = {{ cc }} ORDER BY hm, objnam; |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
23 actions: |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
24 - type: assign |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
25 name: last_row |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
26 expr: (row_number ?? 0) + 6 |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
27 - type: copy |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
28 location: [A6] |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 destination: A{{ last_row }} |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
30 - type: copy |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
31 location: [A7] |
5392
014dc3219bdb
Made DQR template more bullet-proof for missing data.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5390
diff
changeset
|
32 destination: A{{ (last_row ?? 6) + 1 }} |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
33 # Gen Months |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
34 - type: select |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
35 statement: > |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
36 SELECT to_char(d, 'Month YYYY') AS month, d::date |
5444
5be842692d87
Revert dates in report template.
Sascha Wilde <wilde@sha-bang.de>
parents:
5401
diff
changeset
|
37 FROM generate_series( date_trunc('month', |
5be842692d87
Revert dates in report template.
Sascha Wilde <wilde@sha-bang.de>
parents:
5401
diff
changeset
|
38 now() - interval '1 day' |
5be842692d87
Revert dates in report template.
Sascha Wilde <wilde@sha-bang.de>
parents:
5401
diff
changeset
|
39 )::date, |
5be842692d87
Revert dates in report template.
Sascha Wilde <wilde@sha-bang.de>
parents:
5401
diff
changeset
|
40 '2019-10-01'::date, |
5be842692d87
Revert dates in report template.
Sascha Wilde <wilde@sha-bang.de>
parents:
5401
diff
changeset
|
41 - '1 month'::interval ) d; |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
42 actions: |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
43 - type: assign |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
44 name: column_number |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
45 expr: column2num("B") + (row_number ?? 0) * 2 |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
46 - type: copy |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
47 location: [B4,C5] |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
48 destination: '{{ coord2cell(column_number, 4) }}' |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
49 # BN SR-Count |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
50 - type: select |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
51 statement: > |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
52 SELECT objnam, srcnt, fwacnt |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
53 FROM waterway.dqr_bottleneck_stats |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
54 WHERE cc = {{ cc }} AND month = {{ d }} |
5401
fbad74acd23f
Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
5392
diff
changeset
|
55 ORDER BY hm, objnam; |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
56 actions: |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
57 - type: assign |
5392
014dc3219bdb
Made DQR template more bullet-proof for missing data.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5390
diff
changeset
|
58 name: last_row |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
59 expr: (row_number ?? 0) + 6 |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
60 - type: copy |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
61 location: [B6,C6] |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
62 destination: '{{ coord2cell(column_number, last_row) }}' |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
63 - type: copy |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
64 location: [B7,C7] |
5392
014dc3219bdb
Made DQR template more bullet-proof for missing data.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5390
diff
changeset
|
65 destination: '{{ coord2cell(column_number, (last_row ?? 0) + 1) }}' |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
66 - type: assign |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
67 name: offset |
5392
014dc3219bdb
Made DQR template more bullet-proof for missing data.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5390
diff
changeset
|
68 expr: (last_row ?? 0) + 3 |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
69 #-------------------------------------------------------------------------- |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
70 # GAUGES |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
71 # Header: |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
72 - type: copy |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
73 location: [A9] |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
74 destination: A{{ offset }} |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
75 # Gauges names |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
76 - type: select |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
77 statement: > |
5401
fbad74acd23f
Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
5392
diff
changeset
|
78 SELECT DISTINCT objname AS gnam, hm FROM waterway.dqr_gauge_stats |
fbad74acd23f
Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
5392
diff
changeset
|
79 WHERE cc = {{ cc }} ORDER BY hm, objname; |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
80 actions: |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
81 - type: assign |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
82 name: last_row |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
83 expr: (row_number ?? 0) + offset + 1 |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
84 - type: copy |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
85 location: [A10] |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
86 destination: A{{ last_row }} |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
87 - type: copy |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
88 location: [A11] |
5392
014dc3219bdb
Made DQR template more bullet-proof for missing data.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5390
diff
changeset
|
89 destination: A{{ (last_row ?? 10) + 1 }} |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
90 # Gen Months |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
91 - type: select |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
92 statement: > |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
93 SELECT to_char(d, 'Month YYYY') AS month, d::date |
5444
5be842692d87
Revert dates in report template.
Sascha Wilde <wilde@sha-bang.de>
parents:
5401
diff
changeset
|
94 FROM generate_series( date_trunc('month', |
5be842692d87
Revert dates in report template.
Sascha Wilde <wilde@sha-bang.de>
parents:
5401
diff
changeset
|
95 now() - interval '1 day' |
5be842692d87
Revert dates in report template.
Sascha Wilde <wilde@sha-bang.de>
parents:
5401
diff
changeset
|
96 )::date, |
5be842692d87
Revert dates in report template.
Sascha Wilde <wilde@sha-bang.de>
parents:
5401
diff
changeset
|
97 '2019-10-01'::date, |
5be842692d87
Revert dates in report template.
Sascha Wilde <wilde@sha-bang.de>
parents:
5401
diff
changeset
|
98 - '1 month'::interval ) d; |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
99 actions: |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
100 - type: assign |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
101 name: column_number |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
102 expr: column2num("B") + (row_number ?? 0) |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
103 - type: copy |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
104 location: [B9] |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
105 destination: '{{ coord2cell(column_number, offset) }}' |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
106 # BN SR-Count |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
107 - type: select |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
108 statement: > |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
109 SELECT objname, daynodata |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
110 FROM waterway.dqr_gauge_stats |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
111 WHERE cc = {{ cc }} AND month = {{ d }} |
5401
fbad74acd23f
Sort BN and WG in reports by river hectometre.
Sascha Wilde <wilde@sha-bang.de>
parents:
5392
diff
changeset
|
112 ORDER BY hm, objname; |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
113 actions: |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
114 - type: assign |
5392
014dc3219bdb
Made DQR template more bullet-proof for missing data.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5390
diff
changeset
|
115 name: last_row |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
116 expr: (row_number ?? 0) + offset + 1 |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
117 - type: copy |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
118 location: [B10] |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
119 destination: '{{ coord2cell(column_number, last_row) }}' |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
120 - type: copy |
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
121 location: [B11] |
5392
014dc3219bdb
Made DQR template more bullet-proof for missing data.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5390
diff
changeset
|
122 destination: '{{ coord2cell(column_number, (last_row ?? 10) + 1) }}' |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
123 |