rails/arel — pull request #103 by alexstaubo (2012-02-22 15:10:37+00:00)
Title: ANSI SQL2003 window functions

It was necessary to modify Arel to support window functions.

### Example of a simple window function

```ruby
table = Arel::Table.new('users')
table.project(['date', table[:id].count.over])
```

Generates:

```sql
SELECT "users"."date", COUNT("users"."id") OVER ()
FROM "users"
```

### Window function with clauses

```ruby
table = Arel::Table.new('users')
window = Arel::Nodes::Window.new.
order(t['date'].asc).
rows(Arel::Nodes::Preceding.new)
table.project(['date', table[:id].count.over(window)])
```

Generates:

```sql
SELECT "users"."date", COUNT("users"."id") OVER (
ORDER BY "users"."date" ASC
ROWS UNBOUNDED PRECEDING
)
FROM "users"
```

### Declaring named windows

```ruby
table = Arel::Table.new('users')
expr = table.project(['date', table[:id].count.over('mywindow')])
expr.window('mywindow').
order(t['date'].asc).
range(Arel::Nodes::Preceding.new(10))
```

Generates:

```sql
SELECT "users"."date", COUNT("users"."id") OVER "mywindow"
FROM "users"
WINDOW "mywindow" AS (
ORDER BY "users"."date" ASC
RANGE 10 PRECEDING
)
```

### Caveats

* PostgreSQL is the only database that I know (aside from Oracle) that implements window functions. So I have focused on syntax that is compatible with Postgres.

* I have not implemented `PARTITION BY` yet, but I will be working on that.

* I have tried to be careful about fitting this into Arel's node structure (with regard to things like arity), but the AST is somewhat inconsistently defined, and it's possible I have made a bunch of mistakes. For example, I have defined `ROWS` as a unary operator (as it takes an expression: `ROWS UNBOUNDED PRECEDING`, say), but it's debatable whether this is correctly. Similarly, the framing clause ` PRECEDING` is also implemented as unary, even though the value comes first. Also, even though `OVER` is a unary operator, `OVER ()` is a valid clause, which is implemented by giving it `nil` as its right-hand side.

* The AST-building verbs are `window` on `SelectStatement`, and `over` on functions. To write proper window definitions, one will have to build the AST manually. I would like to add some factory methods for building the various clauses.