SAMPLEID Expression | SQL SELECT Statements | Teradata Vantage - 17.05 - SAMPLEID Expression - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

Identifies the sample to which a row belongs, distinguishing rows belonging to different samples specified in the SAMPLE clause of a SELECT statement.

ANSI Compliance

SAMPLEID is a Teradata extension to the ANSI SQL:2011 standard.

Definition of a Sample ID

The sample ID identifies the sample to which a row belongs in the left-to-right order of the SAMPLE clause specification, from 1 through n (where n is the number of samples requested in the SAMPLE clause).

Rules and Restrictions for SAMPLEID

The rules and restrictions are:
  • You can only specify SAMPLEID with a SAMPLE clause, which can appear either as part of a select list or as an ORDER BY clause expression.
  • SAMPLEID cannot be the only term specified in the select list. If you specify SAMPLEID, you must also specify at least one other non-SAMPLEID column expression.

Using SAMPLEID With Stratified Sampling

The SAMPLEID value for stratified sampling is simply 1, 2, 3, … n across n specified samples regardless of stratification. That is, for the following SAMPLE clause,

 
SAMPLE WHEN state = 'CA' THEN
0.3,
0.2
ELSE
0.5,
0.2
the SAMPLEID correspondence would be: 1 2   3 4