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

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

Sarat Muppana
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.

sumcnt = 0
cumcnt = 0 
year = 0
startpoint1 = 0 
startpoint2 = 0 
sumexpect = 0
cntatmind = 0
cntatmaxd = 0
exatmind = 0
exatmaxd = 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 
    {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 Reply to 2018-02-13 16:04:38Z

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:

    startpoint1 + q1 AS startpoint1
            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
            ELSE 0
            END AS q1

    FROM (
        SELECT CASE 
                WHEN year <> 2016
                    THEN 0
                ELSE frac2final
                END AS startpoint1,
                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.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO