kdb q - count subtable between 2 dates

tenticon

I have a table

t:`date xasc ([]date:100?2018.01.01+til 100;price:100?til 100;acc:100?`a`b)

and would like to have a new column in t which contains the counts of entries in t where date is in the daterange of the previous 14 days and the account is the same as in acc. For example, if there is a row

date       price    acc prevdate    prevdate1W   countprev14
2018.01.10  37       a  2018.01.09  2018.01.03   ?

then countprev14 should contain the number of observations between 2018.01.03 and 2018.01.09 where acc=a

The way I am currently doing it can probably be improved:

f:{[dates;ac;t]count select from t where date>=(dates 0),date<=(dates 1),acc=ac}[;;t]
(f')[(exec date-7 from t),'(exec date-1 from t);exec acc from t]

Thanks for the help

jomahony

Another method is using a window join (wj1): https://code.kx.com/q/ref/joins/#wj-wj1-window-join

dates:exec date from t; 
d:(dates-7;dates-1);
wj1[d;`acc`date;t;(`acc`date xasc t;(count;`i))]

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Total number of months (Count) between 2 dates

From Dev

Count number of Saturdays between two dates - MySql

From Dev

Difference between rows in KDB/Q

From Dev

Count the number of users between certain dates

From Dev

Laravel $q->where() between dates

From Dev

Count number of days between 2 dates in JPA

From Dev

How to count no. of days between 2 dates from calendar in string format vb

From Dev

Count difference between two dates - datetimepicker

From Dev

SQL Count between dates

From Dev

How to count the number of sundays between two dates

From Dev

How to count the elapsed nights between two dates?

From Dev

Pandas: use groupby to count difference between dates

From Dev

The matched days count between two dates ranges

From Dev

How to count fractional days between 2 dates

From Dev

Count hour and minutes between multiple dates

From Dev

How to calculate count of records between several dates

From Dev

Mysql count and group between specific dates

From Dev

Join tables and count between two dates

From Dev

Count difference between two dates - datetimepicker

From Dev

SQL count where between dates by month

From Dev

Wrong count of difference days between 2 dates with joda time?

From Dev

Count occurence of values between 2 dynamic dates in MySQL

From Dev

How to count the number of sundays between two dates

From Dev

Pandas: use groupby to count difference between dates

From Dev

Count the number of elements between 2 dates conditionally on a variable in R

From Dev

Count number of including weeks between 2 dates

From Dev

How to count and sum a field between 2 dates?

From Dev

MySQL Count time between 2 dates with multiple rows of data

From Dev

Count the days between two dates

Related Related

  1. 1

    Total number of months (Count) between 2 dates

  2. 2

    Count number of Saturdays between two dates - MySql

  3. 3

    Difference between rows in KDB/Q

  4. 4

    Count the number of users between certain dates

  5. 5

    Laravel $q->where() between dates

  6. 6

    Count number of days between 2 dates in JPA

  7. 7

    How to count no. of days between 2 dates from calendar in string format vb

  8. 8

    Count difference between two dates - datetimepicker

  9. 9

    SQL Count between dates

  10. 10

    How to count the number of sundays between two dates

  11. 11

    How to count the elapsed nights between two dates?

  12. 12

    Pandas: use groupby to count difference between dates

  13. 13

    The matched days count between two dates ranges

  14. 14

    How to count fractional days between 2 dates

  15. 15

    Count hour and minutes between multiple dates

  16. 16

    How to calculate count of records between several dates

  17. 17

    Mysql count and group between specific dates

  18. 18

    Join tables and count between two dates

  19. 19

    Count difference between two dates - datetimepicker

  20. 20

    SQL count where between dates by month

  21. 21

    Wrong count of difference days between 2 dates with joda time?

  22. 22

    Count occurence of values between 2 dynamic dates in MySQL

  23. 23

    How to count the number of sundays between two dates

  24. 24

    Pandas: use groupby to count difference between dates

  25. 25

    Count the number of elements between 2 dates conditionally on a variable in R

  26. 26

    Count number of including weeks between 2 dates

  27. 27

    How to count and sum a field between 2 dates?

  28. 28

    MySQL Count time between 2 dates with multiple rows of data

  29. 29

    Count the days between two dates

HotTag

Archive