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

Featured post

5 Top Teradata DATE functions to read now

The following are the list of DATE functions. These are very frequently being used in Teradata. The following statement requests the cur...

Popular Posts