Hash Aggregate SQL Functions

All Hash Aggregate functions take input and group_by sequence arguments.

(Please see the Hash_Agg_Grouping page for a more detailed explanation.)

seq_hash_agg_count( input, group_by )	Returns a sequence with the count of the number of elements in each group
seq_hash_agg_max( input, group_by )	Returns the sequence with the maximum value for each group of elements
seq_hash_agg_min( input, group_by )	Returns the sequence with the minimum value for each group of elements
seq_hash_agg_sum( input, group_by )	Returns the sequence with the sum of each group of elements
seq_hash_agg_avg( input, group_by )	Returns the sequence with the average of each group of elements
seq_hash_agg_approxdc( input, group_by )	Returns a sequence with the approximate count of distinct values for each group

Example

As explained in Analytics Functions Library Examples page, the data in the following example is taken from historical values from 2013 for IBM. The data is loaded into xSQL by running various scripts in directory samples/xsql/scripts/financial. To reproduce the example below run the g.bat (or g.sh on Linux systems) specifying the example number 9. For instance, with scripts/financial as the current working directory, simply type:

     
    g 9
     

Following is an example code snippet demonstrating some of the hash_agg()functions for the 2013 data.

         
    -------------------------------------------------------------------------
     
    -- count
            
    INSERT INTO SimpleSequence(testNumber,iVal1,iVal2)
        VALUES(1,[1,1,2,2,2,3],[1,2,3,4,5,6]);
     
    SELECT iVal1, iVal2,
        seq_hash_agg_count(iVal1) as "count",
        seq_hash_agg_approxdc(iVal1, iVal2) as "approxdc"
    FROM SimpleSequence WHERE testNumber=1;
     
    iVal1{} 
      iVal2{}   
    count_val1{}
    group_by_count{} 
    approxdc_val1_val2{}                 
    group_by_approxdc{}
    -------------------------------------------------------------------------
    {1, 1, 2, 2, 2, 3}      
    {1, 2, 3, 4, 5, 6}      
    {2, 1, 3}       
    {1, 3, 2}       
    {1, 1, 1, 1, 1, 1}      
    {1, 4, 3, 6, 5, 2}