SQL专项练习第四天

       Hive 在不同数据处理场景下的强大功能和灵活性，包括行转列、多维度统计、访客分析和日期计算等方面。本文将介绍五个 Hive 数据处理问题的解决方案，并通过实际案例进行演示。  

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

一、成绩表行转列

问题描述：给定一个学生成绩表t_student2，包含学生 ID、姓名、课程和成绩，要求将课程列进行行转列操作，展示每个学生的各科成绩。

输出结果：
姓名  语文  数学  英语
张三  95    86   78
李四  80    96   88

解决方案：

使用case when语句和聚合函数，按照学生姓名进行分组，对不同课程的成绩进行条件判断并求和，实现行转列的效果。

建表：

CREATE TABLE `t_student2` (`id`     int     NOT NULL  COMMENT '主键 id',`name`   string  COMMENT '姓名',`course` string  COMMENT '课程',`score`  int     COMMENT '成绩'
)INSERT INTO `t_student2` VALUES (1,'张三', '语文', 95),
(2,'李四', '语文', 99),(3,'王五', '语文', 80),(4,'张三', '数学', 86),(5,'李四', '数学', 96),
(6,'王五', '数学', 81),(7,'张三', '英语', 78),(8,'李四', '英语', 88),(9,'王五', '英语', 87);

代码如下：

selectname,sum(case when course ='语文' then score else 0 end) `语文`,sum(case when course ='数学' then score else 0 end) `数学`,sum(case when course ='英语' then score else 0 end) `英语`
from t_student2 group by name;

二、订单数、用户数、新客数统计

问题

1）给出2017年每个月的订单数、用户数、总成交金额。
2）给出2017年11月的新客数(指在11月才有第一笔订单)

 数据
 

2017-01-01,10029001,1,33.57
2017-01-03,10029002,1,53.57
2017-01-11,10029003,2,63.57
2017-01-11,10029013,3,63.57
2017-02-02,10029004,1,73.57
2017-02-07,10029005,1,83.57
2017-02-15,10029006,1,93.57
2017-03-04,10029007,2,33.57
2017-04-01,10029008,1,53.57
2017-05-11,10029009,1,63.57
2017-05-21,10029010,2,73.57
2017-06-01,10029011,1,93.57
2017-06-07,10029012,1,13.57
2017-07-09,10029013,2,33.57
2017-07-20,10029014,1,23.57
2017-08-01,10029015,2,73.57
2017-09-09,10029016,2,93.57
2017-10-01,10029017,1,33.57
2017-11-11,10029018,1,36.57
2017-11-12,10029119,6,136.57
2017-11-21,10029019,1,37.57
2017-12-11,10029020,1,38.57

建表：

--  建表
create table order_tab(dt string,order_id string,user_id string,amount decimal(10,2))
row format delimited fields terminated by ',';
-- 导入数据
load data local inpath '/home/hivedata/zy03.txt' into table order_tab;

2017 年每个月的订单数、用户数、总成交金额：

使用group by按照月份对数据进行分组。

使用聚合函数分别统计每个月的订单数、用户数和总成交金额。

代码如下：

selectsubstr(dt, 1, 7) AS year_month,count(order_id) AS order_count,count(DISTINCT user_id) AS user_count,sum(amount) AS total_amount
fromorder_tab
wheresubstr(dt, 1, 4) = '2017'
group bysubstr(dt, 1, 7)
order byyear_month;

2017 年 11 月的新客数：

首先找出在 2017 年 11 月之前没有订单记录的用户。

然后统计这些用户在 2017 年 11 月的订单数量，即为新客数。

代码如下：

selectcount(distinct user_id) as new_customers
fromorder_tab
wheremonth(dt) = 11 and year(dt) = 2017and not exists (select 1from order_tab as prev_orderswhereprev_orders.user_id = order_tab.user_idand month(prev_orders.dt) < 11and year(prev_orders.dt) = 2017);

三、京东店铺访问统计

访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，请统计：
1）每个店铺的UV（访客数）

 店铺名称   uv值

2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

店铺名称  用户编号  访问次数
    a        u1         10
    a        u3         8
    a        u6         7
    b        xx         xx
    b        xx         xx
    b        xx         xx

 

建表：

-- 建表
create table visit(user_id string,shop    string
)row format delimited
fields terminated by '\t';
-- 导入数据
load data local inpath '/home/hivedata/jingdong.txt' into table visit;

