· 8 years ago · Apr 07, 2018, 12:04 PM
1CREATE OR REPLACE FUNCTION stat.calc_train_avg_and_max_sensor_value_at_speed(
2 p_date_from date,
3 p_date_to date)
4 RETURNS void AS
5$BODY$
6declare
7 v integer := 0;
8 t BIGINT := 0;
9begin
10 FOR t IN (SELECT stat.get_trains()) LOOP
11 v := 0;
12
13 while v <= 80 loop
14 insert into
15 stat.train_avg_and_max_sensor_value_at_speed (measurement_date, train_number, sensor_uid, speed_value, sensor_avg_value, sensor_max_value)
16 select
17 m.date::date as measurement_date,
18 t AS "train_number",
19 m.sensor_uid as sensor_uid,
20 v as "speed_value",
21 avg(m.value)::integer as sensor_avg_value,
22 max(m.value) as sensor_max_value
23 from
24 measurement as m
25 where
26 m.sensor_uid in (select stat.get_train_sensors(t))
27 and date_trunc('second', m.date) in (select stat.get_train_speed_time_points(t, v, p_date_from, p_date_to))
28 group by
29 m.date::date,
30 m.sensor_uid,
31 speed_value
32 order by
33 m.date::date;
34
35 v = v + 1;
36 end loop;
37 END LOOP;
38end;
39$BODY$
40 LANGUAGE plpgsql VOLATILE STRICT
41 COST 100;
42ALTER FUNCTION stat.calc_train_avg_and_max_sensor_value_at_speed(date, date)
43 OWNER TO postgres;
44
45CREATE OR REPLACE FUNCTION stat.calculate_statistics(
46 p_date_from date,
47 p_date_to date)
48 RETURNS void AS
49$BODY$
50DECLARE calc_date DATE = p_date_from;
51DECLARE current_train BIGINT = 0;
52DECLARE query_text TEXT = '
53 DROP TABLE IF EXISTS stat."stat_%1$s";
54 CREATE TABLE stat."stat_%1$s" (
55 "uid" BIGINT,
56 "min" INTEGER,
57 "avg" INTEGER,
58 "max" INTEGER);
59
60 INSERT INTO stat."stat_%1$s" ("uid", "min", "avg", "max")
61 SELECT m1.sensor_uid AS "uid",
62 min(m1.value) AS "min",
63 avg(m1.value)::INTEGER AS "avg",
64 max(m1.value) AS "max"
65 FROM public."measurement_%1$s" AS m1
66 WHERE m1.sensor_uid IN (SELECT stat.get_train_sensors(%2$s))
67 AND date_trunc(''second'', m1.date) IN (
68 SELECT DISTINCT date_trunc(''second'', m2.date)
69 FROM public."measurement_%1$s" AS m2
70 WHERE m2.sensor_uid = (SELECT stat.get_train_speed_sensor(%2$s))
71 AND m2.value = 60)
72 GROUP BY uid;';
73DECLARE query_check_table_exists TEXT = '
74 SELECT 1 FROM information_schema.tables WHERE table_name = ''measurement_%1$s'' AND table_schema = ''public'';';
75DECLARE rv INTEGER = 0;
76BEGIN
77 WHILE calc_date <= p_date_to LOOP
78 FOR current_train IN (SELECT stat.get_trains()) LOOP
79 EXECUTE format(query_check_table_exists, calc_date::TEXT) INTO rv;
80 IF rv = 1 THEN
81 RAISE NOTICE 'Calculate statistics for train % on %', current_train::TEXT, calc_date;
82
83 EXECUTE format(query_text, calc_date::TEXT, current_train::TEXT);
84 END IF;
85 END LOOP;
86
87 calc_date = (SELECT calc_date + INTERVAL '1 day');
88 END LOOP;
89END
90$BODY$
91 LANGUAGE plpgsql VOLATILE
92 COST 100;
93ALTER FUNCTION stat.calculate_statistics(date, date)
94 OWNER TO postgres;
95
96CREATE OR REPLACE FUNCTION stat.calculate_statistics_agg(
97 p_date_from date,
98 p_date_to date)
99 RETURNS void AS
100$BODY$
101DECLARE exists_tmpl TEXT = '
102 SELECT 1
103 FROM information_schema.tables AS ists
104 WHERE ists.table_name = ''measurement_%1$s''
105 AND ists.table_schema = ''public'';
106 ';
107DECLARE calc_tmpl TEXT = '
108 INSERT INTO stat.stat ("date", "uid", "min")
109 SELECT ''%1$s''::DATE AS "date",
110 m1.sensor_uid AS "uid",
111 min(m1.value) AS "min"
112 FROM public."measurement_%1$s" AS m1
113 WHERE m1.sensor_uid IN (SELECT stat.get_train_sensors(%2$s))
114 AND date_trunc(''second'', m1.date) IN (
115 SELECT DISTINCT date_trunc(''second'', m2.date)
116 FROM public."measurement_%1$s" AS m2
117 WHERE m2.sensor_uid = (SELECT stat.get_train_speed_sensor(%2$s))
118 AND m2.value = 55
119 )
120 GROUP BY "uid"
121 ORDER BY "date", "uid";
122 ';
123DECLARE date_iter DATE = p_date_from;
124DECLARE train_iter BIGINT = 0;
125DECLARE is_exists INTEGER = 0;
126BEGIN
127 CREATE TABLE IF NOT EXISTS stat.stat (
128 "date" DATE,
129 "uid" BIGINT,
130 "min" INTEGER
131 );
132
133 WHILE date_iter <= p_date_to LOOP
134 is_exists = 0;
135 EXECUTE format(exists_tmpl, date_iter::TEXT) INTO is_exists;
136 IF is_exists = 1 THEN
137 FOR train_iter IN (SELECT stat.get_trains()) LOOP
138 RAISE NOTICE 'Calculation started (date = %; train id = %).', date_iter, train_iter;
139 EXECUTE format(calc_tmpl, date_iter::TEXT, train_iter::TEXT);
140 END LOOP;
141 END IF;
142
143 date_iter = (SELECT date_iter + INTERVAL '1 day');
144 END LOOP;
145END
146$BODY$
147 LANGUAGE plpgsql VOLATILE
148 COST 100;
149ALTER FUNCTION stat.calculate_statistics_agg(date, date)
150 OWNER TO postgres;
151
152CREATE OR REPLACE FUNCTION stat.get_train_sensors(a_train_id bigint)
153 RETURNS SETOF bigint AS
154$BODY$
155BEGIN
156 RETURN QUERY (
157 SELECT s.uid
158 FROM public.sensor AS s
159 WHERE s.unit_a_id IN (
160 SELECT u.id
161 FROM public.get_unit_a_of_train(a_train_id) AS u
162 )
163 );
164END
165$BODY$
166 LANGUAGE plpgsql STABLE
167 COST 100
168 ROWS 1000;
169ALTER FUNCTION stat.get_train_sensors(bigint)
170 OWNER TO postgres;
171
172CREATE OR REPLACE FUNCTION stat.get_train_speed_sensor(a_train_id bigint)
173 RETURNS bigint AS
174$BODY$
175BEGIN
176 RETURN (
177 SELECT s.uid
178 FROM public.sensor AS s
179 WHERE s.type_id in (
180 SELECT st.id
181 FROM public.sensor_type AS st
182 WHERE st.type_code = 2
183 )
184 AND s.train_id = a_train_id
185 LIMIT 1
186 );
187END
188$BODY$
189 LANGUAGE plpgsql STABLE
190 COST 100;
191ALTER FUNCTION stat.get_train_speed_sensor(bigint)
192 OWNER TO postgres;
193
194CREATE OR REPLACE FUNCTION stat.get_train_speed_time_points(
195 a_train_id bigint,
196 a_speed_value integer,
197 a_from date,
198 a_to date)
199 RETURNS SETOF timestamp without time zone AS
200$BODY$
201BEGIN
202 RETURN QUERY (
203 SELECT date_trunc('second', m.date)
204 FROM public.measurement AS m
205 WHERE m.sensor_uid = (SELECT stat.get_train_speed_sensor(a_train_id))
206 AND m.value = a_speed_value
207 AND m.date >= a_from
208 and m.date <= a_to
209 ORDER BY m.date
210 );
211END
212$BODY$
213 LANGUAGE plpgsql STABLE
214 COST 100
215 ROWS 1000;
216ALTER FUNCTION stat.get_train_speed_time_points(bigint, integer, date, date)
217 OWNER TO postgres;
218
219CREATE OR REPLACE FUNCTION stat.get_train_speed_time_points(
220 a_train_id bigint,
221 a_speed_value integer,
222 a_interval interval)
223 RETURNS SETOF timestamp without time zone AS
224$BODY$
225BEGIN
226 RETURN QUERY (
227 SELECT date_trunc('second', m.date)
228 FROM public.measurement AS m
229 WHERE m.sensor_uid = (SELECT stat.get_train_speed_sensor(a_train_id))
230 AND m.value = a_speed_value
231 AND m.date > date(now() - a_interval)
232 ORDER BY m.date
233 );
234END
235$BODY$
236 LANGUAGE plpgsql STABLE
237 COST 100
238 ROWS 1000;
239ALTER FUNCTION stat.get_train_speed_time_points(bigint, integer, interval)
240 OWNER TO postgres;
241
242CREATE OR REPLACE FUNCTION stat.get_trains()
243 RETURNS SETOF bigint AS
244$BODY$
245BEGIN
246 RETURN QUERY (
247 SELECT t.id
248 FROM public.train AS t
249 );
250END
251$BODY$
252 LANGUAGE plpgsql STABLE
253 COST 100
254 ROWS 1000;
255ALTER FUNCTION stat.get_trains()
256 OWNER TO postgres;