JDB/Aggregation

Introduction

When aggregation is used with the result columns of a query, a grouping operation (count, sum, average, min, max) is applied to some aggregated columns, being grouped by the rest of the columns.

For example, using sandp database, in

   count jname by city from j

count is a grouping operation, jname is an aggregated column and city is a by-column.

Details

In certain result columns, we may notice that some values are repeated, as here in the the city column:

   Reads__d 'from j'
+---+-------+------+
|jid|jname  |city  |
+---+-------+------+
|J1 |Sorter |Paris |
|J2 |Display|Rome  |
|J3 |OCR    |Athens|
|J4 |Console|Athens|
|J5 |RAID   |London|
|J6 |EDS    |Oslo  |
|J7 |Tape   |London|
+---+-------+------+

This is an indication that we can aggregate by this column:

   Reads__d 'count jname by city from j'
+------+-----+
|city  |jname|
+------+-----+
|Paris |1    |
|Rome  |1    |
|Athens|2    |
|London|2    |
|Oslo  |1    |
+------+-----+

After aggregation, the set of by-columns forms unique combinations, so it can be thought of as the key of the result.

For convenience, aliases can be assigned to the result columns

   Reads__d 'avg weight,pone:first pid by where:city,color from p'
+------+-----+------+----+
|where |color|weight|pone|
+------+-----+------+----+
|London|Red  |15    |P1  |
|Paris |Green|17    |P2  |
|Oslo  |Blue |17    |P3  |
|Paris |Blue |12    |P5  |
+------+-----+------+----+

As with regular queries, aggregated and by-columns can span across multiple related tables

   Reads__d 'avg_qty:avg qty by part_col:p.color from spj'
+--------+-------+
|part_col|avg_qty|
+--------+-------+
|Red     |    400|
|Blue    |353.846|
|Green   |    150|
+--------+-------+

The by-columns are optional, in which case for all values of an aggregated column a single value is returned

   Read__d 'avg_qty:avg qty from spj'
+---+-------+
|qty|354.167|
+---+-------+

More examples are presented in sandp sample database.

   load 'data/jdb'
   demosandp_jdb_ jpath'~temp'

   d=. opensandp_jdb_ jpath'~temp'

See Also

• Formal Definition
• Prototype Implementation