HQL专栏之初级练习1

最新推荐文章于 2024-07-18 16:32:46 发布

ValidationExpression

最新推荐文章于 2024-07-18 16:32:46 发布

阅读量883

点赞数 21

分类专栏： HQL案例练习文章标签： sql hive 大数据

本文链接：https://blog.csdn.net/qq_64125090/article/details/134687684

版权

HQL案例练习专栏收录该内容

9 篇文章 0 订阅

订阅专栏

系列文章目录

第一章 HQL表创建

前言

这里HQL的初级练习,用于复习sql常用的函数等使用方法,并详细分析说明.(如果有错误欢迎指出)

一、习题练习

1.筛选2021年总销量小于100的商品

条件: 不考虑上架时间小于一个月的情况
分析应当使用哪张表:这里需要年份,还需要总销量,商品总上述三个确定为订单详情表(order_detail),这里还需要考虑商品上架的时间确定商品表(sku_info)

订单详情表(order_detail):字段介绍
订单明细id -------------------订单id-----------商品id---------下单时间------------下单时的商品价格----下单数量

商品表(sku_info):字段介绍
------商品id--------------商品名称-----------所属分类id-------------------上架时间------------------上架时的价格

解题思路:可以先对order_detail表进行时间和商品数量的过滤,然后再对sku_info表得出上架时间和不小于30天的商品.
使用的函数介绍:

year(‘年-月-日时:分:秒’) —year(‘年-月-日’) :年, 例子year(‘2021-10-01’) 结果 2021.
同理还有month(), day(), 格式同上.

datediff(‘结束时间’,‘开始时间’) :用于计算两个日期之间的差值.

代码演示

--第一步:过滤时间=2021年的 还有总销量<100的
select sku_id, sum( sku_num ) num
from order_detail
where year( create_date ) = 2021  
group by sku_id   --由于需要求的是总销量,所以对商品进行分组,然后sum()求和
having num < 100;  --过滤掉不符合的数量
---------------
--第二步:上架日期,需要满足2021年时间内的日期和上架的日期差数>30天
select sku_id,name 
from sku_info 
where datediff('2022-01-01',from_date)>30;
---------------
--第三步:将上述第一步和第二步的结果进行join得到满足两者的表
select t1.sku_id, t2.name, t1.num
from (select sku_id, sum( sku_num ) num
      from order_detail
      where year( create_date ) = 2021
      group by sku_id
      having num < 100) t1
         join (select sku_id, name from sku_info where datediff( '2022-01-01' , from_date ) > 30) t2
              on t1.sku_id = t2.sku_id;

结果
在这里插入图片描述

总结:
分析问题找到其中所含的条件,可以使用多个查询满足不同的条件,然后将不同条件的查询表进行关联操作.

2.查询每日新增用户数

分析表: 每一天, 用户 ,新增:确定为登录明细表(user_login_detail)这里会记录每一个用户登录的详情.

登录明细表(user_login_detail):字段介绍
用户id -----------------登录的ip地址-----------------登录时间------------------------------登出时间

解题思路:我们要先知道每一个用户同一天也可能登录多次的情况,找到每一个用户登录最开始的那个时间(这里就会用到分组对用户id),此时最小的那个时间就是用户最开始登录的时间(也就是新增的用户),然后就可以对这个表再按照最小时间分组,得到每一天数据,这里对用户count(*)也就得到了每一天新增的用户数据.
使用函数介绍:

min() 用于求最开始的那个时间
substring(‘字符串’,a,b): 用于字符串的切割,a开始位置,b结束位置(左闭右开)
date_format(‘2021-09-21 08:00:00’,‘YY-MM-dd’) : 格式化所需要的时间.

代码演示

--第一步:过滤出每个用户最开始登录的那个时间
select user_id, min( substring( login_ts , 1 , 11 ) ) min_login_time
from user_login_detail
group by user_id;

--2.第二步: 根据上述数据,按照最小登录的时间进行分组,然后进行计数,就可以得到每一天新增用户的数量.
select t1.min_login_time, count( * ) count_num
from (select user_id, min( substring( login_ts , 1 , 11 ) ) min_login_time
      from user_login_detail
      group by user_id) t1
group by t1.min_login_time;

在这里插入图片描述

总结:需要转换新增用户代表的含义,就是最开始登录时间.

3.用户注册、登录、下单综合统计

需求:查询每个用户的注册日期（首次登录日期）、总登录次数、以及2021年的登录次数、订单数和订单总额.
涉及的表登录表(user_login_detail),订单信息表(order_info)

订单信息表(order_info)
-----订单id----------------------用户id-------------------下单时间-----------------------下单金额

分析:首先注意时间点2021年,分别求出注册的时间和登录的总次数,在求出订单数量和订单金额.
使用函数介绍

