SAMPLEID Expression Examples | SQL SELECT Statements | Teradata Vantage - 17.05 - SAMPLEID Expression Examples - 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)

Example: Three Sample Sets Using SAMPLEID as a Select List Expression

The following SELECT statement provides three mutually exclusive sample sets (60 percent for sample category x, 25 percent for sample category y, and 15 percent for sample category z) from a customer table.

The results are returned in a table with three columns: cust_name, cust_addr, and SAMPLEID.

The integer in the SAMPLEID column identifies whether the row belongs to the 0.6 sample, the 0.25 sample, or the 0.15 sample.

The samples are identified as 1 through 3, in left-to-right order from the SAMPLE clause, so 0.6 is identified by 1, 0.25 by 2, and 0.15 by 3.

     SELECT cust_name, cust_addr, SAMPLEID
     FROM customer_table
     SAMPLE 0.6, 0.25, 0.15;

A partial results table might look something like the following:

cust_name cust_addr SAMPLEID
Jones Pharmaceuticals 4235 Lawler Road

Memphis, TN

USA

1
Fong Furniture 310 East Highway 5

Hong Kong

2
Subramaniam Spice Exports 455 1/2 Gandhi Lane

Hyderabad

India

3
Forrester Property Management 1 West Broadway

Syracuse, New York

USA

1
Otomo Consulting 33 Korakuen Hall

Tokyo

Japan

1
Adler Music Publishing, Ltd. 5 East 245th Street

Nashville, TN

USA

 
O’Brien Metals 83 Heatherington

The Whithers

Cobblestone-on-Treads

United Kingdom

1
Irama Rice Importers 8562 Rhoma Lane

Jakarta

Indonesia

2
Abdelwahab Fine Egyptian Rugs 1723 Kulthum Avenue

Cairo

Egypt

1
Bachar Ouds 18 Rashied Diagonal

Baghdad

Iraq

1

Example: Using SAMPLEID With PERIOD Value Expressions

The following example shows how you can specify a PERIOD value expression with a SAMPLEID expression, where period_of_stay is the PERIOD value expression.

     SELECT emp_no, period_of_stay, SAMPLEID
     FROM employee
     SAMPLE 0.5,0.5;