SQL专项练习第二天

🕗 发布于 2024-10-05 07:35 sql 数据库

在数据处理和分析中，Hive 是一个强大的工具。本文将通过五个 Hive 相关的问题展示其在不同场景下的应用技巧。

先在home文件夹下建一个hivedata文件夹，把我们所需的数据写成txt文件导入到/home/hivedata/文件夹下面。

一、找出连续活跃 3 天及以上的用户

问题描述：给定一个用户活跃表t_useractive，包含用户 ID（uid）和活跃时间（dt），要求找出连续活跃 3 天及以上的用户。

解决方案：

首先使用date_format函数将dt中的日期提取出来，然后按用户 ID 和日期进行分组。
接着使用窗口函数row_number()和date_sub函数计算一个临时列tempdt，用于判断连续日期。
最后按用户 ID 和tempdt分组，统计数量大于等于 3 的用户 ID。

-- 建表
create table t_useractive(
  uid   string,
  dt    string
);
-- 导入数据

insert into t_useractive
values('A','2023-10-01'),('A','2023-10-02'),('A','2023-10-03'),('A','2023-10-04'),
      ('B','2023-10-01'),('B','2023-10-03'),('B','2023-10-04'),('B','2023-10-05'),
      ('C','2023-10-01'),('C','2023-10-03'),('C','2023-10-05'),('C','2023-10-06'),
      ('D','2023-10-02'),('D','2023-10-03'),('D','2023-10-05'),('D','2023-10-06');

代码如下：

with t as (
    select uid, date_format(dt, 'yyyy-MM-dd') dt from t_useractive group by uid, date_format(dt, 'yyyy-MM-dd')
), t2 as(
    select *, date_sub(dt, row_number() over (partition by uid order by dt )) tempdt from t
) select uid from t2 group by uid, tempdt having count(1) >= 3;

二、统计每月会员数量

创建表和导入数据：

[root@hadoop11 data]# cat consumer.txt
1,2021-01-01,2022-01-01
2,2021-02-02,2022-02-02
3,2021-03-03,2022-03-03

create table t_consumer(
  consumerid  string,
  startdate   string,
  enddate     string
)row format delimited fields terminated by ',';

load data local inpath '/home/hivedata/consumer.txt' into table  t_consumer;

添加依赖和自定义 UDTF：

依赖

<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>3.1.2</version>
</dependency>

自定义UDTF

package com.bigdata;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

import java.util.ArrayList;
import java.util.List;

// 这个类是hive 自定义函数类
public class DateExplodeDemo extends GenericUDTF {

    // 定义函数的返回值名称以及数据类型
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        // 返回值的数据类型和名称
        //1.定义输出数据的列名和类型
        List<String> fieldNames = new ArrayList<String>();
        List<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
        //2.添加输出数据的列名和类型
        fieldNames.add("mt");

        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }

    public void process(Object[] args) throws  HiveException {
        String beginDate = args[0].toString();
        String endDate = args[1].toString();
        // 调用之前的方法，返回一个日期列表
        List<String> dateList = DateUtils.getDateList(beginDate, endDate);
        for(String date : dateList)
        {

            forward(new String[]{date});
        }
    }



    @Override
    public void close() throws HiveException {

    }
}

