英文:

Weekdays / weekend filter on a table

问题

以下是代码的翻译部分：

//排除周末：

firstdate: 2023.01.01

lastdate: 2023.01.10

daterange: firstdate + til (lastdate - firstdate) + 1

daterange where 1 < daterange mod 7

// 或：daterange where not (daterange mod 7) in 0 1

(taken from this: https://stackoverflow.com/questions/16115648/how-to-generate-a-date-range-in-kdb-excluding-weekend-days)

所以以下代码可以正常运行：

t1hr: select avg tv by 1 xbar ts.hh from trade where date within (firstdate; lastdate), sym=`BTCUSDT

但每当我添加daterange变量时，就会出现错误：

t1hr_weekdays: select avg tv by 1 xbar ts.hh from trade where date within daterange where 1 < daterange mod 7, sym=`BTCUSDT

错误信息如下：

length
[6] (.Q.ps)

[5] t1hr_weekdays: select avg tv by 1 xbar ts.hh from trade where date within daterange where 1 < daterange mod 7, sym=BTCUSDT
^
[4] {( (1b;`) ;value x)}

英文:

Let's say I have a 'trade' table that lists all trades taken on a given exchange with these columns:

• date
• trading pair (e.g. BTCUSDT)
• ts, rt (timestamp and receiving time)
• price
• trade size
• buy/sell

Now I want to be able to to look at weekends and weekdays separately.

//Exclude weekends:

firstdate:2023.01.01

lastdate:2023.01.10

daterange:firstdate + til (lastdate - firstdate) + 1

daterange where 1<daterange mod 7

// or: daterange where not (daterange mod 7) in 0 1

(taken from this:
https://stackoverflow.com/questions/16115648/how-to-generate-a-date-range-in-kdb-excluding-weekend-days)

So the following code runs fine:

t1hr: select avg tv by 1 xbar ts.hh from trade where date within (firstdate;lastdate), sym=`BTCUSDT

whenever I add the daterange variable, I get errors:

t1hr_weekdays: select avg tv by 1 xbar ts.hh from trade where date within daterange where 1<daterange mod 7, sym=`BTCUSDT

Error reads:

length
[6] (.Q.ps)

[5] t1hr_weekdays: select avg tv by 1 xbar ts.hh from trade where date within daterange where 1<daterange mod 7, sym=BTCUSDT
^
[4] {( (1b;`) ;value x)}

答案1

得分: 2

以下是代码的翻译：

By the looks of it you have a second where statement that shouldn't be in there.
从外观上看，你有一个不应该存在的第二个where语句。

On filtering by weekday, the best way to do it is something like:
在按工作日过滤时，最好的方法是类似以下的方式：

q)5#select from trade where not (date mod 7)in 0 1
date       pair price size side
-------------------------------
2007.01.19 ABC  86    0    S   
2009.09.17 DEF  25    5    S   
2016.12.27 ABC  6     11   B   
2021.03.16 GHI  81    28   S   
2001.01.22 DEF  19    9    S   
q)/- Week ends
q)5#select from trade where (date mod 7)in 0 1
date       pair price size side
-------------------------------
2003.12.07 GHI  41    71   B   
2019.07.14 GHI  37    15   S   
2006.04.01 ABC  57    94   S   
2005.09.11 DEF  99    95   S   
2003.02.23 DEF  26    69   B

If its something that you are querying often its probably best to create a "isweekday" column like:
如果你经常查询这个内容，最好创建一个名为 "isweekday" 的列，类似以下方式：

```q)update isweekday:(date mod 7)>1 from trade trade
q)5#select from trade where isweekday
date pair price size side isweekday

2007.01.19 ABC 86 0 S 1
2009.09.17 DEF 25 5 S 1
2016.12.27 ABC 6 11 B 1
2021.03.16 GHI 81 28 S 1
2001.01.22 DEF 19 9 S 1

q)5#select from trade where date within (2020.01.01;2023.01.01),isweekday
date pair price size side isweekday

2021.03.16 GHI 81 28 S 1
2021.05.18 DEF 66 65 B 1
2021.07.09 ABC 9 83 S 1
2021.02.11 ABC 23 41 S 1
2022.01.07 DEF 87 45 B 1

希望这些翻译对你有所帮助。如果你需要进一步的帮助，请随时提问。

<details>
<summary>英文:</summary>

By the looks of it you have a second where statement that shouldn&#39;t be in there.

On filtering by weekday, the best way to do it is something like:

    q)/- Week days
    q)5#select from trade where not (date mod 7)in 0 1
    date       pair price size side
    -------------------------------
    2007.01.19 ABC  86    0    S   
    2009.09.17 DEF  25    5    S   
    2016.12.27 ABC  6     11   B   
    2021.03.16 GHI  81    28   S   
    2001.01.22 DEF  19    9    S   
    q)/- Week ends
    q)5#select from trade where (date mod 7)in 0 1
    date       pair price size side
    -------------------------------
    2003.12.07 GHI  41    71   B   
    2019.07.14 GHI  37    15   S   
    2006.04.01 ABC  57    94   S   
    2005.09.11 DEF  99    95   S   
    2003.02.23 DEF  26    69   B 

If its something that you are querying often its probably best to create a &quot;isweekday&quot; column like:

    q)update isweekday:(date mod 7)&gt;1 from `trade
    `trade
    q)5#select from trade where isweekday
    date       pair price size side isweekday
    -----------------------------------------
    2007.01.19 ABC  86    0    S    1        
    2009.09.17 DEF  25    5    S    1        
    2016.12.27 ABC  6     11   B    1        
    2021.03.16 GHI  81    28   S    1        
    2001.01.22 DEF  19    9    S    1 

    q)5#select from trade where date within (2020.01.01;2023.01.01),isweekday
    date       pair price size side isweekday
    -----------------------------------------
    2021.03.16 GHI  81    28   S    1        
    2021.05.18 DEF  66    65   B    1        
    2021.07.09 ABC  9     83   S    1        
    2021.02.11 ABC  23    41   S    1        
    2022.01.07 DEF  87    45   B    1



</details>