Mercurial > gemma

comparison 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
comparison
equal deleted inserted replaced
5389:661e8a2deed9 5390:90fc90148ef8
1 actions:
2 # One sheet per CC
3 - type: select
4 statement: >
5 SELECT cc FROM
6 (VALUES ('AT'), ('SK'), ('HU'), ('HR'), ('RS'), ('BG'), ('RO') )
7 AS t (cc);
8 actions:
9 - type: sheet
10 source: CCTmpl
11 destination: "{{ cc }}"
12 vars: [last_row, column_number, offset]
13 actions:
14 # Header:
15 - type: copy
16 location: [A1,A5]
17 # BN names
18 - type: select
19 statement: >
20 SELECT DISTINCT objnam AS bnnam
21 FROM waterway.dqr_bottleneck_stats
22 WHERE cc = {{ cc }} ORDER BY objnam;
23 actions:
24 - type: assign
25 name: last_row
26 expr: (row_number ?? 0) + 6
27 - type: copy
28 location: [A6]
29 destination: A{{ last_row }}
30 - type: copy
31 location: [A7]
32 destination: A{{ last_row + 1 }}
33 # Gen Months
34 - type: select
35 statement: >
36 SELECT to_char(d, 'Month YYYY') AS month, d::date
37 FROM generate_series( '2019-10-01'::date,
38 now() - interval '1 day',
39 '1 month'::interval ) d;
40 actions:
41 - type: assign
42 name: column_number
43 expr: column2num("B") + (row_number ?? 0) * 2
44 - type: copy
45 location: [B4,C5]
46 destination: '{{ coord2cell(column_number, 4) }}'
47 # BN SR-Count
48 - type: select
49 statement: >
50 SELECT objnam, srcnt, fwacnt
51 FROM waterway.dqr_bottleneck_stats
52 WHERE cc = {{ cc }} AND month = {{ d }}
53 ORDER BY objnam;
54 actions:
55 - type: assign
56 name: last_row
57 expr: (row_number ?? 0) + 6
58 - type: copy
59 location: [B6,C6]
60 destination: '{{ coord2cell(column_number, last_row) }}'
61 - type: copy
62 location: [B7,C7]
63 destination: '{{ coord2cell(column_number, last_row + 1) }}'
64 - type: assign
65 name: offset
66 expr: last_row + 3
67 #--------------------------------------------------------------------------
68 # GAUGES
69 # Header:
70 - type: copy
71 location: [A9]
72 destination: A{{ offset }}
73 # Gauges names
74 - type: select
75 statement: >
76 SELECT DISTINCT objname AS gnam FROM waterway.dqr_gauge_stats
77 WHERE cc = {{ cc }} ORDER BY objname;
78 actions:
79 - type: assign
80 name: last_row
81 expr: (row_number ?? 0) + offset + 1
82 - type: copy
83 location: [A10]
84 destination: A{{ last_row }}
85 - type: copy
86 location: [A11]
87 destination: A{{ last_row + 1 }}
88 # Gen Months
89 - type: select
90 statement: >
91 SELECT to_char(d, 'Month YYYY') AS month, d::date
92 FROM generate_series( '2019-10-01'::date,
93 now() - interval '1 day',
94 '1 month'::interval ) d;
95 actions:
96 - type: assign
97 name: column_number
98 expr: column2num("B") + (row_number ?? 0)
99 - type: copy
100 location: [B9]
101 destination: '{{ coord2cell(column_number, offset) }}'
102 # BN SR-Count
103 - type: select
104 statement: >
105 SELECT objname, daynodata
106 FROM waterway.dqr_gauge_stats
107 WHERE cc = {{ cc }} AND month = {{ d }}
108 ORDER BY objname;
109 actions:
110 - type: assign
111 name: last_row
112 expr: (row_number ?? 0) + offset + 1
113 - type: copy
114 location: [B10]
115 destination: '{{ coord2cell(column_number, last_row) }}'
116 - type: copy
117 location: [B11]
118 destination: '{{ coord2cell(column_number, last_row + 1) }}'
119