Performing analysis of data extracted from an SQL database

Question

Below is an SQL query that I generated using DatabaseExplorer as well as a small sample of the result. How, att this point, can I perform analysis on the results of the query. For example how would I create a new variable that is equal to high - low? And how would I graph high?

In essence, I am having difficulty in having the result being used as actual data points.

---

SQLExecute[
 SQLSelect[
  "mySQLtry", {"both4"}, {SQLColumn[{"both4", "id"}], 
   SQLColumn[{"both4", "open"}], SQLColumn[{"both4", "high"}], 
   SQLColumn[{"both4", "low"}], SQLColumn[{"both4", "close"}], 
   SQLColumn[{"both4", "volume"}]}, None, "SortingColumns" -> None, 
  "MaxRows" -> 100000, "Timeout" -> 10, "Distinct" -> False, 
  "GetAsStrings" -> False, "ShowColumnHeadings" -> True]] // TableForm


  {
 {"id", "open", "high", "low", "close", "volume"},
 {1, 167.29, 170.13, 166.93, 168.84, 6401800},
 {2, 164.06, 167.28, 163.69, 166.7, 5996200},
 {3, 164.5, 165.2, 163.21, 164.12, 5214800},
 {4, 164.1, 165.71, 163.46, 163.76, 3586000},
 {5, 164., 164.9, 163.31, 163.97, 1858100},
 {6, 165.06, 165.8, 163.39, 163.63, 4052600},
 {7, 165.5, 166.3, 164.5, 164.6, 4190700},
 {8, 163.79, 166.75, 163.42, 165.45, 9392900},
 {9, 161.81, 163.38, 160.2, 163.19, 8687100},
 {10, 162.31, 163.62, 160.83, 160.93, 8943800},
 {11, 163.85, 165.89, 163.07, 164.99, 8637700},
 {12, 164.81, 166., 162.11, 162.74, 7539800},
 {13, 166.48, 166.5, 163.86, 166.1, 6652900},
 {14, 167.54, 167.95, 165.42, 166., 6425000},
 {15

Answer 1

First of all you'd typically just assign the result of an SQLSelect to a variable and than use that for further processing, e.g.:

data = SQLSelect[
 "mySQLtry", {"both4"}, {SQLColumn[{"both4", "id"}], 
 SQLColumn[{"both4", "open"}], SQLColumn[{"both4", "high"}], 
 SQLColumn[{"both4", "low"}], SQLColumn[{"both4", "close"}], 
 SQLColumn[{"both4", "volume"}]}, None, "SortingColumns" -> None, 
 "MaxRows" -> 100000, "Timeout" -> 10, "Distinct" -> False, 
 "GetAsStrings" -> False, "ShowColumnHeadings" -> True];

what that replaces is just an array of values, which you can access with the standard Mathematica functions, e.g. Part (with shortcut [[]]). This will give the "high" and "low" columns without the headers:

 data[[2 ;; -1, {3, 4}]]

there are many ways to calculate the difference between these, here is a particularly short one:

 Subtract@@@data[[2 ;; -1, {3, 4}]]

to graph data, use the standard functions, e.g.:

 ListLinePlot[data[[2 ;; -1, 3]]]

in typical cases where the use of a database makes sense I'd suggest to extract only the data you need and in a form as you need it. So I'd probably only select those columns I actually want to process and not use the "ShowColumnHeadings" option. Then the processing will be somewhat simpler and the code will typically run faster and use less memory.

Actually IMO all the options given in your generated statement don't make much sense in a code fragment as they are either the defaults or not what you'd typically use, MaxRows and Timeout are most probably just set so that the DatabaseExplorer doesn't run into trouble and stay reactive when you play with a huge database (that is tables with many rows)...

Answer 2

I tried using Dataset but it is giving some odd errors so this answer skips that function.

If you have your result in res then you can setup a function to get the column you want from it. This would be better with Dataset but there appear to be some bugs with it in 10.0.2.

res = {{"id", "open", "high", "low", "close", "volume"}, 
 {1, 167.29, 170.13, 166.93, 168.84, 6401800}, 
 {2, 164.06, 167.28, 163.69,166.7, 5996200}, 
 {3, 164.5, 165.2, 163.21, 164.12, 5214800}, 
 {4, 164.1, 165.71, 163.46, 163.76, 3586000}, 
 {5, 164., 164.9, 163.31, 163.97, 1858100}, 
 {6, 165.06, 165.8, 163.39, 163.63, 4052600}, 
 {7, 165.5, 166.3, 164.5, 164.6, 4190700}, 
 {8, 163.79, 166.75, 163.42, 165.45, 9392900}, 
 {9, 161.81, 163.38, 160.2, 163.19, 8687100}, 
 {10, 162.31, 163.62, 160.83, 160.93, 8943800}, 
 {11, 163.85, 165.89, 163.07, 164.99, 8637700}, 
 {12, 164.81, 166., 162.11, 162.74, 7539800}, 
 {13, 166.48, 166.5, 163.86, 166.1, 6652900}, 
 {14, 167.54, 167.95, 165.42, 166., 6425000}};

This function expects that the First row of res contains the column names. It will find the position of the column name in that row then use it to extra items in the Rest of res using that position index.

getColumn[data_List, colName_String] := 
  Rest /* Flatten@data[[All, Flatten@Position[First@data, colName]]]

It uses RightComposition (/*) from version 10 so swap that around and use @ if you're on an earlier version.

Now you can use getColumn to return the column you are interested in and use them for calculations and plotting.

getColumn[res, "high"]
getColumn[res, "high"] - getColumn[res, "low"]
ListLinePlot[getColumn[res, "high"]]

Hope this helps.