• Aggregated Pivot Table

    Question: A pivot table is used to draw attention to useful information. It groups data based on key column(s) and rearranges the data based on the pivot column. Define a function 'pvt' that takes in a simple table, key column(s), pivot column, values column, and monadic aggregation function, and returns a pivot table with the function applied on each grouping.

    More Information:

    https://en.wikipedia.org/wiki/Pivot_table

    Example

                                    
                                q)q:select from quote where date in 2018.01.03 2018.01.04, sym in `A`AA`AAAP`AABA`AAC`AADR
// single key column
q)pvt[q;`date;`sym;`bid_price;avg]
date       A        AA       AAAP     AABA     AAC      AADR
----------------------------------------------------------------
2018.01.03 68.83324 53.91935 79.77654 72.88424 9.085549 60.43724
2018.01.04 68.93665 54.40963 77.70118 74.27877 9.047451 60.98878
// verify pivot table values
q)0!select avg bid_price by date,sym from q
date       sym  bid_price
-------------------------
2018.01.03 A    68.83324
2018.01.03 AA   53.91935
2018.01.03 AAAP 79.77654
2018.01.03 AABA 72.88424
2018.01.03 AAC  9.085549
..

// multiple key columns
q)pvt[q;`date`sym;`exchange;`bid_price;max]
date       sym  A     B     J     K     M     N     P     Q     T     V     X     Y     Z
---------------------------------------------------------------------------------------------
2018.01.03 A    69.42 69.49 69.45 69.48 69.37 69.49 69.49       69.49 69.46 69.48 69.47 69.49
2018.01.03 AA   55.13 55.13 55.12 55.13 54.82 55.13 55.13       55.13 55.08 55.07 55.13 55.13
2018.01.03 AAAP 81.7  81.76 81.76 81.75 0           81.75 81.76       81.74 81.74 81.76 81.75
2018.01.03 AABA 73.85 73.85 73.85 73.85 73.75       73.85 73.85       73.85 73.85 73.85 73.85
2018.01.03 AAC  9.25  9.27  9.23  9.25  9.23  9.27  9.25        9.27  9.23  9.25  9.25  9.25
..
// verify pivot table values
q)0!select max bid_price by date,sym,exchange from q
date       sym exchange bid_price
---------------------------------
2018.01.03 A   A        69.42
2018.01.03 A   B        69.49
2018.01.03 A   J        69.45
2018.01.03 A   K        69.48
2018.01.03 A   M        69.37
..

    Solution

    Tags:
    functions tables
    Searchable Tags
    algorithms api architecture asynchronous c csv data structures dictionaries disk feedhandler finance functions ingestion ipc iterators machine learning math multithreading optimizations realtime shared library sql statistics streaming strings tables temporal utility websockets