Databases
StackOverflow2013
Postsdbo
POidentifying trends and classifying using sql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POidentifying trends and classifying using sql
    primarykey
    data
    text
    <p>i have a table xyz, with three columns rcvr_id,mth_id and tpv. rcvr_id is an id given to a customer, mth_id is a column which stores the month number( mth_id is calculated as (2012-1900) * 12 + 1,2,3.. ( depending on the month). So for example Dec 2011 will have month_id of 1344, Jan 2012 1345 etc. Tpv is a variable which shows the customers transaction amount.</p> <pre><code>Example table rcvr_id mth_id tpv 1 1344 23 2 1344 27 3 1344 54 1 1345 98 3 1345 102 . . . so on P.S if a customer does not have a transaction in a given month, his row for that month wont exist. </code></pre> <p>Now, the question. Based on transactions for the months 1327 to 1350, i need to classify a customer as steady or sporadic.</p> <p>Here is a description.</p> <p><img src="https://i.stack.imgur.com/F69Y2.png" alt="This pattern is for 1 customer .. i have millions of customers"> </p> <p>The above image is for 1 customer. i have millions of customers.</p> <p>How do i go about it? I have no clue how to identify trends in sql .. or rather how to do it the best way possible.</p> <p><strong>ALSO i am working on teradata.</strong></p> <p><em>Ok i have found out how to get standard deviation. Now the important question is : How do i set a standard deviation limit on my own? i just cant randomly say "if standard dev is above 40% he is sporadic else steady". I thought of calculating average of standard deviation for all customers and if it is above that then he is sporadic else steady. But i feel there could be a better logic</em></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1356163
    1. USuser1356163
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POidentifying trends and classifying using sql
      1. This table or related slice is empty.
      1. VTUpMod
    1. COinstead of average i am now gonna use standard deviation of standard deviation
      singulars
      1. POidentifying trends and classifying using sql
      1. USuser1356163
    2. COLooking at your edit, you might want to ask a new quesiton on how to use standard deviation.
      singulars
      1. POidentifying trends and classifying using sql
      1. USBellevueBob
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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