package com.bigdata;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class DateUtils {
    /**
     *  给定一个开始和结束日期，返回一个日期列表
     * @param beginDate  2021/1/1
     * @param endDate    2022/1/1
     *                   Jan-21
     * @return
     */
    public static  List<String> getDateList(String beginDate, String endDate) {

        // 返回值列表，中间存储日期
        List<String> list = new ArrayList<String>();
        // 解析传递过来的日期的格式
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM");
        // 将Date类型转换为字符串类型，前面是月份，后面是年份
        SimpleDateFormat sdf = new SimpleDateFormat("MMM-yyyy", Locale.ENGLISH);
        try {
            // 将传递过来的日期转换为Date类型
            Date dateFrom = simpleDateFormat.parse(beginDate);
            Date dateTo = simpleDateFormat.parse(endDate);

            // 因为需要用到Calendar 中的 获取下一个月的日期的函数
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(dateFrom);
            // 判断结束日期是否大于开始日期
            while (dateTo.after(calendar.getTime())) {
                // 将一个日期转为  月份-年份
                String yearMonth = sdf.format(calendar.getTime());
                System.out.println(yearMonth);
                list.add(yearMonth);
                calendar.add(Calendar.MONTH,1);
            }
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }

        // list中存放的是月份-年份
        return list;
    }

    public static void main(String[] args) {
        System.out.println(getDateList("2021/1/1","2022/1/1"));
    }
}

打 jar 包并在 Hive 中使用：

add jar /opt/installs/hive/lib/MyFunction-1.0-SNAPSHOT.jar;
create temporary function k1 as 'com.bigdata.DateExlode';

编写 SQL 统计每月会员数量：

select mt,count(*) c1
from(
  select consumerid,mt from t_consumer lateral view k1(startdate,enddate) t1 as mt
)t2 group by mt

假如需要变为以上带有英文的输出，可以使用如下 SQL

select
    concat(
        case split(mt,'-')[1] when '01' then 'Jan'
        when '01' then 'Jan'
        when '02' then 'Feb'
        when '03' then 'Mar'
        when '04' then 'Apr'
        when '05' then 'May'
        when '06' then 'Jun'
        when '07' then 'Jul'
        when '08' then 'Aug'
        when '09' then 'Sept'
        when '10' then 'Oct'
        when '11' then 'Nov'
        when '12' then 'Dec'
        end, '-', substr(mt,3,2)
    ), count(1) from t_consumer lateral view k1(startdate,enddate) t as  mt group by mt ;

假如以上结果需要按照日期排序

可以使用如下方式，造出来一个这样的表：

select * from huiyuan order by
    split(dt,'-')[1] ,
    case split(dt,'-')[0]
        when 'Jan' then 1
        when 'Feb' then 2
        when 'Mar' then 3
        when 'Apr' then 4
        when 'May' then 5
        when 'Jun' then 6
        when 'Jul' then 7
        when 'Aug' then 8
        when 'Sept' then 9
        when 'Oct' then 10
        when 'Nov' then 11
        when 'Dec' then 12 end;

三、统计每个 Top3 歌单以及 Top3 歌单下的 Top3 歌曲

问题描述：有一个听歌流水表songs，存储了用户听歌单歌曲的记录，要求统计每个 Top3 歌单以及 Top3 歌单下的 Top3 歌曲。

解决方案：

首先找到排名前三的歌单，通过对歌单 ID（lid）进行分组计数，然后使用窗口函数dense_rank()进行排名。
接着根据排名前三的歌单 ID，统计每个歌单下的歌曲播放次数，并再次使用窗口函数dense_rank()进行排名。
最后选择排名前三的歌曲。

数据：

用户编号  歌单编号  歌单名称    歌曲编号    歌曲名称
1   1  经典老歌    1   月亮代表我的心
2   1  经典老歌    1   月亮代表我的心
3   1  经典老歌    3   夜来香
4   1  经典老歌    4   我只在乎你
5   1  经典老歌    5   千言万语
6   1  经典老歌    5   千言万语
7   2  流行金曲    7   突然好想你
8   2  流行金曲    8   后来
9   2  流行金曲    9   童话
10  2  流行金曲    10  晴天
11  2  流行金曲    7   突然好想你
12  2  流行金曲    7   突然好想你
13  3  纯音乐集    13  二泉映月
14  3  纯音乐集    14  琵琶语
15  3  纯音乐集    15  梦回还
16  4  欧美音乐    16  Shape of My Heart
17  4  欧美音乐    17  Just the Way You Are
18  4  欧美音乐    18  Hello
19  4  欧美音乐    19  A Thousand Years
20  4  欧美音乐    20  Thinking Out Loud
21  4  欧美音乐    20  Thinking Out Loud
22  4  欧美音乐    18  Hello
23  4  欧美音乐    18  Hello
24  5  民谣时光    24  易燃易爆炸
25  5  民谣时光    25  成全
26  5  民谣时光    25  成全
27  5  民谣时光    25  成全

