Hi,
I have a table data , and sorted by Id1`value. Now I want to select Top-k records (e.g., k=2) in each group. How could I select? thanks!
q)data
id1 id2 value
1 2 0.5
1 3 0.6
1 4 0.1
2 1 0.3
2 3 0.32
2 5 0.42
q)id1value xdesc data
id1 id2 value
2 5 0.42
2 3 0.32
2 1 0.3
1 3 0.6
1 2 0.5
1 4 0.1
select [k] from …
…by id1, …
:) I didn’t read the question properly the first time
Group of what?
On Monday, March 24, 2014 10:52:20 AM UTC-4, afancy wrote:
Hi,
I have a table
data , and sorted byId1`value. Now I want to select Top-k records (e.g., k=2) in each group. How could I select? thanks!q)data
id1 id2 value
1 2 0.5
1 3 0.6
1 4 0.1
2 1 0.3
2 3 0.32
2 5 0.42
q)
id1value xdesc dataid1 id2 value
2 5 0.42
2 3 0.32
2 1 0.3
1 3 0.6
1 2 0.5
1 4 0.1
The group is based on the values of id1. In this case, there are two groups, group one with id1=1, and group two with id1=2. I want to return top-k records in each group. In this example, if k=2, the result that I want is:
id1 id2 value
2 5 0.42
2 3 0.32
1 3 0.6
1 2 0.5
data raze 0 1+/:where differ data`id1 ??
Ye
won’t be as fast as Ye’s but simply set yourself up with functional select:
f:{?[z;();(x,())!x,();(y,())!{(#;2;x)}each y]}
ungroup f[`id1;`id2`value;`id1`value xdesc data]
HTH, Sean.
data:flip id1id2`value!(1 1 1 2 2 2;2 3 4 1 3 5;0.5 0.6 0.1 0.3 0.32 0.42)<o:p></o:p>
ungroup id1 xdesc ?[id1value xdesc data;();b!b:enlist id1;] a!(2#),/: a: cols[data] except `id1<o:p></o:p>
<o:p> </o:p>
You should avoid using value as a column name.<o:p></o:p>
<o:p> </o:p>
HTH,<o:p></o:p>
<o:p> </o:p>
Kim<o:p></o:p>
<o:p> </o:p>
Von: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] Im Auftrag von afancy
Gesendet: Montag, 24. März 2014 17:09
An: personal-kdbplus@googlegroups.com
Betreff: [personal kdb+] Re: How to select Top-k records in each group?<o:p></o:p>
<o:p> </o:p>
On Monday, March 24, 2014 10:52:20 AM UTC-4, afancy wrote:<o:p></o:p>
Hi, <o:p></o:p>
<o:p> </o:p>
I have a table data , and sorted by Id1`value. Now I want to select Top-k records (e.g., k=2) in each group. How could I select? thanks!<o:p></o:p>
<o:p> </o:p>
q)data<o:p></o:p>
id1 id2 value<o:p></o:p>
--------------------------<o:p></o:p>
1 2 0.5 <o:p></o:p>
1 3 0.6 <o:p></o:p>
1 4 0.1 <o:p></o:p>
2 1 0.3 <o:p></o:p>
2 3 0.32 <o:p></o:p>
2 5 0.42 <o:p></o:p>
<o:p> </o:p>
q)id1value xdesc data<o:p></o:p>
id1 id2 value<o:p></o:p>
--------------------------<o:p></o:p>
2 5 0.42 <o:p></o:p>
2 3 0.32 <o:p></o:p>
2 1 0.3 <o:p></o:p>
1 3 0.6 <o:p></o:p>
1 2 0.5 <o:p></o:p>
1 4 0.1 <o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
The group is based on the values of id1. In this case, there are two groups, group one with id1=1, and group two with id1=2. I want to return top-k records in each group. In this example, if k=2, the result that I want is:<o:p></o:p>
id1 id2 value<o:p></o:p>
--------------------------<o:p></o:p>
2 5 0.42 <o:p></o:p>
2 3 0.32 <o:p></o:p>
1 3 0.6 <o:p></o:p>
1 2 0.5 <o:p></o:p>
–
Submitted via Google Groups
I’m using Kim’s example:
q)f:{select from data where fby[({iasc idesc x};val);id1] in til x}
q)f 2
id1 id2 val
1 2 0.5
1 3 0.6
2 3 0.32
2 5 0.42
q)f 1
id1 id2 val
1 3 0.6
2 5 0.42