fbpx

The Teradata Sample Function

By Roland Wenzlofsky

December 15, 2019


What is the Teradata Sample Function?

The SAMPLE function returns a number of rows that were randomly selected. These are the features:

  • Ask for a sample with an absolute number of rows
  • Ask for a sample which contains a certain percentage of the table rows 
  • Ask for more than one sample at the same time
  • Include the column SAMPLEID to determine from which sample a row originates
  • Ask for samples with or without duplicates

?

The Syntax of the Sample Function

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

Here are some examples of how to use the SAMPLE function:

100 Random Rows

SELECT * FROM Customer
SAMPLE 100;

10% of Table Rows

SELECT * FROM Customer
SAMPLE .10;

2 Samples with 10% of Table Rows (no Duplicates)

SELECT *
FROM Customer
SAMPLE .1,.1
;

2 Samples with 10 Rows and SAMPLEID

SELECT
   t01.* ,
   SAMPLEID
FROM Customer t01
SAMPLE 10,10
;

2 Samples with 10% of Table Rows (with Duplicates)

SELECT *
FROM Customer
SAMPLE WITH REPLACEMENT .1,.1
;


2 Samples with Conditional Logic Applied 

SELECT * 
FROM Customer
SAMPLE
WHEN CustomerId > 1000
THEN .1,.1
ELSE .3,.3 END
;

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>