Example 8. Call analytical functions in SQL statements
DolphinDB offers an integration of a high performance database and a fully-featured scripting language. One of the advantages of the integration is that we can analyze data directly within our database. The following example directly calls the function ols in SQL statements. Each day, stock turnover (volume/shares outstanding) is regressed on the absolute value of stock return and bid-ask spread. We plot the time series of the parameter estimates.
schema = extractTextSchema("c:/DolphinDB/Data/USstocks.csv")
update schema set type=`DOUBLE where name=`RET
USstocks = ploadText("c:/DolphinDB/Data/USstocks.csv", , schema)
t=select date, VOL\SHROUT as turnover, abs(RET) as absRet, (ASK-BID)/(BID+ASK)*2 as spread, log(SHROUT*(BID+ASK)/2) as logMV from USstocks where VOL>0
beta = select ols(turnover, (absRet,spread), 1, 2).Coefficient.beta as ``absRet`spread from t group by date
plot(select absRet,spread from beta, beta.date)
Here ols(turnover, (absRet,spread), 1, 2).Coefficient.beta produces a 3-element vector. As the elements in a table column are all scalars, we need to convert the vector/dictionary output to multiple scalars before we can output the results in multiple columns. In other words, we need to convert a composite column into multiple columns. This can be accomplished with "as" keyword and a constant string vector that represents the names of these columns. For details please refer to the section of "group by" in Chapter 8.