每个店铺的 UV（访客数）：

使用group by按照店铺名称进行分组。

使用count(distinct)统计每个店铺的不同访客数量。

代码如下：

select shop, count(distinct user_id) as uv
from visit
group by shop;

每个店铺访问次数 top3 的访客信息：

使用窗口函数按照店铺名称和访客 ID 分组，统计每个访客的访问次数。

使用dense_rank()窗口函数为每个店铺内的访客按照访问次数进行排名。

筛选出排名前三的访客信息。

代码如下：

with t as (selectshop,user_id,count(*) AS visit_count,row_number() over (partition by shop order by count(*) desc ) as rankfrom visitgroup by shop, user_id
)
selectshop,user_id as `用户编号`,visit_count as `访问次数`
from t
where rank <= 3;

四、店铺访问数据统计

问题描述：给定用户访问数据，包含用户 ID、访问日期和访问次数，要求统计每个用户的累计访问次数。

如下所示：

用户 月份 小计 累计
u01 2017-01 11 11
u01 2017-02 12 23
u02 2017-01 12 12
u03 2017-01 8  8
u04 2017-01 3  3

解决方案：

1. 使用substr函数将访问日期转换为月份格式。
2. 使用窗口函数按照用户 ID 和月份进行分区，统计每个用户每个月的小计访问次数。
3. 再次使用窗口函数按照用户 ID 进行分区，对小计访问次数进行累加得到累计访问次数。

数据：

userID visitDate visitCount
u01 2017-01-21 5
u02 2017-01-23 6
u03 2017-01-22 8
u04 2017-01-20 3
u01 2017-01-23 6
u01 2017-02-21 8
u02 2017-01-23 6
u01 2017-02-22 4

建表：

-- 建表
create table shopping(userID string,visitDate string,visitCount int
)row format delimited
fields terminated by ' '
tblproperties("skip.header.line.count"="1");
-- 导入数据
load data local inpath '/home/hivedata/shops.txt' into table shopping;

代码如下：

with t as (select userID,substr(visitDate, 1, 6) as visitMonth,sum(visitCount) as monthCountfrom shoppinggroup by  userID, substr(visitDate, 1, 6)
)
select userID, visitMonth as `月份`, monthCount as `小计`,sum(monthCount) over (partition by userID order by visitMonth) as `累计`
from t order by userID, visitMonth;

五、计算连续用户完成订单间隔最大的天数和最小的天数

问题描述：有一个订单表t15，包含用户 ID、订单日期和订单金额，要求计算每个用户完成订单间隔的最大天数和最小天数。

uid_1  max_day  min_day
uid_1    4         1

解决方案：

1. 使用窗口函数lead获取每个用户下一个订单的日期。
2. 使用datediff函数计算当前订单日期与下一个订单日期的天数差。
3. 对天数差进行处理，减去 1 得到实际的间隔天数。
4. 按用户 ID 分组，使用聚合函数max和min分别计算最大和最小间隔天数。

数据：

user_id   dt            amt
uid_1    20200501       10
uid_1    20200503       160
uid_1    20200508       201
uid_1    20200511       103
uid_1    20200516       119

建表：

-- 建表
create table t15(user_id string,dt      string,amt     int
)row format delimited
fields terminated by ' '
tblproperties("skip.header.line.count"="1");
-- 导入数据
load data local inpath '/home/hivedata/homework0830/t15.txt' into table t15;

代码如下：

with t as (select user_id, dt,lag(dt) over (partition by user_id order by unix_timestamp(dt, 'yyyyMMdd') asc) as prev_dt,row_number() over (partition by user_id order by unix_timestamp(dt, 'yyyyMMdd') asc) as rnFROM t15
),t2 as (select user_id, dt,case when prev_dt is null then null else`floor`((unix_timestamp(dt, 'yyyyMMdd')- unix_timestamp(prev_dt, 'yyyyMMdd') - 1) / 86400)end as days_difffrom t where rn > 1
)
select user_id, max(days_diff) AS max_day, min(days_diff) AS min_day
from t2 group by user_id;

        通过以上五个问题的解决，展示了 Hive 在不同数据处理场景下的强大功能和灵活性，包括行转列、多维度统计、访客分析和日期计算等方面。