\documentclass[12pt]{article}
\usepackage{psfig}
\usepackage{amsfonts, amssymb, amsmath, amscd, framed}
\usepackage{listings}

\usepackage[ps2pdf,
		bookmarks=true,
		colorlinks=true,
		linkcolor=linkblue]{hyperref}

\usepackage{color}
\usepackage{textcomp}
\definecolor{listinggray}{gray}{0.9}
\definecolor{lbcolor}{rgb}{0.9,0.9,0.9}
\definecolor{linkblue}{rgb}{0,0,0.5}

\newcommand{\backtick}{{\texttt{\char"12}}}

\newlength{\toppush}
\setlength{\toppush}{2\headheight}
\addtolength{\toppush}{\headsep}
\newcommand{\htitle}[3]{\noindent\vspace*{-\toppush}\newline\parbox{6.5in}
{Q for smarties
\hfill #1 \newline
Pune\hfill#3\newline
\mbox{}\hrulefill\mbox{}}\vspace*{1ex}\mbox{}\newline
\begin{center}{\Large\bf #2}\end{center}}

\newcommand{\handout}[3]{\thispagestyle{empty}
 \markboth{ #2}{ #2}
 \pagestyle{myheadings}\htitle{\protect\ref{#1}}{#2}{#3}}


\setlength{\oddsidemargin}{0pt}
\setlength{\evensidemargin}{0pt}
\setlength{\textwidth}{6.5in}
\setlength{\topmargin}{0in}
\setlength{\textheight}{8.5in}

\lstset{ %http://www.tjansson.dk/?p=419
	backgroundcolor=\color{lbcolor},
	tabsize=4,
	rulecolor=,
	language=q,
        basicstyle=\small,
        upquote=false;
        aboveskip={1.5\baselineskip},
        columns=fixed,
        showstringspaces=false,
        extendedchars=true,
        breaklines=true,
        prebreak = \raisebox{0ex}[0ex][0ex]{\ensuremath{\hookleftarrow}},
        frame=single,
        showtabs=false,
        showspaces=false,
        showstringspaces=false,
        identifierstyle=\ttfamily,
        keywordstyle=\color[rgb]{0,0,1},
        commentstyle=\color[rgb]{0.133,0.545,0.133},
        stringstyle=\color[rgb]{0.627,0.126,0.941},
}
\begin{document}
\htitle{Professor Dennis Shasha}{q for smarties 3}{2011} 
\url{https://code.kx.com/trac/wiki/QforMortals/tables}\\
\url{https://code.kx.com/trac/browser/kx/kdb+/sp.q}\\


Sweet spots:
\begin{itemize}
\item
Many columns where ordering (e.g. by time)
is needed and operations on ordered data are desirable.
\item
Historical data where need only a few columns per query but table
has many columns.
\item
Transaction processing (over 10,000 per second read-write transactions)
on data that can fit into memory.
\end{itemize}

Sour spot:
Transaction processing on data that cannot fit into memory (take some work).

\paragraph{0. Run setup} setup.q

\begin{lstlisting}
stock: `ibm`bac`usb`bac`bac`usb;
stock,: `ibm`bac`usb`ibm`bac`usb;
stock,: `ibm`ibm`usb;
price: 1.1 2.4 3.3 3.0 2.8 1.8;
price,: 3.1 4.4 1.3 2.0 3.8 5.8;
price,: 5.1 4.3 3.3;
amount: 100 200 300 400 500 300;
amount,: 300 200 400 600 700 800;
amount,: 700 800 700;
time: 09:03:06.000 09:03:23.000 09:04:01.000 09:05:01.000 09:06:01.000 09:07:01.000;
time,: 10:03:06.000 10:03:23.000 10:04:01.000 10:05:01.000 10:06:01.000 10:07:01.000;
time,: 11:03:06.000 11:03:23.000 11:04:01.000;
trade:([]stock; price; amt:amount; time);
save `:trade.csv; / to write a csv file
\end{lstlisting}
Things to note:
\begin{enumerate}
\item
Insert into a table by creating lists and then assigning.
\item
The initial brackets are for key fields. There are none here.
\item
Saving to a csv file is possible for small files (up to a few hundred
megabytes).
\end{enumerate}
\vspace{20mm}
Find all the tables by
\begin{lstlisting}
tables `
\end{lstlisting}


Task: Import a table of stock prices in csv format (`trade.csv
as written above)
and find the average price per stock and write that to a csv file
\begin{lstlisting}
	q readavg.q
\end{lstlisting}
Reading in uses a type field (S for strings, F for floats,
and T for times) with a delimiter of comma.
\begin{lstlisting}
mytrade: ("SFFT"; enlist ",") 0: `trade.csv
\end{lstlisting}
If you know SQL, then kdb will be easier to learn.
It is a semantic extension (arrable model, as I will explain)
but has somewhat different syntax.
Let's look at the syntactic differences first.
\begin{lstlisting}
myavg: select avg price by stock from mytrade
\end{lstlisting}
update columns

In standard SQL the "group by" goes after the "from" and "where" clauses.
Here the "by" clause goes before "from".
\begin{lstlisting}
save `:myavg.csv
\end{lstlisting}


\begin{framed}
Exercise 1. (Middle) The volume weighted average price per trade
is the average of the price times the volume.
Compute the volume weighted average price 
per stock
\begin{lstlisting}
        q vwap.q
\end{lstlisting}
\end{framed}
Can create new columns by update, e.g.
\begin{lstlisting}
mytrade: update weightedprice: price*amt from mytrade
\end{lstlisting}
\vspace{60mm}
The very powerful
semantic  extension that kdb offers is that it is based on 
ordered columns
and therefore it can support queries that make use of that order.
You can therefore create functions that work on arrays
and run them.
Or you can use built-in ones when available.

\begin{lstlisting}
/ given a list of prices, get the 2 way moving average

/ moving average version -- roll your own
movavg2:{[x] (x[0]),avg each ((-1) _ x),'(1 _ x)}
mytrade: ("SFFT"; enlist ",") 0: `trade.csv
myavg2: select movavg2 price by stock from mytrade

/ collective average version
mytrade: ("SFFT"; enlist ",") 0: `trade.csv
myavg2: select avgs price by stock from mytrade
save `:myavg2
\end{lstlisting}
Note the different method of saving.
It doesn't work
to save it to a csv file because the output is not in first normal form.

Also note that this may not be meaningful unless you know that mytrade
is ordered by time.
This is easily corrected in both the roll-your-own version
\begin{lstlisting}
myavg2: select movavg2 price by stock from (`time xasc mytrade)
\end{lstlisting}
and using the built-in moving average
\begin{lstlisting}
myavg2: select mavg[2;price] by stock from (`time xasc mytrade)
\end{lstlisting}
\begin{framed}
Exercise 2. Compute the 4 moving average per stock using
mavg and optionally
in a roll-your-own way.
\end{framed}
Hints for the roll-your-own crowd: 
Take care of the first three moving averages using the avgs
function and then go on from there using the difference of sums.
Display the result and save it as a q file. 
If possible, write a generic moving average function.
\begin{lstlisting}
	q readmovavglong.q
\end{lstlisting}
Note: You can load the table in another q file by saying
\begin{lstlisting}
load `:myavg2
\end{lstlisting}
\vspace{40mm}
Let's say we want to generate a random table having 100,000 rows from
the stocks `ibm `hp `amaz `goog `aapl,
prices in the range 20 to 400,
and volumes in the range 100 to 100000 but multiples of 100,
times arbitrary in the range from 10 AM to 4 PM.
\begin{lstlisting}
	q gendata.q

n: 100000;
stocks: `ibm`hp`amaz`goog`aapl;
rantrade:([]stock: n?stocks; price: 20 + n?380.0;amount: 100*(1+n?1000);
  time: 10:00:00.000 + n?06:00:00.000);
\end{lstlisting}
Points to note:
\begin{enumerate}
\item
n ? stocks
\item
20 + n ? 380.0
\item
n ? 06:00:00.000
\end{enumerate}
\begin{lstlisting}
save `:rantrade.csv
\end{lstlisting}

Use the table generated and 
select the name and last price of
each stock into a new table pricecol.
\begin{lstlisting}
endofdayprices: select last price by stock from (`time xasc rantrade)
\end{lstlisting}

Notes:
\begin{enumerate}
\item
You do not have to put "stock" in the select clause since
already in the by. In fact, you shouldn't do it.
\item
Because the columns aren't ordered in
the original rantrade, this does not give us the
last stock by time.
That is why we use xasc.
\end{enumerate}
\begin{framed}
Exercise 3. (Easy) Find the average value of each trade where time $>$ 12:00:00
\begin{lstlisting}
	q volweight.q
\end{lstlisting}
\end{framed}
\vspace{45mm}
A few notes on inserting data.

\begin{lstlisting}
/ get the prices column as a list.
/ price is the second column, but col numbers start at 0
(value flip rantrade)[1]

`foo insert rantrade; / note that insert creates a table if it doesn't exist

count foo

`foo insert rantrade / or if it already exists

count foo

\end{lstlisting}

Another way to insert data.
Can also insert into a table using columns:
\begin{lstlisting}
newstocks: n?stocks;
newprice: 20 + n?380.0;
newamount: amount: 100*(1+n?1000);
newtime: 10:00:00.000 + n?06:00:00.000;

`rantrade  insert (newstocks; newprice; newamount; newtime);
\end{lstlisting}
\begin{framed}
Exercise 4. (Easy) Generate a second table having the same schema as in 
the random table
and bulk insert it into the random table.
Then form columns corresponding to this schema, each of the same
length as the columns of the original random table. Bulk insert those columns.
Which is faster? Table insert or column insert? (Use \t before
the statement).
\begin{lstlisting}
	q bulkinsert.q
\end{lstlisting}
\end{framed}

Tables can be sorted based on different fields.
e.g. let's sort the table rantrade based on price.
\begin{lstlisting}
ranprice: `price xasc rantrade

x: exec price by stock from rantrade
x
\end{lstlisting}
This gives a dictionary that has stock-price pairs.
Because stock names are keys, you can ask for their values.
\begin{lstlisting}
x[`aapl]
\end{lstlisting}
\begin{framed}
Exercise 5. (Optional, Hard) Compute the correlation of the prices of every
pair of stocks in time order of their trades. 
Use the trade.csv table.
Each stock has the same number of values in the trade table,
but they are not necessarily in time order.
You will have to bring stock-price pairs out and then write a function
to compute over them.
\begin{lstlisting}
	q findcorr.q
\end{lstlisting}
\end{framed}
\begin{framed}
Excercise 5h. (Optional, Slightly Harder variant) 
Generate a random stock-trade 
table and then compute
correlations between every pair of stocks.
If they don't have the same length then truncate to the smaller size.
Ensure the two vectors are ordered in time.
\begin{lstlisting}
	q findcorrharder.q
\end{lstlisting}
\end{framed}

Sometimes, one may want to declare a key for a table.
Suppose that each of these companies is associated with a unique state
which constains its headquarters.
\begin{lstlisting}
stocks: `ibm`hp`amaz`goog`aapl;
states: `ny`ca`wa`ca`ca;
\end{lstlisting}
Then declare a key for the table as follows.
\begin{lstlisting}
stock:([mystock: stocks] place: states);
\end{lstlisting}
Note that the first field is in brackets. That is a signal
to show that no two rows should have the same mystock value.
Having these keys enables us to do table joins in an easier way.
(You can undo the keyness of the table by typing 0$!$`stock.)
First, though, note the new way that the stock field
in rantrade is specified.
The construct `stock means that all elements here
should be subsets of the key of the stock table (the column
entitled mystock).
\begin{lstlisting}
n: 100000;
rantrade:([]stock: `stock$n?stocks; price: 20 + n?380.0;
  amount: 100*(1+n?1000); time: 10:00:00.000 + n?06:00:00.000);
	/ first field must be called stock and must reference the key
	/ field of the stock table. That field can be called something else.

select stock, stock.place from rantrade 
\end{lstlisting}

Note that the field referencing the table stock must be called stock.
This permits an implicit join from rantrade to stock through the field
stock in rantrade.
The field stock in rantrade refers to the foreign key mystock in stock.
We know this because in the rantrade schema we describe the field stock as
\begin{lstlisting}
`stock$n?stocks.
\end{lstlisting}
This says that the stock field of rantrade is a subset of mystock in stock
and allows a row in rantrade to be linked to a single row in table stock.
(Many rows in rantrade may be linked to the same row in table stock.)
\begin{framed}
Exercise 6. (Easy) Add an address table that links the 
stocks `ibm`hp`amaz`goog`aapl with their state addresses
and then find the stock-address 
pairs of all stocks whose average price is above 80.
\begin{lstlisting}
 	q findgoodaddress.q
\end{lstlisting}
\end{framed}
\begin{framed}
Exercise 7. (Harder) Change the name of stock to johnstock
in rantrade and do so consistently.
Also create a second level of hierarchy so that
ca is warm, ny is cold, and wa is tepid.
Then use that.
\begin{lstlisting}
	q findgoodaddressvar.q
\end{lstlisting}
\end{framed}
A little interlude on debugging:

Sometimes the errors messages can be challenging to figure out.
For example, if you declare rantrade as we did originally:
\begin{lstlisting}
stocks: `ibm`hp`amaz`goog`aapl;
states: `ny`ca`wa`ca`ca;

stock:([mystock: stocks] place: states);


/ declare rantrade as before
rantrade:([]stock:n?stocks;price:20+n?380.0;amount:100*(1+n?1000);
  time:10:00:00.000+n?06:00:00.000);

/ Then try the statement 
select stock, stock.place from rantrade 
\end{lstlisting}
This will encounter an error: 
\begin{lstlisting}
k){0N!x y}
'type
@
"q"
"select stock, stock.place from rantrade"
\end{lstlisting}
To debug these, type y and you will
tend to see the problem statement.
In this case the definition of rantrade fails to create
an association between rantrade and the stock table.

Interlude: If you want more exercise, go to\\
\url{https://code.kx.com/trac/wiki/QforMortals2/queries_q_sql#Parameterized-Queries}
and start at section entitled Examples (section 14).

Interlude: eval and run 
Sometimes it is useful to assemble an SQL string and then just
execute it.
One can do that in kdb.
For example, put these statements in a file that
has rantrade defined and execute:
\begin{lstlisting}
value "select stock, price from rantrade"
\end{lstlisting}

One can assemble such queries more formally by forming such strings
on the fly.

\begin{framed}
Exercise 7. (Medium) Write a function that take an arbitrary table,
an arbitrary target column expression, and an arbitrary
where clause and can apply to any table having those columns
and for which the where clause is appropriate.
Test it on trade.csv.
\begin{lstlisting}
	q arbquery.q
\end{lstlisting}
\end{framed}


Spreading Load:

When queries are expensive, it's useful to spread them around
on different servers.
The idea here is that we'll have a generic server that will receive
requests from application-specific clients 
and send them to application-specific slaves.

Please look at 
\url{https://code.kx.com/trac/wiki/Cookbook/LoadBalancing}

Here might be a client:
\begin{lstlisting}
h: hopen `:localhost:5001

neg[h]"select avg price*amount by stock from rantrade"; h[]
neg[h]"select max price*amount by stock from rantrade"; h[]
neg[h]"select min price*amount by stock from rantrade"; h[]
neg[h]"select var price*amount by stock from rantrade"; h[]


Here might be a slave server:

n: 100000;
stocks: `ibm`hp`amaz`goog`aapl;
rantrade:([]stock:n?stocks;price:20+n?380.0;amount:100*(1+n?1000);
  time:10:00:00.000+n?06:00:00.000);
\end{lstlisting}
The generic router is mserve.q, copied from the website
and that we will treat as a black box.

\begin{framed}
Exercise 8. (Middle) Communicate a set of read only requests to a master
asynchronously and then they are routed to slaves.
Import into each slave the trade.csv table and then have the 
client send several requests, each one about a single stock.
\begin{lstlisting}
        q mserve.q -p 5001 3 slaveserver.q
	q masterclient.q 
\end{lstlisting}
That's all you do. No need to start slaveserver.q.
mserve.q does this for you.
Note that the client issues async calls and then waits for the response
with h[].
\end{framed}

For future reference...
It is sometimes useful to access a website, scrape the result,
and present the result as a table.
\url{https://code.kx.com/svn/cookbook_code/yahoo.q}
presents a nice example.
Let's analyze it.
I've added in some print statements that begin with 0N!

Even more useful is a statement that creates
a deliberate type error
\begin{lstlisting}
 1 + `x;
\end{lstlisting}
This causes an error, but from this error, one can
inspect variables and even change them.
Then you resume by typing 
\begin{lstlisting}
:
\end{lstlisting}

\begin{framed}
Exercise 9. (Debugging and modification) Web access.
Take  yahoo.q and put in a deliberate type error
and then modify enddate.
Modify the result to return the Sym,Date,Close columns.
\begin{lstlisting}
q yahoomod.q
\end{lstlisting}
\end{framed}
\end{document}
