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
rank_up(WindowObject winobj)47 rank_up(WindowObject winobj)
48 {
49 bool up = false; /* should rank increase? */
50 int64 curpos = WinGetCurrentPosition(winobj);
51 rank_context *context;
52
53 context = (rank_context *)
54 WinGetPartitionLocalMemory(winobj, sizeof(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
window_row_number(PG_FUNCTION_ARGS)82 window_row_number(PG_FUNCTION_ARGS)
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
window_rank(PG_FUNCTION_ARGS)98 window_rank(PG_FUNCTION_ARGS)
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
window_dense_rank(PG_FUNCTION_ARGS)118 window_dense_rank(PG_FUNCTION_ARGS)
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
window_percent_rank(PG_FUNCTION_ARGS)140 window_percent_rank(PG_FUNCTION_ARGS)
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
window_cume_dist(PG_FUNCTION_ARGS)169 window_cume_dist(PG_FUNCTION_ARGS)
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
window_ntile(PG_FUNCTION_ARGS)211 window_ntile(PG_FUNCTION_ARGS)
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
leadlag_common(FunctionCallInfo fcinfo,bool forward,bool withoffset,bool withdefault)286 leadlag_common(FunctionCallInfo fcinfo,
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),
311 WINDOW_SEEK_CURRENT,
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
window_lag(PG_FUNCTION_ARGS)338 window_lag(PG_FUNCTION_ARGS)
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
window_lag_with_offset(PG_FUNCTION_ARGS)350 window_lag_with_offset(PG_FUNCTION_ARGS)
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
window_lag_with_offset_and_default(PG_FUNCTION_ARGS)361 window_lag_with_offset_and_default(PG_FUNCTION_ARGS)
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
window_lead(PG_FUNCTION_ARGS)373 window_lead(PG_FUNCTION_ARGS)
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
window_lead_with_offset(PG_FUNCTION_ARGS)385 window_lead_with_offset(PG_FUNCTION_ARGS)
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
window_lead_with_offset_and_default(PG_FUNCTION_ARGS)396 window_lead_with_offset_and_default(PG_FUNCTION_ARGS)
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
window_first_value(PG_FUNCTION_ARGS)407 window_first_value(PG_FUNCTION_ARGS)
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
window_last_value(PG_FUNCTION_ARGS)428 window_last_value(PG_FUNCTION_ARGS)
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
window_nth_value(PG_FUNCTION_ARGS)449 window_nth_value(PG_FUNCTION_ARGS)
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 }
475