Frames

We learned that PARTITION BY is one way to limit which rows can be seen in a window for a window function. It limits a row's window to only the rows where the value(s) in named in PARTITION BY are equal. These can be column values or computed ones.

Sometimes, a window needs to be determined by something more complex than just the equality of value(s). This is where frames are useful.

Suppose you're a software engineer working for a bank called "Goliath National Bank". The mobile engineers are building an app to show the users their account balances. You have a table called checking_account_activity which shows the transactions for each account.

The values in the amount_usd column can be positive or negative dollar amounts. Positive values are incoming payments or deposits into the account, and negative amounts are outgoing payments or withdrawals from the account.

Since we don't have account balances stored in any tables directly, we have to calculate them from their transaction history. If we want to do this for every user, we can just take a sum of all of their transactions, and use a familiar GROUP BY account_id.

SELECT
  account_id,
  sum(amount_usd) AS account_balance
FROM checking_account_activity
GROUP BY account_id;

Goliath National Bank isn't so big yet. They only have 3 account holders as of now.

Windows Through Time

This is great. We have the account balances being calculated for the users. But the users want more! They want to see their transaction history plus their account balance after each transaction. Since we need to display the transaction history, we need to preserve each row in checking_account_activity, and therefore we cannot aggregate à la GROUP BY.

Using sum() as a window function can help us here since it will preserve the individual rows. But what should the window be? Let's just start with what we know so far.

SELECT
  account_id,
  created_at,
  exchanged_with,
  amount_usd,
  sum(amount_usd) OVER (
    PARTITION BY account_id
  )
FROM checking_account_activity
ORDER BY account_id, created_at;

This gives us the transaction history plus the current account balance. However, we don't see what the balance was at the time of each transaction. This is because the window for each transaction row includes is erroneously including the future rows. Instead, we want to limit the window for each transaction to be all transactions from the past and not the future. This is done with a frame. A frame is another way to filter down the window within a partition. A frame definition looks like this:

ROWS BETWEEN (frame_start) AND (frame_end)

The (frame_start) can be one of:

• CURRENT ROW: starts the frame at the current row.
• n PRECEDING, where n is an integer: starts the frame at n rows before the current row.
• UNBOUNDED PRECEDING: includes all rows before the current row.

The (frame_end) can be one of:

• CURRENT ROW: ends the frame at the current row.
• n FOLLOWING, where n is an integer: ends the frame at n rows after the current row.
• UNBOUNDED FOLLOWING: includes all rows after the current row.

That limits the window to only see rows that fall within the range specified by the frame.

This is what we want in our case:

SELECT
  account_id,
  created_at,
  exchanged_with,
  amount_usd,
  sum(amount_usd) OVER (
    PARTITION BY account_id ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS account_balance
FROM checking_account_activity
ORDER BY account_id, created_at;

Note that we also added an ORDER BY to the window which is necessary because created_at determines the order. At each row, the window sees all past rows up until the current row. Then, sum(amount_usd) gets the sum, or account balance up to that point in time.

Default Frame

It turns out that the frame definition is optional here. All that's needed is the ORDER BY.

SELECT
  account_id,
  created_at,
  exchanged_with,
  amount_usd,
  sum(amount_usd) OVER (
    PARTITION BY account_id ORDER BY created_at
  ) AS account_balance
FROM checking_account_activity
ORDER BY account_id, created_at;

This produces the same result because ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the default frame when an aggregate function has a window with ORDER BY. It is just assumed. If ORDER BY is used in the window of an aggregate function, you only need to specify a frame if it is different than the default.

Frame Modes

The examples we saw here define the frame in "row mode" where the frame definition begins with ROWS. There are 2 other modes possible: GROUPS and RANGE. These are alternative ways to specify a frame. To briefly define the modes here…

• ROWS - Counts n rows preceding and following.
• GROUPS - Like ROWS except that it counts n groups. A "group" is a set of rows that have the same value for what is specified in the ORDER BY, i.e. rows with tied values are counted together as 1.
• RANGE - Sees all rows where the value for what is specific in ORDER BY is between the range specified by the frame start and frame end. For example, if the window orders by a date, the frame could be between 2 INTERVAL values RANGE BETWEEN '1 day' PRECEDING AND '10 days' FOLLOWING. The values get automatically CASTed here.