用 SQL 回答客户的问题
目标
能够基于他人设计的模式，将客户的问题转化为查询，并避开那些不会报错却会给出错误结果的陷阱。
为什么重要
语法错误的查询会立即提示错误，真正危险的却是能够正常执行的查询。如果在客户面前解读了错误的数字，那么此后在该项目中给出的所有数字都会受到质疑。
本实验将让你亲自遇到其中两类问题。COUNT(*) 与 COUNT(열) 的区别——后者只统计该列不为 NULL 的行。还有，不存在的行不会被连接——从未创建过工单的客户根本不会出现在 INNER JOIN 的结果中，因此若要统计这类客户，必须在 LEFT JOIN 后查找 NULL，或使用 NOT EXISTS。
还有一点：如果 NOT IN 的子查询中哪怕包含一个 NULL，整个结果也会变成 0 行，而且既不报错也不警告。看似符合业务逻辑的结论就这样直接进入报告。养成使用 NOT EXISTS 的习惯更安全。
模式
customers(id, name, plan, signed_at)——plan 为 free / pro / enterpriseorders(id, customer_id, amount, status, created_at)——status 为 paid / pending / refundedtickets(id, customer_id, severity, opened_at, closed_at)——未关闭工单的closed_at为 NULL
步骤
- 创建
/root/sql，并将/opt/data/support.sql导入/root/sql/support.db。 - 将客户总数写入
/root/sql/q1.txt。 - 将
status为paid的订单金额总和写入/root/sql/q2.txt。 - 将已确认收入最高的客户姓名写入
/root/sql/q3.txt。 - 将尚未关闭的工单数量写入
/root/sql/q4.txt。 - 将
SELECT COUNT(closed_at) FROM tickets;的结果写入/root/sql/q5.txt。 - 将从未创建过工单的客户数量写入
/root/sql/q6.txt。 - 将已确认收入总额最高的套餐名称写入
/root/sql/q7.txt。
参考
- 导入：
sqlite3 /root/sql/support.db < /opt/data/support.sql - 查询：
sqlite3 /root/sql/support.db "SELECT COUNT(*) FROM customers;" - 常见错误 1：在第 5 步中使用
closed_at = ''进行比较。NULL 与任何值比较都不会得到真，因此必须使用IS NULL。 - 常见错误 2：在第 8 步中，因为结果与直觉不符而怀疑查询。套餐等级与收入规模是两个不同的问题，向客户说明这一事实本身就是有价值的发现。
导入快照
创建 /root/sql，并将 /opt/data/support.sql 导入 /root/sql/support.db。
sqlite3 通过标准输入接收 SQL。请将 /opt/data/support.sql 导入 /root/sql/support.db。
统计客户总数
将客户总数写入 /root/sql/q1.txt。
从最简单的问题开始。这个数字将作为后续所有比率的分母。
计算已确认收入总额
将 status 为 paid 的订单金额总和写入 /root/sql/q2.txt。
并非所有订单都属于收入。请先查看 status 列中有哪些值。
找出收入最高的客户
将已确认收入最高的客户姓名写入 /root/sql/q3.txt。
因为需要客户姓名，所以必须进行连接。请按已确认收入分组并排序。
统计未解决工单数量
将尚未关闭的工单数量写入 /root/sql/q4.txt。
未关闭工单的 closed_at 为 NULL。空字符串与 NULL 不同，因此必须使用 IS NULL。
计算 COUNT(closed_at) 的结果
将 SELECT COUNT(closed_at) FROM tickets; 的结果写入 /root/sql/q5.txt。
COUNT(列) 只统计该列不为 NULL 的行。总数为 80，请思考为什么这里的结果不同。
统计没有工单的客户
将从未创建过工单的客户数量写入 /root/sql/q6.txt。
不存在的行不会被连接。可以在 LEFT JOIN 后统计右侧为 NULL 的行，或使用 NOT EXISTS。
找出套餐收入第一名
将已确认收入总额最高的套餐名称写入 /root/sql/q7.txt。
按套餐分组并汇总已确认收入。结果可能与你的预期不同，而这正是本步骤的要点。