Hi All,
I have a table
minutecnt
14:003
14:015
14:023
14:037
14:048
14:053
14:066
14:076
14:081
14:092
14:103
and I’d like to add a running count by certain intervals.
ie.
minutecnt 5min
14:003 3
14:015 8
14:023 11
14:037 18
14:048 26
14:053 3 <– reset
14:066 9
14:076 15
14:081 16
14:092 18
14:103 21
Any ideas?
Thanks
something like this
q)t
time cnt
09:00 4
09:01 9
09:02 2
09:03 7
09:04 0
09:05 1
09:06 9
09:07 2
09:08 1
09:09 8
q)update cnt:sums cnt by 5 xbar time.minute from t
time cnt
09:00 4
09:01 13
09:02 15
09:03 22
09:04 22
09:05 1
09:06 10
09:07 12
09:08 13
09:09 21
q)
Thanks
Showvik
Perfect, Thanks<o:p></o:p>
<o:p> </o:p>
From: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] On Behalf Of showvik
Sent: November 3, 2015 2:45 PM
To: personal-kdbplus@googlegroups.com
Subject: Re: [personal kdb+] Resetting Sums<o:p></o:p>
<o:p> </o:p>
something like this<o:p></o:p>
q)t<o:p></o:p>
time cnt<o:p></o:p>
---------<o:p></o:p>
09:00 4<o:p></o:p>
09:01 9<o:p></o:p>
09:02 2<o:p></o:p>
09:03 7<o:p></o:p>
09:04 0<o:p></o:p>
09:05 1<o:p></o:p>
09:06 9<o:p></o:p>
09:07 2<o:p></o:p>
09:08 1<o:p></o:p>
09:09 8<o:p></o:p>
q)update cnt:sums cnt by 5 xbar time.minute from t<o:p></o:p>
time cnt<o:p></o:p>
---------<o:p></o:p>
09:00 4<o:p></o:p>
09:01 13<o:p></o:p>
09:02 15<o:p></o:p>
09:03 22<o:p></o:p>
09:04 22<o:p></o:p>
09:05 1<o:p></o:p>
09:06 10<o:p></o:p>
09:07 12<o:p></o:p>
09:08 13<o:p></o:p>
09:09 21<o:p></o:p>
q)<o:p></o:p>
<o:p> </o:p>
Thanks<o:p></o:p>
Showvik<o:p></o:p>
<o:p> </o:p>
On Wed, Nov 4, 2015 at 12:42 AM, Roni Hoffman <hoffmanroni@gmail.com> wrote:<o:p></o:p>
Hi All,<o:p></o:p>
<o:p> </o:p>
I have a table<o:p></o:p>
<o:p> </o:p>
minute cnt <o:p></o:p>
14:00 3 <o:p></o:p>
14:01 5 <o:p></o:p>
14:02 3 <o:p></o:p>
14:03 7 <o:p></o:p>
14:04 8 <o:p></o:p>
14:05 3 <o:p></o:p>
14:06 6 <o:p></o:p>
14:07 6 <o:p></o:p>
14:08 1 <o:p></o:p>
14:09 2 <o:p></o:p>
14:10 3 <o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
and I’d like to add a running count by certain intervals. <o:p></o:p>
<o:p> </o:p>
ie.<o:p></o:p>
<o:p> </o:p>
minute cnt 5min<o:p></o:p>
14:00 3 3<o:p></o:p>
14:01 5 8<o:p></o:p>
14:02 3 11<o:p></o:p>
14:03 7 18<o:p></o:p>
14:04 8 26<o:p></o:p>
14:05 3 3 <– reset<o:p></o:p>
14:06 6 9<o:p></o:p>
14:07 6 15<o:p></o:p>
14:08 1 16<o:p></o:p>
14:09 2 18<o:p></o:p>
14:10 3 21<o:p></o:p>
<o:p> </o:p>
Any ideas?<o:p></o:p>
<o:p> </o:p>
Thanks<o:p></o:p>
–
Submitted via Google Groups
What if I wanted to to a running 5min interval
minutecnt 5min
14:003 3
14:015 8
14:023 11
14:037 18
14:048 26
14:053 26 <– (3+8+7+3+5)
14:066 27 <– (6+3+8+7+3)
14:076 30 <– (6+6+3+8+7)
14:081 24
14:092 18
14:103 18
Would that be much harder?
Hi Roni,
Maybe a window join would be useful.
Regards,
Paul
q)t:( minute:14:00 + til 11;cnt:3 5 3 7 8 3 6 6 1 2 3)
q)wj1[-4 0 +: t`minute;`minute;t;(`minute`5min xcol t;(sum;`5min))]
minute cnt 5min
---------------
14:00 3 3
14:01 5 8
14:02 3 11
14:03 7 18
14:04 8 26
14:05 3 26
14:06 6 27
14:07 6 30
14:08 1 24
14:09 2 18
14:10 3 18
Apologies. An aj would be more efficient.
q)select minute, cnt, min5:sums[cnt]-sums cnt5 from aj[`minute;t;`minute`cnt5 xcol update minute+5 from t]
minute cnt min5
---------------
14:00 3 3
14:01 5 8
14:02 3 11
14:03 7 18
14:04 8 26
14:05 3 26
14:06 6 27
14:07 6 30
14:08 1 24
14:09 2 18
14:10 3 18
Very cool , thanks
Roni Hoffman
1-519-859-1082
Will your time column always be in Steps of 1 second? If so you could just have:
update min5:5 msum cnt from t
So just does a moving sum of 5 places.
Marcus
Sent from Mailbox
On Wed, Nov 4, 2015 at 6:33 AM, Paul Loughran <pauljloughran@gmail.com> wrote:
Apologies. An aj would be more efficient.
q)select minute, cnt, min5:sums[cnt]-sums cnt5 from aj[`minute;t;`minute`cnt5 xcol update minute+5 from t]
minute cnt min5
---------------
14:00 3 3
14:01 5 8
14:02 3 11
14:03 7 18
14:04 8 26
14:05 3 26
14:06 6 27
14:07 6 30
14:08 1 24
14:09 2 18
14:10 3 18
Hi Paul/Roni,
Your wj1 was actually a better solution. If for example you take the initial table t and remove the line (14:07). The min5 column produced by the aj is:
Which is not correct, however your wj1 gives:
3 8 11 18 26 26 27 18 12 12 which is correct.
So I think if we know that we have an entry for every minute, my update method is slightly better but if you want it to manually look at the specific times that we have then your wj1 is the option which guarantees the correct output.
Thanks,
Marcus
Sent from Mailbox
On Wed, Nov 4, 2015 at 6:33 AM, Paul Loughran <pauljloughran@gmail.com> wrote:
Apologies. An aj would be more efficient.
q)select minute, cnt, min5:sums[cnt]-sums cnt5 from aj[`minute;t;`minute`cnt5 xcol update minute+5 from t]
minute cnt min5
---------------
14:00 3 3
14:01 5 8
14:02 3 11
14:03 7 18
14:04 8 26
14:05 3 26
14:06 6 27
14:07 6 30
14:08 1 24
14:09 2 18
14:10 3 18
Yeah, that’s true. Can still be done with an aj though:
q)tab
time cnt
------------------------
0D00:00:45.241009304 2
0D00:02:38.599338261 5
0D00:03:06.858984315 7
0D00:03:09.926170371 7
0D00:06:32.990612424 7
0D00:07:04.471262376 6
0D00:07:24.866455765 2
0D00:07:25.430022245 9
0D00:08:52.375585427 2
0D00:09:06.221554186 4
q)select time, min5: cnt - 0^cnt5 from aj[`time;update sums cnt from tab;select time+0D00:05, cnt5:sums cnt from tab]
time min5
-------------------------
0D00:00:45.241009304 2
0D00:02:38.599338261 7
0D00:03:06.858984315 14
0D00:03:09.926170371 21
0D00:06:32.990612424 26
0D00:07:04.471262376 32
0D00:07:24.866455765 34
0D00:07:25.430022245 43
0D00:08:52.375585427 26
0D00:09:06.221554186 30
perfect thanks that works