The objective is to implement a window frame capability in Mathematica similar with SQL OVER operator supported by most of popular databases.It allows to perform calculations across a set of related rows, without actually grouping these rows together. The concept behind SQL window frames are explained more in detail at : https://mjk.space/advances-sql-window-frames/The new function syntax :overSQL [Input table , {partition by clause} , {order by clause } , {window frame clause}, {measures to be calculated on window frame spec} ] Its much easier to understand it with a demo.
Load SQL package
Load SQL package
Load the last version of SQLOperators package containing SQL function developed so far.
See my previous post : SQL operators as Wolfram Language functions (https://community.wolfram.com/groups/-/m/t/2195893)
See my previous post : SQL operators as Wolfram Language functions (https://community.wolfram.com/groups/-/m/t/2195893)
In[]:=
CloudImport["https://www.wolframcloud.com/env/damcalrom/Published/SQLOperators.wl"];
Sample data from FinancialData package
Sample data from FinancialData package
In[]:=
(*generatetables*)symbols={"MSFT","TSLA","AMZN"};sampledata=(Flatten/@Tuples[{{#},FinancialData[#,{{2022}}]//Normal}])&/@symbols//Flatten[#,1]&;(*convertsdatainSQLtablestructure*)sampledataSQL=tableSQL[sampledata,{"symbol","time","close"}];(*input:2dimensionalarrayandalistofcolnames*)sampledataSQL//showTableSQL(*displayfirst10linesbydefault*)
Total Rows:309 Elapsed:3.×
-7
10
Out[]//TableForm=
symbol | time | close |
MSFT | ||
MSFT | ||
MSFT | ||
MSFT | ||
MSFT | ||
MSFT | ||
MSFT | ||
MSFT | ||
MSFT | ||
MSFT |
Demo overSQL function
Demo overSQL function
Perform some aggregations on a window frame between current row and 2 preceding row
SQL equivalent will be: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
SQL equivalent will be: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
In[]:=
sampledataSQL//overSQL[#,{symbol}(*partitionby*), {time}(*orderby*), {-2,-1,0}(*windowframerangebetweencurrentrowand2precedingrow*), Association["windowLength"(close//Length),(**) "totalclose"(close//Total), "firstclose"(close//First), "meanclose"(close//Mean) ]]&//tableSQLAsDataset
Out[]=
For consecutive rows we can use Range function in order to avoid specifying each index
In[]:=
sampledataSQL//overSQL[#,{symbol}(*partitionby*), {time}(*orderby*), Range[-2,0](*windowframerangebetweencurrentrowand2precedingrow*), Association["windowLength"(close//Length),(**) "totalclose"(close//Total), "firstclose"(close//First), "meanclose"(close//Mean) ]]&//tableSQLAsDataset
Out[]=
If we want to specify an unbounded frame we can use Range[-length,0]. This is useful for calculating cumulative totals
SQL equivalent will be: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
SQL equivalent will be: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
In[]:=
sampledataSQL//overSQL[#,{symbol}(*partitionby*), {time}(*orderby*), Range[-length,0](*windowframerangebetweencurrentrowandunboundedprecedingrow*), Association["windowLength"(close//Length),(**) "totalclose"(close//Total), "firstclose"(close//First), "meanclose"(close//Mean) ]]&//tableSQLAsDataset
Out[]=
To reverse the frame use Range[0, length].
SQL equivalent will be: ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
SQL equivalent will be: ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
The possibility to combine relative position and ranges gives overSQL greater flexibility than SQL equivalent.
Lets says we want a frame containing only the 4th preceding row and a rage between 2 to 5 following row.
This type of condition is not possible with SQL OVER version
Lets says we want a frame containing only the 4th preceding row and a rage between 2 to 5 following row.
This type of condition is not possible with SQL OVER version
If we use an empty range all rows from the partition are used in calculation.
Another nice feature is rowNumber used in combination with over .
Lets say we want first top 3 close positions ordered by value for each symbol
Lets say we want first top 3 close positions ordered by value for each symbol