Teradata SAMPLE Clause

Teradata SAMPLE Clause

Teradata provides SAMPLE clause to return specific number/percentage of random rows containing required samples of data from the query resultset. There are many option available to use along with SAMPLE clause but this tutorial will cover only 2 simple variations. Table along with data used in the below examples can be found here.

• This will return random rows based on the value of 'n', same query may result in different resultset each time.
• 'n' can be integer or decimal(for value less than 1).
• If 'n' is integer then it will return that many number of rows and if 'n' is decimal(0>n>1) then it will return that much percentage of rows from query.
  
  
• Value of 'n' in below example is specified as 2, therefore output of query is 2 rows.

  
  select emp_no, emp_name, salary from employee SAMPLE 2;
  
  Output:
   emp_no  emp_name  salary
  -------  -------- -------
  1000276  JONES    2975.00
  1000315  TURNER   1500.00
  

  
  
• Value of 'n' in below example is specified as .5, therefore output of query is 7 rows(50% of total 14 rows).

  
  select emp_no, emp_name, salary from employee SAMPLE .5;
  
  Output:
   emp_no  emp_name  salary
  -------  -------- -------
  1000292  FORD     3000.00
  1000315  TURNER   1500.00
  1000288  SCOTT    3000.00
  1000299  ALLEN    1600.00
  1000326  ADAMS    1100.00
  1000245  PRADEEP  5000.00
  1000312  MARTIN   1250.00
  

• This can be used to return sample data based on CASE WHEN like conditions.
• Multiple conditions can be specified to get sample data based on those criteria.
  
  
• In the below example, 2 conditions are specified i.e. when job title is Manager then return 1 row & when salary is less than 2500 then return 20% of rows. When no condition will satisfy then else part will be executed and it will return 1 row as specified.

  
  select emp_no, emp_name, salary,job_title from employee
  SAMPLE WHEN job_title='MANAGER' THEN 1
  WHEN salary < 2500 THEN 0.2
  ELSE 1
  END;
  
  Output:
   emp_no  emp_name   salary  job_title
  -------  --------- -------  -----------
  1000326  ADAMS     1100.00  LDC
  1000276  JONES     2975.00  MANAGER
  1000336  JAMES      950.00  LDC
  1000262  CLARK     2450.00  MANAGER