Issues with Queries and qSQL

KX Academy

Issues with Queries and qSQL

Posted by mn_12 on May 31, 2023 at 12:00 am
Hi,

It is my first time learning about queries and qSQL. I have been going through the Queries, Practical Guidance – Queries and Queries Exercise notebooks.

I have not been able to figure out a few things even after spending much time thinking about them.

1.

In the Queries notebook:

daily is:
```
date       sym  open  high  low   close price        size   
----------------------------------------------------------- 
2020.01.02 AAPL 83.88 87.45 78.69 86.22 4.452568e+07 536408 
2020.01.02 AIG  26.97 29.85 26.36 29.01 1.515896e+07 532160 
2020.01.02 AMD  33.01 34.92 31.3  33.94 1.744796e+07 530579
..
```
meta daily is:
```
c    | t f a 
-----| ----- 
date | d     
sym  | s     
open | f     
high | f     
low  | f     
close| f     
price| f     
size | j
```
Under the segment ‘Queries with grouping – the by clause’, it is stated that ‘We can also use our own defined functions on these lists, e.g. to return the last 5 days closing prices’.

The following example is provided:

last5:{-5 sublist raze x}

select last5DaysClose:last5 close by sym from daily

results in:
```
sym | last5DaysClose                
----| ----------------------------- 
AAPL| 82.63 84.32 85.67 87.88 90.95 
AIG | 31.14 31.87 31.48 31.66 32.76 
AMD | 40.21 43.05 43.09 45.68 43.35
..
```
meta select last5DaysClose:last5 close by sym from daily is:
```
c             | t f a 
--------------| ----- 
sym           | s     
last5DaysClose| F
```
I have read the documentation for raze. However, I am unable to figure out why it was utilized in last5.

I ran the example above without raze:

last5NoRaze:{-5 sublist x}

select last5DaysCloseNoRaze:last5NoRaze close by sym from daily

results in:
```
sym | last5DaysCloseNoRaze          
----| ----------------------------- 
AAPL| 82.63 84.32 85.67 87.88 90.95 
AIG | 31.14 31.87 31.48 31.66 32.76 
AMD | 40.21 43.05 43.09 45.68 43.35
..
```
meta select last5DaysCloseNoRaze:last5NoRaze close by sym from daily is:
```
c                   | t f a 
--------------------| ----- 
sym                 | s     
last5DaysCloseNoRaze| F
```
Apart from the difference in the name of a column (last5DaysClose vs last5DaysCloseNoRaze), the results appear to be identical.

Still, I am not entirely certain if raze can be excluded here and do not know why it was included in the first place.

2.

Not only am I still quite new to q/kdb+, I am unfamiliar with SQL too. I have difficulty comprehending the first segment of the Practical Guidance – Queries notebook which concerns a comparison of SQL and qSQL:

It is written there:

‘Comparing constraints, aggregations

In SQL the where and group clauses are atomic, and the select and update clauses are atomic or aggregate if grouping. In q the where and by clauses are uniform, and the select and update clauses are uniform or aggregate if grouping (by). All clauses execute on the columns and q can therefore take advantage of order. SQL can’t tell the difference.

SQL repeats the group by expressions in the select clause and the where clause is one boolean expression. The q where clause is a cascading list of constraints which nicely obviates some complex sql correlated sub-queries and also gets rid of some parentheses.’

a.

I am unable to comprehend the first paragraph.

b.

I am uncertain as to what the wording ‘uniform’ and ‘the select and update clauses are uniform or aggregate if grouping (by)’ (both in the first paragraph) mean.

(In addition, I am puzzled as in the Queries notebook:

select size by sym from daily

results in:
```
sym | size                                                                   .. 
----| -----------------------------------------------------------------------.. 
AAPL| 536408 554761 535121 518017 534561 515401 509741 538500 528053 527158 5.. 
AIG | 532160 559732 544834 513638 517012 502970 502364 544465 523044 537695 5.. 
AMD | 530579 552365 542053 504638 527263 515827 493567 535153 525213 528873 5..
..
```
It appears to me that there is no notion of ‘aggregate’ even though there is ‘grouping (by)’.)

c.

I am also uncertain as to what the wording ‘correlated sub-queries’ (in the last sentence of the second paragraph) signifies.

I would really appreciate any assistance or clarification with these issues.

Thank you very much.
mn_12 replied 8 months, 3 weeks ago 2 Members · 1 Reply
1 Reply

rocuinneagain

Member
May 31, 2023 at 12:00 am
1. There is no need for raze to be included.
```
q)select type close by sym from daily
```
```
sym | close 
----| ------ 
AAPL| 9h 
AIG | 9h 
AMD | 9h ..
```
Float vectors are being passed to your function (type 9h). raze has no effect on these.

2. select size by sym from daily only does grouping as no aggregate function is included.

It is the same as select {x} size by sym from daily

Including a real aggregate would be select last5 size by sym from daily

KX Community

Issues with Queries and qSQL

rocuinneagain