首页 > 其他分享 >HQL 55 题【持续更新】

HQL 55 题【持续更新】

时间:2024-03-13 21:31:51浏览次数:18  
标签:55 更新 order HQL user date login id select

前言

        今天开始为期一个多月的 HQL 练习,共 55 道 HQL 题,大概每天两道,从初级函数到中级函数。这次的练习不再是基础的 join 那种通用 SQL 语法了,而是引入了更多 Hive 的函数(单行函数、窗口函数等)。  

        我会把 HQL 中函数和语法的一些注意事项写在每一题下面的 "知识点" 中,方便上课复习。同样这博客估计没人看,如果谁实在需要建表语句给我留言就行。

3-10

1、查询累积销量排名第二的商品(中级)

SELECT sku_id from(
    SELECT sku_id,
        rank() OVER(ORDER BY order_sum desc) rk
        from(
          SELECT sku_id,sum(sku_num) order_sum
            FROM order_detail
            GROUP BY sku_id
            ORDER BY order_sum desc
            LIMIT 2
        )as t1
  )as t2
WHERE rk=2;

知识点:

  • SQL 中 distinct 必须跟在 select 之后
  • distinct 不能单独用于选择性地仅对结果集中的某个字段去重,而不影响其他字段
select distinct sku_id, sku_num,rk from(
    ...
);
-- 尽管查询结果中 sku_id 字段的值可能重复,但是不能通 select distinct 来对单个属性去重
sku_id    sku_num    rk
1            2        1
1            3        2
  • Hive 的子查询必须要有别名 !

3-12

1、筛选2021年总销量小于100的商品(初级)

  • 需求:从订单明细表(order_detail)中筛选出2021年总销量小于100的商品及其销量,假设今天的日期是2022-01-10,不考虑上架时间小于一个月的商品。
  • 思路:拿 2021 年总销量小于100的商品id和上架时间大于30的商品id进行join
order_detail_idorder_idsku_idcreate_datepricesku_num
1112021-09-272000.002
2132021-09-275000.005
3242021-09-286000.009
4252021-09-28500.0033

2.1、查询出2021年总销量小于 100 的商品

-- 1.1 2021年销售总量小于100的商品
select sku_id, sum(sku_num) order_sum
from order_detail
where year(create_date)=2021
group by sku_id
having order_sum<100;

2.2、查询出上架时间大于30天的商品

-- 1.2 上架时间小于 30 天的商品
select sku_id,name from sku_info
where datediff('2022-01-10',from_date)>30;

2.3、join

-- join 两个子表
select t1.sku_id,name from (
     select sku_id, sum(sku_num) order_sum
    from order_detail
    where year(create_date)=2021
    group by sku_id
    having order_sum<100
    )t1 join (
        select sku_id,name from sku_info
        where datediff('2022-01-10',from_date)>30
    )t2 on t1.sku_id = t2.sku_id;

知识点

  • datediff('2022-01-10','2021-01-10') = 365,注意:日期1必须大于日期2否则结果是负数

2、查询每日新增用户(初级)

uer_idip_addresslogin_tslogout_ts
101180.149.130.1612021-09-21 08:00:002021-09-27 08:30:00
101180.149.130.1612021-09-27 08:00:002021-09-27 08:30:00
101180.149.130.1612021-09-28 09:00:002021-09-28 09:10:00
101180.149.130.1612021-09-29 13:30:002021-09-29 13:50:00

 思路1:每天有多少人是首日登录就有多少新增用户。查询出每个用户的首日登录时间,然后按照日期分组聚合就得到了每日新增用户。而不是去考虑开窗(我是这么想的)

思路2:开窗也可以实现,用 row_numer 对每个用户的登录时间进行排名(group by user_id),然后根据登录时间进行分区将该天 row_number=1 的值(说明是首次登录)进行聚合。

思路1

2.1、查询用户首日登录日期
-- 查询用户首次登录的日期
select user_id,min(date_format(login_ts,'yyyy-MM-dd')) first_login_date
from user_login_detail
group by user_id;
2.2、查询每天有多少用户是首日登录
-- 按照日期分组得到每天的新增用户
select first_login_date,count(*) from(
    select user_id,min(date_format(login_ts,'yyyy-MM-dd')) first_login_date
           from user_login_detail
            group by user_id
        )t1
