首页 > 数据库 >SQL专项练习第二天

SQL专项练习第二天

时间：2024-10-10 15:21:48浏览次数：10  
标签：10 专项 练习 when t1 num 2023 SQL select

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

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

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

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

解决方案：

  1. 首先使用date_format函数将dt中的日期提取出来，然后按用户 ID 和日期进行分组。
  2. 接着使用窗口函数row_number()和date_sub函数计算一个临时列tempdt，用于判断连续日期。
  3. 最后按用户 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 歌曲。

解决方案：

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

数据：

用户编号  歌单编号  歌单名称    歌曲编号    歌曲名称
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），要求查找所有至少连续出现三次的数字。

解决方案：

方案一：
  1. 使用窗口函数row_number()和date_sub函数计算一个临时列jyl，用于判断连续数字。
  2. 按jyl和num分组，统计数量大于等于 3 的数字。
方案二：
  1. 使用自连接，连接条件为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 都能提供高效的解决方案。

标签：10,专项,练习,when,t1,num,2023,SQL,select
From： https://blog.csdn.net/weixin_64726356/article/details/142423716

相关文章

  • SQL专项练习第六天
            Hive在处理不同数据需求时的灵活性和强大功能，包括间隔连续问题的处理、行列转换、交易数据查询、用户登录统计以及专利数据分析等方面。本文将介绍五个Hive数据处理问题的解决方案，并通过实际案例进行演示。        先在home文件夹下建一个hivedata......
  • [问题记录]SQLserver数据库是否可以新建多个.mdf文件?
    结论：1.可以，但只有第一个(.mdf)为当前数据库主文件。2.当有多个(.mdf)文件时，语句不会出现错误，但不符合命名约定，即命名约定不正确。3.数据库扩展名可以任意，官方文档中推荐主数据文件使用(.mdf)，辅数据文件使用(.ndf)，但如果使用例如：(.abc)作为文件后缀名，也是正确的。（具体官方文件截......
  • Day3 备战CCF-CSP练习
    Day3题目描述目前在一个很大的平面房间里有\(n\)个无线路由器，每个无线路由器都固定在某个点上。任何两个无线路由器只要距离不超过\(r\)就能互相建立网络连接。除此以外，另有\(m\)个可以摆放无线路由器的位置。你可以在这些位置中选择至多\(k\)个增设新的路由器。你的......
  • 安装了sqlite3依赖，编译源码报No package 'sqlite3' found
    如果已经安装了SQLite3.44.0，但在运行./configure时仍然出现“Nopackage'sqlite3'found”的错误，这通常是因为pkg-config没有找到相应的SQLite开发文件（.pc文件）。可按照以下步骤检查和修复此问题：1.确保pkg-config已安装首先，确认系统上安装了pkg-config工具：sudoyuminstall......
  • 团队练习记录10.9
    题目链接：https://qoj.ac/contest/1480这次有个强队去讲课，偶幸校队赛时第一C-CatchYouCatchMe队友写的，签到题吧？#include<bits/stdc++.h>#defineendl'\n'usingnamespacestd;typedeflonglongll;constintINF=0x3f3f3f3f;constllN=1e6+5;constllmod=1e9+......
  • 防止SQL攻击详解
    防止SQL注入攻击是保护数据库安全的重要一环。以下是一些有效的措施来防范SQL注入攻击：使用参数化查询或预编译语句：这是最推荐的方法，通过使用参数化查询（也称为预编译语句），可以确保用户输入的数据不会被解释为SQL代码。在大多数现代编程语言和数据库驱动程序中都支持这种方法。......
  • MSSQL-从字符串转换日期和/或时间时，转换失败
    1、报错的sql为：selectID,Test_timeas时间,fromProcessDatawhereconvert(datetime,test_time,120)betweenconvert(datetime,'2020-10-10',120)andconvert(datetime,'2024-10-11',120)   它是将Test_time转化为datetime格式，再用between进行比较；......
  • Mysql锁机制浅谈一
    mysql是如何加锁的？加锁默认是加临键锁，有特殊情况会优化为其他锁索引上的等值查询：唯一索引，给不存在的记录加锁时，优化为间隙锁普通索引，向右遍历至最后一个不满足查询条件的值时吗退化为间隙锁索引上的范围查询：唯一索引：访问到不满足条件的第一个值为止主键索引ps：如果是......
  • SQL server 存储过程与函数
    一、SQL的存储过程：存储过程其实就是一系列SQL语句的集合体，我们可以理解为一个封装单元，这个单元可以有出入参数，也可以没有。我们举几个简单的例子：（1）无入参无出参的存储过程：createprocusp_testasupdatestusetssex='0'wheressex=''returnexecusp_test（2）无入......
  • SQL Server 基础查询语句
    摘要在SQLServer中，查询语句是数据库管理员和开发人员最常用的工具之一，用于从数据库中提取、筛选和排序数据。以下是一些SQLServer查询语句的详细说明和示例：1、SELECT语句：这是最基本的查询语句，用于从一个或多个表中选择数据。例如，SELECT*FROMtable_name;将返回表中......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99