KDB/Q query for rows matching criteria per month

user_6822876 0
Viewed 442

I would like to perform a query in Q/KDB that retrieves rows the fulfills the following criteria.

  1. Rows with the same name in a month must only have completed marked.
  2. Names should be distinct i.e. if they fulfill condition 1, they should only appear as a singular entry.

Initial Table:

completed name month
--------------------
yes       x    1    
no        x    1    
yes       y    2
yes       y    2    
no        a    3    
yes       a    4    
yes       b    4    
no        b    4    
no        b    4  
yes       y    5

Initial Table Divvied Up by Months

completed name month
--------------------
yes       x    1    
no        x    1
---------------------    
yes       y    2
yes       y    2    
--------------------
no        a    3    
--------------------
yes       a    4    
yes       b    4    
no        b    4    
no        b    4  
--------------------
yes       y    5

Resultant Table:

completed name month
--------------------
yes       y    2    
yes       a    4 
yes       y    5

Explanation:

  1. In month 1, only x transacted but has not completed one deal, hence it is not in our final table.

  2. In month 2, only y transacted with both deals being completed, hence it is in our final table.

  3. In month 3, only a transacted but the deal is not completed.

  4. In month 4, both a and b transacted, but since b has uncompleted deals, it is left out.

  5. In month 5, only y transacted, so it is added to the final table.

kdb
3 Answers

Hi Carrein you can use the following using the each right adverb and the fby filter 

distinct select from tab where ({all`yes=/:x};completed)fby([]name;month)

Additionally you might want to make use of a Boolean vector for the completed column for simplicity

user_32073510

Probably not the most q-ist answer, but at least it works:

t: ([]completed:`yes`no`yes`yes`no`yes`yes`no`no`yes; name:`x`x`y`y`a`a`b`b`b`y;month:1 1 2 2 3 4 4 4 4 5)

(cols t) xcols `month xasc ungroup {select from x where all each completed = `yes} select distinct completed by name,month from t

completed name month
--------------------
yes       y    2    
yes       a    4    
yes       y    5
user_46901800

If you use DolphinDB instead of kdb+, the solution would be more readable.

tbl = table(1 0 1 1 0 1 1 0 0 1 as completed, `x`x`y`y`a`a`b`b`b`y as name, 1 1 2 2 3 4 4 4 4 5 as month)
select top 1 * from tbl context by month, name having min(completed)=1

completed name month
--------- ---- -----
1         y    2
1         a    4
1         y    5

The clause context by in the sql statement is similar to group by. But it doesn't require all columns in select clause be aggregated values. Also, it can work together with having clause to filter groups. The top n clause applies to each group.

user_106858620
Related