Search code examples
aggregatekdb

KDB: Aggregate across consecutive rows with common label

I would like to sum across consecutive rows that share the same label. Any very simple ways to do this?

Example: I start with this table...

qty flag
1 OFF
3 ON
2 ON
2 OFF
9 OFF
4 ON

... and would like to generate...

qty flag
1 OFF
5 ON
11 OFF
4 ON
Solution

  • One method:

    q)show t:flip`qty`flag!(1 3 2 2 9 4;`OFF`ON`ON`OFF`OFF`ON)
    qty flag
    --------
    1   OFF 
    3   ON  
    2   ON  
    2   OFF 
    9   OFF 
    4   ON  

q)show result:select sum qty by d:sums differ flag,flag from t
    d    flag1| qty
    ----------| ---
    1    OFF  | 1  
    2    ON   | 5  
    3    OFF  | 11 
    4    ON   | 4

    Then to get it in the format you require:

    q)`qty`flag#0!result
    qty flag
    --------
    1   OFF 
    5   ON  
    11  OFF 
    4   ON