HQL高级进阶

直接上干货，HiveSQL高级进阶技巧，重要性不言而喻。掌握这10个技巧，你的SQL水平将有一个质的提升！

1.删除

insert overwrite tmp
select * from tmp where id != '666';

2.更新

insert overwrite tmp
select 
    id,
    label,
    if(id = '1' and label = 'grade','25',value) as value
from tmp
where id != '666';

3.行转列

-- Step03：最后将info的内容切分
select id,split(info,':')[0] as label,split(info,':')[1] as value
from 
(
    -- Step01：先将数据拼接成“heit:180,weit:60,age:26”
    select id,concat('heit',':',height,',','weit',':',weight,',','age',':',age) as value     from tmp
) as tmp
-- Step02：然后在借用explode函数将数据膨胀至多行
lateral view explode(split(value,',')) mytable as info;

4.列转行1

select
tmp1.id as id,tmp1.value as height,tmp2.value as weight,tmp3.value as age
from
(select id,label,value from tmp2 where label = 'heit') as tmp1
join
on tmp1.id = tmp2.id
(select id,label,value from tmp2 where label = 'weit') as tmp2
join
on tmp1.id = tmp2.id
(select id,label,value from tmp2 where label = 'age') as tmp3
on tmp1.id = tmp3.id;

5.列转行2

select
id,tmpmap['height'] as height,tmpmap['weight'] as weight,tmpmap['age'] as age
from
(
    select id,           
        str_to_map(concat_ws(',',collect_set(concat(label,':',value))),',',':') as tmpmap
    from tmp2 group by id
) as tmp1;

6.分析函数1

select id,label,value,
       lead(value,1,0)over(partition by id order by label) as lead,                    
       lag(value,1,999)over(partition by id order by label) as lag,       
       first_value(value)over(partition by id order by label) as first_value,       
       last_value(value)over(partition by id order by label) as last_value
from tmp;

7.分析函数2

select id,label,value,
       row_number()over(partition by id order by value) as row_number,       
       rank()over(partition by id order by value) as rank,
       dense_rank()over(partition by id order by value) as dense_rank
from tmp;

8.多维分析1

select col1,col2,col3,count(1),
        Grouping__ID
from tmp
group by col1,col2,col3
grouping sets(col1,col2,col3,(col1,col2),(col1,col3),(col2,col3),())

9.多维分析2

select col1,col2,col3,count(1),
       Grouping__ID
from tmp
group by col1,col2,col3
with cube;

10.数据倾斜groupby

select label,sum(cnt) as all from 
(
    select rd,label,sum(1) as cnt from
    (
        select id,round(rand(),2) as rd,value from tmp1
    ) as tmp
    group by rd,label
) as tmp
group by label;

11.数据倾斜join：

select label,sum(value) as all from 
(
    select rd,label,sum(value) as cnt from
    (
        select tmp1.rd as rd,tmp1.label as label,tmp1.value*tmp2.value as value         
        from
        (
            select id,round(rand(),1) as rd,label,value from tmp1
        ) as tmp1
        join
        (
            select id,rd,label,value from tmp2
            lateral view
            explode(split('0.0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9',',')) mytable as rd
        ) as tmp2
        on tmp1.rd = tmp2.rd and tmp1.label = tmp2.label
    ) as tmp1
    group by rd,label
) as tmp1
group by label;

HQL练习(一)

1.demo1

数据集

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';
load data local inpath '/home/atguigu/data/score.txt' into table score;

需求:找出所有科目成绩都大于某一学科平均成绩的学生

--解法一:不开窗
--4.查出满足条件的学生id
SELECT uid
FROM (
--3.对之前对成绩的标记求和,如果为0,则表示,该学生每门科目成绩都大于其对应学科的平均成绩
         SELECT uid, SUM(flag) sumflag
         FROM
/*
2.通过subject_id,join获取每个同学每门学科对应的该学科的平均成绩,并与自己的成绩做比较,
大于记为0,小于记为1
*/
             (SELECT uid, t1.subject_id, score, avg_score, IF(score > avg_score, 0, 1) flag
              FROM (SELECT uid, subject_id, score
                    FROM score) t2
                       JOIN(
                  --1.求出各学科平均成绩
                  SELECT subject_id, AVG(score) avg_score
                  FROM score
                  GROUP BY subject_id
              ) t1
                           ON t2.subject_id = t1.subject_id) t3
         GROUP BY uid
     ) t4
