挑战100天不停更第32天 -行程和用戶：查询乘客取消率(包含数值运算函数总结和in 、exits、 left join的效率对比)

🌿挑战100天不停更，刷爆 hive sql🧲

详情请点击🔗我的专栏🖲,共同学习,一起进步~

NUM: 第32天 -行程和用戶：查询乘客取消率(包含数值运算函数总结和in 、exits、 left join的效率对比)

今天的sql是通过两张表,用户表,行程表, 计算行程的取消率, 注意:这里的取消率包含乘客和司机的取消率
所用到的函数包括: sum() round() if()等
今天主要复习两个问题:(由于内容比较多, 我放在文章后面~)
一 关于数值的函数(25个)
二 使用in() exits() 和 join的区别(本次的sql 采用两种方式 in 和 left join )

🧨那就不废话了, 开始今天的刷题~~🧨

🎈表结构

t_33_user:Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

t_33_trips:表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’,‘cancelled_by_client’)。

🎉建表并插入数据

-- Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
CREATE TABLE t_33_trips
(
    id         int,
    client_Id  int,
    driver_Id  int,
    city_Id    int,
    status     string,
    request_at string
);

INSERT INTO t_33_trips (id, client_Id, driver_Id, city_Id, status, request_at)
VALUES (1, 1, 10, 1, 'completed', '2013-10-01'),
       (2, 2, 11, 1, 'cancelled_by_driver', '2013-10-01'),
       (3, 3, 12, 6, 'completed', '2013-10-01'),
       (4, 4, 13, 6, 'cancelled_by_client', '2013-10-01'),
       (5, 1, 10, 1, 'completed', '2013-10-02'),
       (6, 2, 11, 6, 'completed', '2013-10-02'),
       (7, 3, 12, 6, 'completed', '2013-10-02'),
       (8, 2, 12, 12, 'completed', '2013-10-03'),
       (9, 3, 10, 12, 'completed', '2013-10-03'),
       (10, 4, 13, 12, 'cancelled_by_driver', '2013-10-03');
       
-- Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。
CREATE TABLE t_33_user
(
    user_id int,
    banned  string,
    role    string
);
INSERT INTO t_33_user (user_id, banned, role)
VALUES (1, 'No', 'client'),
       (2, 'Yes', 'client'),
       (3, 'No', 'client'),
       (4, 'No', 'client'),
       (10, 'No', 'driver'),
       (11, 'No', 'driver'),
       (12, 'No', 'driver'),
       (13, 'No', 'driver');

👓问题:查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率保留两位小数。

取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

查询结果

🧨SQL

-- 写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

-- 取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)
-- 解题一:
SELECT request_at
     , round(sum(if(trips.status == 'completed', 0, 1)) / count(1), 2) AS cancellation_rate
FROM t_33_trips trips
WHERE trips.client_Id NOT IN (
    SELECT user_id
    FROM t_33_user
    WHERE t_33_user.banned = 'yes'
)
  AND trips.driver_Id NOT IN (
    SELECT user_id
    FROM t_33_user
    WHERE t_33_user.banned == 'yes'
)
  AND trips.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at
ORDER BY request_at
;



-- 解题二:
SELECT request_at
     , round(sum(if(t_33_trips.status == 'completed', 0, 1)) / count(1), 2) AS cancellation_rate
FROM t_33_trips
         LEFT JOIN t_33_user t1 ON t1.user_id = client_Id AND t1.banned != 'yes'
         LEFT JOIN t_33_user t2 ON t2.user_id = driver_Id AND t2.banned != 'yes'
WHERE t_33_trips.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at
ORDER BY request_at
;

数值运算相关函数

1. (重要)取整函数: ->round select round(3.1415926) from dual;
2. (重要)指定精度取整函数(四舍五入): round(double a, int d) -> select round(3.1415926,4) from dual;
3. (重要)向下取整函数: floor(double a) ->select floor(3.1415926) from dual;
4. (重要)向上取整函数: ceil(double a) ->select ceil(3.1415926) from dual;
5. (重要)向上取整函数: ceiling -> select ceiling(3.1415926) from dual;
6. 取随机数函数(返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列): rand(),rand(int seed)-> select rand() from dual;
7. 自然指数函数(返回自然对数e的a次方): exp(double a) -> select exp(2) from dual;
8. 以10为底对数函数: log10(double a)
9. 以2为底对数函数: log2(double a)
10. 对数函数(返回以base为底的a的对数): log(double base, double a)
11. 幂运算函数(返回a的p次幂): pow(double a, double p) ->select pow(2,4) from dual;
12. 幂运算函数: power(同pow())
13. 开平方函数: sqrt(double a) -> select sqrt(16) from dual;
14. 二进制函数(返回a的二进制代码): bin(BIGINT a) -> select bin(7) from dual;
15. 十六进制函数: hex(BIGINT a) -> select hex(17) from dual;
16. 反转十六进制函数: unhex(string a) -> select unhex(‘616263’) from dual;
17. 进制转换函数(将数值num从from_base进制转化到to_base进制): conv(BIGINT num, int from_base, int to_base) -> select conv(17,10,16) from dual;
18. (重要)绝对值函数:abs(double a) abs(int a)
19. 正取余函数(返回正的a除以b的余数): pmod(int a, int b),pmod(double a, double b)
20. 正弦函数: sin
21. 反正弦函数: asin
22. 余弦函数: cos
23. 反余弦函数: acos
24. positive函数(返回a): positive(int a), positive(double a)
25. negative函数(返回-a): negative(int a), negative(double a)
    

in 、exits、 left join的区别

1. 执行效率:exists <= in <= join 上题中 left join会产生笛卡尔积容易出现数据倾斜
2. 通常情况下，两个表中数据是一个较大，一个较小，这种情况下，in适合子查询表B 数据小的情况；
   exists适合子查询表B 数据大的情况
   1. 为什么呢?
   2. in在查询的时候，先查子查询的表B，然后将内表和外表做一个笛卡尔积，然后按照条件筛选，所以子查询表比较小的时候，in的速度较快；
      而exists 是对外表A做loop循环，每次loop循环再对内表B进行查询，即我们先查询的不是子查询B的内容，而是查我们的主查询的表A，所以子查询表数据比较大的时候，exists的速度较快
3. not in 和 not exists:无论哪个表大，not exists 总是比not in执行效率高
   1. 原因：not in没有用到索引，同时，内外表都要进行全表扫描；
      而 exists的子查询依然可以使用索引。
      

关于sql的规范问题

1. 尽量把逗号放在前面,为什么?
   1. 方便排查,不会遗漏逗号
   2. 方便注释,可以单行直接注释,不用再改逗号
   3. 排版看起来更紧密,我用datagrip快捷键ctrl + alt+ L可以快速缩进
   4. 特别用datagrip进行快速复制一行的时候,只需要Ctrl + D不需要关注逗号，写起来很快
2. 使用关键字大写,养成好习惯
3. 参考 hive sql 官方conf地址:https://cwiki.apache.org/confluence/display/Hive/LanguageManual