group by first_login_date;

注意:怎么把 login_ts (格式:2021-09-21 08:00:00)这种时间字符串指定的字段取出来?

我是这么实现的:

select concat_ws('-',string(year(date_format(login_ts,'yyyy-MM-dd HH:mm:ss'))),string(month(date_format(login_ts,'yyyy-MM-dd HH:mm:ss'))),string(day(date_format(login_ts,'yyyy-MM-dd HH:mm:ss')))),

标准:

select date_format(login_ts,'yyyy-MM-dd') from user_login_detail;

思路2

select dt,sum(`if`(rk=1,1,0)) new_user_nums from(
    select user_id,
       date_format(login_ts,'yyyy-MM-dd') dt,
       row_number() over (partition by user_id order by login_ts) rk
        from user_login_detail
        )t1
group by dt
having new_user_nums>0;

3、用户注册、登录、下单综合统计(初级)

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

思路:无脑 join 没有什么难度

order_info

序号编号日期金额
11012021-09-2729000.00
21012021-09-2870500.00
31012021-09-2943300.00
41012021-09-30860.00

 user_login_detail

3.1、用户首日登录日期

-- 用户首日登录日期
select user_id,min(date_format(login_ts,'yyyy-MM-dd')) register_date
from user_login_detail 
group by user_id;

注意:能 group by 就 group by 不然 join 之后报错。

3.2、用户累积登录次数

-- 用户累积登录次数
select user_id,size(collect_set(date_format(login_ts,'yyyy-MM-dd'))) total_login_count
from user_login_detail
group by user_id;

知识点: 利用 collect_set() 把登录日期收集到一个集合里,正好做了去重,就不用担心用户一天登录多次的情况了。

3.3、用户2021年登录次数

-- 用户2021登录次数
select user_id,size(collect_set(date_format(login_ts,'yyyy-MM-dd'))) login_count_2021
from user_login_detail
where year(date_format(login_ts,'yyyy-MM-dd'))=2021
group by user_id;

3.4、用户2021年下单次数和下单金额

-- 用户2021年下单次数和下单金额
select user_id,count(order_id) order_count_2021,sum(total_amount) order_amount_2021
from order_info
where year(create_date)=2021
group by user_id,year(create_date);

3.5、join起来

select t1.user_id,register_date,total_login_count,login_count_2021,order_count_2021,order_amount_2021 from(
     select user_id,min(date_format(login_ts,'yyyy-MM-dd')) register_date from user_login_detail group by user_id
    )t1 join (
        select user_id,size(collect_set(date_format(login_ts,'yyyy-MM-dd'))) total_login_count
            from user_login_detail
            group by user_id
    )t2 on t1.user_id=t2.user_id
join (
    select user_id,size(collect_set(date_format(login_ts,'yyyy-MM-dd'))) login_count_2021
from user_login_detail
where year(date_format(login_ts,'yyyy-MM-dd'))=2021
group by user_id
    )t3 on t1.user_id=t3.user_id
join (
    select user_id,count(order_id) order_count_2021,sum(total_amount) order_amount_2021
        from order_info
        where year(create_date)=2021
        group by user_id,year(create_date)
    )t4 on t1.user_id=t4.user_id;

3.13

1、向用户推荐朋友收藏的商品

需求:请向所有用户推荐其朋友收藏但是自己未收藏的商品,从好友关系表(friendship_info)和收藏表(favor_info)中查询出应向哪位用户推荐哪些商品。

firendship_info

user1_iduser2_id
1011010
101108
101106
101104

favor_info

user_idsku_idcreate_date
10132021-09-23
101122021-09-23
10162021-09-25
101102021-09-21

思路

  • 核心就是 left join ,因为 left join 可以把保留左表的内容(这里我们保留的是好友的商品收藏表),我们只要根据用户喜欢的商品id和好友喜欢的商品id进行 left join ,得到的字段"sku_id"如果不为 null 就说明这件商品他俩都收藏了,如果为 null 就说明这件商品好友收藏了,但是用户没有收藏。

