MySQL高阶2051-商店中每个成员的级别

目录

题目

准备数据

分析数据

实现

总结

---

题目

一个商店想对其成员进行分类。有三个层次:

• "钻石": 如果转换率 大于或等于 80.
• "黄金": 如果转换率 大于或等于 50 且小于 80.
• "白银": 如果转化率 小于 50.
• "青铜": 如果该成员从未访问过该商店。

成员的 转化率 为 (100 * 该会员的购买总数) / 该成员的总访问次数.

编写一个 SQL 来查询每个成员的 id、名称和类别。

以 任意顺序 返回结果表。

准备数据

Create table If Not Exists Members (member_id int, name varchar(30))
Create table If Not Exists Visits (visit_id int, member_id int, visit_date date)
Create table If Not Exists Purchases (visit_id int, charged_amount int)
    Truncate table Members
    insert into Members (member_id, name) values ('9', 'Alice')
    insert into Members (member_id, name) values ('11', 'Bob')
    insert into Members (member_id, name) values ('3', 'Winston')
    insert into Members (member_id, name) values ('8', 'Hercy')
    insert into Members (member_id, name) values ('1', 'Narihan')
    Truncate table Visits
    insert into Visits (visit_id, member_id, visit_date) values ('22', '11', '2021-10-28')
    insert into Visits (visit_id, member_id, visit_date) values ('16', '11', '2021-01-12')
    insert into Visits (visit_id, member_id, visit_date) values ('18', '9', '2021-12-10')
    insert into Visits (visit_id, member_id, visit_date) values ('19', '3', '2021-10-19')
    insert into Visits (visit_id, member_id, visit_date) values ('12', '11', '2021-03-01')
    insert into Visits (visit_id, member_id, visit_date) values ('17', '8', '2021-05-07')
    insert into Visits (visit_id, member_id, visit_date) values ('21', '9', '2021-05-12')
    Truncate table Purchases
    insert into Purchases (visit_id, charged_amount) values ('12', '2000')
    insert into Purchases (visit_id, charged_amount) values ('18', '9000')
    insert into Purchases (visit_id, charged_amount) values ('17', '7000')

members表

visits表

purchases表

分析数据

第一步:将三个表进行连接

select *
from members m left join visits v on m.member_id = v.member_id
               left join purchases p on p.visit_id = v.visit_id;

第二步:对成员进行分类

select m.member_id,name,
    (case
        when count(v.visit_id) = 0 then 'Bronze'
        when count(p.visit_id) * 100 / count(v.visit_id) < 50 then 'Silver'
        when count(p.visit_id) * 100 / count(v.visit_id) < 80 then 'Gold'
        else 'Diamond'
    end) as category
    from members m left join visits v on m.member_id = v.member_id
    left join purchases p on p.visit_id = v.visit_id
group by m.member_id, name;

实现

select m.member_id,name,
    (case
        when count(v.visit_id) = 0 then 'Bronze'
        when count(p.visit_id) * 100 / count(v.visit_id) < 50 then 'Silver'
        when count(p.visit_id) * 100 / count(v.visit_id) < 80 then 'Gold'
        else 'Diamond'
    end) as category
    from members m left join visits v on m.member_id = v.member_id
    left join purchases p on p.visit_id = v.visit_id
group by m.member_id, name;

总结

会员的购买总数:count(p.visit_id)

成员的总访问次数:count(v.visit_id)