1.0 - 8.00 - MultiCaseMatch Example - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)

This example labels people with the age groups to which they belong, which overlap:

Age Group Description
infant Younger than 1 year
toddler 1-2 years, inclusive
kid 2-12 years, inclusive
teenager 13-19 years, inclusive
young adult 16-25 years, inclusive
adult 21-40 years, inclusive
middle-aged person 35-60 years, inclusive
senior citizen 60 years or older

Input

people_age
id name age
1 John 0.5
2 Freddy 2
3 Marie 6
4 Tom Sawyer 17
5 Becky Thatcher 16
6 Philip 22
7 Joseph 25
8 Roger 35
9 Natalie 30
10 Henry 40
11 George 50
12 Sir William 65

SQL Call

SELECT * FROM MultiCaseMatch (
  ON (SELECT t.*,
    (CASE WHEN t.age < 1 THEN '1' ELSE '0' END) AS case1,
    (CASE WHEN t.age >= 1 and t.age <= 2 THEN '1' ELSE '0' END) AS case2,
    (CASE WHEN t.age >= 2 and t.age <= 12 THEN '1' ELSE '0' END) AS case3,
    (CASE WHEN t.age >= 13 and t.age <= 19 THEN '1' ELSE '0' END) AS case4,
    (CASE WHEN t.age >= 16 and t.age <= 25 THEN '1' ELSE '0' END) AS case5,
    (CASE WHEN t.age >= 21 and t.age <= 40 THEN '1' ELSE '0' END) AS case6,
    (CASE WHEN t.age >= 35 and t.age <= 60 THEN '1' ELSE '0' END) AS case7,
    (CASE WHEN t.age >= 60 THEN '1' ELSE '0' END) AS case8
    FROM people_age AS t
  )
  USING
  Labels (
    'case1 AS "infant"',
    'case2 AS "toddler"',
    'case3 AS "kid"',
    'case4 AS "teenager"',
    'case5 AS "young adult"',
    'case6 AS "adult"',
    'case7 AS "middle aged person"',
    'case8 AS "senior citizen"'
  )
) AS dt;

Output

Several people have two labels. For example, Freddy is both a toddler and a kid, and Tom Sawyer and Becky Thatcher are both teenagers and young adults.

id name age labels
1 John 0.5 infant
2 Freddy 2 toddler
2 Freddy 2 kid
3 Marie 6 kid
4 Tom Sawyer 17 teenager
4 Tom Sawyer 17 young adult
5 Becky Thatcher 16 teenager
5 Becky Thatcher 16 young adult
6 Philip 22 young adult
6 Philip 22 adult
7 Joseph 25 young adult
7 Joseph 25 adult
8 Roger 35 adult
8 Roger 35 middle aged person
9 Natalie 30 adult
10 Henry 40 adult
10 Henry 40 middle aged person
11 George 50 middle aged person
12 Sir William 65 senior citizen