1.1、获取用户所有好友

-- 查询所有用户的好友
select user1_id user_id,user2_id friend_id from friendship_info
union
select user2_id,user1_id from friendship_info;

知识点

  • join 是横向合并,会形成宽表;而 union 是纵向合并,形成长表(union 会对结果进行排序去重,union all 不会)

1.2、得到用户好友的收藏列表

-- join得到用户好友收藏的商品
    select user1_id user_id,user2_id friend_id from friendship_info
    union
    select user2_id,user1_id from friendship_info
    join favor_info firend_favor
    on user2_id=firend_favor.user_id;

1.3、left join 过滤

select distinct t1.user_id,firend_favor.sku_id
from (
    select user1_id user_id,user2_id friend_id from friendship_info
    union
    select user2_id,user1_id from friendship_info
)t1
    join favor_info firend_favor
    on t1.friend_id=firend_favor.user_id
    left join favor_info user_favor
    on t1.user_id=user_favor.user_id and firend_favor.sku_id=user_favor.sku_id
    where user_favor.sku_id is null;

2、男性和女性每日的购物总金额统计(初级)

需求:从订单信息表(order_info)和用户信息表(user_info)中,分别统计每天男性和女性用户的订单总金额,如果当天男性或者女性没有购物,则统计结果为0。

order_info

user_info

编号性别出生日期
1011990-01-01
1021991-02-01
1031992-03-01
1041993-04-01

思路1

1、获取不同性别的消费信息
select t2.gender,t1.create_date,t1.total_amount
from order_info t1
join user_info t2 on t1.user_id=t2.user_id

 我们没有必要查询用户的 id 信息,只需要性别(后面我们需要根据性别过滤)、创建订单的日期(后面我们需要根据日期分组)和订单总额(我们需要根据不同性别统计每天的订单总额)即可。

2、按照日期 join 不同性别的每天销售总额
select coalesce(t3.create_date,t4.create_date),`if`(t3.total_amount_male is null,0,t3.total_amount_male),`if`(t4.total_amount_female is null ,0,t4.total_amount_female) from(
                       select create_date,sum(total_amount) total_amount_male from(
    select t2.gender,t1.create_date,t1.total_amount
        from order_info t1
        join user_info t2 on t1.user_id=t2.user_id
    )t1
    where gender='男'
    group by create_date
    )t3 full join (
        select create_date,sum(total_amount) total_amount_female from(
        select t2.gender,t1.create_date,t1.total_amount
            from order_info t1
            join user_info t2 on t1.user_id=t2.user_id
        )t2
    where gender='女'
    group by create_date
    )t4 on t3.create_date=t4.create_date

知识点

  • 显然 t3 和 t4 这两个子表分别是男性和女性的每天购物总额,这里我们进行的是 full join 这样会保留两张表的所有数据,因为数据中存在某 一天男生购物了但是女生没有,或者女士购物了男性没有。
  • 对于最后查询结果的日期字段就需要保证这个日期不能为 null,但是我们又不能显示 t3 t4 两个日期,所以我们使用了 coalesce 字段来获取非 null 的日期字段(前后顺序并不影响)
  • COALESCE 函数用于返回多个表达式中的第一个非NULL值。

思路2

思路1是我自己实现的一种方式,思路2是答案,不得不说还是这种写法高级:

select create_date,
       cast(sum(`if`(gender='男',total_amount,0)) as decimal(16,2)) total_amount_male,
       cast(sum(`if`(gender='女',total_amount,0)) as decimal(16,2)) total_amount_female
from order_info oi
join user_info ui on oi.user_id=ui.user_id
group by create_date;

知识点

  • cast(expr as <type>):将expr的执行结果转换为<type>类型的数据并返回,expr可以是函数(可以嵌套)、字段或字面值。转换失败返回null,对于cast(expr as boolean),对任意的非空字符串expr返回true

  • decimal(精度,标度):比如 decimal(16,2)表示一个十进制数,其中16是总的数字数量(精度),而2是小数点后的数字数量(标度)

标签:55,更新,order,HQL,user,date,login,id,select
From: https://blog.csdn.net/m0_64261982/article/details/136598675