min(): 最小值
substr(a,b,c): 字符串a截取,长度b到c,长度范围[b,c)包含前不含后.
if(a,b,c): 如果a的结果为ture,则取b的值,为false则取c的值. -常常可以结合sum,count使用.
nvl(a,b): 如果a的值为null则用b的值替代.
cast(A as decimal(B, C)): 用于指定整数和小数的位数,(或强制类型转换)
A:原数值,B:数值的总位数,C:小数的位数,B-C:整数的位数.

代码演示

--第一步:使用登录表,找出注册时间,和登录总次数.
select user_id,
       min( substr( login_ts , 1 , 11 ) ) min_login,
       count( * )                         all_login_count,
       count( `if`( year( login_ts ) = 2021 , 1 , null ) )
from user_login_detail
group by user_id;

--第二步:计算订单表中用户的订单数量和订单总金额
select user_id, 
       count( `if`( year( create_date ) = 2021 , 1 , null ) ) order_count,
       sum( total_amount ) sum_amount
from order_info
where year( create_date ) = 2021
group by user_id; --计算每一个用户的,一个用户可能有多个订单,所以进行分组.

--第三步:关联上述数据结果.
select t1.user_id, min_login, all_login_count,t1.count, nvl(t2.order_count,0),
       cast(nvl(sum_amount, 0.0) as decimal(16, 2))   --这里的含义是进行了强制类型转换.
from (select user_id,
             min( substr( login_ts , 1 , 11 ) ) min_login,
             count( * )                         all_login_count,
             count( `if`( year( login_ts ) = 2021 , 1 , null ) ) count
      from user_login_detail
      group by user_id) t1
left join    --这里使用左外连接,原因登录的不一定会下单,但是下单的一定要进行登录.
     (select user_id, count( `if`(year(create_date)=2021,1,null )) order_count, sum( total_amount ) sum_amount
            from order_info where year(create_date)=2021
            group by user_id )t2
on t1.user_id=t2.user_id;

在这里插入图片描述
注意应当使用那种连接方式join, left join, right join

4.向用户推荐朋友收藏的商品

需求介绍:自己就是这个用户,你的朋友收藏了某件商品,但是你没有收藏,这里就会向你进行推荐.
分析表:收藏表(favor_info),好友表(friendship_info)

好友表(friendship_info):这里是双向好友表
–好友1---------------------------好友2

收藏表(favor_info):
–用户id-----------------------------商品id-------------------------收藏时间

分析: 这里的表是双向好友表(A, B中A和B互为好友),所以会出现A向B推荐后,B又根据这个表向A推荐.所以这里转换一下表构造,变成单向表(A , B)中B是A的好友.(B , A)中A是B的好友.然后分别找出自己和朋友的收藏商品,将两者进行关联,关联不上的就说明朋友收藏了,自己没有收藏.
函数介绍:

union: 将两个表查询的结果进行拼接(上下拼接),去重.
union all :同上作用,但是不去重.(注意拼接的字段要进行一一对应)
with tmp_01 as (): 用于将括号的查询结果放到一个tmp_01的一个临时表.(可以使用其他方式)
collect_set(): 将值转换为集合的操作.(便于观察)

代码演示

--第一步:转换好友表,双向好友->单项好友(便于推荐的时候,只需要考虑一边)
select user1_id,
       user2_id
from friendship_info
union
select user2_id,
       user1_id
from friendship_info;

--第二步:得出自己的收藏的商品
with tmp_01 as (
    select user1_id,
           user2_id
    from friendship_info
    union
    select user2_id,
           user1_id
    from friendship_info
)
select t01.user1_id,
       collect_set(fi.sku_id)
       from tmp_01 t01 join favor_info fi
on t01.user1_id=fi.user_id
group by t01.user1_id;   --分组使用collect_set():将值转为集合

--第三步:得出朋友的收藏商品,但是这里的格式不同,要的是自己的id和朋友收藏的商品,
with tmp_02 as (
        select user1_id,
           user2_id
    from friendship_info
    union
    select user2_id,
           user1_id
    from friendship_info
)
select t02.user1_id,        --自己的id
       collect_set(fi.sku_id)    ---这里的集合就是朋友收藏的商品
       from tmp_02 t02 join favor_info fi
on t02.user2_id=fi.user_id
group by t02.user1_id;      --对自己进行分组,得到的就都是朋友.

--第四步:将上述2,3步的结果进行关联
select t2.user1_id,
       collect_set( t2.sku_id ) --使用这种方式展示,更容易查看.
from (
    with tmp_01 as (
        select user1_id,
               user2_id
        from friendship_info
        union
        select user2_id,
               user1_id
        from friendship_info
        )
        select t01.user1_id,
               fi.sku_id    --这里不在使用collect_set,由于集合的方式无法进行比对.
        from tmp_01 t01
               join favor_info fi on t01.user1_id = fi.user_id) t1
        right join   --右关联(自己和朋友的收藏商品),关联上说明自己和朋友都收藏了该商品,没有关联上说明朋友收藏了,自己没有收藏,这里就需要进行推荐.
        (
    with tmp_02 as (
        select user1_id,
               user2_id
        from friendship_info
        union
        select user2_id,
               user1_id
        from friendship_info
    )
    select t02.user1_id, --我自己
           fi.sku_id     ---这里的集合就是朋友收藏的商品
    from tmp_02 t02
             join favor_info fi on t02.user2_id = fi.user_id ) t2
                    on t1.user1_id = t2.user1_id and t1.sku_id = t2.sku_id --关联条件,自己收藏的id和朋友收藏id相同时
