Grouped aggregate utility (like SQL GROUP BY)?

Question

Unix has several utilities for performing relational algebra-like operations on streams (grep, join, cut, additional things with awk). Is there a grouped aggregate utility readily available (or installable on most Linux distros)?

The goal would be to take a file with some keys in one column and values in another, such as:

foo.txt   u1    394082
bar.txt   u2    3948
frob.c    u1    29322

And output a file that has the unique values of one column, along with some aggregate of the values in another column. For example, the sum of the 3rd column by the 2nd:

$ aggregate --sum=3 --group-by=2 <data
u1 423404
u2 3948

Does such a utility exist (Perl, Awk, etc. one-liners don't count), or is it something waiting to be written?

Answer 1

I think I found this answer in another StackOverflow question, but I found "q" quite useful for that purpose: https://github.com/harelba/q.

E.g. your example goal would be achievable like that:

$ q "select c2, sum(c3) from data group by c2"
u1 423404
u2 3948

And since it uses sqlite as a backend you can use all sorts of sqlite functions to make calculations.

Answer 2

With some constraints GNU Recutils can do this. First, it needs to be a CSV file, not TSV (Recutils doesn't seem to like TSV files), and needs a header. But then I can do:

csv2rec foo.csv |recsel -G user -p 'user,sum(size)' |rec2csv

Not sure if this is better or worse than a Perl or Awk one-liner.

Answer 3

If awk one-liners don't count, maybe you'll like the following shell (bash/ksh) one-liner:

sort -k2 data | ( while read c1 c2 c3; do if [ "$prev" = "$c2" ]; then
sum=$(expr $c3 + $sum); else if [ $prev ]; then echo $prev $sum; fi;
sum=$c3; prev=$c2; fi; done; echo $prev $sum)

Apart from sort and expr (for grouping and summing, respectively), the interesting ingredient used here is the read statement inside the while. And the parentheses, which create a subprocess, necessary for localizing the $prev and $sum variables.