CASEを使ったデータのビニング

中級 SQL Server

Ginger Grant

Instructor

CASEを使った列値の変更

CASE  
     WHEN Boolean_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END

T-SQLのCASEで列値を変更する

SELECT Continent, 
CASE WHEN Continent = 'Europe' or Continent = 'Asia' THEN 'Eurasia'
     ELSE 'Other'
     END AS NewContinent
FROM EconomicIndicators

+-----------+--------------+
|Continent  |NewContinent  |
+-----------+--------------+
|Europe     |Eurasia       |
|Asia       |Eurasia       |
|Americas   |Other         |
...
+-----------+--------------+

T-SQLのCASEで列値を変更する

SELECT Continent, 
CASE WHEN Continent = 'Europe' or Continent = 'Asia' THEN 'Eurasia'
     ELSE Continent 
     END AS NewContinent
FROM EconomicIndicators

+-----------+--------------+
|Continent  |NewContinent  |
+-----------+--------------+
|Europe     |Eurasia       |
|Asia       |Eurasia       |
|Americas   |Americas      |
...
+-----------+--------------+

CASE文を使った値グループの作成

-- We are binning the data here into discrete groups
SELECT Country, LifeExp, 
CASE WHEN LifeExp < 30 THEN 1
     WHEN LifeExp > 29 AND LifeExp < 40 THEN 2
     WHEN LifeExp > 39 AND LifeExp < 50 THEN 3
     WHEN LifeExp > 49 AND LifeExp < 60 THEN 4
     ELSE 5
     END AS LifeExpGroup
FROM EconomicIndicators
WHERE Year = 2007

+-----------+--------------+
|LifeExp    |LifeExpGroup  |
+-----------+--------------+
|25         |1             |
|30         |2             |
|65         |5             |
...
+-----------+--------------+

練習しましょう！

中級 SQL Server