Mercurial > gemma

annotate report-templates/data-quality-report.yaml @ 5390:90fc90148ef8 extented-report

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added materialized views based data quality report
author Sascha Wilde <wilde@intevation.de>
date Fri, 02 Jul 2021 18:56:59 +0200
parents
children 014dc3219bdb
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: >
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
20 SELECT DISTINCT objnam AS bnnam
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
21 FROM waterway.dqr_bottleneck_stats
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
22 WHERE cc = {{ cc }} ORDER BY objnam;
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]
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
32 destination: A{{ last_row + 1 }}
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
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
37 FROM generate_series( '2019-10-01'::date,
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
38 now() - interval '1 day',
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
39 '1 month'::interval ) d;
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
40 actions:
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
41 - type: assign
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
42 name: column_number
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
43 expr: column2num("B") + (row_number ?? 0) * 2
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
44 - type: copy
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
45 location: [B4,C5]
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
46 destination: '{{ coord2cell(column_number, 4) }}'
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
47 # BN SR-Count
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
48 - type: select
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
49 statement: >
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
50 SELECT objnam, srcnt, fwacnt
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
51 FROM waterway.dqr_bottleneck_stats
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
52 WHERE cc = {{ cc }} AND month = {{ d }}
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
53 ORDER BY objnam;
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
54 actions:
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
55 - type: assign
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
56 name: last_row
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
57 expr: (row_number ?? 0) + 6
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
58 - type: copy
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
59 location: [B6,C6]
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
60 destination: '{{ coord2cell(column_number, last_row) }}'
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
61 - type: copy
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
62 location: [B7,C7]
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
63 destination: '{{ coord2cell(column_number, last_row + 1) }}'
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
64 - type: assign
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
65 name: offset
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
66 expr: last_row + 3
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
67 #--------------------------------------------------------------------------
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
68 # GAUGES
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
69 # Header:
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
70 - type: copy
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
71 location: [A9]
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
72 destination: A{{ offset }}
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
73 # Gauges names
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
74 - type: select
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
75 statement: >
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
76 SELECT DISTINCT objname AS gnam FROM waterway.dqr_gauge_stats
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
77 WHERE cc = {{ cc }} ORDER BY objname;
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
78 actions:
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
79 - type: assign
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
80 name: last_row
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
81 expr: (row_number ?? 0) + offset + 1
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
82 - type: copy
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
83 location: [A10]
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
84 destination: A{{ last_row }}
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
85 - type: copy
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
86 location: [A11]
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
87 destination: A{{ last_row + 1 }}
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
88 # Gen Months
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
89 - type: select
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
90 statement: >
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
91 SELECT to_char(d, 'Month YYYY') AS month, d::date
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
92 FROM generate_series( '2019-10-01'::date,
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
93 now() - interval '1 day',
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
94 '1 month'::interval ) d;
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
95 actions:
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
96 - type: assign
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
97 name: column_number
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
98 expr: column2num("B") + (row_number ?? 0)
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
99 - type: copy
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
100 location: [B9]
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
101 destination: '{{ coord2cell(column_number, offset) }}'
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
102 # BN SR-Count
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
103 - type: select
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
104 statement: >
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
105 SELECT objname, daynodata
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
106 FROM waterway.dqr_gauge_stats
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
107 WHERE cc = {{ cc }} AND month = {{ d }}
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
108 ORDER BY objname;
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
109 actions:
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
110 - type: assign
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
111 name: last_row
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
112 expr: (row_number ?? 0) + offset + 1
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
113 - type: copy
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
114 location: [B10]
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
115 destination: '{{ coord2cell(column_number, last_row) }}'
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
116 - type: copy
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
117 location: [B11]
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
118 destination: '{{ coord2cell(column_number, last_row + 1) }}'
90fc90148ef8 Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
119