WHERE sumflag = 0;

--解法二:开窗
select
  uid
from (select 
  uid,
  if(score > avg_score,0,1) flag
from (select
  uid,
  subject_id,
  score,
  avg(score) over(partition by subject_id) avg_score
from score)t1)t2
group by uid
having sum(flag) = 0;

2.demo2

数据集

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

建表语句

create table action
(userId string,
visitDate string,
visitCount int)
row format delimited fields terminated by "\t";
load data local inpath '/home/atguigu/data/action.txt' into table action;

需求:使用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

--3.开窗求出累计访问次数
SELECT userid, mn, mn_count, SUM(mn_count) OVER (PARTITION BY userid ORDER BY mn)
FROM (
--2.计算每人单月访问量
         SELECT userid, mn, SUM(visitcount) mn_count
         FROM
--1.对visitDate格式化
(SELECT userid, DATE_FORMAT(REGEXP_REPLACE(visitdate, '/', '-'), 'yyyy-MM') mn, visitcount
 FROM action) t1
         GROUP BY userid, mn
     ) t2;

3.demo3

问题描述:
有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，
访客的用户id为user_id，被访问的店铺名称为shop

数据集

user_id shop
u1    a
u2    b
u1    b
u1    a
u3    c
u4    b
u1    a
u2    c
u5    b
u4    b
u6    c
u2    c
u1    b
u2    a
u2    a
u3    a
u5    a
u5    a
u5    a

建表语句

--创建visit表
create table visit(user_id string,shop string) row format delimited fields terminated by '\t';
load data local inpath '/home/atguigu/data/visit.txt' into table visit;

需求一:统计每个店铺的UV(访客数)

#按照店铺分组聚合求,记得去重!
SELECT shop, COUNT(DISTINCT user_id) uv
FROM visit
GROUP BY shop;

需求二:每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

SELECT shop, user_id, ct
FROM (
         --2.统计每个店铺访问次数排名
         SELECT user_id, shop, ct, RANK() OVER (PARTITION BY shop ORDER BY ct DESC) rk
         FROM
--1.计算每个店铺每个访客的访问次数
(SELECT user_id, shop, COUNT(1) ct
 FROM visit
 GROUP BY user_id, shop) t1
     ) t2
WHERE rk <= 3;

4.demo4

数据集

数据集
Data        order_id    user_id     amount
2017-01-01    10029028    1000003251    33.57
2017-02-01    10029029    1000003252    33.57
2017-04-01    10029030    1000003253    33.57
2017-05-01    10029031    1000003254    33.57
2017-06-01    10029032    1000003255    33.57
2017-11-01    10029033    1000003256    33.57
2017-12-01    10029034    1000003257    33.57

建表语句

--创建order_tab表
CREATE TABLE order_tab
(
    dt       STRING,
    order_id STRING,
    user_id  STRING,
    amount   DECIMAL(10, 2)
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
LOAD DATA LOCAL INPATH '/home/atguigu/data/order_tab.txt' INTO TABLE order_tab;

需求一:统计2017年每个月的订单数,用户数,总成交金额

select 
  date_format(dt,'yyyy-MM') mn,
  count(order_id),
  count(distinct(user_id)),
  sum(amount)
from order_tab
where year(dt)=2017
group by date_format(dt,'yyyy-MM');

需求二:统计2017年11月的新客数(指在11月才有第一笔订单)

select 
  count(user_id)
from (select 
  user_id
from order_tab
group by user_id
having date_format(min(dt),'yyyy-MM')='2017-11')t1;

5.demo5

数据集

数据集
dt ,user_id, age
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

建表

--创建user_age表
CREATE TABLE user_age
(
    dt      STRING,
    user_id STRING,
    age     INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
LOAD DATA LOCAL INPATH '/home/atguigu/data/user_age.txt' INTO TABLE user_age;

需求:统计所有用户和活跃用户的总数及平均年龄(活跃用户指连续两天都有访问记录的用户)

select
  sum(user_count),
  sum(user_avg_age),
  sum(active_user_count),
  sum(active_user_avg_age)
from(
select 
  count(*) user_count,
  cast(sum(age)/count(*) as decimal(10,1)) user_avg_age,
  0 active_user_count,
  0 active_user_avg_age
from (select 
  user_id,
  min(age) age
from user_age
group by user_id)t1
union all 
select
  0 user_count,
  0 user_avg_age,
  count(*) active_user_count,
  cast(sum(age)/count(*) as decimal(10,2)) active_user_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)t3)t4
group by user_id,flag
having(count(*)>=2))t5
group by user_id)t6)t7;

