PostgreSQL Source Code  git master
 All Data Structures Namespaces Files Functions Variables Typedefs Enumerations Enumerator Macros
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-2017, 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) */
37 
38 static bool rank_up(WindowObject winobj);
40  bool forward, bool withoffset, bool withdefault);
41 
42 
43 /*
44  * utility routine for *_rank functions.
45  */
46 static bool
48 {
49  bool up = false; /* should rank increase? */
50  int64 curpos = WinGetCurrentPosition(winobj);
51  rank_context *context;
52 
53  context = (rank_context *)
55 
56  if (context->rank == 0)
57  {
58  /* first call: rank of first row is always 1 */
59  Assert(curpos == 0);
60  context->rank = 1;
61  }
62  else
63  {
64  Assert(curpos > 0);
65  /* do current and prior tuples match by ORDER BY clause? */
66  if (!WinRowsArePeers(winobj, curpos - 1, curpos))
67  up = true;
68  }
69 
70  /* We can advance the mark, but only *after* access to prior row */
71  WinSetMarkPosition(winobj, curpos);
72 
73  return up;
74 }
75 
76 
77 /*
78  * row_number
79  * just increment up from 1 until current partition finishes.
80  */
81 Datum
83 {
84  WindowObject winobj = PG_WINDOW_OBJECT();
85  int64 curpos = WinGetCurrentPosition(winobj);
86 
87  WinSetMarkPosition(winobj, curpos);
88  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
99 {
100  WindowObject winobj = PG_WINDOW_OBJECT();
101  rank_context *context;
102  bool up;
103 
104  up = rank_up(winobj);
105  context = (rank_context *)
106  WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
107  if (up)
108  context->rank = WinGetCurrentPosition(winobj) + 1;
109 
110  PG_RETURN_INT64(context->rank);
111 }
112 
113 /*
114  * dense_rank
115  * Rank increases by 1 when key columns change.
116  */
117 Datum
119 {
120  WindowObject winobj = PG_WINDOW_OBJECT();
121  rank_context *context;
122  bool up;
123 
124  up = rank_up(winobj);
125  context = (rank_context *)
126  WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
127  if (up)
128  context->rank++;
129 
130  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
141 {
142  WindowObject winobj = PG_WINDOW_OBJECT();
143  rank_context *context;
144  bool up;
145  int64 totalrows = WinGetPartitionRowCount(winobj);
146 
147  Assert(totalrows > 0);
148 
149  up = rank_up(winobj);
150  context = (rank_context *)
151  WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
152  if (up)
153  context->rank = WinGetCurrentPosition(winobj) + 1;
154 
155  /* return zero if there's only one row, per spec */
156  if (totalrows <= 1)
157  PG_RETURN_FLOAT8(0.0);
158 
159  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
170 {
171  WindowObject winobj = PG_WINDOW_OBJECT();
172  rank_context *context;
173  bool up;
174  int64 totalrows = WinGetPartitionRowCount(winobj);
175 
176  Assert(totalrows > 0);
177 
178  up = rank_up(winobj);
179  context = (rank_context *)
180  WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
181  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  context->rank = WinGetCurrentPosition(winobj) + 1;
190 
191  /*
192  * start from current + 1
193  */
194  for (row = context->rank; row < totalrows; row++)
195  {
196  if (!WinRowsArePeers(winobj, row - 1, row))
197  break;
198  context->rank++;
199  }
200  }
201 
202  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
212 {
213  WindowObject winobj = PG_WINDOW_OBJECT();
214  ntile_context *context;
215 
216  context = (ntile_context *)
217  WinGetPartitionLocalMemory(winobj, sizeof(ntile_context));
218 
219  if (context->ntile == 0)
220  {
221  /* first call */
222  int64 total;
223  int32 nbuckets;
224  bool isnull;
225 
226  total = WinGetPartitionRowCount(winobj);
227  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  if (isnull)
234  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  if (nbuckets <= 0)
241  ereport(ERROR,
242  (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTILE),
243  errmsg("argument of ntile must be greater than zero")));
244 
245  context->ntile = 1;
246  context->rows_per_bucket = 0;
247  context->boundary = total / nbuckets;
248  if (context->boundary <= 0)
249  context->boundary = 1;
250  else
251  {
252  /*
253  * If the total number is not divisible, add 1 row to leading
254  * buckets.
255  */
256  context->remainder = total % nbuckets;
257  if (context->remainder != 0)
258  context->boundary++;
259  }
260  }
261 
262  context->rows_per_bucket++;
263  if (context->boundary < context->rows_per_bucket)
264  {
265  /* ntile up */
266  if (context->remainder != 0 && context->ntile == context->remainder)
267  {
268  context->remainder = 0;
269  context->boundary -= 1;
270  }
271  context->ntile += 1;
272  context->rows_per_bucket = 1;
273  }
274 
275  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
287  bool forward, bool withoffset, bool withdefault)
288 {
289  WindowObject winobj = PG_WINDOW_OBJECT();
290  int32 offset;
291  bool const_offset;
292  Datum result;
293  bool isnull;
294  bool isout;
295 
296  if (withoffset)
297  {
298  offset = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
299  if (isnull)
300  PG_RETURN_NULL();
301  const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
302  }
303  else
304  {
305  offset = 1;
306  const_offset = true;
307  }
308 
309  result = WinGetFuncArgInPartition(winobj, 0,
310  (forward ? offset : -offset),
312  const_offset,
313  &isnull, &isout);
314 
315  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  if (withdefault)
322  result = WinGetFuncArgCurrent(winobj, 2, &isnull);
323  }
324 
325  if (isnull)
326  PG_RETURN_NULL();
327 
328  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
339 {
340  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
351 {
352  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
362 {
363  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
374 {
375  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
386 {
387  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
397 {
398  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
408 {
409  WindowObject winobj = PG_WINDOW_OBJECT();
410  Datum result;
411  bool isnull;
412 
413  result = WinGetFuncArgInFrame(winobj, 0,
414  0, WINDOW_SEEK_HEAD, true,
415  &isnull, NULL);
416  if (isnull)
417  PG_RETURN_NULL();
418 
419  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
429 {
430  WindowObject winobj = PG_WINDOW_OBJECT();
431  Datum result;
432  bool isnull;
433 
434  result = WinGetFuncArgInFrame(winobj, 0,
435  0, WINDOW_SEEK_TAIL, true,
436  &isnull, NULL);
437  if (isnull)
438  PG_RETURN_NULL();
439 
440  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
450 {
451  WindowObject winobj = PG_WINDOW_OBJECT();
452  bool const_offset;
453  Datum result;
454  bool isnull;
455  int32 nth;
456 
457  nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
458  if (isnull)
459  PG_RETURN_NULL();
460  const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
461 
462  if (nth <= 0)
463  ereport(ERROR,
464  (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
465  errmsg("argument of nth_value must be greater than zero")));
466 
467  result = WinGetFuncArgInFrame(winobj, 0,
468  nth - 1, WINDOW_SEEK_HEAD, const_offset,
469  &isnull, NULL);
470  if (isnull)
471  PG_RETURN_NULL();
472 
473  PG_RETURN_DATUM(result);
474 }
Datum WinGetFuncArgCurrent(WindowObject winobj, int argno, bool *isnull)
bool WinRowsArePeers(WindowObject winobj, int64 pos1, int64 pos2)
void * WinGetPartitionLocalMemory(WindowObject winobj, Size sz)
int64 WinGetPartitionRowCount(WindowObject winobj)
static Datum leadlag_common(FunctionCallInfo fcinfo, bool forward, bool withoffset, bool withdefault)
Definition: windowfuncs.c:286
bool get_fn_expr_arg_stable(FmgrInfo *flinfo, int argnum)
Definition: fmgr.c:1980
#define DatumGetInt32(X)
Definition: postgres.h:478
#define PG_RETURN_INT64(x)
Definition: fmgr.h:327
Datum window_row_number(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:82
int64 remainder
Definition: windowfuncs.c:35
Datum window_lag_with_offset_and_default(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:361
Datum window_dense_rank(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:118
#define PG_RETURN_FLOAT8(x)
Definition: fmgr.h:326
Datum window_lag_with_offset(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:350
#define PG_RETURN_INT32(x)
Definition: fmgr.h:314
int errcode(int sqlerrcode)
Definition: elog.c:575
#define PG_WINDOW_OBJECT()
Definition: windowapi.h:39
return result
Definition: formatting.c:1632
Datum window_lead_with_offset_and_default(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:396
Datum window_cume_dist(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:169
signed int int32
Definition: c.h:256
#define WINDOW_SEEK_TAIL
Definition: windowapi.h:34
FmgrInfo * flinfo
Definition: fmgr.h:79
Datum window_first_value(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:407
#define ERROR
Definition: elog.h:43
double float8
Definition: c.h:381
Datum WinGetFuncArgInPartition(WindowObject winobj, int argno, int relpos, int seektype, bool set_mark, bool *isnull, bool *isout)
Datum window_lead(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:373
#define ereport(elevel, rest)
Definition: elog.h:122
static bool rank_up(WindowObject winobj)
Definition: windowfuncs.c:47
Datum window_lead_with_offset(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:385
int64 WinGetCurrentPosition(WindowObject winobj)
int64 rows_per_bucket
Definition: windowfuncs.c:33
#define WINDOW_SEEK_HEAD
Definition: windowapi.h:33
Datum window_last_value(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:428
uintptr_t Datum
Definition: postgres.h:372
#define PG_RETURN_DATUM(x)
Definition: fmgr.h:313
Datum window_ntile(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:211
#define WINDOW_SEEK_CURRENT
Definition: windowapi.h:32
#define NULL
Definition: c.h:229
#define Assert(condition)
Definition: c.h:675
Datum WinGetFuncArgInFrame(WindowObject winobj, int argno, int relpos, int seektype, bool set_mark, bool *isnull, bool *isout)
Datum window_nth_value(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:449
Datum window_lag(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:338
int errmsg(const char *fmt,...)
Definition: elog.c:797
void WinSetMarkPosition(WindowObject winobj, int64 markpos)
Datum window_rank(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:98
#define PG_FUNCTION_ARGS
Definition: fmgr.h:158
int64 boundary
Definition: windowfuncs.c:34
struct rank_context rank_context
Datum window_percent_rank(PG_FUNCTION_ARGS)
Definition: windowfuncs.c:140
#define PG_RETURN_NULL()
Definition: fmgr.h:305