jkk/honeysql — pull request #28 by Menthalion (2014-08-19 09:45:05+00:00)
Title: Added OVER (PARTITION BY) clause

Adds support for the [ANSI '99 `over` clause][1]. This give access to windowed aggregates over (ordered) partitions as fields. For example
```SQL
SELECT
person.id as source_id,
person.name,
count(*) OVER (PARTITION BY name) AS double_count,
rank() OVER (PARTITION BY name ORDER BY edit_date) AS ranking,
first_value(id) OVER (PARTITION BY name ORDER BY edit_date) AS target_id
FROM person
```
This can be created by using the following syntax
```clj
{:select (:person.name
[{:aggregate :%first_value.id,
:over {:order-by (:edit_date)
:partition-by (:name)}} :target_id]
[:person.unid :source_id])}
```
The `:aggregate` clause was added because matching formatters, helpers and builders were getting too complex to support `{:over [:%first_value.id {partition-by [:name] order-by [:edit_date]}]}`. With `:aggregate` everything seemed to fit existing code better. Here's an example with helper functions
```clj
(format (select :person.name
[(-> (over (-> (spartition-by :name)
(order-by :edit_date)))
(aggregate :%first_value.id)) :target_id]
[:person.unid :source_id]))
```
Notes:
* `partition-by` helper name was changed to `spartition-by` to prevent name clash with the core fn. Perhaps `merge-partition-by` should match that?

Suggestions:
* The hardest part in adding these clauses was figuring out the destructuring signatures of `defmethod format-clause`, `defhelper` and `defmethod build-clause`. Annotating them in code or readme might help people get up to speed faster and contribute clauses more easily.
* Another tough part was the interaction between `defhelper`, `defmethod build clause` and additional `defn *clausename*`.

[1]: http://msdn.microsoft.com/en-us/library/ms189461.aspx