case when then else end

需求：提取数据库中的数据

• 1-交易金额
• 2-交易笔数,3-交易人数
• 4-笔单价,5-客单价,6-上午笔数占比
• 7-午间笔数占比,8-下午笔数占比,9-晚间笔数占比,10-深夜笔数占比
• （注：上午 00:00-9:59；午间 10:00-12:59；下午 13:00-16:59；晚间 17:00-20:59；深夜 21:00-23:59）
• 以上都是基于mid，posId维度的

±----------±---------±---------±---------------------------±-------------+
| mid | posId | card | settleDate | settleAmount |
±----------------------------------------------------------------------------+
| D10045… | 55712… | 59D89… | 2018-07-25 19:06:32.300651 | 7723 |
| D10045… | 55712… | 59D89… | 2018-07-25 03:06:32.300651 | 7843 |
| D10045… | 55712… | 59D89… | 2018-07-25 00:06:32.300651 | 5117 |
| D10045… | 55712… | 59D89… | 2018-07-25 19:06:32.300651 | 4105 |
| D10045… | 55712… | 59D89… | 2018-07-25 07:06:32.300651 | 2486 |
| D10045… | 55712… | 59D89… | 2018-07-25 16:06:32.300651 | 9017 |
| D10045… | 55712… | 59D89… | 2018-07-25 21:06:32.300651 | 5132 |
| D10045… | 55712… | 59D89… | 2018-07-25 08:06:32.300651 | 1615 |
| D10045… | 55712… | 59D89… | 2018-07-25 05:06:32.300651 | 3254 |
| D10045… | 55712… | 59D89… | 2018-07-25 19:06:32.300651 | 6633 |
±----------±---------±---------±---------------------------±-------------+

优化之后的结果

create table z1 as select mid,posId,card,settleDate,sum(settleAmount) as amt,count(*) as cnt ,
(case 
when substring(settleDate,12) >="00:00" and SUBSTRING(settleDate,12)<"10:00" then 'morning' 
when substring(settleDate,12) >="10:00" and SUBSTRING(settleDate,12)<"13:00" then  'noon'
when substring(settleDate,12) >="13:00" and SUBSTRING(settleDate,12)<"17:00" then  'afternoon'
when substring(settleDate,12) >="17:00" and SUBSTRING(settleDate,12)<"21:00" then  'evening'
when substring(settleDate,12) >="21:00" and SUBSTRING(settleDate,12)<"23:59:59" then  'lateNight'
else ' ' end )as t
from transaction group by mid,posId,card,settleDate;



create table result as select mid,posId,
sum(amt) as allAmt,count(*),count(distinct card) as distcard,
sum(amt)/sum(cnt) as perTranAmt,sum(amt)/count(distinct card) as perCardAmt ,
sum(case t when 'morning' then cnt else 0 end )/sum(cnt) 'morning' ,
sum(case t when 'noon' then cnt else 0 end )/sum(cnt) 'noon' ,
sum(case t when 'afternoon' then cnt else 0 end )/sum(cnt) 'afternoon' ,
sum(case t when 'evening' then cnt else 0 end )/sum(cnt) 'evening', 
sum(case t when 'lateNight' then cnt else 0 end )/sum(cnt) 'lateNight' 
from z1 group by mid,posId;

最终优化

create table result as select mid,posId,
sum(settleAmount) as allAmt,count(*),count(distinct card) as distcard,
sum(settleAmount)/count(*) as perTranAmt,sum(settleAmount)/count(distinct card) as perCardAmt ,
sum(case  when substring(settleDate,12) >="00:00" and SUBSTRING(settleDate,12)<"10:00" then 1 else 0 end )/count(1) 'morning' ,
sum(case  when substring(settleDate,12) >="10:00" and SUBSTRING(settleDate,12)<"13:00" then 1 else 0 end )/count(1) 'noon' ,
sum(case  when substring(settleDate,12) >="13:00" and SUBSTRING(settleDate,12)<"17:00" then 1 else 0 end )/count(1) 'afternoon' ,
sum(case  when substring(settleDate,12) >="17:00" and SUBSTRING(settleDate,12)<"21:00" then 1 else 0 end )/count(1) 'evening', 
sum(case  when substring(settleDate,12) >="21:00" and SUBSTRING(settleDate,12)<"23:59:59" then 1 else 0 end )/count(1) 'lateNight' 
from transaction group by mid,posId;

case when then else end
有两种用法
语法形式1：
CASE expression
WHEN expression_11 THEN expression_12
…
WHEN expression_n1 THEN expression_n2
[ELSE expression_m]
END
该语句的执行过程是：将CASE后面表达式的值与各WHEN子句中的表达式的值进行比较，如果两者相等，则返回THEN后的表达式的值，然后跳出CASE语句，否则返回ELSE子句中的表达式的值。ELSE子句是可选项。当CASE语句中不包含ELSE子句时，如果所有比较失败时，CASE语句将返回NULL。
语法形式2：
CASE WHEN condition_expression_1 THEN expression_1
…
WHEN condition_expression_n THEN expression_n
[ELSE expression_m]
END
该语句的执行过程是：首先测试WHEN后的条件表达式的值，如果其值为真，则返回THEN后面的表达式的值，否则测试下一个WHEN子句中的表达式的值，如果所有WHEN子句后的表达式的值都为假，则返回ELSE后的表达式的值，如果在CASE语句中没有ELSE子句，则CASE表达式返回NULL。

上面用的是的是第二种形式，

sum(case  when substring(settleDate,12) >="00:00" and SUBSTRING(settleDate,12)<"10:00" then 1 else 0 end )/count(1) 'morning' ,

当时间在"00:00"和"10:00"之间时 ，取值1，通过sum()函数取到所有在这个时间段消费的笔数，最后将得到的值放入morning列 实现行转列