Mercurial > gemma

annotate report-templates/data-quality-report.yaml @ 5488:a726a92ea5c9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Removed trailing whitespace in dqr template instructions.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Sat, 21 Aug 2021 15:01:52 +0200
parents 5be842692d87
children b4ee50490592
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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: >
5488
a726a92ea5c9 Removed trailing whitespace in dqr template instructions.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5444
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