Mercurial > gemma

annotate report-templates/data-quality-report.yaml @ 5679:03dfbe675842 sr-v2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Simplified version handling.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Sun, 11 Feb 2024 12:37:09 +0100
parents e8aa69040bec
children
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:
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
88fdf143daf1 finalize report
Thomas Junk <thomas.junk@intevation.de>
parents: 5644
diff changeset
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
88fdf143daf1 finalize report
Thomas Junk <thomas.junk@intevation.de>
parents: 5644
diff changeset
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
Thomas Junk <thomas.junk@intevation.de>
parents: 5643
diff changeset
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) }}"