PostgreSQL Source Code  git master
windowfuncs.c
Go to the documentation of this file.
1 /*-------------------------------------------------------------------------
2  *
3  * windowfuncs.c
4  * Standard window functions defined in SQL spec.
5  *
6  * Portions Copyright (c) 2000-2023, 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 */
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) */
38 
39 static bool rank_up(WindowObject winobj);
41  bool forward, bool withoffset, bool withdefault);
42 
43 
44 /*
45  * utility routine for *_rank functions.
46  */
47 static bool
49 {
50  bool up = false; /* should rank increase? */
51  int64 curpos = WinGetCurrentPosition(winobj);
52  rank_context *context;
53 
54  context = (rank_context *)
56 
57  if (context->rank == 0)
58  {
59  /* first call: rank of first row is always 1 */
60  Assert(curpos == 0);
61  context->rank = 1;
62  }
63  else
64  {
65  Assert(curpos > 0);
66  /* do current and prior tuples match by ORDER BY clause? */
67  if (!WinRowsArePeers(winobj, curpos - 1, curpos))
68  up = true;
69  }
70 
71  /* We can advance the mark, but only *after* access to prior row */
72  WinSetMarkPosition(winobj, curpos);
73 
74  return up;
75 }
76 
77 
78 /*
79  * row_number
80  * just increment up from 1 until current partition finishes.
81  */
82 Datum
84 {
85  WindowObject winobj = PG_WINDOW_OBJECT();
86  int64 curpos = WinGetCurrentPosition(winobj);
87 
88  WinSetMarkPosition(winobj, curpos);
89  PG_RETURN_INT64(curpos + 1);
90 }
91 
92 /*
93  * window_row_number_support
94  * prosupport function for window_row_number()
95  */
96 Datum
98 {
99  Node *rawreq = (Node *) PG_GETARG_POINTER(0);
100 
101  if (IsA(rawreq, SupportRequestWFuncMonotonic))
102  {
104 
105  /* row_number() is monotonically increasing */
107  PG_RETURN_POINTER(req);
108  }
109 
111  {
113 
114  /*
115  * The frame options can always become "ROWS BETWEEN UNBOUNDED
116  * PRECEDING AND CURRENT ROW". row_number() always just increments by
117  * 1 with each row in the partition. Using ROWS instead of RANGE
118  * saves effort checking peer rows during execution.
119  */
124 
125  PG_RETURN_POINTER(req);
126  }
127 
128  PG_RETURN_POINTER(NULL);
129 }
130 
131 /*
132  * rank
133  * Rank changes when key columns change.
134  * The new rank number is the current row number.
135  */
136 Datum
138 {
139  WindowObject winobj = PG_WINDOW_OBJECT();
140  rank_context *context;
141  bool up;
142 
143  up = rank_up(winobj);
144  context = (rank_context *)
145  WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
146  if (up)
147  context->rank = WinGetCurrentPosition(winobj) + 1;
148 
149  PG_RETURN_INT64(context->rank);
150 }
151 
152 /*
153  * window_rank_support
154  * prosupport function for window_rank()
155  */
156 Datum
158 {
159  Node *rawreq = (Node *) PG_GETARG_POINTER(0);
160 
161  if (IsA(rawreq, SupportRequestWFuncMonotonic))
162  {
164 
165  /* rank() is monotonically increasing */
167  PG_RETURN_POINTER(req);
168  }
169 
171  {
173 
174  /*
175  * rank() is coded in such a way that it returns "(COUNT (*) OVER
176  * (<opt> RANGE UNBOUNDED PRECEDING) - COUNT (*) OVER (<opt> RANGE
177  * CURRENT ROW) + 1)" regardless of the frame options. We'll set the
178  * frame options to "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
179  * so they agree with what window_row_number_support() optimized the
180  * frame options to be. Using ROWS instead of RANGE saves from doing
181  * peer row checks during execution.
182  */
187 
188  PG_RETURN_POINTER(req);
189  }
190 
191  PG_RETURN_POINTER(NULL);
192 }
193 
194 /*
195  * dense_rank
196  * Rank increases by 1 when key columns change.
197  */
198 Datum
200 {
201  WindowObject winobj = PG_WINDOW_OBJECT();
202  rank_context *context;
203  bool up;
204 
205  up = rank_up(winobj);
206  context = (rank_context *)
207  WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
208  if (up)
209  context->rank++;
210 
211  PG_RETURN_INT64(context->rank);
212 }
213 
214 /*
215  * window_dense_rank_support
216  * prosupport function for window_dense_rank()
217  */
218 Datum
220 {
221  Node *rawreq = (Node *) PG_GETARG_POINTER(0);
222 
223  if (IsA(rawreq, SupportRequestWFuncMonotonic))
224  {
226 
227  /* dense_rank() is monotonically increasing */
229  PG_RETURN_POINTER(req);
230  }
231 
233  {
235 
236  /*
237  * dense_rank() is unaffected by the frame options. Here we set the
238  * frame options to match what's done in row_number's support
239  * function. Using ROWS instead of RANGE (the default) saves the
240  * executor from having to check for peer rows.
241  */
246 
247  PG_RETURN_POINTER(req);
248  }
249 
250  PG_RETURN_POINTER(NULL);
251 }
252 
253 /*
254  * percent_rank
255  * return fraction between 0 and 1 inclusive,
256  * which is described as (RK - 1) / (NR - 1), where RK is the current row's
257  * rank and NR is the total number of rows, per spec.
258  */
259 Datum
261 {
262  WindowObject winobj = PG_WINDOW_OBJECT();
263  rank_context *context;
264  bool up;
265  int64 totalrows = WinGetPartitionRowCount(winobj);
266 
267  Assert(totalrows > 0);
268 
269  up = rank_up(winobj);
270  context = (rank_context *)
271  WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
272  if (up)
273  context->rank = WinGetCurrentPosition(winobj) + 1;
274 
275  /* return zero if there's only one row, per spec */
276  if (totalrows <= 1)
277  PG_RETURN_FLOAT8(0.0);
278 
279  PG_RETURN_FLOAT8((float8) (context->rank - 1) / (float8) (totalrows - 1));
280 }
281 
282 /*
283  * window_percent_rank_support
284  * prosupport function for window_percent_rank()
285  */
286 Datum
288 {
289  Node *rawreq = (Node *) PG_GETARG_POINTER(0);
290 
291  if (IsA(rawreq, SupportRequestWFuncMonotonic))
292  {
294 
295  /* percent_rank() is monotonically increasing */
297  PG_RETURN_POINTER(req);
298  }
299 
301  {
303 
304  /*
305  * percent_rank() is unaffected by the frame options. Here we set the
306  * frame options to match what's done in row_number's support
307  * function. Using ROWS instead of RANGE (the default) saves the
308  * executor from having to check for peer rows.
309  */
314 
315  PG_RETURN_POINTER(req);
316  }
317 
318  PG_RETURN_POINTER(NULL);
319 }
320 
321 
322 /*
323  * cume_dist
324  * return fraction between 0 and 1 inclusive,
325  * which is described as NP / NR, where NP is the number of rows preceding or
326  * peers to the current row, and NR is the total number of rows, per spec.
327  */
328 Datum
330 {
331  WindowObject winobj = PG_WINDOW_OBJECT();
332  rank_context *context;
333  bool up;
334  int64 totalrows = WinGetPartitionRowCount(winobj);
335 
336  Assert(totalrows > 0);
337 
338  up = rank_up(winobj);
339  context = (rank_context *)
340  WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
341  if (up || context->rank == 1)
342  {
343  /*
344  * The current row is not peer to prior row or is just the first, so
345  * count up the number of rows that are peer to the current.
346  */
347  int64 row;
348 
349  context->rank = WinGetCurrentPosition(winobj) + 1;
350 
351  /*
352  * start from current + 1
353  */
354  for (row = context->rank; row < totalrows; row++)
355  {
356  if (!WinRowsArePeers(winobj, row - 1, row))
357  break;
358  context->rank++;
359  }
360  }
361 
362  PG_RETURN_FLOAT8((float8) context->rank / (float8) totalrows);
363 }
364 
365 /*
366  * window_cume_dist_support
367  * prosupport function for window_cume_dist()
368  */
369 Datum
371 {
372  Node *rawreq = (Node *) PG_GETARG_POINTER(0);
373 
374  if (IsA(rawreq, SupportRequestWFuncMonotonic))
375  {
377 
378  /* cume_dist() is monotonically increasing */
380  PG_RETURN_POINTER(req);
381  }
382 
384  {
386 
387  /*
388  * cume_dist() is unaffected by the frame options. Here we set the
389  * frame options to match what's done in row_number's support
390  * function. Using ROWS instead of RANGE (the default) saves the
391  * executor from having to check for peer rows.
392  */
397 
398  PG_RETURN_POINTER(req);
399  }
400 
401  PG_RETURN_POINTER(NULL);
402 }
403 
404 /*
405  * ntile
406  * compute an exact numeric value with scale 0 (zero),
407  * ranging from 1 (one) to n, per spec.
408  */
409 Datum
411 {
412  WindowObject winobj = PG_WINDOW_OBJECT();
413  ntile_context *context;
414 
415  context = (ntile_context *)
416  WinGetPartitionLocalMemory(winobj, sizeof(ntile_context));
417 
418  if (context->ntile == 0)
419  {
420  /* first call */
421  int64 total;
422  int32 nbuckets;
423  bool isnull;
424 
425  total = WinGetPartitionRowCount(winobj);
426  nbuckets = DatumGetInt32(WinGetFuncArgCurrent(winobj, 0, &isnull));
427 
428  /*
429  * per spec: If NT is the null value, then the result is the null
430  * value.
431  */
432  if (isnull)
433  PG_RETURN_NULL();
434 
435  /*
436  * per spec: If NT is less than or equal to 0 (zero), then an
437  * exception condition is raised.
438  */
439  if (nbuckets <= 0)
440  ereport(ERROR,
441  (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTILE),
442  errmsg("argument of ntile must be greater than zero")));
443 
444  context->ntile = 1;
445  context->rows_per_bucket = 0;
446  context->boundary = total / nbuckets;
447  if (context->boundary <= 0)
448  context->boundary = 1;
449  else
450  {
451  /*
452  * If the total number is not divisible, add 1 row to leading
453  * buckets.
454  */
455  context->remainder = total % nbuckets;
456  if (context->remainder != 0)
457  context->boundary++;
458  }
459  }
460 
461  context->rows_per_bucket++;
462  if (context->boundary < context->rows_per_bucket)
463  {
464  /* ntile up */
465  if (context->remainder != 0 && context->ntile == context->remainder)
466  {
467  context->remainder = 0;
468  context->boundary -= 1;
469  }
470  context->ntile += 1;
471  context->rows_per_bucket = 1;
472  }
473 
474  PG_RETURN_INT32(context->ntile);
475 }
476 
477 /*
478  * window_ntile_support
479  * prosupport function for window_ntile()
480  */
481 Datum
483 {
484  Node *rawreq = (Node *) PG_GETARG_POINTER(0);
485 
486  if (IsA(rawreq, SupportRequestWFuncMonotonic))
487  {
489 
490  /*
491  * ntile() is monotonically increasing as the number of buckets cannot
492  * change after the first call
493  */
495  PG_RETURN_POINTER(req);
496  }
497 
499  {
501 
502  /*
503  * ntile() is unaffected by the frame options. Here we set the frame
504  * options to match what's done in row_number's support function.
505  * Using ROWS instead of RANGE (the default) saves the executor from
506  * having to check for peer rows.
507  */
512 
513  PG_RETURN_POINTER(req);
514  }
515 
516  PG_RETURN_POINTER(NULL);
517 }
518 
519 /*
520  * leadlag_common
521  * common operation of lead() and lag()
522  * For lead() forward is true, whereas for lag() it is false.
523  * withoffset indicates we have an offset second argument.
524  * withdefault indicates we have a default third argument.
525  */
526 static Datum
528  bool forward, bool withoffset, bool withdefault)
529 {
530  WindowObject winobj = PG_WINDOW_OBJECT();
531  int32 offset;
532  bool const_offset;
533  Datum result;
534  bool isnull;
535  bool isout;
536 
537  if (withoffset)
538  {
539  offset = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
540  if (isnull)
541  PG_RETURN_NULL();
542  const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
543  }
544  else
545  {
546  offset = 1;
547  const_offset = true;
548  }
549 
550  result = WinGetFuncArgInPartition(winobj, 0,
551  (forward ? offset : -offset),
553  const_offset,
554  &isnull, &isout);
555 
556  if (isout)
557  {
558  /*
559  * target row is out of the partition; supply default value if
560  * provided. otherwise it'll stay NULL
561  */
562  if (withdefault)
563  result = WinGetFuncArgCurrent(winobj, 2, &isnull);
564  }
565 
566  if (isnull)
567  PG_RETURN_NULL();
568 
569  PG_RETURN_DATUM(result);
570 }
571 
572 /*
573  * lag
574  * returns the value of VE evaluated on a row that is 1
575  * row before the current row within a partition,
576  * per spec.
577  */
578 Datum
580 {
581  return leadlag_common(fcinfo, false, false, false);
582 }
583 
584 /*
585  * lag_with_offset
586  * returns the value of VE evaluated on a row that is OFFSET
587  * rows before the current row within a partition,
588  * per spec.
589  */
590 Datum
592 {
593  return leadlag_common(fcinfo, false, true, false);
594 }
595 
596 /*
597  * lag_with_offset_and_default
598  * same as lag_with_offset but accepts default value
599  * as its third argument.
600  */
601 Datum
603 {
604  return leadlag_common(fcinfo, false, true, true);
605 }
606 
607 /*
608  * lead
609  * returns the value of VE evaluated on a row that is 1
610  * row after the current row within a partition,
611  * per spec.
612  */
613 Datum
615 {
616  return leadlag_common(fcinfo, true, false, false);
617 }
618 
619 /*
620  * lead_with_offset
621  * returns the value of VE evaluated on a row that is OFFSET
622  * number of rows after the current row within a partition,
623  * per spec.
624  */
625 Datum
627 {
628  return leadlag_common(fcinfo, true, true, false);
629 }
630 
631 /*
632  * lead_with_offset_and_default
633  * same as lead_with_offset but accepts default value
634  * as its third argument.
635  */
636 Datum
638 {
639  return leadlag_common(fcinfo, true, true, true);
640 }
641 
642 /*
643  * first_value
644  * return the value of VE evaluated on the first row of the
645  * window frame, per spec.
646  */
647 Datum
649 {
650  WindowObject winobj = PG_WINDOW_OBJECT();
651  Datum result;
652  bool isnull;
653 
654  result = WinGetFuncArgInFrame(winobj, 0,
655  0, WINDOW_SEEK_HEAD, true,
656  &isnull, NULL);
657  if (isnull)
658  PG_RETURN_NULL();
659 
660  PG_RETURN_DATUM(result);
661 }
662 
663 /*
664  * last_value
665  * return the value of VE evaluated on the last row of the
666  * window frame, per spec.
667  */
668 Datum
670 {
671  WindowObject winobj = PG_WINDOW_OBJECT();
672  Datum result;
673  bool isnull;
674 
675  result = WinGetFuncArgInFrame(winobj, 0,
676  0, WINDOW_SEEK_TAIL, true,
677  &isnull, NULL);
678  if (isnull)
679  PG_RETURN_NULL();
680 
681  PG_RETURN_DATUM(result);
682 }
683 
684 /*
685  * nth_value
686  * return the value of VE evaluated on the n-th row from the first
687  * row of the window frame, per spec.
688  */
689 Datum
691 {
692  WindowObject winobj = PG_WINDOW_OBJECT();
693  bool const_offset;
694  Datum result;
695  bool isnull;
696  int32 nth;
697 
698  nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
699  if (isnull)
700  PG_RETURN_NULL();
701  const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
702 
703  if (nth <= 0)
704  ereport(ERROR,
705  (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
706  errmsg("argument of nth_value must be greater than zero")));
707 
708  result = WinGetFuncArgInFrame(winobj, 0,
709  nth - 1, WINDOW_SEEK_HEAD, const_offset,
710  &isnull, NULL);
711  if (isnull)
712  PG_RETURN_NULL();
713 
714  PG_RETURN_DATUM(result);
715 }
signed int int32
Definition: c.h:483
double float8
Definition: c.h:619
int errcode(int sqlerrcode)
Definition: elog.c:858
int errmsg(const char *fmt,...)
Definition: elog.c:1069
#define ERROR
Definition: elog.h:39
#define ereport(elevel,...)
Definition: elog.h:149
bool get_fn_expr_arg_stable(FmgrInfo *flinfo, int argnum)
Definition: fmgr.c:1958
#define PG_RETURN_FLOAT8(x)
Definition: fmgr.h:367
#define PG_GETARG_POINTER(n)
Definition: fmgr.h:276
#define PG_RETURN_INT64(x)
Definition: fmgr.h:368
#define PG_RETURN_NULL()
Definition: fmgr.h:345
#define PG_RETURN_INT32(x)
Definition: fmgr.h:354
#define PG_RETURN_DATUM(x)
Definition: fmgr.h:353
#define PG_RETURN_POINTER(x)
Definition: fmgr.h:361
#define PG_FUNCTION_ARGS
Definition: fmgr.h:193
Assert(fmt[strlen(fmt) - 1] !='\n')
Datum WinGetFuncArgInPartition(WindowObject winobj, int argno, int relpos, int seektype, bool set_mark, bool *isnull, bool *isout)
Datum WinGetFuncArgInFrame(WindowObject winobj, int argno, int relpos, int seektype, bool set_mark, bool *isnull, bool *isout)
int64 WinGetCurrentPosition(WindowObject winobj)
bool WinRowsArePeers(WindowObject winobj, int64 pos1, int64 pos2)
void WinSetMarkPosition(WindowObject winobj, int64 markpos)
void * WinGetPartitionLocalMemory(WindowObject winobj, Size sz)
Datum WinGetFuncArgCurrent(WindowObject winobj, int argno, bool *isnull)
int64 WinGetPartitionRowCount(WindowObject winobj)
#define IsA(nodeptr, _type_)
Definition: nodes.h:179
#define FRAMEOPTION_END_CURRENT_ROW
Definition: parsenodes.h:588
#define FRAMEOPTION_START_UNBOUNDED_PRECEDING
Definition: parsenodes.h:583
#define FRAMEOPTION_NONDEFAULT
Definition: parsenodes.h:578
#define FRAMEOPTION_ROWS
Definition: parsenodes.h:580
@ MONOTONICFUNC_INCREASING
Definition: plannodes.h:1586
uintptr_t Datum
Definition: postgres.h:64
static int32 DatumGetInt32(Datum X)
Definition: postgres.h:202
FmgrInfo * flinfo
Definition: fmgr.h:87
Definition: nodes.h:129
MonotonicFunction monotonic
Definition: supportnodes.h:299
int64 rows_per_bucket
Definition: windowfuncs.c:34
int64 remainder
Definition: windowfuncs.c:36
int64 boundary
Definition: windowfuncs.c:35
#define WINDOW_SEEK_TAIL
Definition: windowapi.h:34
#define WINDOW_SEEK_HEAD
Definition: windowapi.h:33
#define PG_WINDOW_OBJECT()
Definition: windowapi.h:39
#define WINDOW_SEEK_CURRENT
Definition: windowapi.h:32
Datum window_lag_with_offset(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:591
static bool rank_up(WindowObject winobj)
Definition: windowfuncs.c:48
Datum window_dense_rank(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:199
Datum window_cume_dist_support(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:370
Datum window_last_value(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:669
Datum window_percent_rank(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:260
static Datum leadlag_common(FunctionCallInfo fcinfo, bool forward, bool withoffset, bool withdefault)
Definition: windowfuncs.c:527
Datum window_ntile(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:410
Datum window_row_number(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:83
Datum window_percent_rank_support(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:287
Datum window_first_value(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:648
Datum window_lead(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:614
Datum window_lead_with_offset(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:626
Datum window_lag_with_offset_and_default(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:602
Datum window_lag(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:579
Datum window_row_number_support(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:97
Datum window_lead_with_offset_and_default(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:637
Datum window_cume_dist(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:329
Datum window_rank(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:137
Datum window_ntile_support(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:482
struct rank_context rank_context
Datum window_dense_rank_support(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:219
Datum window_nth_value(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:690
Datum window_rank_support(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:157