Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * windowfuncs.c
4 : * Standard window functions defined in SQL spec.
5 : *
6 : * Portions Copyright (c) 2000-2020, PostgreSQL Global Development Group
7 : *
8 : *
9 : * IDENTIFICATION
10 : * src/backend/utils/adt/windowfuncs.c
11 : *
12 : *-------------------------------------------------------------------------
13 : */
14 : #include "postgres.h"
15 :
16 : #include "utils/builtins.h"
17 : #include "windowapi.h"
18 :
19 : /*
20 : * ranking process information
21 : */
22 : typedef struct rank_context
23 : {
24 : int64 rank; /* current rank */
25 : } rank_context;
26 :
27 : /*
28 : * ntile process information
29 : */
30 : typedef struct
31 : {
32 : int32 ntile; /* current result */
33 : int64 rows_per_bucket; /* row number of current bucket */
34 : int64 boundary; /* how many rows should be in the bucket */
35 : int64 remainder; /* (total rows) % (bucket num) */
36 : } ntile_context;
37 :
38 : static bool rank_up(WindowObject winobj);
39 : static Datum leadlag_common(FunctionCallInfo fcinfo,
40 : bool forward, bool withoffset, bool withdefault);
41 :
42 :
43 : /*
44 : * utility routine for *_rank functions.
45 : */
46 : static bool
47 110340 : rank_up(WindowObject winobj)
48 : {
49 110340 : bool up = false; /* should rank increase? */
50 110340 : int64 curpos = WinGetCurrentPosition(winobj);
51 : rank_context *context;
52 :
53 : context = (rank_context *)
54 110340 : WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
55 :
56 110340 : if (context->rank == 0)
57 : {
58 : /* first call: rank of first row is always 1 */
59 : Assert(curpos == 0);
60 240 : context->rank = 1;
61 : }
62 : else
63 : {
64 : Assert(curpos > 0);
65 : /* do current and prior tuples match by ORDER BY clause? */
66 110100 : if (!WinRowsArePeers(winobj, curpos - 1, curpos))
67 89436 : up = true;
68 : }
69 :
70 : /* We can advance the mark, but only *after* access to prior row */
71 110340 : WinSetMarkPosition(winobj, curpos);
72 :
73 110340 : return up;
74 : }
75 :
76 :
77 : /*
78 : * row_number
79 : * just increment up from 1 until current partition finishes.
80 : */
81 : Datum
82 276924 : window_row_number(PG_FUNCTION_ARGS)
83 : {
84 276924 : WindowObject winobj = PG_WINDOW_OBJECT();
85 276924 : int64 curpos = WinGetCurrentPosition(winobj);
86 :
87 276924 : WinSetMarkPosition(winobj, curpos);
88 276924 : PG_RETURN_INT64(curpos + 1);
89 : }
90 :
91 :
92 : /*
93 : * rank
94 : * Rank changes when key columns change.
95 : * The new rank number is the current row number.
96 : */
97 : Datum
98 110220 : window_rank(PG_FUNCTION_ARGS)
99 : {
100 110220 : WindowObject winobj = PG_WINDOW_OBJECT();
101 : rank_context *context;
102 : bool up;
103 :
104 110220 : up = rank_up(winobj);
105 : context = (rank_context *)
106 110220 : WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
107 110220 : if (up)
108 89388 : context->rank = WinGetCurrentPosition(winobj) + 1;
109 :
110 110220 : PG_RETURN_INT64(context->rank);
111 : }
112 :
113 : /*
114 : * dense_rank
115 : * Rank increases by 1 when key columns change.
116 : */
117 : Datum
118 40 : window_dense_rank(PG_FUNCTION_ARGS)
119 : {
120 40 : WindowObject winobj = PG_WINDOW_OBJECT();
121 : rank_context *context;
122 : bool up;
123 :
124 40 : up = rank_up(winobj);
125 : context = (rank_context *)
126 40 : WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
127 40 : if (up)
128 16 : context->rank++;
129 :
130 40 : PG_RETURN_INT64(context->rank);
131 : }
132 :
133 : /*
134 : * percent_rank
135 : * return fraction between 0 and 1 inclusive,
136 : * which is described as (RK - 1) / (NR - 1), where RK is the current row's
137 : * rank and NR is the total number of rows, per spec.
138 : */
139 : Datum
140 40 : window_percent_rank(PG_FUNCTION_ARGS)
141 : {
142 40 : WindowObject winobj = PG_WINDOW_OBJECT();
143 : rank_context *context;
144 : bool up;
145 40 : int64 totalrows = WinGetPartitionRowCount(winobj);
146 :
147 : Assert(totalrows > 0);
148 :
149 40 : up = rank_up(winobj);
150 : context = (rank_context *)
151 40 : WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
152 40 : if (up)
153 16 : context->rank = WinGetCurrentPosition(winobj) + 1;
154 :
155 : /* return zero if there's only one row, per spec */
156 40 : if (totalrows <= 1)
157 4 : PG_RETURN_FLOAT8(0.0);
158 :
159 36 : PG_RETURN_FLOAT8((float8) (context->rank - 1) / (float8) (totalrows - 1));
160 : }
161 :
162 : /*
163 : * cume_dist
164 : * return fraction between 0 and 1 inclusive,
165 : * which is described as NP / NR, where NP is the number of rows preceding or
166 : * peers to the current row, and NR is the total number of rows, per spec.
167 : */
168 : Datum
169 40 : window_cume_dist(PG_FUNCTION_ARGS)
170 : {
171 40 : WindowObject winobj = PG_WINDOW_OBJECT();
172 : rank_context *context;
173 : bool up;
174 40 : int64 totalrows = WinGetPartitionRowCount(winobj);
175 :
176 : Assert(totalrows > 0);
177 :
178 40 : up = rank_up(winobj);
179 : context = (rank_context *)
180 40 : WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
181 40 : if (up || context->rank == 1)
182 : {
183 : /*
184 : * The current row is not peer to prior row or is just the first, so
185 : * count up the number of rows that are peer to the current.
186 : */
187 : int64 row;
188 :
189 32 : context->rank = WinGetCurrentPosition(winobj) + 1;
190 :
191 : /*
192 : * start from current + 1
193 : */
194 40 : for (row = context->rank; row < totalrows; row++)
195 : {
196 24 : if (!WinRowsArePeers(winobj, row - 1, row))
197 16 : break;
198 8 : context->rank++;
199 : }
200 : }
201 :
202 40 : PG_RETURN_FLOAT8((float8) context->rank / (float8) totalrows);
203 : }
204 :
205 : /*
206 : * ntile
207 : * compute an exact numeric value with scale 0 (zero),
208 : * ranging from 1 (one) to n, per spec.
209 : */
210 : Datum
211 52 : window_ntile(PG_FUNCTION_ARGS)
212 : {
213 52 : WindowObject winobj = PG_WINDOW_OBJECT();
214 : ntile_context *context;
215 :
216 : context = (ntile_context *)
217 52 : WinGetPartitionLocalMemory(winobj, sizeof(ntile_context));
218 :
219 52 : if (context->ntile == 0)
220 : {
221 : /* first call */
222 : int64 total;
223 : int32 nbuckets;
224 : bool isnull;
225 :
226 16 : total = WinGetPartitionRowCount(winobj);
227 16 : nbuckets = DatumGetInt32(WinGetFuncArgCurrent(winobj, 0, &isnull));
228 :
229 : /*
230 : * per spec: If NT is the null value, then the result is the null
231 : * value.
232 : */
233 16 : if (isnull)
234 8 : PG_RETURN_NULL();
235 :
236 : /*
237 : * per spec: If NT is less than or equal to 0 (zero), then an
238 : * exception condition is raised.
239 : */
240 8 : if (nbuckets <= 0)
241 4 : ereport(ERROR,
242 : (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTILE),
243 : errmsg("argument of ntile must be greater than zero")));
244 :
245 4 : context->ntile = 1;
246 4 : context->rows_per_bucket = 0;
247 4 : context->boundary = total / nbuckets;
248 4 : if (context->boundary <= 0)
249 0 : context->boundary = 1;
250 : else
251 : {
252 : /*
253 : * If the total number is not divisible, add 1 row to leading
254 : * buckets.
255 : */
256 4 : context->remainder = total % nbuckets;
257 4 : if (context->remainder != 0)
258 4 : context->boundary++;
259 : }
260 : }
261 :
262 40 : context->rows_per_bucket++;
263 40 : if (context->boundary < context->rows_per_bucket)
264 : {
265 : /* ntile up */
266 8 : if (context->remainder != 0 && context->ntile == context->remainder)
267 : {
268 4 : context->remainder = 0;
269 4 : context->boundary -= 1;
270 : }
271 8 : context->ntile += 1;
272 8 : context->rows_per_bucket = 1;
273 : }
274 :
275 40 : PG_RETURN_INT32(context->ntile);
276 : }
277 :
278 : /*
279 : * leadlag_common
280 : * common operation of lead() and lag()
281 : * For lead() forward is true, whereas for lag() it is false.
282 : * withoffset indicates we have an offset second argument.
283 : * withdefault indicates we have a default third argument.
284 : */
285 : static Datum
286 120708 : leadlag_common(FunctionCallInfo fcinfo,
287 : bool forward, bool withoffset, bool withdefault)
288 : {
289 120708 : WindowObject winobj = PG_WINDOW_OBJECT();
290 : int32 offset;
291 : bool const_offset;
292 : Datum result;
293 : bool isnull;
294 : bool isout;
295 :
296 120708 : if (withoffset)
297 : {
298 280 : offset = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
299 280 : if (isnull)
300 0 : PG_RETURN_NULL();
301 280 : const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
302 : }
303 : else
304 : {
305 120428 : offset = 1;
306 120428 : const_offset = true;
307 : }
308 :
309 120708 : result = WinGetFuncArgInPartition(winobj, 0,
310 : (forward ? offset : -offset),
311 : WINDOW_SEEK_CURRENT,
312 : const_offset,
313 : &isnull, &isout);
314 :
315 120708 : if (isout)
316 : {
317 : /*
318 : * target row is out of the partition; supply default value if
319 : * provided. otherwise it'll stay NULL
320 : */
321 188 : if (withdefault)
322 64 : result = WinGetFuncArgCurrent(winobj, 2, &isnull);
323 : }
324 :
325 120708 : if (isnull)
326 124 : PG_RETURN_NULL();
327 :
328 120584 : PG_RETURN_DATUM(result);
329 : }
330 :
331 : /*
332 : * lag
333 : * returns the value of VE evaluated on a row that is 1
334 : * row before the current row within a partition,
335 : * per spec.
336 : */
337 : Datum
338 120228 : window_lag(PG_FUNCTION_ARGS)
339 : {
340 120228 : return leadlag_common(fcinfo, false, false, false);
341 : }
342 :
343 : /*
344 : * lag_with_offset
345 : * returns the value of VE evaluated on a row that is OFFSET
346 : * rows before the current row within a partition,
347 : * per spec.
348 : */
349 : Datum
350 40 : window_lag_with_offset(PG_FUNCTION_ARGS)
351 : {
352 40 : return leadlag_common(fcinfo, false, true, false);
353 : }
354 :
355 : /*
356 : * lag_with_offset_and_default
357 : * same as lag_with_offset but accepts default value
358 : * as its third argument.
359 : */
360 : Datum
361 80 : window_lag_with_offset_and_default(PG_FUNCTION_ARGS)
362 : {
363 80 : return leadlag_common(fcinfo, false, true, true);
364 : }
365 :
366 : /*
367 : * lead
368 : * returns the value of VE evaluated on a row that is 1
369 : * row after the current row within a partition,
370 : * per spec.
371 : */
372 : Datum
373 200 : window_lead(PG_FUNCTION_ARGS)
374 : {
375 200 : return leadlag_common(fcinfo, true, false, false);
376 : }
377 :
378 : /*
379 : * lead_with_offset
380 : * returns the value of VE evaluated on a row that is OFFSET
381 : * number of rows after the current row within a partition,
382 : * per spec.
383 : */
384 : Datum
385 80 : window_lead_with_offset(PG_FUNCTION_ARGS)
386 : {
387 80 : return leadlag_common(fcinfo, true, true, false);
388 : }
389 :
390 : /*
391 : * lead_with_offset_and_default
392 : * same as lead_with_offset but accepts default value
393 : * as its third argument.
394 : */
395 : Datum
396 80 : window_lead_with_offset_and_default(PG_FUNCTION_ARGS)
397 : {
398 80 : return leadlag_common(fcinfo, true, true, true);
399 : }
400 :
401 : /*
402 : * first_value
403 : * return the value of VE evaluated on the first row of the
404 : * window frame, per spec.
405 : */
406 : Datum
407 1644 : window_first_value(PG_FUNCTION_ARGS)
408 : {
409 1644 : WindowObject winobj = PG_WINDOW_OBJECT();
410 : Datum result;
411 : bool isnull;
412 :
413 1644 : result = WinGetFuncArgInFrame(winobj, 0,
414 : 0, WINDOW_SEEK_HEAD, true,
415 : &isnull, NULL);
416 1632 : if (isnull)
417 40 : PG_RETURN_NULL();
418 :
419 1592 : PG_RETURN_DATUM(result);
420 : }
421 :
422 : /*
423 : * last_value
424 : * return the value of VE evaluated on the last row of the
425 : * window frame, per spec.
426 : */
427 : Datum
428 2144 : window_last_value(PG_FUNCTION_ARGS)
429 : {
430 2144 : WindowObject winobj = PG_WINDOW_OBJECT();
431 : Datum result;
432 : bool isnull;
433 :
434 2144 : result = WinGetFuncArgInFrame(winobj, 0,
435 : 0, WINDOW_SEEK_TAIL, true,
436 : &isnull, NULL);
437 2144 : if (isnull)
438 40 : PG_RETURN_NULL();
439 :
440 2104 : PG_RETURN_DATUM(result);
441 : }
442 :
443 : /*
444 : * nth_value
445 : * return the value of VE evaluated on the n-th row from the first
446 : * row of the window frame, per spec.
447 : */
448 : Datum
449 324 : window_nth_value(PG_FUNCTION_ARGS)
450 : {
451 324 : WindowObject winobj = PG_WINDOW_OBJECT();
452 : bool const_offset;
453 : Datum result;
454 : bool isnull;
455 : int32 nth;
456 :
457 324 : nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
458 324 : if (isnull)
459 0 : PG_RETURN_NULL();
460 324 : const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
461 :
462 324 : if (nth <= 0)
463 4 : ereport(ERROR,
464 : (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
465 : errmsg("argument of nth_value must be greater than zero")));
466 :
467 320 : result = WinGetFuncArgInFrame(winobj, 0,
468 : nth - 1, WINDOW_SEEK_HEAD, const_offset,
469 : &isnull, NULL);
470 320 : if (isnull)
471 36 : PG_RETURN_NULL();
472 :
473 284 : PG_RETURN_DATUM(result);
474 : }
|
