SQL专项练习第六天

标签：count patent 练习第六天 user SQL date apply select

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

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

一、间隔连续问题

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

解决方案：

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

数据：

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;

二、行列转换

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

解决方案：

使用case when语句和聚合函数，按照年份进行分组，对不同部门的绩效得分进行条件判断并聚合。
通过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 天每天平均登录用户数量。

解决方案：

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

建表：

-- 建表
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申请人，以及对应的专利申请数

解决方案：

首先使用lateral view explode函数将申请人字段拆分成多行。
然后按照申请人进行分组，统计每个申请人的专利申请数。
使用窗口函数rank按照专利申请数降序排序，为每个申请人分配一个排名。
最后选择排名在前 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;

代码如下：

方法一

使用lateral view explode函数将apply_users字段拆分成多行，每个申请人成为一条独立的记录。
按patent_type（专利类型）和apply_user（申请人）进行分组，统计每个申请人的专利申请数。
最后按照专利类型和申请数降序排序。

-- 方法一
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 ;

方法三

首先同样使用lateral view explode函数拆分申请人字段，得到中间表t2。
对t2按专利类型和申请人分组，统计申请数，并使用窗口函数row_number()按照申请数降序为每个专利类型内的申请人分配排名。
筛选出排名小于等于 10 的记录，即每个专利类型的 top 10 申请人。
最后按照专利类型和申请数降序排序。

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

一、间隔连续问题

解决方案：

数据：

建表：

代码如下：

二、行列转换

解决方案：

数据：

建表：

代码如下：

1）多行转多列

2）将结果转换为源表（多列转多行）

3）多个绩效求多行转多列

三、交易表查询

建表：

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

代码如下：

方法一

方法二

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

代码如下：

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

解决方案：

建表：

代码如下：

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

解决方案：

建表：

代码如下：

方法一

方法二

方法三

方法四

六、总结

相关文章

赞助商

阅读排行