IRI Blog Articles

Diving Deeper into Data Management

 

 

Multiply Aggregation

by Paul Friedland

As CoSort users have known for a long time, the Sort Control Language (SortCL) program supports multi-level, conditional aggregation functions on a static or running basis. These functions have included value ranking, and:

  • /SUM
  • /MIN
  • /MAX
  • /AVERAGE
  • /COUNT

aggregations, but not multiplication. However, multiplication in SortCL is possible.

Consider this source data for example:

1.00
2.00
3.00
4.00
5.00

The SortCL job script below uses exponentiation on the sum of logs to get the product of down-values, based on the idea provided at http://viralpatel.net/blogs/row-data-multiplication-in-oracle/.

/INFILE=Source
  /field = (x,pos = 1,size = 5.2, numeric)
/REPORT  # no sort
/OUTFILE=Target
  /HEADREC = "    x       log(x)    Sigma   exp(Sigma)\n"
  /FIELD = (x,                type = numeric, pos =  1, size =7.2)
  /FIELD = (log_x = log(x),   type = numeric, pos = 12, size =7.5)
  /FIELD = (Sigma_log_x ,     type = numeric, pos = 22, size =7.5)
  /FIELD = (exp(Sigma_log_x), type = numeric, pos = 32, size =7.2)
  /SUM Sigma_log_x from log_x running

The output contains the multiple (product) in the last column.

x       log(x)    Sigma   exp(Sigma)
1.00    0.00000   0.00000      1.00
2.00    0.69315   0.69315      2.00
3.00    1.09861   1.79176      6.00
4.00    1.38629   3.17805     24.00
5.00    1.60944   4.78749    120.00
Print Friendly

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: