Home T - SQL variable programming like in R/C++

# T - SQL variable programming like in R/C++

Sarat Muppana
1#
Sarat Muppana Published in 2018-02-13 15:28:40Z
 I have sql data of 2,907,735 rows and 32 columns.. and I want to perform simple mathematical calculations for each row using the attributes in that row. Currently, I had to program this in R which is taking so much time, and I'm not sure SQL has the capability to do this simple programming calculations are not. If it has the capability, I think SQL will be faster. Please help. q1=0 q2=0 q3=0 q4=0 frac2final=0 sumcnt = 0 cumcnt = 0 year = 0 startpoint1 = 0 startpoint2 = 0 sumexpect = 0 cntatmind = 0 cntatmaxd = 0 exatmind = 0 exatmaxd = 0 ex1=0 ex2=0 ex3=0 ex4=0 cumcntfirst = 0 for (i in 1:2907735) { frac2final = a[i,25] sumcnt = a[i,31] cumcnt = frac2final + sumcnt year = a[i,12] sumexpect = a[i,32] cntatmind = a[i,4] cntatmaxd = a[i,5] exatmind = a[i,6] exatmaxd = a[i,7] if(year !=2016) { startpoint1 = 0 startpoint2=0} else {startpoint1 = frac2final startpoint2 = frac2final} #cumcntfirst = startpoint2 + cntatmind if (startpoint1<=0.25) q1 = max( (min(0.25,cumcnt) - startpoint1),0) else q1=0 startpoint1 = startpoint1 + q1 if(startpoint1<=0.5) q2 = max( (min(0.5,cumcnt) - startpoint1),0) else q2=0 startpoint1 = startpoint1 + q2 if(startpoint1<=0.75) q3 = max( (min(0.75,cumcnt) - startpoint1),0) else q3=0 startpoint1 = startpoint1 + q3 q4 = max(0,sumcnt-q1-q2-q3) a[i,33] = q1 a[i,34] = q2 a[i,35] = q3 a[i,36] = q4 } 
JNevill
2#
 Remember in SQL we work with sets, not loops. An "Intermediate result" would be an in intermediate result set, which is synonymous with a subquery (for the most part). As an example using your R as a starting point: SELECT q1, startpoint1 + q1 AS startpoint1 FROM ( startpoint1, SELECT CASE WHEN startpoint1 <= 0.25 THEN CASE WHEN 0 > CASE WHEN 0.25 > cumcnt THEN 0.25 ELSE cumcnt END - startpoint1 THEN 0 ELSE CASE WHEN 0.25 > cumcnt THEN 0.25 ELSE cumcnt END - startpoint1 END ELSE 0 END AS q1 FROM ( SELECT CASE WHEN year <> 2016 THEN 0 ELSE frac2final END AS startpoint1, CASE WHEN year <> 2016 THEN 0 ELSE frac2final END AS startpoint2 FROM yourTable ) subquery1 ) subquery2  There is no function to replicate your min and max here so we have to use CASE statements to do the logic. There are many examples of UDFs out there to give this functionality though. The trick is that min and max DO exist in tsql, but it takes the min/max of values in a column when aggregating many records together. Your use here is synonymous with UDFs generally called GREATEST() and LEAST() to compare two or more fields from the same record. To get a grasp on how this is working, run the inner-most subquery and see what it produces, then run the next subquery (which has subquery1 inside of it) to see how it uses those results... then you can just reuse this logic to build your remaining fields for your final result set.