🌿挑战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
;
数值运算相关函数
- (重要)取整函数: ->
round select round(3.1415926) from dual;
- (重要)指定精度取整函数(四舍五入): r
ound(double a, int d) -> select round(3.1415926,4) from dual;
- (重要)向下取整函数:
floor(double a) ->select floor(3.1415926) from dual;
- (重要)向上取整函数:
ceil(double a) ->select ceil(3.1415926) from dual;
- (重要)向上取整函数:
ceiling -> select ceiling(3.1415926) from dual;
- 取随机数函数(返回一个
0到1
范围内的随机数。如果指定种子seed,则会等到一个稳定的随机数序列):rand(),rand(int seed)-> select rand() from dual;
- 自然指数函数(返回自然对数e的a次方):
exp(double a) -> select exp(2) from dual;
- 以10为底对数函数:
log10(double a)
- 以2为底对数函数:
log2(double a)
- 对数函数(返回以base为底的a的对数): l
og(double base, double a)
- 幂运算函数(返回a的p次幂):
pow(double a, double p) ->select pow(2,4) from dual;
- 幂运算函数:
power(同pow())
- 开平方函数:
sqrt(double a) -> select sqrt(16) from dual;
- 二进制函数(返回a的二进制代码):
bin(BIGINT a) -> select bin(7) from dual;
- 十六进制函数:
hex(BIGINT a) -> select hex(17) from dual;
- 反转十六进制函数:
unhex(string a) -> select unhex(‘616263’) from dual;
- 进制转换函数(将数值num从from_base进制转化到to_base进制):
conv(BIGINT num, int from_base, int to_base) -> select conv(17,10,16) from dual;
- (重要)绝对值函数:
abs(double a) abs(int a)
- 正取余函数(返回正的a除以b的余数):
pmod(int a, int b),pmod(double a, double b)
- 正弦函数:
sin
- 反正弦函数:
asin
- 余弦函数:
cos
- 反余弦函数:
acos
- positive函数(返回a):
positive(int a), positive(double a)
- negative函数(返回-a):
negative(int a), negative(double a)
in 、exits、 left join的区别
- 执行效率:
exists <= in <= join
上题中left join
会产生笛卡尔积容易出现数据倾斜 - 通常情况下,两个表中数据是一个较大,一个较小,这种情况下,in适合子查询表
B
数据小的情况;exists
适合子查询表B
数据大的情况为什么呢?
in
在查询的时候,先查子查询的表B
,然后将内表和外表做一个笛卡尔积,然后按照条件筛选,所以子查询表比较小的时候,in的速度较快;
而exists
是对外表A
做loop
循环,每次loop
循环再对内表B
进行查询,即我们先查询的不是子查询B
的内容,而是查我们的主查询的表A
,所以子查询表数据比较大的时候,exists
的速度较快
not in
和not exists
:无论哪个表大,not exists
总是比not in
执行效率高- 原因:
not in
没有用到索引,同时,内外表都要进行全表扫描;
而exists
的子查询依然可以使用索引。
- 原因:
关于sql的规范问题
- 尽量把逗号放在前面,为什么?
- 方便排查,不会遗漏逗号
- 方便注释,可以单行直接注释,不用再改逗号
- 排版看起来更紧密,我用
datagrip
快捷键ctrl + alt+ L
可以快速缩进- 特别用
datagrip
进行快速复制一行的时候,只需要Ctrl + D
不需要关注逗号,写起来很快- 使用关键字大写,养成好习惯
- 参考
hive sql
官方conf
地址:https://cwiki.apache.org/confluence/display/Hive/LanguageManual