6.demo6

数据集

userid money paymenttime orderid
20211024    45    2017-10-01    10029028
20211025    55    2017-09-01    10029027
20211025    60    2017-10-02    10029029
20211024    100    2017-10-04    10029030

建表

--创建ordertable表
CREATE TABLE ordertable
(
    userid      STRING,
    money       INT,
    paymenttime STRING,
    orderid     STRING
)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
LOAD DATA LOCAL INPATH '/home/atguigu/data/ordertable.txt' INTO TABLE ordertable;

需求:统计所有用户在10月份第一次购买商品的金额

--解法一:不开窗
select 
  t1.userid,
  t1.min_paymenttime,
  ordertable.money
from (select
  userid,
  min(paymenttime) min_paymenttime
from ordertable
where date_format(paymenttime,'yyyy-MM')='2017-10'
group by userid)t1
join ordertable
on t1.userid=ordertable.userid
and t1.min_paymenttime=ordertable.paymenttime;

--解法二:开窗
select 
  userid,
  money,
  paymenttime,
  orderid
from (select 
  userid,
  money,
  paymenttime,
  orderid,
  rank() over(partition by userid order by paymenttime) rk
from ordertable
where date_format(paymenttime,'yyyy-MM')='2017-10')t1
where rk=1;

7.demo7

数据集

dt interface ip
2016-11-09 14:22:05    /api/user/login    110.23.5.33
2016-11-09 11:23:10    /api/user/detail    57.3.2.16
2016-11-09 14:59:40    /api/user/login    200.6.5.166
2016-11-09 14:22:05    /api/user/login    110.23.5.34
2016-11-09 14:22:05    /api/user/login    110.23.5.34
2016-11-09 14:22:05    /api/user/login    110.23.5.34
2016-11-09 11:23:10    /api/user/detail    57.3.2.16
2016-11-09 23:59:40    /api/user/login    200.6.5.166
2016-11-09 14:22:05    /api/user/login    110.23.5.34
2016-11-09 11:23:10    /api/user/detail    57.3.2.16
2016-11-09 23:59:40    /api/user/login    200.6.5.166
2016-11-09 14:22:05    /api/user/login    110.23.5.35
2016-11-09 14:23:10    /api/user/detail    57.3.2.16
2016-11-09 23:59:40    /api/user/login    200.6.5.166
2016-11-09 14:59:40    /api/user/login    200.6.5.166
2016-11-09 14:59:40    /api/user/login    200.6.5.166

建表

CREATE TABLE ip
(
    dt        STRING,
    interface STRING,
    ip        STRING
)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
LOAD DATA LOCAL INPATH '/home/atguigu/data/ip.txt' INTO TABLE ip;

需求:统计11月9号下午14点(14-15点),访问 api/user/login 接口的top10的ip地址

select 
  ip,
  count(*) ct
from  ip
where date_format(dt,'yyyy-MM-dd HH') >= '2016-11-09 14'
and date_format(dt,'yyyy-MM-dd HH') <= '2016-11-09 15'
and interface='/api/user/login'
group by ip
order by ct desc 
limit 10;

8.demo8

数据集

dist_id(区id) account(账号) gold(金币)
1,a,1
1,x,2
1,d,3
1,f,4
1,e,8
2,u,7
2,i,10
2,h,12
2,b,14
2,k,15

建表

CREATE TABLE account
(
    dist_id INT,
    account STRING,
    gold    INT
)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
LOAD DATA LOCAL INPATH '/home/atguigu/data/account.txt' INTO TABLE account;

需求:统计每个区的金币排名前三的账号

解法一:开窗
select 
  dist_id,
  account,
  gold,
  rank() over(partition by dist_id order by gold) rk
from account;t1;
select
  dist_id,
  account,
  gold
from (select 
  dist_id,
  account,
  gold,
  rank() over(partition by dist_id order by gold desc) rk
from account)t1
where rk<=3;

解法二:不开窗
select *
from account a1
where(select count(*) from 
  account a2 
  where a2.dist_id = a1.dist_id and a2.gold >= a1.gold) <=3
