Method: ROM::SQL::Function#over

Defined in:: lib/rom/sql/function.rb

#over(partition: nil, order: nil, frame: nil) ⇒ `SQL::Function`

Add an OVER clause making a window function call

Examples:

users.select { [id, integer::row_number().over(partition: name, order: id).as(:row_no)] }
users.select {
  [
    id,
    integer::row_number().over(
      partition: [first_name, last_name],
      order: id
    ).as(:row_no)
  ]
}

frame variants

# ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
row_number.over(frame: { rows: [-3, :current] })

# ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
row_number.over(frame: { rows: [-3, 3] })

# ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
row_number.over(frame: { rows: [:start, :current] })

# ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
row_number.over(frame: { rows: [:current, :end] })

frame shortcuts

# ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
row_number.over(frame: :all)

# ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
row_number.over(frame: :rows)

# RANGE BETWEEN CURRENT ROW AND CURRENT ROW
row_number.over(frame: { range: :current} )

Parameters:

:partition (Hash) —

a customizable set of options
:order (Hash) —

a customizable set of options
:frame (Hash) —

a customizable set of options

Returns:

(SQL::Function)

See Also:

https://www.postgresql.org/docs/9.6/static/tutorial-window.html



168
169
170

# File 'lib/rom/sql/function.rb', line 168

def over(partition: nil, order: nil, frame: nil)
  super(partition: partition, order: order, frame: WINDOW_FRAMES[frame])
end