Optimize the query using group by on certain condition in q kdb

Question

We have a table t as below

q)t:([] sym:10?`GOOG`AMZN`IBM; px:10?100.; size:10?1000; mkt:10?`ab`cd`ef)

Our requirement is to 'group by' the table 't' by column 'sym' if column 'mkt' value is 'ef', for rest of the markets('ab`cd') we need all the values(not group by). For this use case I have written below query which works as expected,

q)(select px, size, sym, mkt from select by sym from t where mkt=`ef), select px, size, sym, mkt from t where mkt in `ab`cd

please help me optimize the above query in a way i.e

sudo code - 
if mkt=`ef: 
    then use group by on table
else if mkt in `ab`cd
    don't use group by on table

Your query already follows that pseudo code. The first part is your if clause execution (operating when mkt=ef), and the second qsql statement if your else clause. The use of where clauses in queries is highly optimized in kdb and you likely won't improve this further. — Callum Biggs
– Callum Biggs, Commented Nov 15, 2019 at 9:36

Rob Sketch · Accepted Answer · 2019-11-14 10:11:24Z

I have found two different ways to make your query that are different from the one you have provided.

You can use the following query to accomplish what you want in one select statement:

select from t where (mkt<>`ef)|(mkt=`ef)&i=(last;i)fby ([]sym;mkt)

However if you compare its speed:

q)\t:1000 select from t where (mkt<>`ef)|(mkt=`ef)&i=(last;i)fby ([]sym;mkt)
68

to your original query:

q)\t:1000 (select px, size, sym, mkt from select by sym from t where mkt=`ef), select px, size, sym, mkt from t where mkt in `ab`cd
40

You can see that your query is faster.

Additionally you can try this which does not require explicitly stating every mkt in t you wish to not group by sym

(0!select by sym from t where mkt=`ef),select from t where mkt<>`ef

But again this ends up being around the same speed as your original solution:

q)\t:1000 (0!select by sym from t where mkt=`ef),select from t where mkt<>`ef
42

So in terms of optimization it seems your query works well for what you want it to accomplish.

terrylynch · Accepted Answer · 2019-11-14 11:00:08Z

2

This isn't any quicker either (as Rob says, your query is already good in terms of speed), but is shorter at least

delete x from select by sym,(1+i)*`ef<>mkt from t

...provided you don't mind the order changing a little.

In fby form

select from t where i=(last;i)fby([]sym;(1+i)*`ef<>mkt)

answered Nov 14, 2019 at 11:00

terrylynch

14k1 gold badge15 silver badges23 bronze badges

Collectives™ on Stack Overflow

Optimize the query using group by on certain condition in q kdb

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related