Introducing splitSQL table operator​
​by Damian Calin
The objective is to have a function that can split a source table base on given conditions.Could have multiple application as in ETL data processing , partitioning or creating streams of data based on given conditions .​​splitSQL [table , { name1  condition1, name2  condition2 , ...} ]​The input is a list or association of conditions based on columns of the table.The output is an association of tables with same structures as source table and containing rows filtered on corresponding condition

Load SQL functions

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)
CloudImport["https://www.wolframcloud.com/env/damcalrom/Published/SQLOperators.wl"];

Generate sample sql table

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

Using splitSQL

Split sample data into 2 separate outputs with based on simple criteria
In[]:=
tbsplit= sampledataSQL //​​ splitSQL [# ,  "output1" symbol"TSLA" ,​​ "output2" symbol"MSFT" ] &;//AbsoluteTiming​​​​​​tbsplit["output1"]//​​showTableSQL​​​​tbsplit["output2"]//​​showTableSQL
Out[]=
{0.0065403,Null}
Total Rows:253 Elapsed:1.×
-6
10
Out[]//TableForm=
symbol
time
close
TSLA
Thu 2 Jan 2020 00:00:00GMT+2.
$
86.05
TSLA
Fri 3 Jan 2020 00:00:00GMT+2.
$
88.60
TSLA
Mon 6 Jan 2020 00:00:00GMT+2.
$
90.31
TSLA
Tue 7 Jan 2020 00:00:00GMT+2.
$
93.81
TSLA
Wed 8 Jan 2020 00:00:00GMT+2.
$
98.43
TSLA
Thu 9 Jan 2020 00:00:00GMT+2.
$
96.27
TSLA
Fri 10 Jan 2020 00:00:00GMT+2.
$
95.63
TSLA
Mon 13 Jan 2020 00:00:00GMT+2.
$
104.97
TSLA
Tue 14 Jan 2020 00:00:00GMT+2.
$
107.58
TSLA
Wed 15 Jan 2020 00:00:00GMT+2.
$
103.70
Total Rows:253 Elapsed:2.5×
-6
10
Out[]//TableForm=
symbol
time
close
MSFT
Thu 2 Jan 2020 00:00:00GMT+2.
$
160.62
MSFT
Fri 3 Jan 2020 00:00:00GMT+2.
$
158.62
MSFT
Mon 6 Jan 2020 00:00:00GMT+2.
$
159.03
MSFT
Tue 7 Jan 2020 00:00:00GMT+2.
$
157.58
MSFT
Wed 8 Jan 2020 00:00:00GMT+2.
$
160.09
MSFT
Thu 9 Jan 2020 00:00:00GMT+2.
$
162.09
MSFT
Fri 10 Jan 2020 00:00:00GMT+2.
$
161.34
MSFT
Mon 13 Jan 2020 00:00:00GMT+2.
$
163.28
MSFT
Tue 14 Jan 2020 00:00:00GMT+2.
$
162.13
MSFT
Wed 15 Jan 2020 00:00:00GMT+2.
$
163.18
Split table on multiple conditions and add rows not matching any specify conditions in separate stream.
​rowsLeft is a special keyword that will include all rows not matching any of specified conditions
In[]:=
tbsplit=sampledataSQL//​​ splitSQL[#,"202001NotAMZN"DateValue[time,{"Year","Month"}]{2020,1}&&symbol≠"AMZN",​​ "202002"DateValue[time,{"Year","Month"}]{2020,2},​​ "RowsNotMach"rowsLeft]&;//AbsoluteTiming​​​​tbsplit["202001NotAMZN"]//​​showTableSQL​​​​tbsplit["202002"]//​​showTableSQL​​​​tbsplit["RowsNotMach"]//​​showTableSQL​​
Out[]=
{0.248863,Null}
Total Rows:42 Elapsed:1.×
-6
10
Out[]//TableForm=
symbol
time
close
MSFT
Thu 2 Jan 2020 00:00:00GMT+2.
$
160.62
MSFT
Fri 3 Jan 2020 00:00:00GMT+2.
$
158.62
MSFT
Mon 6 Jan 2020 00:00:00GMT+2.
$
159.03
MSFT
Tue 7 Jan 2020 00:00:00GMT+2.
$
157.58
MSFT
Wed 8 Jan 2020 00:00:00GMT+2.
$
160.09
MSFT
Thu 9 Jan 2020 00:00:00GMT+2.
$
162.09
MSFT
Fri 10 Jan 2020 00:00:00GMT+2.
$
161.34
MSFT
Mon 13 Jan 2020 00:00:00GMT+2.
$
163.28
MSFT
Tue 14 Jan 2020 00:00:00GMT+2.
$
162.13
MSFT
Wed 15 Jan 2020 00:00:00GMT+2.
$
163.18
Total Rows:57 Elapsed:1.9×
-6
10
Out[]//TableForm=
symbol
time
close
MSFT
Mon 3 Feb 2020 00:00:00GMT+2.
$
174.38
MSFT
Tue 4 Feb 2020 00:00:00GMT+2.
$
180.12
MSFT
Wed 5 Feb 2020 00:00:00GMT+2.
$
179.90
MSFT
Thu 6 Feb 2020 00:00:00GMT+2.
$
183.63
MSFT
Fri 7 Feb 2020 00:00:00GMT+2.
$
183.89
MSFT
Mon 10 Feb 2020 00:00:00GMT+2.
$
188.70
MSFT
Tue 11 Feb 2020 00:00:00GMT+2.
$
184.44
MSFT
Wed 12 Feb 2020 00:00:00GMT+2.
$
184.71
MSFT
Thu 13 Feb 2020 00:00:00GMT+2.
$
183.71
MSFT
Fri 14 Feb 2020 00:00:00GMT+2.
$
185.35
Total Rows:660 Elapsed:2.3×
-6
10
Out[]//TableForm=
symbol
time
close
MSFT
Mon 2 Mar 2020 00:00:00GMT+2.
$
172.79
MSFT
Tue 3 Mar 2020 00:00:00GMT+2.
$
164.51
MSFT
Wed 4 Mar 2020 00:00:00GMT+2.
$
170.55
MSFT
Thu 5 Mar 2020 00:00:00GMT+2.
$
166.27
MSFT
Fri 6 Mar 2020 00:00:00GMT+2.
$
161.57
MSFT
Mon 9 Mar 2020 00:00:00GMT+2.
$
150.62
MSFT
Tue 10 Mar 2020 00:00:00GMT+2.
$
161.02
MSFT
Wed 11 Mar 2020 00:00:00GMT+2.
$
153.57
MSFT
Thu 12 Mar 2020 00:00:00GMT+2.
$
139.06
MSFT
Fri 13 Mar 2020 00:00:00GMT+2.
$
158.83
Generate a list of multiple conditions based on monthly date range. Useful to partition table by month.
Export table to multiple files based on condition