Skip to content

fby

Apply an aggregate to groups

(aggr;d) fby g

Where

  • aggr is an aggregate function
  • d and g are vectors of the same length (g can also be a table, to group on several columns)

collects the items of d into sublists according to the corresponding items of g, applies aggr to each sublist, and returns the results as a vector with the same count as d. If d and g have different lengths, fby signals a length error.

When to use fby

Use fby to collapse cascaded

select … from select … by … from t

expressions into a single expression:

select … by … from … where … fby …

Think of fby when you find yourself trying to apply a filter to the aggregated column of a table produced by select … by ….

q)dat:4 9 2 7 0 1 9 2 1 8
q)grp:`a`b`a`b`c`d`c`d`d`a
q)(sum;dat) fby grp
14 16 14 16 9 4 9 4 4 14

Collect the items of dat into sublists according to the items of grp.

q)group grp
a| 0 2 9
b| 1 3
c| 4 6
d| 5 7 8

q)dat group grp
a| 4 2 8
b| 9 7
c| 0 9
d| 1 2 1

Apply aggr to each sublist.

q)sum each dat group grp
a| 14
b| 16
c| 9
d| 4

fby builds the result by replacing each item of grp with the result of applying aggr to its sublist.

q)(sum;dat) fby grp
14 16 14 16 9 4 9 4 4 14
q)(sum each dat group grp)grp / without fby
14 16 14 16 9 4 9 4 4 14

If aggr returns a list with the same count as its argument, such as sums, each item of the result is the corresponding item of that list.

q)(sums;dat) fby grp
4 9 6 16 0 1 9 3 4 14

Vectors

q)dat:2 5 4 1 7             / data
q)grp:"abbac"               / group by
q)(sum;dat) fby grp         / apply sum to the groups
3 9 9 3 7
q)(first;dat) fby grp       / apply first to the groups
2 5 5 2 7

Tables

In a select, you usually apply a comparison function to the result of fby, for example:

select from t where 10 < (sum;d) fby a
q)sp:([]s:`s1`s1`s1`s1`s4`s1`s2`s2`s3`s4`s4`s1;p:`p1`p2`p3`p4`p5`p6`p1`p2`p2`p2`p4`p5;qty:300 200 400 200 100 100 300 400 200 200 300 400)
q)sp
s  p  qty
---------
s1 p1 300
s1 p2 200
s1 p3 400
s1 p4 200
s4 p5 100
s1 p6 100
s2 p1 300
s2 p2 400
s3 p2 200
s4 p2 200
s4 p4 300
s1 p5 400

Sales where the quantity is greater than the average quantity for that part:

q)select from sp where qty > (avg;qty) fby p
s  p  qty
---------
s2 p2 400
s4 p4 300
s1 p5 400

Sales where the quantity equals the maximum quantity for that part:

q)select from sp where qty = (max;qty) fby p
s  p  qty
---------
s1 p1 300
s1 p3 400
s1 p6 100
s2 p1 300
s2 p2 400
s4 p4 300
s1 p5 400

To group on multiple columns, tabulate them in g.

q)update x:0 2 0 1 0 0 0 2 2 2 1 1 from `sp
`sp
q)sp
s  p  qty x
-----------
s1 p1 300 0
s1 p2 200 2
s1 p3 400 0
s1 p4 200 1
s4 p5 100 0
s1 p6 100 0
s2 p1 300 0
s2 p2 400 2
s3 p2 200 2
s4 p2 200 2
s4 p4 300 1
s1 p5 400 1

q)select from sp where qty = (max;qty) fby ([]s;x)
s  p  qty x
-----------
s1 p2 200 2
s1 p3 400 0
s4 p5 100 0
s2 p1 300 0
s2 p2 400 2
s3 p2 200 2
s4 p2 200 2
s4 p4 300 1
s1 p5 400 1

The aggregator can be more complex and receive a subset of the table.

For example, suppose each supplier has a limit on the total quantity, and you want all the sales up to the point where the limit is breached. To find them, compute the cumulative quantity by supplier s: the sales whose cumulative quantity is within the limit are valid, and the others are ignored. The aggregator is a custom function that checks, for each s, whether the cumulative quantity is within the limit.

q)update lmt:0.5*sum qty by s from `sp
`sp
q)select from sp where ({exec lmt>=sums qty from x};([]qty;lmt)) fby s
s  p  qty x lmt
---------------
s1 p1 300 0 800
s1 p2 200 2 800
s4 p5 100 0 300
s2 p1 300 0 350
s4 p2 200 2 300

q-SQL