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