SQL专项练习第二天

最新推荐文章于 2024-11-06 10:53:30 发布

天冬忘忧

最新推荐文章于 2024-11-06 10:53:30 发布

阅读量904

点赞数 19

分类专栏： SQL 文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_64726356/article/details/142423716

版权

SQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

在数据处理和分析中，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 都能提供高效的解决方案。