skydb/sky — pull request #144 by matthelm (2013-09-23 19:42:37+00:00)
Title: Add distinct (count) to available aggregations.

Hi Ben,

I'd like to add the ability to do a distinct (count) using Sky. Currently if you want to get the distinct count of fields you need to grab all the entries, serialize the entries in Sky, deserialize the entries in the client (Ruby) and then find the length of the distinct set. We're currently doing this at Shopify with our customer count.

```
WHEN financial_status != '' && deleted_at == '' THEN
WHEN financial_status != '' && deleted_at == '' THEN
SELECT count() AS order_count GROUP BY customer_id INTO 'query'
END
END
```

There really is no need to send the full set of customer IDs back to the client to distinctly count. Instead I'd like to see something like this.

```
WHEN financial_status != '' && deleted_at == '' THEN
WHEN financial_status != '' && deleted_at == '' THEN
SELECT distinct(customer_id) AS customer_count INTO 'query'
END
END
```

I've implemented a simple version, but there are some aspects I really don't like.
- Lua doesn't appear to have a good way of getting the length of a table (array). I've implemented a count in the `sky_distinct_merge` procedure.
- This strategy serializes all distinct values into the JSON. I feel I should be adding a way to take `histogram`, `avg`, or `distinct` objects and simplify them into a single value. How about adding a `RequiresSerialization` hook similar to `RequiresInitialization` that would serialize the object? It would result in something along the lines of the following...

```json
{
"_": {
"avg": 60,
"sum": 2720,
"distinct": 6
}
}

{
"_": {
"avg": { "__average__": 60 },
"distinct": { "__distinct__": 6 }
}
}
```

I'd love to hear your thoughts.

Matt Helm
Developer at Shopify