Mercurial > gemma

annotate report-templates/data-quality-report.yaml @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
parents a726a92ea5c9
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