Sunday, 19 July 2015

3 Uses of SAMPLE function in Teradata

Teradata+Career+Jobs+Articles+Apply Today
Usage-1

It returns a rows randomly from Teradata database.

Syntax for SAMPLE function:

SAMPLE [WITH REPLACEMENT]
   [RANDOMIZED ALLOCATION]
                 [WHEN <condition>  THEN]
                 {<number-of-rows> | <percentage>}
   […,<number-of-rows> | <percentage>]
                 [ELSE {<number-of-rows> |
   <percentage }   END]

Usage 2

It allows the user to get absolute number of rows or percentage of rows.

Usage 3

It also allows the rows to get from multiple samples

How to get random rows from a table:

SELECT * from sample_table
SAMPLE 5;

How to get certain percentage of rows:

SELECT * from sample_table
SAMPLE .30;

How to get multiple SAMPLE data from a single tables with out duplicates:

SELECT * from sample_table

SAMPLE .20, SAMPLE .30

ORDER BY 1,2;

SQL query to get 3 samples with duplicates: So 'WITH REPLACEMENT" you will get duplicate rows:

SELECT ST_ID
        ,CC_ID
        ,SSID
 FROM  sample_table
 SAMPLE WITH REPLACEMENT 8, 8, 8
 ORDER BY 3, 1, 2 ;

What is percentage calculation in SAMPLE:

Total rows present in a tables/Percentage given in SQL query

Ex:

Total rows: 100
Percentage: 0.5

Therefore" 100/.5= 100/(5/10x100): 2 rows


No comments:

Post a Comment