order by dist_id,gold desc;

9.demo9

数据集

sale表
memberid（会员id，外键） 购买金额（MNAccount）
1001    50.3
1002    56.5
1003    235
1001    23.6
1005    56.2
        25.6
        33.5
regoods表
memberid（会员id，外键）退货金额（RMNAccount）
1001    20.1
1002    23.6
1001    10.1
        23.5
        10.2
1005    0.8

建表

--建表member
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';
LOAD DATA LOCAL INPATH '/home/atguigu/data/sale.txt' INTO TABLE sale;

--建表regoods
CREATE TABLE regoods
(
    memberid   STRING,
    rmnaccount DOUBLE
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
LOAD DATA LOCAL INPATH '/home/atguigu/data/regoods.txt' INTO TABLE regoods;

需求

1）有三张表分别为会员表（member）销售表（sale）退货表（regoods）
（1）会员表有字段memberid（会员id，主键）credits（积分）；
（2）销售表有字段memberid（会员id，外键）购买金额（MNAccount）；
（3）退货表中有字段memberid（会员id，外键）退货金额（RMNAccount）。
2）业务说明
（1）销售表中的销售记录可以是会员购买，也可以是非会员购买。（即销售表中的memberid可以为空）；
（2）销售表中的一个会员可以有多条购买记录；
（3）退货表中的退货记录可以是会员，也可是非会员；
（4）一个会员可以有一条或多条退货记录。
查询需求：分组查出销售表中所有会员购买金额，同时分组查出退货表中所有会员的退货金额，把会员id相同的购买金额-退款金额得到的结果
更新到表会员表中对应会员的积分字段（credits）

INSERT INTO TABLE member
SELECT memberid, ROUND((t3.mnaccount - t3.rmnaccount), 2) credits

FROM (
         SELECT t1.memberid, t1.mnaccount, t2.rmnaccount
         FROM (SELECT memberid, ROUND(SUM(mnaccount), 2) mnaccount
               FROM sale
               WHERE LENGTH(TRIM(memberid)) > 1
               GROUP BY memberid) t1
                  JOIN
              (
                  SELECT memberid, ROUND(SUM(rmnaccount), 2) rmnaccount

                  FROM regoods
                  WHERE LENGTH(TRIM(memberid)) > 1
                  GROUP BY memberid
              ) t2
              ON t1.memberid = t2.memberid) t3;

10.demo10

一.用一条SQL语句查询出每门课都大于80 分的学生姓名

name 课程    分数
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90

解法一:select name from table group by name having min(fenshu)>80
解法二:select distinct name from table where name not in(select distinct name from table where fenshu<=80)

二.学生表如下:删除除了自动编号不同,其他都相同的学生冗余信息

自动编号     学号     姓名     课程编号     课程名称     分数
1         2005001 张三     0001         数学         69
2         2005002 李四     0001         数学         89
3         2005001 张三     0001         数学         69

delete tablename where 自动编号 not in(select min(自动编号) from tablename group by 学号,姓名,课程编号,课程名称,分数)

三.一个叫team的表,里面只有一个字段name,一共有4条记录,分别是a,b,c,d,,对应四个球队,现在四个球队进行比赛,用一条SQL语句显示所有可能的比赛组合

select a.name b.name from team a, team b where a.name<b.name

四.面试题

怎么把
year month amount
1991  1     1.1
1991  2     1.2 
1991  3     1.3
1991  4     1.4
1992  1     2.1
1992  2     2.2
1992  3     2.3
1992  4     2.4
查成这样一个结果
year  m1   m2   m3  m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

select year,
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4,
from aaa
group by year;

五.说明:复制表(只复制结构, 源表名:a 新表名:b)

select * into b from a where 1<>1   #(where 1=1,拷贝表结构和数据内容)

六.修改表,以及格分数为60为准添加字段

原表:
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
写出查询语句

select courseid,
    coursename,
    score,
    if(score>=60,"pass","fail") as mark from course;

七.给出所有购物商品为两种或两种以上的购物人记录

表名:购物信息
购物人 商品名称 数量
A     甲      2
B     乙      4
C     丙      1
A     丁      2
B     丙      5

select * from 购物信息 where 购物人 in (select 购物人 from 购物信息 group by 购物人 having count(*) >= 2);

八.将info表的result进行累加统计

info 表
date       result
2005-05-09 win
2005-05-09 lose
2005-05-09 lose
2005-05-09 lose
2005-05-10 win
2005-05-10 lose
2005-05-10 lose
如果要生成下列结果, 该如何写 sql 语句?
 date      win lose
2005-05-09 1   3
2005-05-10 1   2

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"
from info
group by date;

select date,
(select count(*) from info t2 where result="win" and t2.date = t1.date) as "win",
(select count(*) from info t2 where result="lose" and t2.date = t1.date) as "lose",
from info t1
group by date;

11.demo11

一.有一个订单表order,已知字段有:order_id(订单ID),user_id(用户ID),amount(金额),pay_datetime(付费时间),channel_id(渠道id),dt(分区字段)

在hive中创建这个表

create external table order(
    order_id int,
    user_id int,
    amount double,
    pay_datatime timestamp,
    channel_id int)
    partitioned by(dt string)
    row format delimited fields terminated by '\t';

查询dt=’2018-09-01’里每个渠道的订单数,下单人数(去重),总金额

select channel_id count(order_id),count(distinct(user_id),sum(amount))
from order
where dt = '2018-09-01'
group by channel_id

查询dt=’2018-09-01’里每个渠道的金额最大的3笔订单

select order_id, cahnnel_id, amount
from(
select order_id, channel_id,
amount,
row_number() over(partition by channel_id order by amount desc) rank
from order
where dt = '2019-09-01')t
where t.rank<4;

有一天发现订单数据重复,请分析原因

订单属于业务数据,在关系型数据库中不会存在数据重复,hive建表时也不会导致数据重复,我推测是在数据迁移时,迁移失败导致重复迁移数据冗余了

二.已知有订单表t_order,商品表t_item,完成以下需求

t_order 订单表
order_id,//订单 id
item_id, //商品 id
create_time,//下单时间
amount//下单金额
---------------------------------------------------
t_item 商品表
item_id,//商品 id
item_name,//商品名称
category//品类

最近一个月,销售数量最多的10个商品

select item_id,count(order_id) a
from t_order
where datadiff(create_time,current_date)<=30
group by item_id
order by a desc
limit 10;

最近一个月,每个品类中销售数量最多的10个商品(一个订单对应一个商品,一个商品对应一个品类)

with(
 select order_id,item_id,item_name,category
    from t_order join t_item
    on t_order.item_id = t_item.item_id
) t
select order_id,item_id,item_name,category,
count(item_id) over(partition by category) item_count
from t
group by category
order by item_count desc
limit 10;

三.计算平台的每一个用户发过多少朋友圈,获得多少点赞

已知,数据如下

T1:10万行数据

uid(用户ID)	log_id(日记id)
uid1	log_id1
uid1	log_id2
uid2	log_id3
……	……

T2:1000万行数据(注意:没有被点赞的日记此表不做记录)

log_id(日记id)	like_uid(点赞的用户id)
log_id1	uid2
log_id1	uid3
log_id1	uid4
log_id3	uid2
…….	…….

需求:请用SQL计算出如下结果

uid(用户id)	发过多少日记	获得多少点赞
uid1	2	3
uid2	1	1
……	……	……

with t3 as(
    select * from t1 left join t2
    on t1.log_id = t2.log_id
)
select
  uid,//用户ID
  count(log_id) over(partition by uid) log_cnt,//发过多少日记
  count(like_uid) over(partition by log_id) liked_cnt//获得多少点赞数
  from t3;

四.处理产品版权号

版本号信息存储在数据表中每行一个版本号

补充说明:产品版本号由三个部分组成

如: 9.11.2

第一部分的9为主版本号,为1-99之间的数字

第二部分的11为子版本号,为0-99之间的数字

第三部分2为阶段版本号,为0-99之间的数字

请根据具体条件和问题,使用hive SQL编程

需求一:T1表有100个版本号,找出其中最大的版本号

v_id(版本号)
9.9.2
8.1
9.9.2
9.20
31.0.1
……

with t2 as(
  select v_id v1,
     v2
    from t1
    lateral view explode(v_id) tmp as v2
)
select v1,max(v2)
from t2;

需求二:T1表有1000万个版本号,给如下格式的版本号排序,对于相同的版本号,顺序号一致

v_id(版本号)	seq(顺序号)
31.0.1	0
9.20	1
9.9.2	2
9.9.2	3
9.0.8	4
……….	…………

select v_id,
  rank() over(partition by v_id order by v_id) seq
  from t1;

HQL练习(二)

1.连续问题

如下数据为蚂蚁森林中用户领取的减少碳排放量

id        dt          lowcarbon
1001    2021-12-12    123
1002    2021-12-12    45
1001    2021-12-13    43
1001    2021-12-13    45
1001    2021-12-13    23
1002    2021-12-14    45
1001    2021-12-14    230
1002    2021-12-15    45
1001    2021-12-15    23

建表语句

create table t1(
    id string,
    dt string,
    lowcarbon string
)
row format delimited fields terminated by "\t";
LOAD DATA LOCAL INPATH '/home/atguigu/data/t1.txt' INTO TABLE t1;

需求:找出连续3天及以上减少碳排放量在100以上的用户

select id
from (select 
  id,
  dt,
  lowcarbon,
  date_sub(dt,rn) flag
from (select 
  id,
  dt,
  lowcarbon,
  rank() over(partition by id order by dt) rn
from (select 
  id,
  dt,
  sum(lowcarbon) lowcarbon
from t1
group by id,dt
having lowcarbon>100)t2)t3)t4
group by id,flag 
having count(*) >=3;

2.分组问题

如下为电商公司用户访问时间数据
id        ts(秒)
1001    17523641234
1001    17523641256
1002    17523641278
1001    17523641334
1002    17523641434
1001    17523641534
1001    17523641544
1002    17523641634
1001    17523641638
1001    17523641654

建表语句

create table t2(
    id string,
    ts string
)
row format delimited fields terminated by "\t";
LOAD DATA LOCAL INPATH '/home/atguigu/data/t2.txt' INTO TABLE t2;

某个用户连续的访问记录如果时间间隔小于60秒,则分为同一个组,结果为:
id        ts(秒)            group
1001    17523641234        1
1001    17523641256        1
1001    17523641334        2
1001    17523641534        3
1001    17523641544        3
1001    17523641638        4
1001    17523641654        4
1002    17523641278        1
1002    17523641434        2
1002    17523641634        3

请用SQL实现这个需求

select 
  id,
  ts,
  sum(if(tsdiff>60,1,0)) over(partition by id order by ts) groupid
from (select 
  id,
  ts,
  lagts,
  ts - lagts tsdiff
from (select 
  id,
  ts,
  lag(ts,1,0) over(partition by id order by ts) lagts
 from t2)tmp1)tmp2;

3.间隔连续问题

某游戏公司记录的用户每日登陆数据
id        dt
1001    2021-12-12
1002    2021-12-12
1001    2021-12-13
1001    2021-12-14
1001    2021-12-16
1002    2021-12-16
1001    2021-12-19
1002    2021-12-17
1001    2021-12-20

建表语句

create table t3(
    id string,
    dt string
)
row format delimited fields terminated by "\t";
LOAD DATA LOCAL INPATH '/home/atguigu/data/t3.txt' INTO TABLE t3;

需求:计算每个用户的连续登陆天数,可以间隔一天,解释:如果一个用户在1,3,5,6登陆游戏,则视为连续6天登陆

解法一:等差数列法
select 
 id,
 max(ct + new_ct -1) ct
from (select 
  id,
  new_flag,
  sum(ct) ct,
  count(*) new_ct
from (select 
  id,
  flag,
  ct,
  rn,
  date_sub(flag,rn) new_flag
from (select 
  id,
  flag,
  ct,
  rank() over(partition by id order by flag) rn
from (select 
  id,
  flag,
  count(*) ct
from (select 
  id,
  dt,
  rn,
  date_sub(dt,rn) flag
from (select 
  id,
  dt,
  rank() over(partition by id order by dt) rn
from t3)tmp1)tmp2
group by id,flag)tmp3)tmp4)tmp5
group by id,new_flag)tmp6
group by id;

--解法二:分组
select 
  id,
  max(days) + 1
from (select 
  id,
  flag,
  datediff(max(dt),min(dt)) days
from (select 
  id,
  dt,
  sum(if(flag>2,1,0)) over(partition by id order by dt) flag
from (select 
  id,
  dt,
  lagdt,
  datediff(dt,lagdt) flag
from (select 
  id,
  dt,
  lag(dt,1,'1970-01-01') over(partition by id order by dt) lagdt
from t3)tmp1)tmp2)tmp3
group by id,flag)tmp4
group by id;

4.打折日期交叉问题

如下为平台商品促销数据:字段为品牌,打折开始日期,打折结束日期
brand    stt            edt
oppo    2021-06-05    2021-06-09
oppo    2021-06-11    2021-06-21
vivo    2021-06-05    2021-06-15
vivo    2021-06-09    2021-06-21
redmi    2021-06-05    2021-06-21
redmi    2021-06-09    2021-06-15
redmi    2021-06-17    2021-06-26
huawei    2021-06-05    2021-06-26
huawei    2021-06-09    2021-06-15
huawei    2021-06-17    2021-06-21

建表语句

create table t4(
    brand string,
    stt string，
    edt string
)
row format delimited fields terminated by "\t";

需求:计算每个品牌总的打折销售天数，注意其中的交叉日期，比如vivo品牌，第一次活动时间为2021-06-05到2021-06-15，第二次活动时间为2021-06-09到2021-06-21其中9号到15号为重复天数，只统计一次，即vivo总打折天数为2021-06-05到2021-06-21共计17天。

select 
  brand,
  sum(days+1) days
from (select 
  brand,
  datediff(edt,new_stt) days
from (select 
  brand,
  if(maxedt is null or maxedt < stt,stt,date_add(maxedt,1)) new_stt,
  edt
from (select 
  brand,
  stt,
  edt,
  max(edt) over(partition by brand order by stt rows between unbounded preceding and 1 preceding) maxedt
from t4)tmp1)tmp2)tmp3
where days >0
group by brand;

5.同时在线问题

如下为某直播平台主播开播及关播时间,根据该数据计算出平台最高峰同时在线的主播人数

id        stt                        edt
1001    2021-06-14 12:12:12        2021-06-14 18:12:12
1003    2021-06-14 13:12:12        2021-06-14 16:12:12
1004    2021-06-14 13:15:12        2021-06-14 20:12:12
1002    2021-06-14 15:12:12        2021-06-14 16:12:12
1005    2021-06-14 15:18:12        2021-06-14 20:12:12
1001    2021-06-14 20:12:12        2021-06-14 23:12:12
1006    2021-06-14 21:12:12        2021-06-14 23:15:12
1007    2021-06-14 22:12:12        2021-06-14 23:10:12

建表语句

create table t5(
    id string,
    stt string,
    edt string
)
row format delimited fields terminated by "\t";

select 
  max(sum_p)
from (select 
  id,
  dt,
  sum(p) over(order by dt) sum_p
from (select 
  id,
  stt dt,
  1 p
from t5
union 
select
  id,
  edt dt,
  -1 p
from t5)tmp1)tmp2;

$06[HQL练习]

HQL高级进阶

1.删除

2.更新

3.行转列

4.列转行1

5.列转行2

6.分析函数1

7.分析函数2

8.多维分析1

9.多维分析2

10.数据倾斜groupby

11.数据倾斜join：

HQL练习(一)

1.demo1

2.demo2

3.demo3

4.demo4

5.demo5

6.demo6

7.demo7

8.demo8

9.demo9

10.demo10

一.用一条SQL语句查询出每门课都大于80 分的学生姓名

二.学生表如下:删除除了自动编号不同,其他都相同的学生冗余信息

三.一个叫team的表,里面只有一个字段name,一共有4条记录,分别是a,b,c,d,,对应四个球队,现在四个球队进行比赛,用一条SQL语句显示所有可能的比赛组合

四.面试题

五.说明:复制表(只复制结构, 源表名:a 新表名:b)

六.修改表,以及格分数为60为准添加字段

七.给出所有购物商品为两种或两种以上的购物人记录

八.将info表的result进行累加统计

11.demo11

一.有一个订单表order,已知字段有:order_id(订单ID),user_id(用户ID),amount(金额),pay_datetime(付费时间),channel_id(渠道id),dt(分区字段)

二.已知有订单表t_order,商品表t_item,完成以下需求

三.计算平台的每一个用户发过多少朋友圈,获得多少点赞

四.处理产品版权号

HQL练习(二)

1.连续问题

2.分组问题

3.间隔连续问题

4.打折日期交叉问题

5.同时在线问题