where t1.user1_id is null --是null的说明朋友收藏的自己没有收藏.
group by t2.user1_id;
-----还有一个方式使用:集合和行进行包含操作array_contains()

在这里插入图片描述

总结:这里好友表的转化,查询朋友收藏商品的格式是(用户,用于朋友收藏的商品形式),便于后面的连接操作,用户id和用户商品id与朋友商品id之间的关联,来判断商品是否收藏.

5.男性和女性每日的购物总金额统计

表分析:需要有购物金额和性别,考虑订单信息表(order_info),用户信息表(user_info).
需求分析:直接将两个表进行join,并按照时间分组, 还有一种方式分别求出男女的时间和购物金额.

表结构(user_info)
用户id------------------------------性别----------------------------生日时间

代码演示

--方法一:
select create_date,
       ui.gender,
       sum(total_amount)
from user_info ui
         join order_info oi on ui.user_id = oi.user_id
group by oi.create_date , ui.gender;
---这一种方式的到的结果,不直观.

在这里插入图片描述

--方法二:分别得出男女的时间和购物金额,进而全连接.
select `if`(t1.create_date is null, t2.create_date,t1.create_date) data,
       cast(nvl(t1.man_sum,0.00) as decimal(16,2)),
       cast(nvl(t2.gril_sum,0.00) as decimal(16,2))
       from
(select create_date,
       sum(total_amount) man_sum
       from user_info ui
        join order_info oi on ui.user_id = oi.user_id
where ui.gender='男'
group by oi.create_date) t1
full join   ---全外连接
(select create_date,
       sum(total_amount) gril_sum
       from user_info ui
         join order_info oi on ui.user_id = oi.user_id
where ui.gender='女'
group by oi.create_date)t2
on t1.create_date=t2.create_date;
---这种方式有的繁琐.

在这里插入图片描述

--方法三:将第一种方法搭配sum(if())使用
select create_date,
       cast(sum(`if`(ui.gender='男',total_amount,0))as decimal(16,2)) man_amount,
       cast(sum(`if`(ui.gender='女',total_amount,0)) as decimal(16,2)) girl_amount
from user_info ui
         join order_info oi on ui.user_id = oi.user_id
group by create_date;

在这里插入图片描述

6.购买过商品1和商品2但是没有购买商品3的顾客

分析表:需要知道商品id和顾客购买了那些商品,考虑订单信息表(order_info)含有订单和用户,订单详情表(order_detail):含有一个商品所属订单id和商品id等其他详细信息.
需求分析:将两个表进行关联,然后进行用户分组,就可以得到一个用户购买过那些商品.然后判断是否含有1,2,3这些商品.

函数说明:

array_contains(A, B): 用于判断A集合中是否含有B这个元素.
collect_set(): 值转数组,去重.
collect_list(): 值转数组,不去重.

代码演示

--方法一:
select t1.user_id
from (
         select oi.user_id,
                `if`( array_contains( collect_set( od.sku_id ) , '1' ) and
                      array_contains( collect_set( od.sku_id ) , '2' ) and
                      !array_contains( collect_set( od.sku_id ) , '3' ) , 1 , 0 ) contain
         from order_detail od
                  join order_info oi
                       on od.order_id = oi.order_id
         group by oi.user_id) t1
where t1.contain = '1';
--方法二:不使用if将array_contains()放在where后进行判断.
select t1.user_id
from (
         select oi.user_id,
                collect_set(od.sku_id) s
         from order_detail od
                  join order_info oi
                       on od.order_id = oi.order_id
         group by oi.user_id) t1
where array_contains(t1.s,'1') and array_contains(t1.s,'2') and !array_contains(t1.s,'3');

结果:
在这里插入图片描述

7.统计每日商品1和商品2销量的差值

表分析: 每天的数据,商品,销量可以使用订单详情表(order_detail)
需求分析:按照时间分组,在使用求和函数+if函数分别得出1,2商品的销售数量.

代码演示

select create_date,
       sum( `if`( sku_id = '1' , sku_num , 0 ) ) num_1,
       sum( `if`( sku_id = '2' , sku_num , 0 ) ) num_2,
       sum( `if`( sku_id = '1' , sku_num , 0 ) )-sum( `if`( sku_id = '2' , sku_num , 0 ) ) sku_1_to_sku_2
from order_detail where sku_id in ('1','2') --过滤其他数据,以提高效率
group by create_date
order by create_date; --排序