窗口函数

SELECT
  id,

  -- A sliding average over 3 rows, including the current row
  avg(amount) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  amount,

  -- A cumulative sum over all previous rows
  sum(amount) OVER (ORDER BY id)
FROM (VALUES
  (1, 10.0),
  (2, 15.0),
  (3, 20.0),
  (4, 25.0),
  (5, 30.0),
  (6, 35.0)
) AS t (id, amount)

+----+-----------------+--------+-----------------+
| id | sliding average | amount |  cumulative sum |
+----+-----------------+--------+-----------------+
|  1 |  12.5           |   10.0 | --\        10.0 |
|  2 |  15.0           |   15.0 |   |        25.0 |
|  3 |  20.0           |   20.0 |   |        45.0 |
|  4 |  25.0       /-- |   25.0 |   |        70.0 |
|  5 |  30.0 = AVG +   |   30.0 | --+ SUM = 100.0 |
|  6 |  32.5       \-- |   35.0 |           135.0 |
+----+-----------------+--------+-----------------+