Mercurial > gemma
annotate report-templates/data-quality-report.yaml @ 5668:296569ad839b clickable-links
closing clickable-links
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Thu, 07 Dec 2023 16:55:54 +0100 |
parents | e8aa69040bec |
children |
rev | line source |
---|---|
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 actions: |
5643
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
2 # One sheet per CC |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
3 - type: select |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
4 statement: > |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
5 SELECT cc FROM |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
6 (VALUES ('AT'), ('SK'), ('HU'), ('HR'), ('RS'), ('BG'), ('RO') ) |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
7 AS t (cc); |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 actions: |
5643
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
9 - type: sheet |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
10 source: CCTmpl |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
11 destination: "{{ cc }}" |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
12 vars: [last_row, column_number, offset] |
5390
90fc90148ef8
Added materialized views based data quality report
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 actions: |
5643
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
14 # Header: |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
15 - type: copy |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
16 location: [A1, A5] |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
17 # BN names |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
18 - type: select |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
19 statement: > |
5648
e8aa69040bec
Adapt column width in report template. Switch bnid (objnam) to objnam (bnid) in report
Thomas Junk <thomas.junk@intevation.de>
parents:
5646
diff
changeset
|
20 SELECT DISTINCT bottleneck_id, concat(objnam, ' (', bottleneck_id, ')') AS bnnam, hm |
5643
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
21 FROM waterway.dqr_bottleneck_stats |
5646 | 22 WHERE cc = {{ cc }} ORDER BY hm, bottleneck_id; |
5643
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
23 actions: |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
24 - type: assign |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
25 name: last_row |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
26 expr: (row_number ?? 0) + 6 |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
27 - type: copy |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
28 location: [A6] |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
29 destination: A{{ last_row }} |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
30 - type: copy |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
31 location: [A7] |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
32 destination: A{{ (last_row ?? 6) + 1 }} |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
33 # Gen Months |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
34 - type: select |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
35 statement: > |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
36 SELECT to_char(d, 'Month YYYY') AS month, d::date |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
37 FROM generate_series( date_trunc('month', |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
38 now() - interval '1 day' |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
39 )::date, |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
40 '2019-10-01'::date, |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
41 - '1 month'::interval ) d; |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
42 actions: |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
43 - type: assign |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
44 name: column_number |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
45 expr: column2num("B") + (row_number ?? 0) * 2 |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
46 - type: copy |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
47 location: [B4, C5] |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
48 destination: "{{ coord2cell(column_number, 4) }}" |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
49 # BN SR-Count |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
50 - type: select |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
51 statement: > |
5646 | 52 SELECT concat(bottleneck_id, ' (', objnam, ')') AS objnam, srcnt, fwacnt |
5643
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
53 FROM waterway.dqr_bottleneck_stats |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
54 WHERE cc = {{ cc }} AND month = {{ d }} |
5644 | 55 ORDER BY hm, bottleneck_id; |
5643
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
56 actions: |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
57 - type: assign |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
58 name: last_row |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
59 expr: (row_number ?? 0) + 6 |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
60 - type: copy |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
61 location: [B6, C6] |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
62 destination: "{{ coord2cell(column_number, last_row) }}" |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
63 - type: copy |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
64 location: [B7, C7] |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
65 destination: "{{ coord2cell(column_number, (last_row ?? 0) + 1) }}" |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
66 - type: assign |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
67 name: offset |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
68 expr: (last_row ?? 0) + 3 |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
69 #-------------------------------------------------------------------------- |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
70 # GAUGES |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
71 # Header: |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
72 - type: copy |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
73 location: [A9] |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
74 destination: A{{ offset }} |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
75 # Gauges names |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
76 - type: select |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
77 statement: > |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
78 SELECT DISTINCT objname AS gnam, hm FROM waterway.dqr_gauge_stats |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
79 WHERE cc = {{ cc }} ORDER BY hm, objname; |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
80 actions: |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
81 - type: assign |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
82 name: last_row |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
83 expr: (row_number ?? 0) + offset + 1 |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
84 - type: copy |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
85 location: [A10] |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
86 destination: A{{ last_row }} |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
87 - type: copy |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
88 location: [A11] |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
89 destination: A{{ (last_row ?? 10) + 1 }} |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
90 # Gen Months |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
91 - type: select |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
92 statement: > |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
93 SELECT to_char(d, 'Month YYYY') AS month, d::date |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
94 FROM generate_series( date_trunc('month', |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
95 now() - interval '1 day' |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
96 )::date, |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
97 '2019-10-01'::date, |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
98 - '1 month'::interval ) d; |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
99 actions: |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
100 - type: assign |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
101 name: column_number |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
102 expr: column2num("B") + (row_number ?? 0) |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
103 - type: copy |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
104 location: [B9] |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
105 destination: "{{ coord2cell(column_number, offset) }}" |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
106 # BN SR-Count |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
107 - type: select |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
108 statement: > |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
109 SELECT objname, daynodata |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
110 FROM waterway.dqr_gauge_stats |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
111 WHERE cc = {{ cc }} AND month = {{ d }} |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
112 ORDER BY hm, objname; |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
113 actions: |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
114 - type: assign |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
115 name: last_row |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
116 expr: (row_number ?? 0) + offset + 1 |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
117 - type: copy |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
118 location: [B10] |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
119 destination: "{{ coord2cell(column_number, last_row) }}" |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
120 - type: copy |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
121 location: [B11] |
b4ee50490592
Correct formatting DQR-Report
Thomas Junk <thomas.junk@intevation.de>
parents:
5488
diff
changeset
|
122 destination: "{{ coord2cell(column_number, (last_row ?? 10) + 1) }}" |