第1题 手写HQL
表结构：uid，subject_id，score
求：找出所有科目成绩都大于某一学科平均成绩的学生
数据集如下：
1001 01 90
1001 02 90
1001 03 90
1002 01 85
1002 02 85
1002 03 70
1003 01 70
1003 02 70
1003 03 85
建表语句
create table score(uid string,subject_id string,score int)row format delimited fields terminated by '\t';
思路
- 先求出每个科目的平均成绩 —t1
- 再用 if 判断 学生成绩 是否大于平均成绩；大于则记为0 否则记为1，用别名flag记录 —t2
- 因为是求学生的所有成绩都大于平均成绩（即每科都大于该科的平均成绩），所以要根据id分组，然后分组后having过滤出sum(flag)=0即可（因为只要有一科成绩大于平均成绩，该记录的flag就为0，只要sum(flag)=0就说明该学生的所以科目成绩都大于平均成绩）
1）求出每个学科平均成绩
SELECT
uid,
score,
avg(score) OVER (PARTITION BY subject_id) score_avg
FROM
hql_score --t1
2）根据是否大于平均成绩记录flag，大于则记为0否则记为1
SELECT
uid,
if(score>avg_score,0,1) flag
FROM
t1 --t2
3）根据学生id进行分组统计flag的和，和为0则是所有学科都大于平均成绩
SELECT
uid
FROM
t2
GROUP BY uid
HAVING sum(flag)=0;
5）最终SQL
SELECT
uid
FROM
(
SELECT
uid,
if(score > score_avg, 0, 1) flag
FROM
(
SELECT
uid,
score,
avg(score) OVER (PARTITION BY subject_id) score_avg
FROM
hql_score
) t1
) t2
GROUP BY
uid
HAVING
sum(flag) = 0
------------------------------------------
--如果用where过滤的话，就需要多套一层子查询
SELECT
uid
FROM
(
SELECT
uid,
sum(flag) flag
FROM
(
SELECT
uid,
if(score > score_avg, 0, 1) flag
FROM
(
SELECT
uid,
score,
avg(score) OVER (PARTITION BY subject_id) score_avg
FROM
hql_1
) t1
) t2
GROUP BY
uid
) t3
WHERE
t3.flag = 0
第2题 手写HQL
我们有如下的用户访问数据
userId visitDate visitCount
u01 2017/1/21 5
u02 2017/1/23 6
u03 2017/1/22 8
u04 2017/1/20 3
u01 2017/1/23 6
u01 2017/2/21 8
U02 2017/1/23 6
U01 2017/2/22 4
要求使用SQL统计出每个用户的累积访问次数，如下表所示：
用户id 月份 小计 累积
u01 2017-01 11 11
u01 2017-02 12 23
u02 2017-01 12 12
u03 2017-01 8 8
u04 2017-01 3 3
建表语句
CREATE TABLE action
(
userId STRING,
visitDate STRING,
visitCount int
)
ROW FORMAT delimited FIELDS TERMINATED BY "\t";
思路
- 先将日期转成指定的格式 别名date，即yyyy-MM —t1
- 再统计出每人每月的访问量(mn_count)，即按照id和date分组 —t2
- 再求出按月累计的访问量，即对sum(mn_count)进行开窗，按照id分组，日期升序，有order by默认上无边界到当前行 ```plsql 1）将日期转成指定的格式 select userId, date_format(regexp_replace(visitDate,’/‘,’-‘),’yyyy-MM’) mn, visitCount from action; —t1
2）计算每人单月访问量 select userId, mn, sum(visitCount) mn_count from t1 group by userId,mn; —t2 3）计算按月累计的访问量 select userId, mn, mn_count, sum(mn_count) over(partition by userId order by mn) from t2;
4）最终SQL
select
userId,
mn,
mn_count,
sum(mn_count) over(partition by userId order by mn)
from
(
select
userId,
mn,
sum(visitCount) mn_count
from
(
select
userId,
date_format(regexp_replace(visitDate,’/‘,’-‘),’yyyy-MM’) mn,
visitCount
from
action
)t1
group by userId,mn
)t2
<a name="bTIsz"></a>
# 第3题 手写HQL
<br />有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，请统计：<br /> <br />1）每个店铺的UV（访客人数）<br /> <br />2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数<br /> <br />数据集如下：<br />u1 a<br />u2 b<br />u1 b<br />u1 a<br />u3 c<br />u4 b<br />u1 a<br />u2 c<br />u5 b<br />u4 b<br />u6 c<br />u2 c<br />u1 b<br />u2 a<br />u2 a<br />u3 a<br />u5 a<br />u5 a<br />u5 a<br /> <br />1）建表语句
```plsql
CREATE TABLE visit
(
user_id STRING,
shop STRING
)
ROW FORMAT delimited FIELDS TERMINATED BY '\t';
思路（每个店铺的UV（访客数））
一、每个店铺的UV（访客数）
- 因为题目说，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，所以一个用户可能会访问同一个店铺的不同商品，即一个用户会在同一个店铺产生多个访问日志
- 题目是求每个店铺的访客数（即人数，不是次数），所以先要去重，可以根据shop和user_id分组去重
再对店铺分组，求出每个店铺的访客数（即count(user_id)）
或者直接对stop分组，统计user_id的人数，用distinct去重，即（count(distinct user_id)） ```plsql 1）先去重，确保每个店铺只对应一个userid SELECT shop, user_id FROM visit GROUP BY shop, user_id —t1 2）根据shop分组，求出每个店铺的userid个数即可 SELECT shop, count(*) FROM t1 GROUP BY t1.shop
3）最终sql SELECT t1.shop, count(*) FROM ( SELECT shop, user_id FROM visit GROUP BY shop, user_id ) t1 GROUP BY
t1.shop;
SELECT shop, count(DISTINCT user_id) FROM visit GROUP BY shop
<br />思路（每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数）<br /> <br />二、每个店铺访问次数top3的访客信息<br />1、先求出每个店铺被每个用户访问的次数（即根据shop和userI分组，就是一个店铺一个用户的访问次数，再count即可）<br />2、因为是求topN，所以肯定要算出rank，即根据店铺分区，访问次数count降序，算出排名<br />3、再取top3，即where过滤出rank<=3的即可
```plsql
1）查询每个店铺被每个用户访问次数
SELECT
user_id,
shop,
count(*) ct
FROM
visit
GROUP BY
shop, user_id; --t1
2）计算每个店铺被用户访问次数排名
SELECT
shop,
user_id,
ct,
rank() OVER (PARTITION BY shop ORDER BY ct desc) rk
FROM
t1; --t2
3）取每个店铺排名前3的
SELECT
shop,
user_id,
ct
FROM
t2
WHERE
rk<=3;
4）最终sql
SELECT
shop,
user_id,
ct
FROM
(
SELECT
shop,
user_id,
ct,
rank() OVER (PARTITION BY shop ORDER BY ct desc) rk
FROM
(
SELECT
user_id,
shop,
count(*) ct
FROM
visit
GROUP BY
shop, user_id
) t1
) t2
WHERE
rk <= 3
第4题 手写HQL
已知一个表order，有如下字段：dt，order_id，user_id，amount。
请给出sql进行统计：数据样例：2017-01-01,10029028,1000003251,33.57。
1）给出 2017年每个月的订单数、用户数、总成交金额。
2）给出2017年11月的新客数(指在11月才有第一笔订单)，求某月新客数
建表语句
CREATE TABLE order_tab
(
dt STRING,
order_id STRING,
user_id STRING,
amount decimal(10, 2)
)
ROW FORMAT delimited FIELDS TERMINATED BY '\t';
思路（给出 2017年每个月的订单数、用户数、总成交金额）
1、因为求的是2017年的数据，所以先要过滤出2017年的数据，再根据月份分组
2、求每个月份的明细，肯定要根据月份分组
SELECT
date_format(dt, 'yyyy-MM'), --每个月份
count(order_id), --每个月份的订单数
count(user_id), --每个月份的用户数
sum(amount) --每个月份的总成交额
FROM
order_tab
WHERE
date_format(dt, 'yyyy') = '2017' --先过滤出2017年的数据
GROUP BY
date_format(dt, 'yyyy-MM'); --根据月份分组
思路（给出2017年11月的新客数（指在11月才有第一笔订单））
- 求每月新客数，可以使用组过滤的方式，即按照用户分组，在每组里筛选出符合条件的用户，一般就是gourp by + having的思路
- 这里求的是某月的新客数，要先根据用户分组，再（having）分组过滤出，最小时间是某月的；
- 注意：不能先过滤时间，再根据用户分组，因为一个用户可能再前面的月份也下了单，如果先过滤时间，是无法判断出是否是第一次下单
SELECT count(user_id) FROM order_tab GROUP BY user_id HAVING date_format(min(dt), 'yyyy-MM') = '2017-11';
第5题 手写HQL
有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户）日期 用户 年龄
数据集
2019-02-11,test_1,23
2019-02-11,test_2,19
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-12,test_2,19
2019-02-13,test_1,23
2019-02-15,test_2,19
2019-02-16,test_2,19
建表语句
CREATE TABLE user_age
(
dt STRING,
user_id STRING,
age int
) ROW FORMAT delimited FIELDS TERMINATED BY ',';
思路（求得所有用户和活跃用户的总数及平均年龄）
- 主要是求出活跃用户数，题目是指连续两天都有访问记录的用户，即可以转化为求连续两天有访问记录的用户数
- 求连续N天活跃数，老套路，先用开窗求出行号/排行，利用等差数列（日期减去行号/排行获得差值），如果是连续的，那么差值一定是相等的，再根据用户和差值分组，然后having过滤出，count(diff)>=N的记录，就可以求出连续N天的用户 ```plsql 一、先求出活跃用户数和平均年龄，再求出所有用户数和平均年龄，最后可以union all连接起来 1）先按照日期以及用户分组，开窗根据用户分区、日期排序求出排行 select dt, user_id, min(age) age, —保留age字段，因为age字段没加入有分组，所以不能单独出现，但是age每个用户只有一个并且是唯一的，所以用min、max函数保留都可以 rank() over(partition by user_id order by dt) rk from user_age group by dt, user_id; —t1
2）计算日期及排名的差值 diff select user_id, age, date_sub(dt,rk) diff from t1; —t2
3）过滤出差值大于等于2的，即为连续两天活跃的用户 select user_id, min(age) age —这里也是一样，保留age字段 from t2 group by user_id, diff having count(diff) >= 2; —t3
4）对数据进行去重处理（一个用户可以在两个不同的时间点连续登录）， 例如：a用户在1月10号1月11号以及1月20号和1月21号4天登录，就会有两次连续登录，因为是求人数，所以要去重 select user_id, min(age) age —保留age字段 from t3 group by user_id; —t4
5）计算活跃用户（两天连续有访问）的人数以及平均年龄 select count() twice_count, —连续两天访问的人数 cast(sum(age)/count() as decimal(10,2)) twice_count_avg_age —连续两天访问的人数的平均年龄 from t4;
二、求所有用户数和平均年龄 6）对全量数据按照用户去重 select user_id, —每个用户 min(age) age —每个用户的年龄 from user_age group by user_id; —去重 —t5
7）计算所有用户的数量以及平均年龄 select count() user_total_count, —所有用户数 cast((sum(age)/count()) as decimal(10,1)) user_total_avg_age —所有用户的平均年龄 from t5;
‘三、对上述的结果进行union all整合’ SELECT —活跃用户的人数和平均年龄 0 user_total_count, 0 user_total_avg_age, count() twice_count, cast(sum(age) / count() AS decimal(10, 2)) twice_count_avg_age FROM ( SELECT —一个用户可能多次连续两天登录，因为是求人数，所有根据用户分组去重 user_id, min(age) age FROM ( SELECT —根据用户和差值分组，过滤出相同差值个数大于等于2的，即连续2天 user_id, min(age) age FROM ( SELECT —求出日期和排行的差值 user_id, age, date_sub(dt, rk) diff FROM ( SELECT —开窗求出排行 dt, user_id, min(age) age, rank() OVER (PARTITION BY user_id ORDER BY dt) rk FROM user_age GROUP BY dt, user_id ) t1 ) t2 GROUP BY user_id, diff HAVING count() >= 2 ) t3 GROUP BY user_id ) t4 UNION ALL —将两个结果集进行连接 SELECT —求出所有用户数和平均年龄 count() user_total_count, cast((sum(age) / count(*)) AS decimal(10, 1)), 0 twice_count, 0 twice_count_avg_age FROM ( SELECT —求出所以用户，因为是求人数，一个用户可能多次登录，所以去重 user_id, min(age) age FROM user_age GROUP BY user_id ) t5; t6
—求和并拼接为最终SQL SELECT sum(user_total_count), sum(user_total_avg_age), sum(twice_count), sum(twice_count_avg_age) FROM ( SELECT 0 user_total_count, 0 user_total_avg_age, count() twice_count, cast(sum(age) / count() AS decimal(10, 2)) twice_count_avg_age FROM ( SELECT user_id, min(age) age FROM ( SELECT user_id, min(age) age FROM ( SELECT user_id, age, date_sub(dt, rk) flag FROM ( SELECT dt, user_id, min(age) age, rank() OVER (PARTITION BY user_id ORDER BY dt) rk FROM user_age GROUP BY dt, user_id ) t1 ) t2 GROUP BY user_id, flag HAVING count() >= 2) t3 GROUP BY user_id ) t4 UNION ALL SELECT count() user_total_count, cast((sum(age) / count(*)) AS decimal(10, 1)), 0 twice_count, 0 twice_count_avg_age FROM ( SELECT user_id, min(age) age FROM user_age GROUP BY user_id ) t5
<a name="w0u9F"></a>
# 第6题 手写HQL
<br />请用sql写出所有用户中在2017年10月份第一次购买商品的金额，表ordertable字段<br /> <br />（购买用户：user_id，金额：money，购买时间：payment_time(格式：2017-10-01)，订单id：order_id）<br /> <br />建表语句
```plsql
CREATE TABLE ordertable
(
user_id STRING,
money int,
payment_time STRING,
order_id STRING
)
ROW FORMAT delimited FIELDS TERMINATED BY '\t';
思路
- 求所有用户在2017年10月份第一次购买商品的金额
- 可以先过滤出2017年10月份的数据，因为一个用户可能在10月份购买过多次，所有可以根据用户分组，然后min(支付时间)，时间最早的就是第一次购买的
- 因为要求商品的金额，但是刚刚过滤出的数据没有商品金额字段，所以就需要用到自连接，用户和支付时间做连接条件，这样可以唯一确定一条记录 ```plsql 1）先过滤出在2017年10月份，第一次购买商品的用户 SELECT user_id, min(paymenttime) payment_time —得出最小时间 FROM ordertable WHERE date_format(payment_time, ‘yyyy-MM’) = ‘2017-10’ GROUP BY user_id —根据用户分组
2）通过自连接，关联商品的金额 SELECT t1.user_id, t1.payment_time, o.money FROM ( SELECT user_id, min(paymenttime) payment_time FROM hql_6 WHERE date_format(payment_time, ‘yyyy-MM’) = ‘2017-10’ GROUP BY user_id ) AS t1 JOIN ordertable AS o —自连接 ON t1.user_id = o.user_id AND t1.payment_time = o.payment_time; —用户和支付时间做连接条件
<a name="spvAT"></a>
# 第7题 手写HQL
<br />有一个线上服务器访问日志格式如下<br /> <br /> 时间 接口 ip地址<br /> <br />2016-11-09 11：22：05 /api/user/login 110.23.5.33<br /> <br />2016-11-09 11：23：10 /api/user/detail 57.3.2.16<br /> <br />.....<br /> <br />2017-11-09 23：59：40 /api/user/login 200.6.5.166<br /> <br />求2016年11月9号下午14点（14-15点），访问/api/user/login接口的Top10的ip地址<br /> <br />1）建表语句
```plsql
CREATE TABLE log
(
time STRING,
interface STRING,
ip STRING
)
ROW FORMAT delimited FIELDS TERMINATED BY '\t';
2）思路
1、spark三部曲：过滤、列裁剪、去重
2、这里也可以借鉴，先过滤出2016年11月9号下午14点（14-15点）这个时间范围，并且是访问/api/user/login接口的数据
3、然后再统计次数，要用到聚合函数，就需要分组，所以就要根据ip和接口确定唯一一条记录
4、然后再order by降序排序，limit取前10即可
SELECT
interface,
ip,
count(*) count_ip --统计ip次数
FROM
log
WHERE
date_format(time, 'yyyy-MM-dd HH') >= '2016-11-09 14'
AND date_format(time, 'yyyy-MM-dd HH') <= '2016-11-09 15'
AND interface = '/api/user/login' --先过滤出符合条件的数据
GROUP BY
interface, ip --分组确定唯一记录
ORDER BY
count_ip DESC --根据次数降序排序
LIMIT 10;
第8题 手写HQL
有一个账号表如下，请写出SQL语句，查询各自区组的 gold 排名前十的账号（分组取前10）TopN问题
建表语句
CREATE TABLE `account`
(
`dist_id` int（11）DEFAULT NULL COMMENT '区组id',
`account` varchar（100）DEFAULT NULL COMMENT '账号',
`gold` int（11）DEFAULT 0 COMMENT '金币'
);
思路
1、TopN问题，所以可以直接先开窗排行，用组id分区、gold降序排序 别名rk —t1
2、再对t1表进行where过滤，rk<=10的即是top10的记录
SELECT
dist_id,
account,
gold
FROM
(
SELECT
dist_id,
account,
gold,
rank() OVER (PARTITION BY dist_id ORDER BY gold DESC) rk
FROM
hql_8
) t1
WHERE
rk <= 10;
第9题 手写HQL
1）有三张表分别为会员表（member）销售表（sale）退货表（regoods）
（1）会员表有字段memberid（会员id，主键）credits（积分）；
（2）销售表有字段memberid（会员id，外键）购买金额（MNAccount）；
（3）退货表中有字段memberid（会员id，外键）退货金额（RMNAccount）。
2）业务说明
（1）销售表中的销售记录可以是会员购买，也可以是非会员购买。（即销售表中的memberid可以为空）；
（2）销售表中的一个会员可以有多条购买记录；
（3）退货表中的退货记录可以是会员，也可是非会员；
（4）一个会员可以有一条或多条退货记录。
查询需求：分组查出销售表中所有会员购买金额，同时分组查出退货表中所有会员的退货金额，把会员id相同的购买金额-退款金额得到的结果更新到表会员表中对应会员的积分字段（credits）
数据集
sale
1001 50.3
1002 56.5
1003 235
1001 23.6
1005 56.2
25.6
33.5
regoods
1001 20.1
1002 23.6
1001 10.1
23.5
10.2
1005 0.8
1）建表
CREATE TABLE member
(
memberid STRING,
credits double
) ROW FORMAT delimited FIELDS TERMINATED BY '\t';
CREATE TABLE sale
(
memberid STRING,
MNAccount double
) ROW FORMAT delimited FIELDS TERMINATED BY '\t';
CREATE TABLE regoods
(
memberid STRING,
RMNAccount double
) ROW FORMAT delimited FIELDS TERMINATED BY '\t'
2）思路
—查询需求：分组查出销售表中所有会员购买金额，同时分组查出退货表中所有会员的退货金额，把会员id相同的购买金额-退款金额得到的结果更新到表会员表中对应会员的积分字段（credits）
1、根据业务可知，销售表和退货表都有非会员的操作记录，所有需要先过滤非会员的记录，再根据会员id分组
2、再将两张表join，连接条件就是会员id
INSERT INTO table hql_9_member
SELECT
t1.memberid,
MNAccount - RMNAccount --购买金额-退款金额=积分字段（credits）
FROM
(
SELECT --查询出会员所有的购买金额
memberid,
sum(MNAccount) MNAccount
FROM
sale
WHERE
memberid IS NOT NULL OR memberid != ''
GROUP BY
memberid
) t1
INNER JOIN
(
SELECT --所有会员的退款金额
memberid,
sum(RMNAccount) RMNAccount
FROM
regoods
WHERE
memberid != '' OR memberid IS NOT NULL
GROUP BY
memberid
) t2
ON
t1.memberid = t2.memberid;
第10题 手写HQL
一、分组过滤
用一条SQL语句查询出每门课都大于80分的学生姓名
name scorename score
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
思路
- 因为是学生的每门课程都大于80，所有是从学生的角度
- 可以根据学生分组，然后分组过滤出having最小的成绩大于80，即最小的成绩都大于80，说明其他成绩肯定在80以上
SELECT `name` FROM table GROUP BY `name` HAVING min(score) > 80
二、IN 和 NOT IN
删除除了自动编号不同, 其他都相同的学生冗余信息
学生表如下：
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005003 王五 0001 数学 90
4 2005002 李四 0001 数学 89
5 2005001 张三 0001 数学 69
思路
- 因为是删除重复数据，所以可以先找出需要的数据，即根据相同字段来分组，因为有些数据重复（可能出现多条记录），所以保留前面的数据即可（id小的），即min(id)
- 上面就找到需要的数据了，然后再把结果集封装成一个临时表
- 再用where id not in对临时表过滤，过滤掉临时表不存在的数据（即重复数据）
注意：不能直接用not in结果集，这样会报并发修改异常，因为同时对同一个表进行删除和查询，所以可以把结果集封装成临时表（另一张表），这样就不是同时删除和查询一张表了
DELETE
FROM
tablename
WHERE 自动编号 NOT IN (
SELECT * FROM (
SELECT min(自动编号)
FROM tablename
GROUP BY 学号, 姓名, 课程编号, 课程名称, 分数
) temp
)
三、自连接
一个叫team的表，里面只有一个字段name，一共有4条纪录，分别是a，b，c，d，对应四个球队，现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合
思路：
需要自连接实现
如果两张表没有连接条件，会产生笛卡尔乘积，结果会有 4*4=16条，
现在在最终结果集上加了条件t1.name < t2.name，意味着进一步筛选，最终只有6条数据，即：
a b
a c
a d
b c
b d
c d
第一个球队的名字按照字母顺序应该小于第二个球队的名字。
这样做的主要目的，是为了：
1. 排除无意义的组合，如a a, b b, c c, d d
2. 排除重复数据, 如 有了a b就不再需要b a，有了b d, 就不需要d b.
SELECT
t1.name,
t2.name
FROM
team t1
JOIN team t2
ON t1.name < t2.name
四、select子查询
表table如下：
year month amount
2020 1 1.1
2020 2 1.2
2020 3 1.3
2020 4 1.4
2019 1 2.1
2019 2 2.2
2019 3 2.3
2019 4 2.4
查成这样一个结果
year m1 m2 m3 m4
2019 1.1 1.2 1.3 1.4
2020 2.1 2.2 2.3 2.4
思路
- 因为结果只有两行，是已year为单位，所以肯定要根据year分组
- 列就是每个月份的数据，可以用select子查询的到
SELECT t1.year, (SELECT amount FROM tablename temp WHERE temp.month = 1 AND temp.year = t1.year) m1, (SELECT amount FROM tablename temp WHERE temp.month = 2 AND temp.year = t1.year) m2, (SELECT amount FROM tablename temp WHERE temp.month = 3 AND temp.year = t1.year) m3, (SELECT amount FROM tablename temp WHERE temp.month = 4 AND temp.year = t1.year) m4 FROM tablename t1 GROUP BY year
五、复制表
既复制表结构也复制表内容的SQL语句：
CREATE TABLE tab_new AS SELECT * FROM tab_old;
只复制表结构不复制表内容的SQL语句：
CREATE TABLE tab_new AS SELECT * FROM tab_old WHERE 1=2;
不复制表结构，只复制内容的sql语句：
INSERT INTO tab_new SELECT * FROM tab_old;
注意：mysql不支持 SELECT INTO FROM 这种语句<br /> SELECT vale1, value2 INTO Table2 from Table1 不支持<br />
六、IF判断
原表：
courseid coursename score
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
为了便于阅读，查询此表后的结果显式如下(及格分数为60)：
courseid coursename score mark
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
思路：
加一个字段mark列
SELECT
courseid,
coursename,
score,
if(score >= 60, "pass", "fail") AS mark
FROM
course
七、IN + 分组过滤
购物信息表：shop
name product amount
A 甲 2
B 乙 4
C 丙 1
A 甲 2
B 丙 5
A 甲 3
给出所有购入商品为两种或两种以上的购物人记录
思路：
1、因为是求两种或两种以上的购物人数记录，所以肯定要去重，因为一个用户可能多次购买相同种类的商品
2、去重思想有三种
3、因为要求显示记录信息（产品、金额），因为过滤后的表没有其他字段的，只有userid字段，所以需要和原表进行关联
4、多表关联有两种方式：where 字段 in、inner join都行
SELECT
userid, product, amount
FROM
hql_10_shop
WHERE
userid IN (
SELECT
userid
FROM
(
SELECT
userid
FROM
hql_10_shop
GROUP BY userid, product
) t1
GROUP BY
userid
HAVING
count(*) >= 2
)
---------------------------------------------
SELECT
a.userid,
product,
amount
FROM
hql_10_shop a
JOIN (
SELECT
userid
FROM
(
SELECT
userid
FROM
hql_10_shop
GROUP BY userid, product
) t1
GROUP BY
userid
HAVING
count(*) >= 2
) b
on a.userid = b.userid
八、case when或if
info 表
date result
2020-10-09 win
2020-10-09 lose
2020-10-09 lose
2020-10-09 lose
2020-10-10 win
2020-10-10 lose
2020-10-10 lose
如果要生成下列结果, 该如何写sql语句?
date win lose
2020-10-09 2 2
2020-10-10 1 2
思路：
1、相当于求win和lose的个数
2、可以使用if或者case when来判断
3、也可以用inner join
SELECT
date,
sum(CASE WHEN result = 'win' THEN 1 ELSE 0 END) AS 'win',
sum(CASE WHEN result = 'lose' THEN 1 ELSE 0 END) AS 'lose'
--sum(IF(result = 'win', 1, 0)) AS 'win',
--sum(IF(result = 'lose', 1, 0)) AS 'lose',
FROM
hql_10_case
GROUP BY
date;
--------------------------------
SELECT
a.date,
a.result AS win,
b.result AS lose
FROM
(SELECT date, count(result) AS result FROM hql_10_case WHERE result = 'win' GROUP BY date) AS a
JOIN
(SELECT date, COUNT(result) AS result FROM hql_10_case WHERE result = 'lose' GROUP BY date) AS b
ON a.date = b.date;
一、去重，TopN问题
有一个订单表order。已知字段有：order_id(订单ID)，user_id(用户ID)，amount(金额)，pay_datetime(付费时间)，channel_id(渠道ID)，dt(分区字段)。
- 在Hive中创建这个表。
- 查询 dt=‘2020-09-01‘ 里每个渠道的订单数，下单人数（去重），总金额。
- 查询 dt=‘2020-09-01‘ 里每个渠道的金额最大3笔订单。
- 有一天发现订单数据重复，请分析原因
CREATE external TABLE ORDER(
order_id INT,
user_id INT,
amount DECIMAL(16,2),
pay_datatime TIMESTAMP,
channel_id INT
)partitioned BY(dt STRING)
ROW FORMAT delimited FIELDS TERMINATED BY '\t';
思路
1、因为是订单表，所以不用分组，直接求count的话，统计的就是订单数
2、求下单人数，就count(user_id)，因为一个user可能下多次下单，所以要去重
3、求订单总金额，就直接sum(amount)即可
SELECT
count(order_id),
count(DISTINCT user_id),
sum(amount)
FROM
hql_11_order
WHERE
dt = '2020-09-01'
思路
1、可以先求出用户数，即根据user_id分组，但是order_id和amount字段无法得到，所以直接对order_id求count，就是每个用户的订单数，amount求sum，就是每个用户的下单总金额
2、再对上面的结果进行查询，count(t1.user_id)就是统计下单人数；对cnu求sum，就是把每个用户的订单数相加，即是总订单数；对am求sum，就是把每个用户的下单总金额相加，即是总的金额
SELECT
sum(t1.cnu),
count(t1.user_id),
sum(t1.am)
FROM
(
SELECT
count(order_id) cnu,
user_id,
sum(amount) am
FROM
hql_11_order
WHERE
dt = '2020-09-01'
GROUP BY user_id
) t1;
思路
1、排序去重
SELECT
count,
sum,
count(*)
FROM
(
SELECT
count(order_id) OVER () count,
sum(amount) OVER () sum,
row_number() OVER (PARTITION BY user_id) rn
FROM
hql_11_order
WHERE
dt = '2020-09-01'
) t1
WHERE
rn = 1
GROUP BY
count, sum;
思路：查询 dt=‘2020-09-01‘ 里每个渠道的金额最大3笔订单
1、这种TopN题目就比较简单了，直接开窗求行号，根据渠道分区、金额降序 as rn
2、然后再套一个查询，where<=3即是前三的
SELECT
order_id,
channel_id,
rn
FROM
(
SELECT
order_id,
channel_id,
amount,
ROW_NUMBER() OVER (PARTITION BY channel_id ORDER BY amount DESC) rn
FROM
hql_11_order
WHERE
dt = '2020-09-01'
) t
WHERE
t.rn <= 3
思路：有一天发现订单数据重复，请分析原因
订单属于业务数据，在关系型数据库中不会存在数据重复， hive建表时也不会导致数据重复， 我推测是在数据迁移时，迁移失败导致重复迁移数据冗余了， 或者是网络原因 ， 或者一个订单同一个用户买了多个商品，每个商品一条数据也有可能造成订单id重复。
二、
| t_order 订单表 | |
|---|---|
| order_id | 订单id |
| item_id | 商品id |
| create_time | 下单时间 |
| amount | 下单金额 |
| t_item 商品表 | |
|---|---|
| item_id | 商品id |
| item_name | 商品名称 |
| category | 品类 |
需求1、统计最近一个月，销售数量最多的10个商品
思路：
1、根据t_order表可知，求销量数量，可以理解为商品的数量
2、先过滤出最近一个月的数据（add_months创建时间+1个月 >= 当前时间，若小于则说明不是最近一个月），再根据商品id分组，count出个数，再根据个数降序排序，然后limit取10即可
SELECT
item_id,
count(order_id) cn
FROM
hql_11_t_order
WHERE
add_months(create_time, 1) >= current_date
GROUP BY
item_id
ORDER BY cn DESC
LIMIT 10;
需求2、最近一个月，每个种类里销售数量最多的10个商品
思路
求最大连续天数允许间隔N天
需求：求连续最大天数。允许间隔1天，比如02号和04号之间也属于连续的
数据集：表hql_date，日期字段t_date
t_date
2020-10-01
2020-10-02
2020-10-04
2020-10-05
2020-10-08
2020-10-11
2020-10-14
2020-10-15
2020-10-16
思路：
1、可以先用开窗求出 当前日期 与 下一个日期（下一行的日期）做差值，因为数据日期是递增的，所以要在结果前加个负号，即【 -(t_date - lead(t_date) OVER (ORDER BY t_date) 】；
然后对其if判断，因为允许间隔一天，所以差值 小于等于2 即满足条件记1，否则记0，即【if(-(t_date - lead(t_date) OVER (ORDER BY t_date)) <= 2, 1, 0) 】
如果是允许间隔2天，那小于等于3即可，允许间隔N天，即小于等于N+1天
从结果可以看出，当前行日期与下一个日期若连续，则会在当前行记录1，若有3个连续的1，则说明有4天连续
2、在表t1 的基础上，已经判断出差值diff 哪些是连续，哪些是不连续，则再对 diff 进行开窗，根据diff差值分区求行号 diff_rn，此时1与0就会被分为两个分区；同时对整个数据进行开窗求行号rn
因为rn - diff_rn 只要是连续的，那么差值一定相等，因为分两个区，所以不连续的差值也是相等的，等下先过滤掉即可
3、此时表t2 已经求出diff_rn 和 rn ，此时先过滤掉diff = 0不连续的记录，再根据diff和diff_rn - rn 分组，因为diff_rn - rn可以确定多个连续的日期，即使中间有不连续的间隔；
最后count(*)统计个数，因为中间可能有间隔（若有间隔，那么前后两段的diff_rn - rn 差值是不一样的，会被分到两个组里），所以会出现多个count记录；
因为是求最大连续天数，所以需要对count求最大值max，因为聚合函数是不可以嵌套的，所以需要用开窗转换，最后再+1即是最大的连续天数（因为count是diff分组的行记录，所以需要+1天）
完整sql
SELECT
-- 因为中间可能会被某日期中断为两个连续部分，所以会出现多个count记录
-- 求出最大的count即可，注聚合函数不能嵌套，需要用over()转换下
-- 最大记录数+1 即是最大天数
max(count(*)) OVER () + 1 AS the_max
FROM
(
SELECT
t_date,
diff,
-- 对diff进行开窗求行号，连续和不连续就会被分为两个组
row_number() OVER (PARTITION BY diff ORDER BY t_date) diff_rn,
-- 对整个记录求行号
row_number() OVER (ORDER BY t_date) rn
FROM
(
SELECT
t_date,
-- 先求出当前行日期与下一行日期的差值，<=2即满足间隔一天连续，记1，否则即0
if(-(t_date - lead(t_date) OVER (ORDER BY t_date)) <= 2, 1, 0) diff
FROM
hql_date
) t1
) t2
WHERE
diff != 0 -- 过滤掉不连续日期差值
GROUP BY
diff, rn - diff_rn -- 差值和 rn - diff_rn 分组，可以确定连续的日期记录
若有收获，就点个赞吧
0 人点赞
