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

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

user1513
1#
user1513 Published in May 21, 2018, 6:43 pm

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 



}
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.375818 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO