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

SQL专项练习第六天

时间：2024-10-10 15:21:11浏览次数：3  
标签：count patent 练习 第六天 user SQL date apply select

        Hive 在处理不同数据需求时的灵活性和强大功能，包括间隔连续问题的处理、行列转换、交易数据查询、用户登录统计以及专利数据分析等方面。本文将介绍五个 Hive 数据处理问题的解决方案，并通过实际案例进行演示。

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

一、间隔连续问题

问题描述：给定一个游戏公司记录的用户每日登录数据表，要求计算每个用户最大的连续登录天数，可以间隔一天。

解决方案：

  1. 使用窗口函数lead获取每个用户下一次登录的日期，并计算与当前登录日期的天数差。
  2. 通过条件判断，如果天数差大于 2，则视为中断，否则继续累计连续登录天数。
  3. 使用窗口函数sum和条件判断，为连续登录的记录分配一个组 ID。
  4. 最后计算每个用户每个组的连续登录天数，并取最大值作为该用户的最大连续登录天数。

数据：

id         dt
1001 2021-12-12
1002 2021-12-12
1001 2021-12-13
1001 2021-12-14
1001 2021-12-16
1002 2021-12-16
1001 2021-12-19
1002 2021-12-17
1001 2021-12-20

建表：

-- 建表
create table games_login_data(
    id int,
    dt string
)row format delimited
fields terminated by ' '
tblproperties("skip.header.line.count"="1");

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

代码如下：
with t as (
  select *,
  lead(dt,1,dt) over (partition by id order by dt ) next_dt,
    if(
        datediff(lead(dt, 1, dt) over (partition by id order by dt ), dt) > 2, null,
        datediff(lead(dt, 1, dt) over (partition by id order by dt ), dt)
    )days
  from games_login_data
),t2 as (
  select *,sum(if(days <=2 ,0,1)) over (partition by id order by dt) groupId from t
),t3 as (
  select id,sum(days)+1 activeDays from t2 group by id,groupId
)
select id,max(activeDays) from t3 group by id;

二、行列转换

问题描述：有一个表记录了各年份各部门的平均绩效考核成绩，要求进行行列转换。

解决方案：

  1. 使用case when语句和聚合函数，按照年份进行分组，对不同部门的绩效得分进行条件判断并聚合。
  2. 通过case when语句将部门作为列名，绩效得分作为对应的值，实现行转列的效果。

数据：

t1.a    t1.b    t1.c
2014    B       9
2015    A       8
2014    A       10
2015    B       7

建表：

-- 建表
create table t25(
  a string,
  b string,
  c int
)row format delimited
fields terminated by ',';
-- 导入数据
load data local inpath '/home/hivedata/t25.txt' into table t25;

代码如下：
1）多行转多列
-- 多行转多列
select a,
    max(case  when b='A' then c else 0 end) col_A,
    max(case  when b='B' then c else 0 end) col_B
from t25
group by a;
2）将结果转换为源表（多列转多行）
-- 结果表
create table t25_1 as
    select a,
    max(case  when b='A' then c else 0 end) col_A,
    max(case  when b='B' then c else 0 end) col_B
    from t25
    group by a;
-- 查询
select * from t25_1;
-- 多列转多行
select a, 'A' b, col_A c from t25_1
union all
select a, 'B' b, col_B c from t25_1;
3）多个绩效求多行转多列
-- 建表
create table t26(
  a string,
  b string,
  c int
)row format delimited
fields terminated by ',';
-- 导入数据
load data local inpath '/home/hivedata/t26.txt' into table t26;
-- 查询
select * from t26;
-- 多个绩效求多行转多列
select a,
    concat_ws(',', collect_list(case  when b='A' then cast(c as string) end)) col_A,
    concat_ws(',', collect_list(case  when b='B' then cast(c as string) end)) col_B
from t26
group by a;

三、交易表查询

建表：

create table transactions(
    user_id int,
    order_id int,
    pay_time string,
    order_amount decimal(10, 2)
)row format delimited
fields terminated by ',';
-- 导入数据 数据为AI生成
load data local inpath '/home/hivedata/transactions.txt' overwrite into table transactions;

查询过去一个月付款用户量最高的三天：

  • 使用date_format函数将付款时间转换为日期格式。
  • 使用count(distinct)统计每天不同的付款用户数量。
  • 使用where子句筛选出过去一个月的付款记录。
  • 按照付款用户数量降序排序，取前三天的记录。
代码如下：
方法一
-- 方法一
select to_date(pay_time), count(user_id) from transactions
where to_date(pay_time) >= date_sub(current_date(), 30)
group by to_date(pay_time)
order by count(user_id) desc
limit 3;
方法二
-- 方法二
with t as (
    select to_date(pay_time) days, count(user_id) countOrder from transactions
    where to_date(pay_time) >= date_sub(current_date(), 30)
    group by to_date(pay_time)
)select days, countOrder from t
order by countOrder desc limit 3;

查询昨天每个用户最后付款的订单 ID 及金额：

  • 使用窗口函数row_number按照用户 ID 和付款时间降序排序，为每个用户的付款记录分配一个序号。
  • 使用where子句筛选出昨天的付款记录。
  • 选择序号为 1 的记录，即每个用户昨天最后付款的记录。

代码如下：
select user_id, order_id, order_amount, pay_time from (
    select user_id, order_id, order_amount, pay_time, row_number() over (
    partition by user_id order by to_date(pay_time) desc ) as rn
    from transactions
    where to_date(pay_time) = date_sub(current_date(), 1)) t
