-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathfunctions.sql
117 lines (109 loc) · 5.26 KB
/
functions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT $1[s.i] AS "foo"
FROM
generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
ORDER BY foo
);
$$;
CREATE OR REPLACE FUNCTION percentile_cont(myarray REAL[], percentile REAL)
RETURNS REAL AS
$$
DECLARE
ary_cnt INTEGER;
row_num REAL;
crn REAL;
frn REAL;
calc_result REAL;
new_array REAL[];
BEGIN
ary_cnt = array_length(myarray,1);
row_num = 1 + ( percentile * ( ary_cnt - 1 ));
new_array = array_sort(myarray);
crn = ceiling(row_num);
frn = floor(row_num);
if crn = frn and frn = row_num then
calc_result = new_array[row_num];
else
calc_result = (crn - row_num) * new_array[frn]
+ (row_num - frn) * new_array[crn];
end if;
RETURN calc_result;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
CREATE OR REPLACE FUNCTION speed_summary (period bigint, outstationid integer) RETURNS table(outstationid smallint, period bigint, volume bigint, speed_min smallint, speed_max smallint, speed_avg numeric, speed_stddev numeric, speed_85th_percentile real, _0_to_10 bigint, _11_to_20 bigint, _21_to_30 bigint, _31_to_40 bigint, _41_to_50 bigint, _51_to_60 bigint, _61_to_70 bigint, _70_plus bigint) AS $$
SELECT
outstationid,
(time - (time % $1)) as period,
count(speed) as volume,
round((min(speed) / 1.6))::smallint as speed_min,
round((max(speed)/1.6))::smallint as speed_max,
round((avg(speed)/1.6)) as speed_avg,
round(stddev((speed/1.6)),2) as speed_stddev,
round((percentile_cont(array_agg(speed), 0.85)/1.6)::numeric)::real as speed_85th_percentile,
count(CASE WHEN (speed/1.6) BETWEEN 0 AND 10 THEN 1 ELSE null END) as _0_to_10,
count(CASE WHEN (speed/1.6) BETWEEN 11 AND 20 THEN 1 ELSE null END) as _11_to_20,
count(CASE WHEN (speed/1.6) BETWEEN 21 AND 30 THEN 1 ELSE null END) as _21_to_30,
count(CASE WHEN (speed/1.6) BETWEEN 31 AND 40 THEN 1 ELSE null END) as _31_to_40,
count(CASE WHEN (speed/1.6) BETWEEN 41 AND 50 THEN 1 ELSE null END) as _41_to_50,
count(CASE WHEN (speed/1.6) BETWEEN 51 AND 60 THEN 1 ELSE null END) as _51_to_60,
count(CASE WHEN (speed/1.6) BETWEEN 61 AND 70 THEN 1 ELSE null END) as _61_to_70,
count(CASE WHEN (speed/1.6) > 70 THEN 1 ELSE null END) as _70_plus
FROM dbdata
WHERE outstationid = $2
GROUP BY outstationid,period;
$$ LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION speed_summary_lane (period bigint, outstationid integer) RETURNS table(outstationid smallint, period bigint, lane smallint, volume bigint, speed_min smallint, speed_max smallint, speed_avg numeric, speed_stddev numeric, speed_85th_percentile real, _0_to_10 bigint, _11_to_20 bigint, _21_to_30 bigint, _31_to_40 bigint, _41_to_50 bigint, _51_to_60 bigint, _61_to_70 bigint, _70_plus bigint) AS $$
SELECT
outstationid,
(time - (time % $1)) as period,
lane,
count(speed) as volume,
round((min(speed) / 1.6))::smallint as speed_min,
round((max(speed)/1.6))::smallint as speed_max,
round((avg(speed)/1.6)) as speed_avg,
round(stddev((speed/1.6)),2) as speed_stddev,
round((percentile_cont(array_agg(speed), 0.85)/1.6)::numeric)::real as speed_85th_percentile,
count(CASE WHEN (speed/1.6) BETWEEN 0 AND 10 THEN 1 ELSE null END) as _0_to_10,
count(CASE WHEN (speed/1.6) BETWEEN 11 AND 20 THEN 1 ELSE null END) as _11_to_20,
count(CASE WHEN (speed/1.6) BETWEEN 21 AND 30 THEN 1 ELSE null END) as _21_to_30,
count(CASE WHEN (speed/1.6) BETWEEN 31 AND 40 THEN 1 ELSE null END) as _31_to_40,
count(CASE WHEN (speed/1.6) BETWEEN 41 AND 50 THEN 1 ELSE null END) as _41_to_50,
count(CASE WHEN (speed/1.6) BETWEEN 51 AND 60 THEN 1 ELSE null END) as _51_to_60,
count(CASE WHEN (speed/1.6) BETWEEN 61 AND 70 THEN 1 ELSE null END) as _61_to_70,
count(CASE WHEN (speed/1.6) > 70 THEN 1 ELSE null END) as _70_plus
FROM dbdata
WHERE outstationid = $2
GROUP BY outstationid,period,lane;
$$ LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION volume_summary (period bigint, outstationid integer) RETURNS table(outstationid smallint, period bigint, volume bigint, _unclassified bigint, _0_to_52 bigint, _52_to_70 bigint, _70_to_110 bigint, _110_plus bigint) AS $$
SELECT
outstationid,
(time - (time % $1)) as period,
count(classcode) as volume,
count(CASE WHEN classcode = 0 THEN 1 ELSE null END) as _unclassified,
count(CASE WHEN classcode = 1 THEN 1 ELSE null END) as _0_to_52,
count(CASE WHEN classcode = 2 THEN 1 ELSE null END) as _50_to_70,
count(CASE WHEN classcode = 3 THEN 1 ELSE null END) as _70_to_110,
count(CASE WHEN classcode = 4 THEN 1 ELSE null END) as _110_plus
FROM dbdata
WHERE outstationid = $2
GROUP BY outstationid,period;
$$ LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION volume_summary_lane (period bigint, outstationid integer) RETURNS table(outstationid smallint, period bigint, lane smallint, volume bigint, _unclassified bigint, _0_to_52 bigint, _52_to_70 bigint, _70_to_110 bigint, _110_plus bigint) AS $$
SELECT
outstationid,
(time - (time % $1)) as period,
lane,
count(classcode) as volume,
count(CASE WHEN classcode = 0 THEN 1 ELSE null END) as _unclassified,
count(CASE WHEN classcode = 1 THEN 1 ELSE null END) as _0_to_52,
count(CASE WHEN classcode = 2 THEN 1 ELSE null END) as _50_to_70,
count(CASE WHEN classcode = 3 THEN 1 ELSE null END) as _70_to_110,
count(CASE WHEN classcode = 4 THEN 1 ELSE null END) as _110_plus
FROM dbdata
WHERE outstationid = $2
GROUP BY outstationid,period,lane;
$$ LANGUAGE 'sql';