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
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.