【面试题】

“交易表”有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 t1left join a1 on 1 = 1left 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 t1left join (select min(总消费金额) as 最低总消费金额from (select 会员id,       sum(消费金额) as 总消费金额from 交易表group by 会员id) as t1) as a1 on 1 = 1left 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 t1left join (select min(总消费金额) as 最低总消费金额from (select 会员id,       sum(消费金额) as 总消费金额from 交易表group by 会员id) as t1) as a1 on 1 = 1left join (select max(总消费金额) as 最高总消费金额from (select 会员id,       sum(消费金额) as 总消费金额from 交易表group by 会员id) as t1) as a2 on 1 = 1)t2;

【本题考点】

1）考查对分组汇总的了解，以及灵活使用来解决业务问题；

2）考查对多表联结的了解，以及灵活使用来解决业务问题；

3）考查对分档问题的解决能力。

⬇️ 点击「阅读原文」

免费报名数据分析训练营

ITPub博客

如何将会员消费能力分类？

最新文章