建表

-- 创建表
create table song(
    uid int,
    pid int,
    pname string,
    sid int,
    sname string
)row format delimited
fields terminated by ' '
tblproperties("skip.header.line.count"="1");
-- 导入数据
load data local inpath '/home/hivedata/songs.txt' into table song;

代码如下：

-- 先找到 top3 歌单
with t as (
    select lid,count(1) num from songs group by lid
),
t2 as (
select *,dense_rank() over (order by num desc) xh from t
) select lid from t2 where xh <=3;
-- 根据 top3 歌单的数据进行排序，取前三名
with t as (
    select lid,count(1) num from songs group by lid
),
t2 as (
select *,dense_rank()
    over (order by num desc) xh from t
),
t3 as(
select lid from t2 where xh <=3
),t4 as (
    select distinct list_name,song_name,count(1) over(partition by song_name ) cs  from songs where lid in (select lid from t3)
),t5 as (
    select *,dense_rank() over (partition by list_name order by cs desc) xh from t4
)
select list_name,song_name from t5 where xh <=3;

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

问题描述：给定一个学生成绩表t1，包含学生姓名（name）、课程名称（course）和成绩（grade），要求查询出每门课都大于 80 分的学生姓名。

解决方案：

使用group by和having子句，先按学生姓名分组，然后计算每个学生的最低成绩，最后筛选出最低成绩大于等于 80 分的学生姓名。

建表：

-- 建表
create table t1(
     name   string,
     course string,
     grade  int
);
-- 导入数据
INSERT INTO t1 (name, course, grade) VALUES
('张三', '英语', 85),
('张三', '语文', 90),
('张三', '数学', 92),
('李四', '英语', 75),
('李四', '语文', 98),
('李四', '数学', 72),
('王五', '英语', 90),
('王五', '语文', 85),
('王五', '数学', 92),
('王五', '体育', 80);

代码如下：

select sc.name from (select t1.name,
    sum(case  when t1.course='英语' then t1.grade else 0 end) english,
    sum(case  when t1.course='语文' then t1.grade else 0 end) chinese,
    sum(case  when t1.course='数学' then t1.grade else 0 end) math,
    sum(case  when t1.course='体育' then t1.grade else 0 end) pe
from t1 group by name) sc
    where english >=80 and chinese >=80 and math >= 80 and pe >= 80
group by name;

假如不考虑没考试的课

select name,min(grade) minScore from t1 group by name having minScore >=80;

五、连续出现的数字

问题描述：有一个表Logs，包含 ID（id）和数字（num），要求查找所有至少连续出现三次的数字。

解决方案：

方案一：

使用窗口函数row_number()和date_sub函数计算一个临时列jyl，用于判断连续数字。
按jyl和num分组，统计数量大于等于 3 的数字。

方案二：

使用自连接，连接条件为l1.id = l2.id+1、l2.id = l3.id + 1、l1.num = l2.num和l2.num = l3.num。

建表：

-- 建表
create table logs(
    id int,
    num string
)row format delimited
fields terminated by ' ';

-- 导入数据
load data local inpath '/home/hivedata/logs.txt' into table logs;

代码如下：

-- 方案一
with t as (
    select *, row_number() over (partition by num order by id ),
           id - row_number() over (partition by num order by id) jyl from logs
) select num, count(1) from t group by jyl, num having count(1) >= 3;
-- 方案二
select l1.num from logs l1, logs l2, logs l3
    where l3.id = l2.id+1 and l2.id = l1.id+1 and l1.num = l2.num and l2.num = l3.num;

