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.