PL/R User's Guide - R Procedural Language
Prev | Home |  | Next

# Chapter 9. Window Functions

Starting with version 8.4, PostgreSQL supports `WINDOW` functions which provide the ability to perform calculations across sets of rows that are related to the current query row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row; the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result. See the PostgreSQL documentation for more general information related to the use of this capability.

PL/R functions may be defined as `WINDOW`. For example:

    CREATE OR REPLACE
    FUNCTION r_regr_slope(float8, float8)
    RETURNS float8 AS
    $BODY$
      slope <- NA
      y <- farg1
      x <- farg2
      if (fnumrows==9) try (slope <- lm(y ~ x)$coefficients[2])
      return(slope)
    $BODY$
    LANGUAGE plr WINDOW;

A number of variables are automatically provided by PL/R to the R interpreter:

`farg` `N`

  `farg1` and `farg2` are R vectors containing the current row's data plus that of the related rows.

`fnumrows`

  The number of rows in the current `WINDOW` frame.

`prownum` (not shown)

  Provides the 1-based row offset of the current row in the current `PARTITION`.

A more complete example follows:

    -- create test table
    CREATE TABLE test_data (
      fyear integer,
      firm float8,
      eps float8
    );

    -- insert randomly pertubated data for test
    INSERT INTO test_data
    SELECT (b.f + 1) % 10 + 2000 AS fyear,
    	floor((b.f+1)/10) + 50 AS firm,
           f::float8/100 + random()/10 AS eps
    FROM generate_series(-500,499,1) b(f);

    CREATE OR REPLACE
    FUNCTION r_regr_slope(float8, float8)
    RETURNS float8 AS
    $BODY$
      slope <- NA
      y <- farg1
      x <- farg2
      if (fnumrows==9) try (slope <- lm(y ~ x)$coefficients[2])
      return(slope)
    $BODY$
    LANGUAGE plr WINDOW;

    SELECT *, r_regr_slope(eps, lag_eps) OVER w AS slope_R
    FROM (SELECT firm, fyear, eps,
      lag(eps) OVER (ORDER BY firm, fyear) AS lag_eps
    FROM test_data) AS a
    WHERE eps IS NOT NULL
    WINDOW w AS (ORDER BY firm, fyear ROWS 8 PRECEDING);

In this example, the variables `farg1` and `farg2` contain the current row value for eps and lag_eps, as well as the preceding 8 rows which are also in the same `WINDOW` frame within the same `PARTITION`. In this case since no `PARTITION` is explicitly defined, the `PARTITION` is the entire set of rows returned from the inner sub-select.

Another interesting example follows. The idea of "Winsorizing" is to return either the original value or, if that value is outside certain bounds, a trimmed value. So for example winsor(eps, 0.1) would return the value at the 10th percentile for values of eps less that that, the value of the 90th percentile for eps greater than that value, and the unmodified value of eps otherwise.

    CREATE OR REPLACE FUNCTION winsorize(float8, float8)
    RETURNS float8 AS
    $BODY$
      library(psych)
      return(winsor(as.vector(farg1), arg2)[prownum])
    $BODY$ LANGUAGE plr VOLATILE WINDOW;

    SELECT fyear, eps,
      winsorize(eps, 0.1) OVER (PARTITION BY fyear) AS w_eps
    FROM test_data ORDER BY fyear, eps;

In this example, use of the variable `prownum` is illustrated.

Prev | Home | Next
Aggregate Functions |  | Loading R Modules at Startup