SQL专项练习第三天

        在大数据处理中，Hive 是一个常用的工具，它可以对大规模数据进行高效的查询和分析。本文将介绍五个 Hive 数据处理问题的解决方案，并通过实际案例进行演示。 

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

一、统计每个成绩段人数

问题描述：给定一组包含学生姓名和成绩的数据，要求按照成绩段统计人数，成绩大于 90 为优、大于 80 为良、大于 60 为中，不及格为差。

解决方案：

1. 使用get_json_object函数从 JSON 格式的字符串中提取学生姓名和成绩。
2. 使用if函数判断成绩所属的成绩段，并为每个成绩分配一个等级。
3. 按等级分组，统计每个等级的人数。

数据：

data数据如下：
{"username":"张三","score":95}
{"username":"李四","score":76}
{"username":"赵本山","score":92}
{"username":"王五","score":76}
{"username":"赵六","score":62}
{"username":"赵六1","score":62}
{"username":"赵六2","score":26}
{"username":"赵六3","score":89}
{"username":"赵六4","score":77}

建表：

-- 建表
create table if not exists scores(
   c1  string
)
row format serde 'org.openx.data.jsonserde.JsonSerDe';
-- 导入数据
load data local inpath '/home/hivedata/zy01.txt' into table scores;

代码如下：

with t as (
    select get_json_object(c1,'$.username')  username,
       get_json_object(c1,'$.score') score,
       `if`(get_json_object(c1,'$.score') > 90,'优',
           if(get_json_object(c1,'$.score')>=80,'良',
               `if`(get_json_object(c1,'$.score')>=60,'中','不及格')) ) grade
from scores
)
select grade,count(1) rs from t group by grade  ;

二、支付次数和累计充值金额统计

问题描述：有一个包含用户 ID、日期、交易类型、交易金额和交易方向的表，要求统计每个用户的支付次数和累计充值金额。

解决方案：

1. 对表进行筛选，只保留充值交易类型的记录。
2. 按用户 ID 分组，使用sum函数计算每个用户的累计充值金额。
3. 使用窗口函数统计每个用户的支付次数。

数据：

u_001,20191201,支付,20000,支
u_001,20191203,充值,30010,收
u_001,20191203,提现,50000,支
u_001,20191208,支付,20000,支
u_001,20191210,充值,30010,收
u_001,20191220,提现,50000,支
u_002,20191202,支付,20000,支
u_002,20191202,转入,30010,收
u_002,20191230,充值,50000,收
u_003,20200110,支付,60.68,支
u_004,20200111,支付,90.05,支
u_004,20200114,充值,100.1,收
u_005,20200101,还款,30010,支

建表：

-- 建表
create table t7(
  user_id     string,
  date_dt     string,
  trans_type  string,
  trans_amt   double,
  trans_dc    string
)row format delimited fields terminated by ',';
-- 导入数据
load data local inpath '/home/hivedata/zy02.txt' into table t7;

代码如下：

-- 您需要找出每个月支付次数大于3次，且每月累计充值金额大于10000元的用户交易记录。结果集需要包含 user_id 和 data_month（月份）
select user_id, substr(date_dt,1,6) as data_month
from t7 where trans_dc = '支' or trans_type = '充值'
group by user_id, substr(date_dt,1,6)
having count(*) > 3 or sum(trans_type) > 10000;

三、订单数据统计

1. 统计每个用户截至每次下单的累积下单总额：

   • 使用窗口函数sum和over子句，按照用户姓名分组并按照下单日期排序，计算每个用户截至每次下单的累积下单总额。

2. 统计每个用户截至每次下单的当月累积下单总额：

   • 同样使用窗口函数sum和over子句，但是在分组时除了用户姓名还加上下单日期的月份部分，按照下单日期排序，计算每个用户截至每次下单的当月累积下单总额。

3. 统计每个用户每次下单距离上次下单相隔的天数（首次下单按 0 天算）：

   • 使用窗口函数lag获取每个用户上一次下单的日期，然后使用datediff函数计算当前下单日期与上一次下单日期的天数差。如果是首次下单，则默认相隔天数为 0。

4. 为每个用户的所有下单记录按照订单金额进行排名：

   • 使用窗口函数dense_rank按照用户姓名分组并按照订单金额降序排序，为每个用户的下单记录进行排名。

建表：

