MultiCaseMatch Example | Teradata Vantage - MultiCaseMatch Example - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
9.02
9.01
2.0
1.3
Published
February 2022
Language
English (United States)
Last Update
2022-02-10
dita:mapPath
rnn1580259159235.ditamap
dita:ditavalPath
ybt1582220416951.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

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             
 -- -------------- ---- ------------------ 
  7 joseph         25.0 young adult       
  6 philip         22.0 young adult       
  7 joseph         25.0 adult             
  6 philip         22.0 adult             
 12 sir william    65.0 senior citizen    
 11 george         50.0 middle aged person
  4 tom sawyer     17.0 teenager          
  9 natalie        30.0 adult             
  4 tom sawyer     17.0 young adult       
 10 henry          40.0 adult             
  5 becky thatcher 16.0 teenager          
 10 henry          40.0 middle aged person
  5 becky thatcher 16.0 young adult       
  3 marie           6.0 kid               
  1 john            0.5 infant            
  8 roger          35.0 adult             
  8 roger          35.0 middle aged person
  2 freddy          2.0 toddler           
  2 freddy          2.0 kid

Download a zip file of all examples and a SQL script file that creates their input tables.