Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Newer
Older
100644 99 lines (69 sloc) 3.051 kB
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
1 Trimmed aggregates
2 ==================
3 This PostgreSQL extension provides several aggregate functions that
4 trim the input data set before applying the function, i.e. remove
5 lowest/highest values. The number of values to be removed is configured
6 using the parameters.
7
8 WARNING: Those aggregates require the whole set, as they need to collect
9 and sort the whole data set ((to trim low/high values). This may be
10 a time consuming process and require a lot of memory. Keep this in mind
11 when using those functions.
12
13
14 Available aggregates
15 --------------------
16 The extension implements aggregates that resemble those described here:
17 http://www.postgresql.org/docs/9.1/static/functions-aggregate.html, i.e.
18 AVG, VARIANCE, VAR_POP, VAR_SAMP, STDDEV, STDDEV_POP and STDDEV_SAMP
19
feb7350 @tvondra README switched to markdown (.md).
authored
20 * AVG
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
21
feb7350 @tvondra README switched to markdown (.md).
authored
22 avg_trimmed(value, low_cut, high_cut)
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
23
feb7350 @tvondra README switched to markdown (.md).
authored
24 * VARIANCE
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
25
feb7350 @tvondra README switched to markdown (.md).
authored
26 var_trimmed(value, low_cut, high_cut);
27 var_pop_trimmed(value, low_cut, high_cut)
28 var_samp_trimmed(value, low_cut, high_cut)
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
29
feb7350 @tvondra README switched to markdown (.md).
authored
30 * STDDEV (standard deviation)
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
31
feb7350 @tvondra README switched to markdown (.md).
authored
32 stddev_trimmed(value, low_cut, high_cut)
33 stddev_pop_trimmed(value, low_cut, high_cut)
34 stddev_samp_trimmed(value, low_cut, high_cut)
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
35
b746f26 @tvondra Implemented combined aggregates (computing all the values at once).
authored
36 * combined aggregate (computes all seven values at once)
37
38 trimmed(value, low_cut, high_cut)
39
40 All those functions are overloaded for numeric, double precision, int32
41 and int64 data types.
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
42
43 Using the aggregates
44 --------------------
45 All the aggregates are used the same way so let's see how to use the
46 avg_trimmed aggregate. For example this
47
feb7350 @tvondra README switched to markdown (.md).
authored
48 SELECT avg_trimmed(i, 0.1, 0.1) FROM generate_series(1,1000) s(i);
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
49
50 means 10% of the values will be removed on both ends, and the average
fa3b7bb @tvondra Rewritten to C, reorganized to match PGXN structure, added META.json …
authored
51 will be computed using the middle 80%. On the other hand this
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
52
feb7350 @tvondra README switched to markdown (.md).
authored
53 SELECT avg_trimmed(i, 0.2, 0.1) FROM generate_series(1,1000) s(i);
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
54
55 means 20% of the lowest and 10% of the highest values will be removed,
fa3b7bb @tvondra Rewritten to C, reorganized to match PGXN structure, added META.json …
authored
56 so the average will be computed using the remaining 70% of values.
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
57
b746f26 @tvondra Implemented combined aggregates (computing all the values at once).
authored
58 The combined aggregate computes and returns all values at once as an
59 array. The values are stored in this order
60
61 * average
62 * var_pop
63 * var_samp
64 * variance
65 * stddev_pop
66 * stddev_samp
67 * stddev
68
69 If you need more of the values at once this may be much more efficient
70 as it shares the memory and can compute the values with only two passes
71 through the data (to compute exact variance and stddev).
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
72
73 Installation
74 ------------
19d8bfc @tvondra Additional info about using the pgxn client to install the extension.
authored
75 Installing this extension is very simple - if you're using pgxn client
76 (and you should), just do this:
77
78 $ pgxn install --testing trimmed_aggregates
79 $ pgxn load --testing -d mydb trimmed_aggregates
80
81 You can also install manually, just it like any other extension, i.e.
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
82
feb7350 @tvondra README switched to markdown (.md).
authored
83 $ make install
84 $ psql dbname -c "CREATE EXTENSION trimmed_averages"
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
85
19d8bfc @tvondra Additional info about using the pgxn client to install the extension.
authored
86 And if you're on an older PostgreSQL version, you have to run the SQL
87 script manually (use the proper version).
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
88
feb7350 @tvondra README switched to markdown (.md).
authored
89 $ psql dbname < trimmed_averages--1.0.sql
5ab59fe @tvondra Initial version, providing trimming alternatives to AVG, VARIANCE, VA…
authored
90
30cc987 @tvondra Added license details (GPL v3).
authored
91 That's all.
92
93
94 License
95 -------
812a6a5 @tvondra Relicensed under 2-clause BSD license.
authored
96 This software is distributed under the terms of BSD 2-clause license.
97 See LICENSE or http://www.opensource.org/licenses/bsd-license.php for
98 more details.
Something went wrong with that request. Please try again.