SQL专项练习第四天

🕗 发布于 2024-10-07 17:18 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);

代码如下：

select
    name,
    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按照月份对数据进行分组。

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

代码如下：

select
  substr(dt, 1, 7) AS year_month,
  count(order_id) AS order_count,
  count(DISTINCT user_id) AS user_count,
  sum(amount) AS total_amount
from
  order_tab
where
  substr(dt, 1, 4) = '2017'
group by
  substr(dt, 1, 7)
order by
  year_month;

2017 年 11 月的新客数：

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

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

代码如下：

select
    count(distinct user_id) as new_customers
from
    order_tab
where
    month(dt) = 11 and year(dt) = 2017
    and not exists (
        select 1
        from order_tab as prev_orders
        where
            prev_orders.user_id = order_tab.user_id
            and month(prev_orders.dt) < 11
            and 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 (
    select
        shop,
        user_id,
        count(*) AS visit_count,
        row_number() over (partition by shop order by count(*) desc ) as rank
    from visit
    group by shop, user_id
)
select
    shop,
    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

解决方案：

使用substr函数将访问日期转换为月份格式。
使用窗口函数按照用户 ID 和月份进行分区，统计每个用户每个月的小计访问次数。
再次使用窗口函数按照用户 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 monthCount
    from shopping
    group 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

解决方案：

使用窗口函数lead获取每个用户下一个订单的日期。
使用datediff函数计算当前订单日期与下一个订单日期的天数差。
对天数差进行处理，减去 1 得到实际的间隔天数。
按用户 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 rn
    FROM 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_diff
    from 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 在不同数据处理场景下的强大功能和灵活性，包括行转列、多维度统计、访客分析和日期计算等方面。

原文地址：https://blog.csdn.net/weixin_64726356/article/details/142423748

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：C++ 游戏编程：最新引擎使用指南
下一篇：LabVIEW激光诱导击穿光谱识别与分析系统

计算机毕业设计 | SpringBoot 房屋租赁网租房买房卖房平台(附源码)
Springboot房屋租赁网，项目功能：用户：房源搜索，房源查看，房屋租赁，房源发布，个人信息管理，管理员：用户管理，房源管理
阅读更多2024-10-09
第一章建设工程基本法律知识 1.4-1.7
1.建设工程基本法律知识1.4 建设工程侵权责任制度1.4.1 侵权责任主体和损害赔偿1.侵权责任主体侵权行为归责原则过错责任原则：行为人因过错侵害他人民事权益造成损害的无过错责任原则:不论行为人有无
阅读更多2024-10-09
Python中的数据可视化艺术：用Matplotlib和Seaborn讲故事
Matplotlib是Python中最基础的绘图库之一，灵活且功能强大，几乎可以绘制任何类型的图表。它的API相对底层，意味着用户可以完全控制图表的各个方面。虽然Matplotlib功能强大，但在美观
阅读更多2024-10-09
单片机（学习）2024.10.8
计算机基础（io逻辑，计算机组成，总线，ALU，指令集，CPU组成），单片机基础，STM32简介，ARM体系结构
阅读更多2024-10-09
点可云ERP进销存V8版本—转账单的使用操作
转账单用于资金在不同账户之间转移的财务单据，可以从资金账户A中转账到资金账户B中的操作，选择转出、转入账户、填写结算金额、结算号等数据，保存生成，审核之后资金数据生效。导出功能需要在列表中勾选单据再在
阅读更多2024-10-09
使用YOLO11实例分割模型进行人物分割【附完整源码】
使用YOLO11实例分割模型进行人物分割【附完整源码】
阅读更多2024-10-09
宠物咖啡馆数字化平台：SpringBoot框架的实现策略
如果采用电子化的存储方式就会带来很大的改善，而且给用户的查询带来了很大便利，因此设计一个基于Spring Boot的宠物咖啡馆平台的设计与实现刻不容缓，能够提高信息的管理水平。开发合适的基于Sprin
阅读更多2024-10-09
竹壳天气时钟（一）使用数组保存扫描到的WiFi列表信息
准备用基于esp8266的nodemcu开发板做一个天气时钟。一步一步记录代码编写过程。/*竹壳天气时钟使用基于esp8266的NodeMCU制作。计划用竹子做最后成品的外壳，所以才有了这个名称。第一
阅读更多2024-10-09
Vue vben admin开源库中table组件tips
Vue vben admin开源库
阅读更多2024-10-09
【python3】tornado高性能编程
【代码】【python3】tornado高性能编程。
阅读更多2024-10-09

SQL专项练习第四天

一、成绩表行转列

解决方案：

建表：

代码如下：

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

问题

数据

建表：

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

代码如下：

2017 年 11 月的新客数：

代码如下：

三、京东店铺访问统计

建表：

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

代码如下：

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

代码如下：

四、店铺访问数据统计

解决方案：

数据：

建表：

代码如下：

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

解决方案：

数据：

建表：

相关文章