LCOV - code coverage report
Current view: top level - src/backend/utils/adt - windowfuncs.c (source / functions) Hit Total Coverage
Test: PostgreSQL 12beta2 Lines: 126 129 97.7 %
Date: 2019-06-19 14:06:47 Functions: 17 17 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-2019, 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        7128 : rank_up(WindowObject winobj)
      48             : {
      49        7128 :     bool        up = false;     /* should rank increase? */
      50        7128 :     int64       curpos = WinGetCurrentPosition(winobj);
      51             :     rank_context *context;
      52             : 
      53        7128 :     context = (rank_context *)
      54             :         WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
      55             : 
      56        7128 :     if (context->rank == 0)
      57             :     {
      58             :         /* first call: rank of first row is always 1 */
      59             :         Assert(curpos == 0);
      60         244 :         context->rank = 1;
      61             :     }
      62             :     else
      63             :     {
      64             :         Assert(curpos > 0);
      65             :         /* do current and prior tuples match by ORDER BY clause? */
      66        6884 :         if (!WinRowsArePeers(winobj, curpos - 1, curpos))
      67        6796 :             up = true;
      68             :     }
      69             : 
      70             :     /* We can advance the mark, but only *after* access to prior row */
      71        7128 :     WinSetMarkPosition(winobj, curpos);
      72             : 
      73        7128 :     return up;
      74             : }
      75             : 
      76             : 
      77             : /*
      78             :  * row_number
      79             :  * just increment up from 1 until current partition finishes.
      80             :  */
      81             : Datum
      82      276844 : window_row_number(PG_FUNCTION_ARGS)
      83             : {
      84      276844 :     WindowObject winobj = PG_WINDOW_OBJECT();
      85      276844 :     int64       curpos = WinGetCurrentPosition(winobj);
      86             : 
      87      276844 :     WinSetMarkPosition(winobj, curpos);
      88      276844 :     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        7008 : window_rank(PG_FUNCTION_ARGS)
      99             : {
     100        7008 :     WindowObject winobj = PG_WINDOW_OBJECT();
     101             :     rank_context *context;
     102             :     bool        up;
     103             : 
     104        7008 :     up = rank_up(winobj);
     105        7008 :     context = (rank_context *)
     106             :         WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
     107        7008 :     if (up)
     108        6748 :         context->rank = WinGetCurrentPosition(winobj) + 1;
     109             : 
     110        7008 :     PG_RETURN_INT64(context->rank);
     111             : }
     112             : 
     113             : /*
     114             :  * dense_rank
     115             :  * Rank increases by 1 when key columns change.
     116             :  */
     117             : Datum
     118          40 : window_dense_rank(PG_FUNCTION_ARGS)
     119             : {
     120          40 :     WindowObject winobj = PG_WINDOW_OBJECT();
     121             :     rank_context *context;
     122             :     bool        up;
     123             : 
     124          40 :     up = rank_up(winobj);
     125          40 :     context = (rank_context *)
     126             :         WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
     127          40 :     if (up)
     128          16 :         context->rank++;
     129             : 
     130          40 :     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          40 : window_percent_rank(PG_FUNCTION_ARGS)
     141             : {
     142          40 :     WindowObject winobj = PG_WINDOW_OBJECT();
     143             :     rank_context *context;
     144             :     bool        up;
     145          40 :     int64       totalrows = WinGetPartitionRowCount(winobj);
     146             : 
     147             :     Assert(totalrows > 0);
     148             : 
     149          40 :     up = rank_up(winobj);
     150          40 :     context = (rank_context *)
     151             :         WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
     152          40 :     if (up)
     153          16 :         context->rank = WinGetCurrentPosition(winobj) + 1;
     154             : 
     155             :     /* return zero if there's only one row, per spec */
     156          40 :     if (totalrows <= 1)
     157           4 :         PG_RETURN_FLOAT8(0.0);
     158             : 
     159          36 :     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          40 : window_cume_dist(PG_FUNCTION_ARGS)
     170             : {
     171          40 :     WindowObject winobj = PG_WINDOW_OBJECT();
     172             :     rank_context *context;
     173             :     bool        up;
     174          40 :     int64       totalrows = WinGetPartitionRowCount(winobj);
     175             : 
     176             :     Assert(totalrows > 0);
     177             : 
     178          40 :     up = rank_up(winobj);
     179          40 :     context = (rank_context *)
     180             :         WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
     181          40 :     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          32 :         context->rank = WinGetCurrentPosition(winobj) + 1;
     190             : 
     191             :         /*
     192             :          * start from current + 1
     193             :          */
     194          40 :         for (row = context->rank; row < totalrows; row++)
     195             :         {
     196          24 :             if (!WinRowsArePeers(winobj, row - 1, row))
     197          16 :                 break;
     198           8 :             context->rank++;
     199             :         }
     200             :     }
     201             : 
     202          40 :     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          52 : window_ntile(PG_FUNCTION_ARGS)
     212             : {
     213          52 :     WindowObject winobj = PG_WINDOW_OBJECT();
     214             :     ntile_context *context;
     215             : 
     216          52 :     context = (ntile_context *)
     217             :         WinGetPartitionLocalMemory(winobj, sizeof(ntile_context));
     218             : 
     219          52 :     if (context->ntile == 0)
     220             :     {
     221             :         /* first call */
     222             :         int64       total;
     223             :         int32       nbuckets;
     224             :         bool        isnull;
     225             : 
     226          16 :         total = WinGetPartitionRowCount(winobj);
     227          16 :         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          16 :         if (isnull)
     234           8 :             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           8 :         if (nbuckets <= 0)
     241           4 :             ereport(ERROR,
     242             :                     (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTILE),
     243             :                      errmsg("argument of ntile must be greater than zero")));
     244             : 
     245           4 :         context->ntile = 1;
     246           4 :         context->rows_per_bucket = 0;
     247           4 :         context->boundary = total / nbuckets;
     248           4 :         if (context->boundary <= 0)
     249           0 :             context->boundary = 1;
     250             :         else
     251             :         {
     252             :             /*
     253             :              * If the total number is not divisible, add 1 row to leading
     254             :              * buckets.
     255             :              */
     256           4 :             context->remainder = total % nbuckets;
     257           4 :             if (context->remainder != 0)
     258           4 :                 context->boundary++;
     259             :         }
     260             :     }
     261             : 
     262          40 :     context->rows_per_bucket++;
     263          40 :     if (context->boundary < context->rows_per_bucket)
     264             :     {
     265             :         /* ntile up */
     266           8 :         if (context->remainder != 0 && context->ntile == context->remainder)
     267             :         {
     268           4 :             context->remainder = 0;
     269           4 :             context->boundary -= 1;
     270             :         }
     271           8 :         context->ntile += 1;
     272           8 :         context->rows_per_bucket = 1;
     273             :     }
     274             : 
     275          40 :     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      120628 : leadlag_common(FunctionCallInfo fcinfo,
     287             :                bool forward, bool withoffset, bool withdefault)
     288             : {
     289      120628 :     WindowObject winobj = PG_WINDOW_OBJECT();
     290             :     int32       offset;
     291             :     bool        const_offset;
     292             :     Datum       result;
     293             :     bool        isnull;
     294             :     bool        isout;
     295             : 
     296      120628 :     if (withoffset)
     297             :     {
     298         200 :         offset = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
     299         200 :         if (isnull)
     300           0 :             PG_RETURN_NULL();
     301         200 :         const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
     302             :     }
     303             :     else
     304             :     {
     305      120428 :         offset = 1;
     306      120428 :         const_offset = true;
     307             :     }
     308             : 
     309      120628 :     result = WinGetFuncArgInPartition(winobj, 0,
     310             :                                       (forward ? offset : -offset),
     311             :                                       WINDOW_SEEK_CURRENT,
     312             :                                       const_offset,
     313             :                                       &isnull, &isout);
     314             : 
     315      120628 :     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         156 :         if (withdefault)
     322          32 :             result = WinGetFuncArgCurrent(winobj, 2, &isnull);
     323             :     }
     324             : 
     325      120628 :     if (isnull)
     326         124 :         PG_RETURN_NULL();
     327             : 
     328      120504 :     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      120228 : window_lag(PG_FUNCTION_ARGS)
     339             : {
     340      120228 :     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
     350          40 : window_lag_with_offset(PG_FUNCTION_ARGS)
     351             : {
     352          40 :     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          40 : window_lag_with_offset_and_default(PG_FUNCTION_ARGS)
     362             : {
     363          40 :     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         200 : window_lead(PG_FUNCTION_ARGS)
     374             : {
     375         200 :     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          80 : window_lead_with_offset(PG_FUNCTION_ARGS)
     386             : {
     387          80 :     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          40 : window_lead_with_offset_and_default(PG_FUNCTION_ARGS)
     397             : {
     398          40 :     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        1352 : window_first_value(PG_FUNCTION_ARGS)
     408             : {
     409        1352 :     WindowObject winobj = PG_WINDOW_OBJECT();
     410             :     Datum       result;
     411             :     bool        isnull;
     412             : 
     413        1352 :     result = WinGetFuncArgInFrame(winobj, 0,
     414             :                                   0, WINDOW_SEEK_HEAD, true,
     415             :                                   &isnull, NULL);
     416        1352 :     if (isnull)
     417          40 :         PG_RETURN_NULL();
     418             : 
     419        1312 :     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        1876 : window_last_value(PG_FUNCTION_ARGS)
     429             : {
     430        1876 :     WindowObject winobj = PG_WINDOW_OBJECT();
     431             :     Datum       result;
     432             :     bool        isnull;
     433             : 
     434        1876 :     result = WinGetFuncArgInFrame(winobj, 0,
     435             :                                   0, WINDOW_SEEK_TAIL, true,
     436             :                                   &isnull, NULL);
     437        1864 :     if (isnull)
     438          40 :         PG_RETURN_NULL();
     439             : 
     440        1824 :     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         324 : window_nth_value(PG_FUNCTION_ARGS)
     450             : {
     451         324 :     WindowObject winobj = PG_WINDOW_OBJECT();
     452             :     bool        const_offset;
     453             :     Datum       result;
     454             :     bool        isnull;
     455             :     int32       nth;
     456             : 
     457         324 :     nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
     458         324 :     if (isnull)
     459           0 :         PG_RETURN_NULL();
     460         324 :     const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
     461             : 
     462         324 :     if (nth <= 0)
     463           4 :         ereport(ERROR,
     464             :                 (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
     465             :                  errmsg("argument of nth_value must be greater than zero")));
     466             : 
     467         320 :     result = WinGetFuncArgInFrame(winobj, 0,
     468             :                                   nth - 1, WINDOW_SEEK_HEAD, const_offset,
     469             :                                   &isnull, NULL);
     470         320 :     if (isnull)
     471          36 :         PG_RETURN_NULL();
     472             : 
     473         284 :     PG_RETURN_DATUM(result);
     474             : }

Generated by: LCOV version 1.13