SPARK SQL - cas quand alors

97

SELECT IF(1=1, 1, 0) FROM table 

SELECT CASE WHEN key = 1 THEN 1 ELSE 2 END FROM testData 

SELECT     CASE WHEN id = 1 OR id = 2 THEN "OneOrTwo" ELSE "NotOneOrTwo" END AS IdRedux FROM customer 

SELECT     CASE WHEN id = 1 OR state = 'MA'           THEN "OneOrMA"           ELSE "NotOneOrMA" END AS IdRedux FROM customer 

SELECT     CASE WHEN id = 1           THEN "OneOrMA"          ELSE              CASE WHEN state = 'MA' THEN "OneOrMA" ELSE "NotOneOrMA" END     END AS IdRedux FROM customer 

82

 // Example: encoding gender string column into integer.     // Scala:    people.select(when(people("gender") === "male", 0)      .when(people("gender") === "female", 1)      .otherwise(2))     // Java:    people.select(when(col("gender").equalTo("male"), 0)      .when(col("gender").equalTo("female"), 1)      .otherwise(2)) 

71

  select      org,      patient_id,     case        when (age is null) then 'Not Available'       when (age < 15) then 'Less than 15'       when (age >= 15 and age < 25) then '15 to 25'       when (age >= 25 and age < 35) then '25 to 35'       when (age >= 35 and age < 45) then '35 to 45'       when (age >= 45) then '45 and Older'     end as age_range   from demo 

64

​ case ​ ​ ​ when exp1 in ('a','b','c') ​ ​ ​ ​ then element_at(map('a','A','b','B','c','C'), exp1) ​ ​ ​ else exp1 ​ ​ end 

54

Based on my current production code, this works     val identifierDF =     tempIdentifierDF.select(tempIdentifierDF("t_item_account_id"),    when(tempIdentifierDF("h_description").contains(tempIdentifierDF("t_cusip")),100)         .when(tempIdentifierDF("h_description").contains(tempIdentifierDF("t_ticker")),100)         .when(tempIdentifierDF("h_description").contains(tempIdentifierDF("t_isin")),100)         .when(tempIdentifierDF("h_description").contains(tempIdentifierDF("t_sedol")),100)         .when(tempIdentifierDF("h_description").contains(tempIdentifierDF("t_valoren")),100)         .otherwise(0)         .alias("identifier_in_description_score")     )