通过以上五个问题的解决，展示了 Hive 在数据处理和分析中的强大功能和灵活性。无论是找出连续活跃的用户、统计会员数量、分析歌单数据、筛选学生成绩还是查找连续出现的数字，Hive 都能提供高效的解决方案。

原文地址：https://blog.csdn.net/weixin_64726356/article/details/142423716

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：PyTorch实现卷积神经网络CNN
下一篇：C#串口温度读取

小白必看一文彻底搞懂 Transformer（图解+手撕）
Transformer 通过其捕捉上下文和理解语言的能力，彻底改变了自然语言处理（NLP）领域。通过注意力机制、编码器-解码器架构和多头注意力，它们使得诸如机器翻译和情感分析等任务得以在前所未有的规模
阅读更多2024-10-06
【Java SE 题库】移除元素(暴力解法)--力扣
假设nums中不等于val的元素数量为k，要通过此题，您需要执行以下操作：更改nums数组，使nums的前k个元素包含不等于val的元素。nums的其余元素和nums的大小并不重要。返回k
阅读更多2024-10-06
Vue - 路由用法
前端路由就是URL中的hash与组件之间的对应关系。到地址 C ，从而展示特定的组件页面。在组件中，使用 vue-router 提供的。路由重定向指的是：用户在。是Vue的官方路由。
阅读更多2024-10-06
如何基于vite实现清除特定环境下的console和debugger
Terser 是一个 JavaScript 代码压缩工具，通过去除不必要的空格、换行和注释，能够减少 JavaScript 文件的大小，提高页面加载速度。
阅读更多2024-10-06
MySql的基本语法操作
查看所有的数据库建立一个新的数据库也可以是表示这个数据库不存在才建立而不会打断其他sql语句的执行，而如果没有加的话，创建的数据库存在就会直接报错终止程序的执行数据库中储存数据的表是数据的主要组织单
阅读更多2024-10-06
[C++][第三方库][httplib]详细讲解
[C++][第三方库][httplib]详细讲解
阅读更多2024-10-06
STM32重启源深度解析
STM32单片机的重启是指单片机在运行过程中因各种原因导致系统复位，并重新启动程序执行的过程。重启可以分为硬件层面和软件层面两类。硬件层面的重启主要由外部硬件因素引起，如电源异常、复位电路故障、外部干
阅读更多2024-10-06
Github 2024-10-02C开源项目日报 Top9
根据Github Trendings的统计，今日(2024-10-02统计)共有9个项目上榜。
阅读更多2024-10-06
SHA-1 是一种不可逆的、固定长度的哈希函数，在 Git 等场景用于生成唯一的标识符来管理对象和数据完整性
是一种，它能将任意大小的数据（如文件、消息）转换为一个固定长度的 160 位（20 字节）哈希值。这种哈希值通常以 40 个十六进制字符的形式表示，是数据的“指纹”或“摘要”。
阅读更多2024-10-06
【C++驾轻就熟】vector深入了解及模拟实现
erase删除pos位置元素后，pos位置之后的元素会往前搬移，没有导致底层空间的改变，理论上讲迭代器不应该会失效，但是：如果pos刚好是最后一个元素，删完之后pos刚好是end的位置，而end位置
阅读更多2024-10-06

SQL专项练习第二天

一、找出连续活跃 3 天及以上的用户

解决方案：

代码如下：

二、统计每月会员数量

创建表和导入数据：

添加依赖和自定义 UDTF：

打 jar 包并在 Hive 中使用：

编写 SQL 统计每月会员数量：

假如需要变为以上带有英文的输出，可以使用如下 SQL

假如以上结果需要按照日期排序

三、统计每个 Top3 歌单以及 Top3 歌单下的 Top3 歌曲

解决方案：

数据：

建表

代码如下：

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

解决方案：

建表：

代码如下：

假如不考虑没考试的课

五、连续出现的数字

解决方案：

方案一：

方案二：

建表：

代码如下：

相关文章