A window function calculates a summary statistic, such as a mean, a sum, or a count, over a set of related rows and reports the result on each row. That set of rows is the window. Without any clauses, the window is the whole variable. Clauses written after the function split the rows into groups, choose which rows count, set the order of the rows, and limit the window to rows near the current row.

Window functions let you calculate group means, percentages of a group total, z-scores within groups, cumulative sums, and moving averages directly in a data table.

Where You Can Use Window Functions

Window functions work in calculated variables created with in-table formulas on Multiple Variables data tables, and in custom transforms in Select and Transform. They are not available in user-defined equations for nonlinear regression, in Plot a function, or in the Transform analysis.

Functions That Can Be Window Functions

These functions accept window clauses. Each one is described in the function reference.

•Summary statistics: MEAN, STDEV, STDERR, COUNT, COUNTA, and the single-variable forms of MIN, MAX, and SUM. The result for the window is reported on each row.

•Row-by-row statistics: CENTER, STANDARDIZE, ZSCORE, and RANK. Each row gets its own value, calculated relative to the other rows in its window.

•Conditional statistics: MINIF, MAXIF, SUMIF, MEANIF, STDEVIF, STDERRIF, COUNTIF, and COUNTAIF.

The Parts of a Window Function

A window function has this form:

FUNCTION(Variable) OVER partition WHERE filter ORDER BY sort ROWS start end

Every clause is optional, but the clauses you use must appear in this order:

•OVER partition splits the rows into groups and calculates the function separately within each group.

•WHERE filter chooses which rows contribute to the calculation, without removing any rows from the output.

•ORDER BY sort sets the order of the rows within each group.

•ROWS start end limits the window to a range of rows around the current row, in that order.

Keywords aren't case-sensitive. You can write OVER, over, or Over, and the same applies to WHERE, ORDER BY, ROWS, and the other keywords.

Example Data

The examples on this page use the data below, which records the tumor volume of two animals over several days. The column letters match the examples in Prism's list of available functions.

 

OVER: Calculate Within Groups

OVER divides the rows into groups that share the same value of the partition expression, and calculates the function separately for each group. Each row gets the result for its own group.

MEAN(E) OVER B

This returns the mean tumor volume of each treatment group: 270 on the four Control rows and 150 on the five Treated rows.

The partition can be any expression, not only a variable. For example, MEAN(E) OVER C > 0 calculates one mean for the day 0 rows and another for all later days. Rows where the partition expression is blank get a blank result.

WHERE: Choose Which Rows Contribute

WHERE restricts the calculation to rows where the filter expression is true. Rows that don't meet the filter still appear in the output and still get the result for their group.

COUNT(E) OVER B WHERE E >= 170

This counts the measurements of 170 mm³ or more in each treatment group. Every Control row shows 3, and every Treated row shows 2.

You can use WHERE without OVER. MEAN(E) WHERE C = 0 returns the mean day 0 volume, 100, on every row.

The conditional statistics functions are a shorter way to write a function with a WHERE clause. SUMIF(F, C <= 14) is the same as SUM(F) WHERE C <= 14. You can add OVER to these functions too. SUMIF(F, C <= 14) OVER A returns the total dose each animal received through day 14: 0 for M1 and 15 for M2.

ORDER BY and ROWS: Running and Moving Calculations

ORDER BY sets the order of the rows within each group, and ROWS limits the window to rows near the current row in that order. Together, they produce cumulative and moving calculations.

Rows are sorted in ascending order unless you add DESC after the sort expression (ASC is also accepted). To break ties, list more than one sort expression, separated by commas.

ROWS takes a start boundary, an end boundary, or both:

•N PRECEDING starts the window N rows before the current row.

•UNBOUNDED PRECEDING starts the window at the first row of the group.

•N FOLLOWING ends the window N rows after the current row.

•UNBOUNDED FOLLOWING ends the window at the last row of the group.

If you give only one boundary, the other boundary is the current row. For example, ROWS UNBOUNDED PRECEDING means from the first row of the group through the current row.

Examples

•SUM(F) OVER A ORDER BY C ROWS UNBOUNDED PRECEDING returns the cumulative dose for each animal, in order of day: 0, 0, 0, 0 for M1 and 5, 10, 15, 20, 25 for M2.

•MEAN(E) OVER A ORDER BY C ROWS 1 PRECEDING 1 FOLLOWING returns a centered three-point moving average: 140, 193.3, 326.7, 400 for M1 and 120, 133.3, 160, 170, 175 for M2. The first and last rows of each animal average only two values.

•MAX(E) OVER A ORDER BY C ROWS UNBOUNDED PRECEDING returns the largest volume so far: 100, 180, 300, 500 for M1 and 100, 140, 160, 180, 180 for M2.

•COUNT(E) OVER A ORDER BY C ROWS UNBOUNDED PRECEDING numbers each animal's visits: 1, 2, 3, 4 for M1 and 1, 2, 3, 4, 5 for M2.

•COUNT(E) OVER A ORDER BY C DESC ROWS UNBOUNDED PRECEDING counts the visits remaining, including the current one: 4, 3, 2, 1 for M1 and 5, 4, 3, 2, 1 for M2.

Without a ROWS clause, the window is the whole group, even when you use ORDER BY. This differs from SQL, where ORDER BY on its own produces a running total. To get a running total in Prism, add ROWS UNBOUNDED PRECEDING.

When you combine WHERE with ROWS, only rows that meet the filter get a result. The other rows are blank.

Using Window Functions in Larger Formulas

A window function can be part of a larger formula. When it is, put the window function and its clauses in parentheses. Without parentheses, Prism reads everything after OVER, WHERE, or ORDER BY as part of that clause. For example, E / SUM(E) OVER B * 100 groups the rows by B * 100; it does NOT group the rows by B then multiply the resulting values by 100. This is an important distinction since - in this case - B is a categorical variable, and the term "B * 100" won't generate a valid output. If you wanted to group by B and then multiply by 100, make the formula explicit using parentheses such as (E / SUM(E) OVER B) * 100.

•100 * E / (SUM(E) OVER A) expresses each value as a percentage of that animal's total.

•E / (MEAN(E) OVER B) divides each value by the mean of its treatment group.

•E - (MEANIF(E, C = 0) OVER A) subtracts each animal's day 0 value: 0, 80, 200, 400 for M1 and 0, 40, 60, 80, 70 for M2.

•ZSCORE(E) OVER B calculates z-scores within each treatment group.

Blank Values

Blank values are left out of the calculation. COUNT counts numeric values only, while COUNTA also counts text values. When no rows meet the condition, SUMIF, COUNTIF, and COUNTAIF return 0, and the other conditional statistics functions return blank.

Opening Files in Earlier Versions of Prism

Earlier versions of Prism can't calculate window functions or conditional statistics functions. When you open a file that uses them in an earlier version, calculated variables show their values as last calculated and can't be recalculated.