SQL operators as Wolfram Language functions
​by Calin Damian
Updated on 18-February-2021

Objective

New to Wolfram language I’m very impressed by its expressive power .However coming from a database background I found sometimes counterintuitive to manipulate data using Datasets.In my journey of learning Wolfram language I’m trying to implement SQL like operators in order to explore data structures with the simplicity of SQL Language .​A small query example using stocks data imported from FinancialData package:sampledataSQL is a 2 dimensional list with 3 columns { symbol, time , close}​​​ whereSQL[sampledataSQL , (! MissingQ[close]) ] // groupBySQL[# , {time} ]&// summarySQL [# , Association[“maxclose”  (close//Max) , “minclose”  (close//Min) , “count”  (table//Length) ] ]&// orderBySQL[#, {maxclose}]&// showTableSQL​​​​

Main Code


Demo

Get demo data using FinancialData package

(*getsdataandflattenitin2dimensionalarray*)​​symbols={"MSFT","TSLA","AMZN"};​​sampledata=(Flatten/@Tuples[{{#},FinancialData[#,{{2020}}]//Normal}])&/@symbols//Flatten[#,1]&;​​​​​​(*convertsdatainSQLtablestructure*)​​sampledataSQL=tableSQL[sampledata,{"symbol","time","close"}];(*input:2dimensionalarrayandalistofcolnames*)​​​​sampledataSQL//​​showTableSQL(*displayfirst10linesbydefault*)
Total Rows:759 Elapsed:1.×
-7
10
Out[]//TableForm=
symbol
time
close
MSFT
Thu 2 Jan 2020 00:00:00GMT+1.
$
160.62
MSFT
Fri 3 Jan 2020 00:00:00GMT+1.
$
158.62
MSFT
Mon 6 Jan 2020 00:00:00GMT+1.
$
159.03
MSFT
Tue 7 Jan 2020 00:00:00GMT+1.
$
157.58
MSFT
Wed 8 Jan 2020 00:00:00GMT+1.
$
160.09
MSFT
Thu 9 Jan 2020 00:00:00GMT+1.
$
162.09
MSFT
Fri 10 Jan 2020 00:00:00GMT+1.
$
161.34
MSFT
Mon 13 Jan 2020 00:00:00GMT+1.
$
163.28
MSFT
Tue 14 Jan 2020 00:00:00GMT+1.
$
162.13
MSFT
Wed 15 Jan 2020 00:00:00GMT+1.
$
163.18

Lets use the new SQL operators

projectSQL

​​(*Projectsubsetsdistinctvaluesofselectedcolumns*)​​projectSQL[sampledataSQL,{symbol}]//(*thecolumnisreferencedasasymbolinthetablecontext*)​​showTableSQL
Total Rows:3 Elapsed:0.0026903
Out[]//TableForm=
symbol
MSFT
TSLA
AMZN
(*projectonmultiplecolumns*)​​projectSQL[sampledataSQL,{symbol,close}]//​​showTableSQL
Total Rows:753 Elapsed:0.0012248
Out[]//TableForm=
symbol
close
MSFT
$
160.62
MSFT
$
158.62
MSFT
$
159.03
MSFT
$
157.58
MSFT
$
160.09
MSFT
$
162.09
MSFT
$
161.34
MSFT
$
163.28
MSFT
$
162.13
MSFT
$
163.18

selectSQL

(*Selectasubsetofcolumns*)​​​​selectSQL[sampledataSQL,{symbol,close}]//​​showTableSQL
Total Rows:759 Elapsed:0.0008263
Out[]//TableForm=
symbol
close
MSFT
$
160.62
MSFT
$
158.62
MSFT
$
159.03
MSFT
$
157.58
MSFT
$
160.09
MSFT
$
162.09
MSFT
$
161.34
MSFT
$
163.28
MSFT
$
162.13
MSFT
$
163.18
close=6;(*globalsymbolshavingthesamenameascolumnsarenotimpacted*)​​time="r"​​selectSQL[sampledataSQL,{time,close}]//(*here"close"isevaluatedintablecontext*)​​showTableSQL
Out[]=
r
Total Rows:759 Elapsed:0.0020424
Out[]//TableForm=
time
close
Thu 2 Jan 2020 00:00:00GMT+1.
$
160.62
Fri 3 Jan 2020 00:00:00GMT+1.
$
158.62
Mon 6 Jan 2020 00:00:00GMT+1.
$
159.03
Tue 7 Jan 2020 00:00:00GMT+1.
$
157.58
Wed 8 Jan 2020 00:00:00GMT+1.
$
160.09
Thu 9 Jan 2020 00:00:00GMT+1.
$
162.09
Fri 10 Jan 2020 00:00:00GMT+1.
$
161.34
Mon 13 Jan 2020 00:00:00GMT+1.
$
163.28
Tue 14 Jan 2020 00:00:00GMT+1.
$
162.13
Wed 15 Jan 2020 00:00:00GMT+1.
$
163.18
selectSQL[sampledataSQL,{3,1}]//(*youcanselectonordinalpositionsofcolumns*)​​showTableSQL
Total Rows:759 Elapsed:0.0021006
Out[]//TableForm=
close
symbol
$
160.62
MSFT
$
158.62
MSFT
$
159.03
MSFT
$
157.58
MSFT
$
160.09
MSFT
$
162.09
MSFT
$
161.34
MSFT
$
163.28
MSFT
$
162.13
MSFT
$
163.18
MSFT

whereSQL, ordeBySQL

addColumnsSQL, rowTransformSQL , rowMapSQL

groupBySQL , summarySQL , summaryMapSQL

combining operators

unnestSQL

pivotSQL , unpivotSQL

unionSQL

joinSQL, crossJoinSQL

Contact

Author : Calin Damian
​
Email : damcalrom@yahoo.com
​
GitHub: https://github.com/calindamian