Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Newer
Older
100644 78 lines (54 sloc) 2.62 kb
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
1 Quantile aggregates
2 ===================
3 This extension provides three simple aggregate functions to compute
8e306dc @tvondra Fixed reference to 'median' function which was removed some time ago …
authored
4 quantiles (http://en.wikipedia.org/wiki/Quantile). There are two
7fcc95f @tvondra Minor fixes and clarifications in the README.
authored
5 forms of aggregate functions available - the first one returns
6 a single quantile, the second one returns an arbitrary number of
7 quantiles (as an array).
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
8
9
8e306dc @tvondra Fixed reference to 'median' function which was removed some time ago …
authored
10 1) quantile(p_value numeric, p_quantile float)
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
11 ----------------------------------------------
7fcc95f @tvondra Minor fixes and clarifications in the README.
authored
12 Computes arbitrary quantile of the values - the p_quantile has to be
13 between 0 and 1. For example this should return 500 because 500 is the
14 middle value of a sequence 1 .. 1000.
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
15
691c6f7 @tvondra README format switched to markdown (.md).
authored
16 SELECT quantile(i, 0.5) FROM generate_series(1,1000) s(i);
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
17
7fcc95f @tvondra Minor fixes and clarifications in the README.
authored
18 but you can choose arbitrary quantile (for example 0.95).
19
20 This function is overloaded for the four basic numeric types, i.e.
21 int, bigint, double precision and numeric.
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
22
23
8e306dc @tvondra Fixed reference to 'median' function which was removed some time ago …
authored
24 2) quantile(p_value numeric, p_quantiles float[])
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
25 -------------------------------------------------
8e306dc @tvondra Fixed reference to 'median' function which was removed some time ago …
authored
26 If you need multiple quantiles at the same time (e.g. all four
27 quartiles), you can use this function instead of the one described
28 above. This version allows you to pass an array of quantiles and
29 returns an array of values.
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
30
31 So if you need all three quartiles, you may do this
32
691c6f7 @tvondra README format switched to markdown (.md).
authored
33 SELECT quantile(i, ARRAY[0.25, 0.5, 0.75])
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
34 FROM generate_series(1,1000) s(i);
35
36 and it should return ARRAY[250, 500, 750]. Compared to calling
37 the simple quantile function like this
38
691c6f7 @tvondra README format switched to markdown (.md).
authored
39 SELECT quantile(i, 0.25), quantile(i, 0.5), quantile(i, 0.75)
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
40 FROM generate_series(1,1000) s(i);
41
8e306dc @tvondra Fixed reference to 'median' function which was removed some time ago …
authored
42 the advantage is that the values are collected just once (into
43 a single array), not for each expression separately. If you're
7fcc95f @tvondra Minor fixes and clarifications in the README.
authored
44 working with large data sets, this may save a significant amount
45 of time and memory (if may even be the factor that allows the query
46 to finish and not being killed by OOM killer or something).
8e306dc @tvondra Fixed reference to 'median' function which was removed some time ago …
authored
47
7fcc95f @tvondra Minor fixes and clarifications in the README.
authored
48 Just as in the first case, there are four functions handling other
49 basic numeric types, i.e. double, int, bigint and numeric.
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
50
51
52 Installation
53 ------------
21fd9b5 @tvondra Minor doc improvement, switched to 'stable'.
authored
54 Installing this is very simple, especially if you're using pgxn client.
55 All you need to do is this:
56
57 $ pgxn install quantile
58 $ pgxn load -d mydb quantile
59
60 and you're done. You may also install the extension manually:
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
61
691c6f7 @tvondra README format switched to markdown (.md).
authored
62 $ make install
63 $ psql dbname -c "CREATE EXTENSION quantile"
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
64
21fd9b5 @tvondra Minor doc improvement, switched to 'stable'.
authored
65 And if you're on an older version (pre-9.1), you have to run the SQL
66 script manually
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
67
e712873 Install quantile version 1.1 on older postgresql versions
Kim Hansen authored
68 $ psql dbname < `pg_config --sharedir`/contrib/quantile--1.1.sql
6ddfb9b @tvondra Initial version - provides median(numeric), quantile(numeric, float) …
authored
69
d00f312 @tvondra Added info about the license, removed obsolete FIXME comments, added …
authored
70 That's all.
71
72
73 License
74 -------
5d829e9 @tvondra Relicensed to BSD 2-clause license. This should make it easier to dis…
authored
75 This software is distributed under the terms of BSD 2-clause license.
76 See LICENSE or http://www.opensource.org/licenses/bsd-license.php for
77 more details.
Something went wrong with that request. Please try again.