where rn = 1;

四、近 30 天每天平均登录用户数量

问题描述：给定一个用户登录日志表，要求查询近 30 天每天平均登录用户数量。

解决方案：

  1. 使用date_format函数将登录时间转换为日期格式。
  2. 使用count(distinct)统计每天不同的登录用户数量。
  3. 使用where子句筛选出近 30 天的登录记录。
  4. 对每天的登录用户数量进行平均计算。

建表：

-- 建表
create table user_logs(
    user_id int,
    log_id int,
    session_id string,
    visit_time string
)row format delimited
fields terminated by ',';

-- 导入数据 数据为AI生成
load data local inpath '/home/hivedata/user_logs.txt' overwrite into table user_logs;

代码如下：
select avg(userNum) as `每天平均登录用户数量`
from(
    select to_date(visit_time), count(distinct user_id) userNum from user_logs
where  to_date(visit_time) >= date_sub(current_date(), 30)
group by to_date(visit_time)) as t;

五、各类型专利 top 10 申请人及专利申请数

问题描述：给定一个专利明细表，要求查询各类型专利 top 10 申请人以及对应的专利申请数。

1）表名：t_patent_detail （专利明细表）

2）表字段：专利号(patent_id)、专利名称(patent_name)、专利类型(patent_type)、申请时间

(aplly_date)、授权时间(authorize_date)、申请人(apply_users)

3）说明：同一个专利，可以有1到多个申请人，多人之间按分号隔开。

4）请写出hive查询语句，各类型专利top 10申请人，以及对应的专利申请数

解决方案：

  1. 首先使用lateral view explode函数将申请人字段拆分成多行。

  2. 然后按照申请人进行分组，统计每个申请人的专利申请数。

  3. 使用窗口函数rank按照专利申请数降序排序，为每个申请人分配一个排名。

  4. 最后选择排名在前 10 的申请人及其专利申请数。

建表：

-- 建表
create table t_patent_detail(
    patent_id string,
    patent_name string,
    patent_type string,
    apply_date string,
    authorize_date string,
    apply_users string
)row format delimited
fields terminated by '\t'
tblproperties("skip.header.line.count"="1");

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

代码如下：
方法一

  1. 使用lateral view explode函数将apply_users字段拆分成多行，每个申请人成为一条独立的记录。

  2. 按patent_type（专利类型）和apply_user（申请人）进行分组，统计每个申请人的专利申请数。

  3. 最后按照专利类型和申请数降序排序。

-- 方法一
select patent_type, apply_user, count(*) as application_count
from (
  select patent_type, apply_user from t_patent_detail
  lateral view explode(split(apply_users, '；')) t1 as apply_user
) t2
group by patent_type, apply_user
order by patent_type, application_count desc ;
方法二

与方法一类似，先使用lateral view explode函数拆分申请人字段，然后按专利类型和申请人分组统计申请数，最后排序。

with t as (
    select patent_type, apply_user from t_patent_detail
  lateral view explode(split(apply_users, '；')) t1 as apply_user
) select  patent_type, apply_user, count(*) as application_count
from t group by patent_type, apply_user
order by patent_type, application_count desc ;
方法三

  1. 首先同样使用lateral view explode函数拆分申请人字段，得到中间表t2。

  2. 对t2按专利类型和申请人分组，统计申请数，并使用窗口函数row_number()按照申请数降序为每个专利类型内的申请人分配排名。

  3. 筛选出排名小于等于 10 的记录，即每个专利类型的 top 10 申请人。

  4. 最后按照专利类型和申请数降序排序。

select patent_type, apply_user, application_count
from (
    select patent_type, apply_user, count(*) as application_count,
           row_number() over (partition by patent_type order by count(*) desc ) as rank
    from (
        select patent_type, apply_user from t_patent_detail
        lateral view explode(split(apply_users, '；')) t1 as apply_user
        ) t2
    group by patent_type, apply_user
    ) t3 where t3.rank <=10
order by patent_type, application_count desc ;

方法四

与方法三类似，使用临时表和窗口函数来筛选出每个专利类型的 top 10 申请人，并进行排序。

with t as (
    select patent_type, apply_user from t_patent_detail
    lateral view explode(split(apply_users, '；')) t1 as apply_user
), t2 as (
    select  patent_type, apply_user, count(*) as application_count,
            row_number() over (partition by patent_type order by count(*) desc ) as rank
    from t group by patent_type, apply_user
) select patent_type, apply_user, application_count
from t2 where t2.rank <= 10
order by patent_type, application_count desc ;

        这四种方法都可以实现查询各类型专利 top 10 申请人及专利申请数的需求，但在性能和可读性上可能会有所不同。可以根据实际数据量和查询需求选择合适的方法。

六、总结

        通过以上问题的解决，展示了 Hive 在处理不同数据需求时的灵活性和强大功能，包括间隔连续问题的处理、行列转换、交易数据查询、用户登录统计以及专利数据分析等方面。

标签：count,patent,练习,第六天,user,SQL,date,apply,select
From： https://blog.csdn.net/weixin_64726356/article/details/142423820

相关文章

  • [问题记录]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;将返回表中......
  • Django替换sqlite默认数据库到mysql的一系列操作
    将这部分注释掉：DATABASES={'default':{'ENGINE':'django.db.backends.sqlite3','NAME':BASE_DIR/'db.sqlite3',}} 并替换为：DATABASES={'default':{......

赞助商

阅读排行

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

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

Copyright © 2020-2023 IPS99 版权所有 IPS99