Tracking: Over Window (a.k.a. Window Function or Over Aggregation)

[stdrc]

User-facing functionality:

• EOWC version of streaming over window
• General version of streaming over window function, incl. lag and lead #9932
• Batch over window function, incl. lag and lead #9931

Detailed tasks:

• Streaming executor with EOWC semantics (described in RFC: Over Window on watermark rfcs#8)
  • feat(stream): implement streaming EOWC OverWindow executor #9110
• General version of streaming executor (described in RFC: General Window Functions rfcs#60)
  • refactor(over window): generalize window states for reuse in general streaming over window #10435
  • feat(over window): general streaming over window executor #10260
• Batch executor
• WindowStates (also tracked in Tracking: window functions and window aggregation #8961)
  • lag and lead with constant offset
  • Support lag/lead window function with default argument #10817
  • first_value (automatically supported by supporting aggregate functions)
  • last_value
    • feat(agg,over_window): support first_value, last_value and refactor LogicalAgg #10740
  • nth_value, lag and lead with expr offset
  • Aggregate over window (Support aggregate function as OVER window function #4978)
    • refactor(agg, over window): rearrange AggCall and related structures #9234
    • feat(stream,over-window): implement AggregateState to allow aggregate function over window #9248
  • General row_number, rank & dense_rank (cases that can't be converted to GroupTopN)
    • feat(over window): support general streaming row_number window function #10869
    • Support dense_rank and rank #8965
  • Aggregate function with filter over window #11506
• Frontend
  • Binder & LogicalOverWindow
    • feat(over-window): binder & planner support for general over window queries #9323
    • feat(over window): generate EOWC stream plan from LogicalOverAgg #9597
  • binder(over window): window function in expression #10850
  • Optimizer
    • feat(planner, overAgg): support expression in the ORDER BY & PARTITION BY clause #9395
    • optimizer: split the OverAgg with different "Order by ... partition by ..." #9727
    • optimizer: split the OverAgg if there is some part of window function can be rewritten to group topn #9726
    • feat(bind overWindow): rewrite Avg and other complex aggKind in overWindow #9929
    • Extract agg call rewriting logic to reuse between LogicalOverWindow and LogicalAgg #11507
  • StreamEowcOverWindow
    • feat(over-window): add StreamEowcOverWindow and corresponding from_proto executor builder #9429
  • frontend: StreamOverWindow node (general version) #10072
    • feat(over window): general streaming over window frontend part #10525
  • frontend: BatchOverWindow node (general version) #10016
• Window frames
  • ROWS
  • Support over window function with RANGE frame #11109
  • GROUPS
  • EXCLUDE CURRENT ROW
    • feat(over window): support EXCLUDE CURRENT ROW for window functions #9701
  • EXCLUDE GROUP
  • EXCLUDE TIES
• Performance
  • Better cache strategy for general OverWindowExecutor #11502
  • Incremental computation when getting output for a range of window frames #11503
  • Restore previous output from the row before first affected current row to reduce computation
    • not sure whether it's feasible
• Misc
  • Output watermark when there's neither lead nor window function call with following window frames #11504
  • Support OVER clause without PARTITION BY #11505
  • State cleaning in general streaming over window executor #13907
  • Emit-on-window-close for general over window with watermark on PARTITION BY column #13908