{"id":3336,"date":"2016-03-09T01:51:36","date_gmt":"2016-03-09T01:51:36","guid":{"rendered":"http:\/\/www.garysieling.com\/blog\/?p=3336"},"modified":"2016-03-09T01:51:36","modified_gmt":"2016-03-09T01:51:36","slug":"rethinkdb-pivot-table-example","status":"publish","type":"post","link":"https:\/\/www.garysieling.com\/blog\/rethinkdb-pivot-table-example\/","title":{"rendered":"RethinkDB: Pivot Table Example"},"content":{"rendered":"

Making a pivot table is a relatively common reporting task. I thought it’d be interesting to demonstrate how to do this with RethinkDB, as it shows how to build a complex query.<\/p>\n

In my sample data, we’ll use a table that lists salary information for a list of people. We’ll generate a pivot table that shows average salary by department, role and level.<\/p>\n

The first step is to compute all the averages, so that we can apply the pivot:<\/p>\n

r.db('test')\n .table('salaries')\n .group('Department', 'role', 'level')\n .avg('salary')\n .ungroup()\n<\/pre>\n
In SQL, this would have been equivalent to:<\/p>\n
SELECT Department, role, level, avg(salary)\nGROUP BY 1, 2, 3\n<\/pre>\n
Notice how in SQL this would only return rows for values that exist. Values for each combination of role and level are in different rows, rather than columns.<\/p>\n
To start the pivot, we first turn each result into an array:<\/p>\n
 ...\n .map( (doc) =>\n    [doc('group')(0), \n     doc('group')(1), \n     doc('group')(2), \n     doc('reduction')]\n )\n<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n
<\/td>\n\n
\n
value<\/em><\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
1<\/div>\n<\/td>\n
\n
\n
\n
\n[“Maintenance”, “Engineer”, 2, 72000]\n<\/div>\n<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
2<\/div>\n<\/td>\n
\n
\n
\n
\n
[“Maintenance”, “Manager”, 1, 120000]<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
3<\/div>\n<\/td>\n
\n
\n
\n
\n
[“New Product Development”, “Designer”, 1, 40500]<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
4<\/div>\n<\/td>\n
\n
\n
\n
\n
[“New Product Development”, “Designer”, 2, 64500]<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
6<\/div>\n<\/td>\n
\n
\n
\n
\n
[“New Product Development”, “Engineer”, 1, 40000]<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
7<\/div>\n<\/td>\n
\n
\n
\n
\n
[“New Product Development”, “Engineer”, 2, 65000]<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
8<\/div>\n<\/td>\n
\n
\n
\n
\n
[“New Product Development”, “Engineer”, 3, 112500]<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
9<\/div>\n<\/td>\n
\n
\n
\n
\n
[“New Product Development”, “Manager”, 1, 103500]<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
This allows us to apply transformations to all the row values, so we can use some as column names.<\/p>\n
Once we do this, we can combine the role and level fields into column names – i.e. to produce names like “Engineer_1”, “Engineer_2”, “Manager_1”, “Manager_2”, and so on.<\/p>\n
...\n  .map( (doc) =>\n    r.object(\n     'Department', \n     doc(0), \n     doc(1).add('_').add(doc(2).coerceTo('string')), \n     doc(3))\n  )\n<\/pre>\n
The above expression is requires you to coerce numeric values to strings – “add”expects to operate on two values of the same type.<\/p>\n
If you get null reference errors, you can start this with “expr(”).add(…)” to ensure the absence of null values.<\/p>\n
\n
\n
\n
\n
\n
\n
\n
\n
The above query will return a result that looks a bit odd, but almost a pivot table:<\/p>\n
9 rows returned in 4ms.<\/p>\n<\/div>\n
<\/div>\n
\n
\n
\n
\n\n\n\n\n\n\n\n\n\n\n\n\n
<\/td>\n\n
\n
Department<\/div>\n<\/div>\n<\/td>\n
\n
\n
Engineer_2<\/div>\n<\/div>\n<\/td>\n
\n
\n
Manager_1<\/div>\n<\/div>\n<\/td>\n
\n
\n
Designer_1<\/div>\n<\/div>\n<\/td>\n
\n
\n
Designer_2<\/div>\n<\/div>\n<\/td>\n
\n
\n
Engineer_1<\/div>\n<\/div>\n<\/td>\n
\n
\n
Engineer_3<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
1<\/div>\n<\/td>\n
\n
\n
Maintenance<\/div>\n<\/div>\n<\/td>\n
\n
\n
72000<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
2<\/div>\n<\/td>\n
\n
\n
Maintenance<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
120000<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
3<\/div>\n<\/td>\n
\n
\n
New Product Development<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
40500<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
4<\/div>\n<\/td>\n
\n
\n
New Product Development<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
64500<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
5<\/div>\n<\/td>\n
\n
\n
New Product Development<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
6<\/div>\n<\/td>\n
\n
\n
New Product Development<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
40000<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
7<\/div>\n<\/td>\n
\n
\n
New Product Development<\/div>\n<\/div>\n<\/td>\n
\n
\n
65000<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
8<\/div>\n<\/td>\n
\n
\n
New Product Development<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
112500<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
9<\/div>\n<\/td>\n
\n
\n
New Product Development<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
103500<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\u00a0We combine the rows by grouping by the column we want to keep as a label (in this case, ‘Department’). Then, do a\u00a0reduction using the\u00a0‘merge’ function\u00a0to combine rows. Since each column is only defined in a single row, it’s safe to combine these (this will occur in whatever order RethinkDB chooses)<\/div>\n
...\n .group('Department')\n .reduce(\n   (a, b) => a.merge(b)\n )\n<\/pre>\n
This produces a nice looking result. Note that we still have ‘reduction’ bit.<\/p>\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n\n\n\n\n\n
\n
\n
group<\/div>\n<\/div>\n<\/td>\n
\n
\n
reduction.Department<\/div>\n<\/div>\n<\/td>\n
\n
\n
reduction.Engineer_2<\/div>\n<\/div>\n<\/td>\n
\n
\n
reduction.Manager_1<\/div>\n<\/div>\n<\/td>\n
\n
\n
reduction.Designer_1<\/div>\n<\/div>\n<\/td>\n
\n
\n
reduction.Designer_2<\/div>\n<\/div>\n<\/td>\n
\n
\n
reduction.Engineer_1<\/div>\n<\/div>\n<\/td>\n
\n
\n
reduction.Engineer_3<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
1<\/div>\n<\/td>\n
\n
\n
Maintenance<\/div>\n<\/div>\n<\/td>\n
\n
\n
Maintenance<\/div>\n<\/div>\n<\/td>\n
\n
\n
72000<\/div>\n<\/div>\n<\/td>\n
\n
\n
120000<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
2<\/div>\n<\/td>\n
\n
\n
New Product Development<\/div>\n<\/div>\n<\/td>\n
\n
\n
New Product Development<\/div>\n<\/div>\n<\/td>\n
\n
\n
65000<\/div>\n<\/div>\n<\/td>\n
\n
\n
103500<\/div>\n<\/div>\n<\/td>\n
\n
\n
40500<\/div>\n<\/div>\n<\/td>\n
\n
\n
64500<\/div>\n<\/div>\n<\/td>\n
\n
\n
40000<\/div>\n<\/div>\n<\/td>\n
\n
\n
112500<\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
<\/div>\n
We can fix this with one more transformation, to remove the effects of ‘group’ and just get the child objects:<\/div>\n
\n…
\n.ungroup()
\n.map( (doc) => doc(‘reduction’) )<\/div>\n
<\/div>\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
2 rows returned in 4ms.<\/p>\n<\/div>\n
<\/div>\n
\n
\n
\n
\n\n\n\n\n\n
<\/td>\n\n
\n
Department<\/div>\n<\/div>\n<\/td>\n
\n
\n
Engineer_2<\/div>\n<\/div>\n<\/td>\n
\n
\n
Manager_1<\/div>\n<\/div>\n<\/td>\n
\n
\n
Designer_1<\/div>\n<\/div>\n<\/td>\n
\n
\n
Designer_2<\/div>\n<\/div>\n<\/td>\n
\n
\n
Engineer_1<\/div>\n<\/div>\n<\/td>\n
\n
\n
Engineer_3<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
1<\/div>\n<\/td>\n
\n
\n
Maintenance<\/div>\n<\/div>\n<\/td>\n
\n
\n
72000<\/div>\n<\/div>\n<\/td>\n
\n
\n
120000<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n
\n
\n
undefined<\/div>\n<\/div>\n<\/td>\n<\/tr>\n
\n
2<\/div>\n<\/td>\n
\n
\n
New Product Development<\/div>\n<\/div>\n<\/td>\n
\n
\n
65000<\/div>\n<\/div>\n<\/td>\n
\n
\n
103500<\/div>\n<\/div>\n<\/td>\n
\n
\n
40500<\/div>\n<\/div>\n<\/td>\n
\n
\n
64500<\/div>\n<\/div>\n<\/td>\n
\n
\n
40000<\/div>\n<\/div>\n<\/td>\n
\n
\n
112500<\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
<\/div>\n
And here is the final query, in all it’s glory:<\/div>\n
r.db('test')\n .table('salaries')\n .group('Department', 'role', 'level')\n .avg('salary')\n .ungroup()\n .map( (doc) => [\n   doc('group')(0), \n   doc('group')(1), \n   doc('group')(2), \n   doc('reduction')\n  ])\n .map( (doc) =>\n   r.object(\n     'Department', \n     doc(0), \n     doc(1).add('_').add(doc(2).coerceTo('string')), \n     doc(3))\n )\n .group('Department')\n .reduce(\n   (a, b) => a.merge(b)\n )\n .ungroup()\n .map( \n   (doc) => doc('reduction') \n )\n<\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Making a pivot table is a relatively common reporting task. I thought it’d be interesting to demonstrate how to do this with RethinkDB, as it shows how to build a complex query. In my sample data, we’ll use a table that lists salary information for a list of people. We’ll generate a pivot table that … <\/p>\n
Continue reading “RethinkDB: Pivot Table Example”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[4],"tags":[204,302,435,461,462,466,523],"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/3336"}],"collection":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/comments?post=3336"}],"version-history":[{"count":0,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/3336\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/media?parent=3336"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/categories?post=3336"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/tags?post=3336"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}