granicus.if.org Git - postgresql/blob - src/backend/utils/adt/windowfuncs.c

   1 /*-------------------------------------------------------------------------
   2  *
   3  * windowfuncs.c
   4  *        Standard window functions defined in SQL spec.
   5  *
   6  * Portions Copyright (c) 2000-2014, 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 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* acccess 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
  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
  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
 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
 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
 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
 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
 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
 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 evelulated on a row that is OFFSET
 346  * rows before the current row within a partition,
 347  * per spec.
 348  */
 349 Datum
 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
 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
 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
 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
 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
 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
 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
 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 }