CODEWITHSUNDEEP
group-byaggregaterethinkdb
tldr
tldr

Reputation: 12112

rethinkdb: group documents by price range

I want to group documents in rethinkdb by price range (0-100, 100-200, 200-300, and so on), instead of a single price value. How do I do that?

Upvotes: 0

Views: 127

Answers (1)

Tryneus
Tryneus

Reputation: 371

Unfortunately, ReQL doesn't support rounding at the moment (see github issue #866), but you can get something similar through some minor annoyances.

First of all, I would recommend making this an index on the given table if you're going to be running this regularly or on large data sets. The function I have here is not the most efficient because we can't round numbers, and an index would help mitigate that a lot.

These code samples are in Python, since I didn't see any particular language referenced. To create the index, run something like:

r.db('foo').table('bar').index_create('price_range',
    lambda row: row['price'].coerce_to('STRING').split('.')[0].coerce_to('NUMBER')
                   .do(lambda x: x.sub(x.mod(100)))).run()

This will create a secondary index based on the price where 0 indicates [0-100), 100 is [100-200), and so on. At this point, a group-by is trivial:

r.db('foo').table('bar').group(index='price_range').run()

If you would really rather not create an index, the mapping can be done during the group in a single query:

r.db('foo').table('bar').group(
    lambda row: row['price'].coerce_to('STRING').split('.')[0].coerce_to('NUMBER')
                   .do(lambda x: x.sub(x.mod(100)))).run()

This query is fairly straight-forward, but to document what is going on:

  • coerce_to('STRING') - we obtain a string representation of the number, e.g. 318.12 becomes "318.12".
  • split('.') - we split the string on the decimal point, e.g. "318.12". becomes ["318", "12"]. If there is no decimal point, everything else should still work.
  • [0] - we take the first value of the split string, which is equivalent the original number rounded down. e.g. "318".
  • coerce_to('NUMBER') - we convert the string back into an integer, which allows us to do modulo arithmetic on it so we can round, e.g. "318" becomes 318.
  • .do(lambda x: x.sub(x.mod(100))) - we round the resulting integer down to the nearest 100 by running (essentially) x = x - (x % 100), e.g. 318 becomes 300.

Upvotes: 1

Related Questions