create table order_info
(
  order_id     string,         --订单id
  user_id      string,         --用户id
  user_name    string,         --用户姓名
  order_date   string,         --下单日期
  order_amount  int            --订单金额
);
insert overwrite table order_info
values('1','1001','小元','2022-01-01','10'),
('2','1002','小海','2022-01-02','15'),
('3','1001','小元','2022-02-03','23'),
('4','1002','小海','2022-01-04','29'),
('5','1001','小元','2022-01-05','46'),
('6','1001','小元','2022-04-06','42'),
('7','1002','小海','2022-01-07','50'),
('8','1001','小元','2022-01-08','50'),
('9','1003','小辉','2022-04-08','62'),
('10','1003','小辉','2022-04-09','62'),
('11','1004','小猛','2022-05-10','12'),
('12','1003','小辉','2022-04-11','75'),
('13','1004','小猛','2022-06-12','80'),
('14','1003','小辉','2022-04-13','94');

代码如下：

-- 1.统计每个用户截至每次下单的累积下单总额
select *,sum(order_amount) over(partition by user_name order by order_date) from order_info;

-- 2.统计每个用户截至每次下单的当月累积下单总额
select *,sum(order_amount) over(partition by user_name,substr(order_date,1,7) order by order_date) from order_info;

-- 3.统计每个用户每次下单距离上次下单相隔的天数（首次下单按0天算）
select *,datediff(
    to_date(order_date),
    to_date(lag(order_date,1,order_date) over(partition by user_name order by order_date))) days from order_info;

-- 4.为每个用户的所有下单记录按照订单金额进行排名
select *,dense_rank() over (partition by user_name order by order_amount desc) from order_info;

四、网站登录时间间隔统计

问题描述：给定一个网站登录记录表，包含用户 ID 和登录时间，要求计算每个用户登录日期间隔小于 5 天的次数。

解决方案：

1. 使用窗口函数lag获取每个用户上一次登录的时间，并计算当前登录时间与上一次登录时间的天数差。
2. 筛选出天数差大于等于 1 且小于等于 5 的记录。
3. 按用户 ID 分组，统计每个用户登录日期间隔小于 5 天的次数。

建表：

create table login_info(
   user_id string,
   login_time string
);

INSERT INTO
login_info(user_id,login_time)
VALUEs('a001','2021-01-01')
,('b001','2021-01-01')
,('a001','2021-01-03')
,('a001','2021-01-06')
,('a001','2021-01-07')
,('b001','2021-01-07')
,('a001','2021-01-08')
,('a001','2021-01-09')
,('b001','2021-01-09')
,('b001','2021-01-10')
,('b001','2021-01-15')
,('a001','2021-01-16')
,('a001','2021-01-18')
,('a001','2021-01-19')
,('b001','2021-01-20')
,('a001','2021-01-23');

代码如下：

with t as (
    select *,
       lag(login_time,1,login_time) over (partition by user_id order by login_time ) last_time,
       datediff(login_time,lag(login_time,1,login_time) over (partition by user_id order by login_time )) days  from login_info
)
select user_id,count(1) from t where days >=1 and days <=5 group by user_id;

五、蚂蚁森林，连续问题

问题描述：有一个蚂蚁森林的数据表，包含用户 ID、日期和减少的碳排放量，要求找出连续 3 天及以上减少碳排放量在 100 以上的用户。

解决方案：

1. 首先使用group by和having子句筛选出减少碳排放量在 100 以上的记录。
2. 然后使用窗口函数row_number和date_sub计算一个临时列，用于判断连续日期。
3. 最后按用户 ID 和临时列分组，统计数量大于等于 3 的用户 ID。

数据：

id     dt       lowcarbon
1001 2021-12-12 123
1002 2021-12-12 45
1001 2021-12-13 43
1001 2021-12-13 45
1001 2021-12-13 23
1002 2021-12-14 45
1001 2021-12-14 230
1002 2021-12-15 45
1001 2021-12-15 23

建表：

create table mayi(
  uid int,
  dt string,
  lowcarbon int
)row format delimited
fields terminated by ' ';

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

代码如下：

with t as (
  select id,dt,sum(lowcarbon) sumCarbon
  from lowcarbon_data group by id,dt having sumCarbon >=100
),t2 as (
  select *,date_sub(dt,row_number() over (
      partition by id order by dt )) temp_date
  from t
)
select id from t2 group by id,temp_date having count(1) >=3;

        通过以上五个问题的解决，展示了 Hive 在数据处理中的强大功能和灵活性。无论是统计成绩段人数、分析支付和充值数据、处理订单数据、统计网站登录时间间隔还是解决蚂蚁森林的连续问题，Hive 都能提供高效的解决方案。