如何将会员消费能力分类？
【面试题】
“交易表”有4个字段：订单号、会员id、消费金额、购买时间。
问题：将会员按照总消费金额从高到低分成50档。
【解题步骤】
1. 解题思路
将某一个字段按区间分档，最先想到的是《猴子 从零学会SQL》里讲过的多条件语句（case when）。
但是，如果分成50档，需要写50个case when，显然不太实际，有没有更好的解决办法呢？
我们还可以通过最大值、最小值和分档数量三个信息来实现：
2. 找到最大值和最小值
首先，使用group by获取每个会员的“总消费金额”。
select 会员id,
sum(消费金额) as 总消费金额
from 交易表
group by 会员id;
把这个SQL命名为子查询t1。
再获取“最低总消费金额”和“最高总消费金额”。
select min(总消费金额) as 最低总消费金额
from t1;
把这个SQL命名为子查询a1。
select max(总消费金额) as 最高总消费金额
from t1;
把这个SQL命名为子查询a2。
3. 计算区间长度
区间长度 = (最大值 - 最小值) / 分档数量
为了计算50档“总消费金额”的“区间长度”，使用多表联结将子查询 t1、a1、a2 联结。
为了保留所有会员id的记录，所以t1为左表，使用左联结。
select t1.*,
a1.最低总消费金额,
a2.最高总消费金额,
(a2.最高总消费金额-a1.最低总消费金额)/50 as 区间长度
from t1
left join a1 on 1 = 1
left join a2 on 1 = 1;小技巧：两表联结时，使用 1=1 得到两表的笛卡尔积。
将子查询 t1、a1、a2 代入：
select t1.*,
a1.最低总消费金额,
a2.最高总消费金额,
(a2.最高总消费金额-a1.最低总消费金额)/50 as 区间长度
from (
select 会员id,
sum(消费金额) as 总消费金额
from 交易表
group by 会员id
) as t1
left join (
select min(总消费金额) as 最低总消费金额
from (
select 会员id,
sum(消费金额) as 总消费金额
from 交易表
group by 会员id
) as t1
) as a1 on 1 = 1
left join (
select max(总消费金额) as 最高总消费金额
from (
select 会员id,
sum(消费金额) as 总消费金额
from 交易表
group by 会员id
) as t1
) as a2 on 1 = 1;
把这个SQL命名为子查询t2。
4. 计算档位
基于“总消费金额”、“最高总消费金额”和“区间长度”计算“档位”。
x = (最高总消费金额-总消费金额)/区间长度
将以上结果向上取整数（使用ceil函数），即“总消费金额从高到低”的档位。
select 会员id,
总消费金额,
ceil((最高总消费金额-总消费金额)/区间长度) as 档位
from t2;
“M003”会员消费金额最低，档位是50；而“M005”会员消费金额最高，档位却是0，正确结果应为1。
原因是0向上取整还是0，这里需要特殊处理。
特殊情况的处理一般也是使用《猴子 从零学会SQL》里讲过的多条件语句（case when）。
最终结果为：
select 会员id,
总消费金额,
case when ceil((最高总消费金额-总消费金额)/区间长度) = 0 then 1 else ceil((最高总消费金额-总消费金额)/区间长度) end as 档位
from t2;将子查询t2代入：
select 会员id,
总消费金额,
case when ceil((最高总消费金额-总消费金额)/区间长度) = 0 then 1 else ceil((最高总消费金额-总消费金额)/区间长度) end as 档位
from (
select t1.*,
a1.最低总消费金额,
a2.最高总消费金额,
(a2.最高总消费金额-a1.最低总消费金额)/50 as 区间长度
from (
select 会员id,
sum(消费金额) as 总消费金额
from 交易表
group by 会员id
) as t1
left join (
select min(总消费金额) as 最低总消费金额
from (
select 会员id,
sum(消费金额) as 总消费金额
from 交易表
group by 会员id
) as t1
) as a1 on 1 = 1
left join (
select max(总消费金额) as 最高总消费金额
from (
select 会员id,
sum(消费金额) as 总消费金额
from 交易表
group by 会员id
) as t1
) as a2 on 1 = 1
)t2;
【本题考点】
1）考查对分组汇总的了解，以及灵活使用来解决业务问题；
2）考查对多表联结的了解，以及灵活使用来解决业务问题；
3）考查对分档问题的解决能力。