Pencere Fonksiyonları — Ekstrede Yürüyen Bakiye Nasıl Hesaplanır?

1
WITH running AS (
2
    SELECT id,
3
           booked_at,
4
           description,
5
           amount,
6
           SUM(amount) OVER (
7
               PARTITION BY account_id
8
               ORDER BY booked_at, id
9
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
10
           ) AS balance,
11
           ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY booked_at DESC, id DESC) AS from_end
12
    FROM transactions
13
    WHERE account_id = :accountId
14
)
15
SELECT id, booked_at, description, amount, balance
16
FROM running
17
WHERE from_end BETWEEN :offset + 1 AND :offset + :pageSize
18
ORDER BY booked_at, id;

1
@Repository
2
class StatementRepository {
3

4
    private final NamedParameterJdbcTemplate jdbc;
5
    private final String pageSql;
6

7
    StatementRepository(NamedParameterJdbcTemplate jdbc, @Value("classpath:sql/statement-page.sql") Resource sql) throws IOException {
8
        this.jdbc = jdbc;
9
        this.pageSql = sql.getContentAsString(StandardCharsets.UTF_8);
10
    }
11

12
    /** Only the page's rows travel to the app; the balance was computed in the database. */
13
    List<StatementLine> page(UUID accountId, int offset, int pageSize) {
14
        return jdbc.query(pageSql,
15
                Map.of("accountId", accountId, "offset", offset, "pageSize", pageSize),
16
                (rs, i) -> new StatementLine(
17
                        rs.getObject("booked_at", OffsetDateTime.class),
18
                        rs.getString("description"),
19
                        rs.getBigDecimal("amount"),
20
                        rs.getBigDecimal("balance")));
21
    }
22
}

1
-- Same order as the window, so rows are read already sorted.
2
CREATE INDEX transactions_account_time ON transactions (account_id, booked_at, id);

1
-- Tempting, but look what happens:
2
-- each row re-reads every earlier row; work grows with the square of the history.
3
SELECT t.id, t.booked_at, t.description, t.amount,
4
       (SELECT SUM(p.amount)
5
          FROM transactions p
6
         WHERE p.account_id = t.account_id
7
           AND (p.booked_at, p.id) <= (t.booked_at, t.id)) AS balance
8
FROM transactions t
9
WHERE t.account_id = :accountId
10
ORDER BY t.booked_at, t.id;