Debian Codegraph @

www/percentile.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>The Percentile Extension</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 The Percentile Extension
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div>
126 <div class="fancy-toc2"><a href="#availability">1.1. Availability</a></div>
127 <div class="fancy-toc1"><a href="#aggregate_functions_implemented_by_percentile">2. Aggregate Functions Implemented By Percentile</a></div>
128 <div class="fancy-toc2"><a href="#the_median_y_aggregate_function">2.1. The median(Y) aggregate function</a></div>
129 <div class="fancy-toc2"><a href="#the_percentile_y_p_aggregate_function">2.2. The percentile(Y,P) aggregate function</a></div>
130 <div class="fancy-toc2"><a href="#the_percentile_cont_y_p_aggregate_function">2.3. The percentile_cont(Y,P) aggregate function</a></div>
131 <div class="fancy-toc2"><a href="#the_percentile_disc_y_p_aggregate_function">2.4. The percentile_disc(Y,P) aggregate function</a></div>
132 <div class="fancy-toc1"><a href="#design_requirements">3. Design Requirements</a></div>
133 </div>
134 </details>
135 </div>
136
137
138
139
140 <h1 id="overview"><span>1. </span>Overview</h1>
141
142 <p> The percentile extension provides four aggregate functions
143 that compute a percentile score and/or the median value for a distribution.
144
145 </p><h2 id="availability"><span>1.1. </span>Availability</h2>
146
147 <p>The percentile extension has been part of <a href="amalgamation.html">the amalgamation</a> since
148 SQLite version 3.51.0 (2025-11-04). However, the percentile extension
149 is disabled by default and must be activated at compile-time using
150 the <a href="compile.html#enable_percentile">-DSQLITE_ENABLE_PERCENTILE</a> compile-time option.
151
152 </p><p>Prior to SQLite 3.51.0, the percentile
153 extension was in a separate source code file that needed to be compiled
154 independently and treated as a <a href="loadext.html">loadable extension</a>.
155
156 </p><h1 id="aggregate_functions_implemented_by_percentile"><span>2. </span>Aggregate Functions Implemented By Percentile</h1>
157
158 <p>The percentile extension implements the aggregate SQL functions
159 described below. The algorithms used by all of these functions
160 use O(N) space and O(NlogN) time, where N is the number of non-NULL
161 inputs.
162
163 <a name="*medianfunc"></a>
164
165 </p><h2 id="the_median_y_aggregate_function"><span>2.1. </span>The median(Y) aggregate function</h2>
166
167 <p>The median(Y) function is an aggregate that computes the median value of
168 all non-NULL inputs Y. If any Y input to median() is not NULL and is not
169 a numeric value, an error is raised. If there are no non-NULL, numeric
170 inputs, then the result of median() is NULL.
171
172 </p><p>The median is the value of the center element when all the inputs are
173 sorted and the number of inputs is odd. If the number of
174 inputs is even, then the median is the average of the two center inputs.
175
176 </p><p>The median(Y) function is equivalent to <a href="lang_aggfunc.html#percentile">percentile(Y,50)</a>.
177
178 <a name="*percentilefunc"></a>
179
180 </p><h2 id="the_percentile_y_p_aggregate_function"><span>2.2. </span>The percentile(Y,P) aggregate function</h2>
181
182 <p>The percentile(Y,P) aggregate function computes an answer X which is
183 a value that is greater than or equal to P percent of the non-NULL inputs
184 and which is less than or equal to 100-P percent of the inputs. The
185 parameter P must be a number between 0.0 and 100.0. The value of P must
186 be the same for all terms of the aggregate and may not be NULL. Y inputs
187 must be either NULL or numeric. NULL values for Y are ignored. Any
188 non-NULL Y input that is not numeric causes an error to be raised.
189
190 </p><p>The percentile() function works by sorting the non-NULL inputs and then
191 computing the input or inputs that are closest to P percent from the first
192 to the last. The return value is the weighted average of the two closest
193 inputs.
194
195 <a name="*percentilecontfunc"></a>
196
197 </p><h2 id="the_percentile_cont_y_p_aggregate_function"><span>2.3. </span>The percentile_cont(Y,P) aggregate function</h2>
198
199 <p>The percentile_cont(Y,P) function works like <a href="lang_aggfunc.html#percentile">percentile(Y,P)</a> except that
200 the P value spans the range of 0.0 to 1.0 instead of 0.0 to 100.0. Thus
201 the result of percentile_cont(Y,P) is the same as <a href="lang_aggfunc.html#percentile">percentile(Y,P*100)</a>.
202
203 </p><p>The percentile_cont() function is defined by SQL standards. However, instead
204 of being a simple function call "percentile_cont(Y,P)", the
205 SQL-standard syntax goes like this:
206
207 </p><div class="codeblock"><pre>SELECT percentile_cont(P) WITHIN GROUP (ORDER BY Y) FROM tab;
208 </pre></div>
209
210 <p>That is a lot of syntax to mean exactly the same thing as:
211
212 </p><div class="codeblock"><pre>SELECT percentile_cont(Y,P) FROM tab;
213 </pre></div>
214
215 <p>SQLite will support the SQL-standard syntax, but only if it is compiled
216 (from canonical sources, not from the <a href="amalgamation.html">amalgamation</a>) using the
217 <a href="compile.html#enable_ordered_set_aggregates">-DSQLITE_ENABLE_ORDERED_SET_AGGREGATES=1</a> compile-time option. Without that
218 compile-time option, only the simpler "percentile_cont(Y,P)" form is supported. Since
219 there are no advantages to the prolix SQL-standard format, and dramatic
220 readability disadvantages, and because the <a href="compile.html#enable_ordered_set_aggregates">SQLITE_ENABLE_ORDERED_SET_AGGREGATES</a>
221 compile-time option causes the SQLite library to be larger, that option is
222 omitted from most builds.
223
224 </p><p>This author believes that the "_cont" suffix on this function name is
225 an abbreviation for "continuous" and reflects the fact that the return value
226 is a weighted average of the two closest input values to the actual percentile
227 rank. The name is an SQL standard, not something
228 chosen by the SQLite developers.
229
230 <a name="*percentilediscfunc"></a>
231
232 </p><h2 id="the_percentile_disc_y_p_aggregate_function"><span>2.4. </span>The percentile_disc(Y,P) aggregate function</h2>
233
234 <p>The percentile_disc(Y,P) function works like <a href="lang_aggfunc.html#percentile_cont">percentile_cont(Y,P)</a> except
235 that instead of doing a weighted average of the closest available inputs, it
236 always returns a value that is one of the input values - the smaller of the
237 two possible choices. The percentile_disc(Y,P) function is defined by SQL
238 standards. As with percentile_cont(), the prolix ordered-set aggregate syntax
239 is required, but that syntax is only supported by SQLite when SQLite is
240 compiled using the <a href="compile.html#enable_ordered_set_aggregates">SQLITE_ENABLE_ORDERED_SET_AGGREGATES</a> compile-time option.
241
242 </p><p>This author believes that the "_disc" suffix on this function name is
243 an abbreviation for "discrete". The name is an SQL standard, not something
244 chosen by the SQLite developers.
245
246
247 </p><h1 id="design_requirements"><span>3. </span>Design Requirements</h1>
248
249 <p>The following requirements define the percentile extension.
250
251 </p><ol>
252 <li><p> The percentile(Y,P) function is an aggregate function taking
253 exactly two arguments.
254
255 </p></li><li><p> If the P argument to percentile(Y,P) is not the same for every
256 row in the aggregate then an error is thrown. The word "same"
257 in the previous sentence means that the value differs by less
258 than 0.001.
259
260 </p></li><li><p> If the P argument to percentile(Y,P) evaluates to anything other
261 than a number in the range of 0.0 to 100.0 inclusive then an
262 error is thrown.
263
264 </p></li><li><p> If any Y argument to percentile(Y,P) evaluates to a value that
265 is not NULL and is not numeric then an error is thrown.
266
267 </p></li><li><p> If any Y argument to percentile(Y,P) evaluates to plus or minus
268 infinity then an error is thrown. (SQLite always interprets NaN
269 values as NULL.)
270
271 </p></li><li><p> Both Y and P in percentile(Y,P) can be arbitrary expressions,
272 including CASE WHEN expressions.
273
274 </p></li><li><p> The percentile(Y,P) aggregate is able to handle inputs of at least
275 one million (1,000,000) rows.
276
277 </p></li><li><p> If there are no non-NULL values for Y, then percentile(Y,P)
278 returns NULL.
279
280 </p></li><li><p> If there is exactly one non-NULL value for Y, the percentile(Y,P)
281 returns the one Y value.
282
283 </p></li><li><p> If there are N non-NULL values of Y where N is two or more and
284 the Y values are ordered from least to greatest and a graph is
285 drawn from 0 to N-1 such that the height of the graph at J is
286 the J-th Y value and such that straight lines are drawn between
287 adjacent Y values, then the percentile(Y,P) function returns
288 the height of the graph at P*(N-1)/100.
289
290 </p></li><li><p> The percentile(Y,P) function always returns either a floating
291 point number or NULL.
292
293 </p></li><li><p> The percentile(Y,P) is implemented as a single C99 source-code
294 file that compiles into a shared-library or DLL that can be loaded
295 into SQLite using the sqlite3_load_extension() interface.
296
297 </p></li><li><p> A separate median(Y) function is the equivalent of percentile(Y,50).
298
299 </p></li><li><p> A separate percentile_cont(Y,P) function returns the same result as
300 percentile(Y,P*100.0). In other words, the fraction value in
301 the second argument to percentile_cont() should be in the range
302 of 0 to 1 instead of 0 to 100 as it is for percentile().
303
304 </p></li><li><p> A separate percentile_disc(Y,P) function is like
305 percentile_cont(Y,P) except that instead of returning the weighted
306 average of the nearest two input values, it returns the next lower
307 value. So the percentile_disc(Y,P) will always return a value
308 that was one of the inputs.
309
310 </p></li><li><p> All of median(), percentile(Y,P), percentile_cont(Y,P) and
311 percentile_disc(Y,P) can be used as window functions.
312 </p></li></ol>
313 <p align="center"><small><i>This page was last updated on 2026-02-25 22:25:39Z </small></i></p>
314