相关文章

  • 如何关闭Windows自动更新
    前言作为一个Windows系统的资深用户,我们最难受的点肯定有Windows每个月的定期更新有时选择拒绝更新,系统会一直不停的提示系统更新,更新了之后,就会发现电脑的存储空间又要被占满了。所以,我们能不能关闭自动更新呢?当然可以,而且我们的方法不只一种,本文就来给大家介绍一下关闭Windows......
  • 【持续更新】华为 OD 机试 C卷抽中题库清单(全真题库)含考点说明以及在线OJ
    华为OD机考:统一考试C卷+D卷+B卷+A卷2023年11月份,华为官方已经将华为OD机考:OD统一考试(A卷/B卷)切换到OD统一考试(C卷)和OD统一考试(D卷)。目前在考C卷,经过两个月的收集整理,C卷真题已基本整理完毕抽到原题的概率为2/3到3/3,也就是最少抽到两道原题。请注意:大家刷......
  • 编写Makefile文件语法,持续更新中~
    一、什么是Makefile?我们写大型项目时,会用到很多源文件,源文件在不同目录中的文件夹里包含着,一个一个编译起来很麻烦,makefile就能够方便我们编译链接。使用makefile进行编译连接时会用到make命令,Makefile的会在执行make命令时指定编译和链接的规则,包括源代码文件之间的链接......
  • 【DL经典回顾】激活函数大汇总列表(持续更新中...)
    激活函数大汇总列表(持续更新中…)欢迎来到我们深入探索神经网络核心组成部分——激活函数的系列博客。在人工智能的世界里,激活函数扮演着不可或缺的角色,它们决定着神经元的输出,并且影响着网络的学习能力与表现力。鉴于激活函数的重要性和多样性,我们将通过一系列博客详细介绍......
  • Acwing255.第k小数
    可持久化权值线段树#include<iostream>#include<stdio.h>#include<algorithm>#include<string>#include<cmath>#include<cstring>#include<vector>#defineFor(i,j,n)for(inti=j;i<=n;++i)usingnamespace......
  • 蓝队应急响应工具箱v10 [重要更新]
    / 蓝队工具箱V10 /1  简介蓝队工具箱是为打造一款专业级应急响应的集成多种工具的工具集,由真实应急响应环境所用到的工具进行总结打包而来,由ChinaRan404,W啥都学,清辉等开发者编写.把项目现场中所用到的工具连同环境一同打包,并实现“可移植性”“兼容性”“使用便......
  • 55_创建私人服务
    脚本模板!/bin/bashNOW_SIZE=REQUIRE_SIZE=2500LOG_FILE=/var/log/mem.logSERVICE_STATUS=0functioncheck_mem(){NOW_SIZE=$(free-m|awk'/Mem/{print$4}')if[[$NOW_SIZE-le$REQUIRE_SIZE]];thenecho"警告:$(date+%F_%H:%m:%S)内存低于2.5G,......
  • 工作中用到的一些git操作(持续更新)
    绑定远程gitgitremoteaddupstreamhttps://xxx.gitgitpullupstreammaster 通过命令指定upstream:比如,当前代码仓跟踪了两个upstream:$gitremote-vnisssh://[email protected](fetch)nisssh://[email protected](push)originssh://[email protected](fetch)originssh:......
  • Ubuntu更新显卡驱动失败解决
    在更新显卡驱动的时候无论是命令还是手动点都是显示报错的sudoaptinstallnvidia-driver-535报错信息为:错误:18http://security.ubuntu.com/ubuntufocal-security/restrictedamd64nvidia-driver-535amd64535.146.02-0ubuntu0.20.04.1404NotFound[IP:2620:2......
  • CF-933(已更新:B-D)
    CF-933当天晚上舍友在玩剧本杀,不得不说那剧情实在是太狗血了,想不通他们是怎么能玩得那么起劲的但也不能当作这次发挥不好的借口/_\A题最开始没看到数据范围(D也是),B一开始就想到了思路,但调了二十多分钟,甚至因为数组开小了白白多了一次RE……D题才是最难绷的,把题看懂后自己就用......