Databases
StackOverflow2013
Postsdbo
POiterating through an sql table efficiently
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POiterating through an sql table efficiently
    text
    copied!<p>i have a table with 3 columns rcvr_id(user id),mth_id and tpv. mth_id is calculated as (2012-1900)*12+1,2,3(depending on if it is jan,feb,march). For example mth_id for Dec 2011 is 1344,Jan 2012 is 1345. the third column is tpv which is a decimal number and holds the transaction for the user for that month. An example table would be</p> <pre><code>rcvr_id mth_id tpv . . . 1 1326 23 1 1327 13 1 1329 9 1 1345 2 1 1330 25 1 1350 22 2 1325 31 2 1351 23 3 1327 130 3 1329 90 3 1345 20 3 1330 250 3 1350 220 . . . </code></pre> <p>and so on for other users(mth_ids may not be ordered)(rcvr_id and mth_id together make the primary key).Rcvr 2 has to be ignored because he does not have a tpv in between months 1326 and 1350. </p> <p>A missing row value in mth_id indicates that for that month the rcvr had 0 tpv. i.e,1328,1331 to 1344,1346 to 1350 tpv is 0.</p> <p>Problem: I want to create a table which holds the two columns rcvr_id,mth_id and the third column -change_in_tpv. For example for the month 1327 .. the row would be like</p> <pre><code>1 1327 10,i.e (tpv of 1327-tpv of 1326) </code></pre> <p><strong>For user 1</strong>: <em>For month 1347, change in tpv=tpv of month 1347- tpv of month 1346(even though both rows are not existing, i have to take their tpvs as 0). For 1346, tpv would be = tpv of 1346-tpv of 1345=-2.</em> </p> <p>For every receiver(that has a tpv between 1326 and 1350) i need to calculate the change in tpv for months 1327 to 1350.</p> <p>Details : Teradata, more than million rows.How do i do it and do it with efficiency. </p> <p>Multiple Queries/temp tables can be used</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload