LCOV - code coverage report
Current view: top level - src/backend/utils/adt - windowfuncs.c (source / functions) Hit Total Coverage
Test: PostgreSQL 15beta1 Lines: 147 150 98.0 %
Date: 2022-05-18 03:10:05 Functions: 20 20 100.0 %
Legend: Lines: hit not hit

          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             : }

Generated by: LCOV version 1.14