【Hive SQL 每日一题】分析电商平台的用户行为和订单数据

最新推荐文章于 2024-08-05 12:37:12 发布
最新推荐文章于 2024-08-05 12:37:12 发布
阅读量1.1k 收藏 10
点赞数 25
分类专栏： Hive SQL 每日一题 文章标签： hive sql hadoop 数据分析 数据仓库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46389691/article/details/139134823
版权

需求描述

假设你是一位数据分析师，负责分析某电商平台的用户行为和订单数据，平台上有多个用户，用户可以在不同的日期下单，每个订单包含多个商品。请你完成相关业务分析，帮助平台优化运营策略和用户体验。

数据准备

我们有三张表，表的字段信息如下：

users 表

记录用户的信息

字段名字段类型备注
user_idint用户ID
namestring用户姓名
ageint用户年龄
genderstring用户性别
register_datestring注册日期

示例数据：

user_idnameagegenderregister_date
1‘Alice’23‘F’‘2023-01-01’
2‘Bob’22‘M’‘2023-02-01’
3‘Cathy’24‘F’‘2023-03-01’
4‘David’23‘M’‘2023-04-01’
5‘Eve’25‘F’‘2023-05-01’

orders 表

记录订单的信息

字段名字段类型备注
order_idint订单ID
user_idint用户ID
order_datestring订单日期
amountdouble订单金额

示例数据：

order_iduser_idorder_dateamount
1011‘2024-01-01’100.0
1021‘2024-01-02’150.0
1032‘2024-01-03’200.0
1043‘2024-01-04’50.0
1054‘2024-01-05’300.0
1065‘2024-01-06’250.0

order_items 表

记录订单中商品的信息

字段名字段类型备注
order_item_idint订单项ID
order_idint订单ID
product_idint商品ID
quantityint商品数量
pricedouble商品单价

示例数据：

order_item_idorder_idproduct_idquantityprice
10011011150.0
10021012150.0
10031023350.0
100410342100.0
10051045150.0
100610562150.0
10071067550.0

数据集

-- 创建用户表
CREATE TABLE users (
    user_id INT,
    name STRING,
    age INT,
    gender STRING,
    register_date STRING
);

-- 插入用户数据
INSERT INTO users VALUES
(1, 'Alice', 23, 'F', '2023-01-01'),
(2, 'Bob', 22, 'M', '2023-02-01'),
(3, 'Cathy', 24, 'F', '2023-03-01'),
(4, 'David', 23, 'M', '2023-04-01'),
(5, 'Eve', 25, 'F', '2023-05-01'),
(6, 'Frank', 28, 'M', '2023-06-01'),
(7, 'Grace', 27, 'F', '2023-07-01'),
(8, 'Hank', 26, 'M', '2023-08-01'),
(9, 'Ivy', 29, 'F', '2023-09-01'),
(10, 'Jack', 30, 'M', '2023-10-01');

-- 创建订单表
CREATE TABLE orders (
    order_id INT,
    user_id INT,
    order_date STRING,
    amount DOUBLE
);

-- 插入订单数据
INSERT INTO orders VALUES
(101, 1, '2024-01-01', 100.0),
(102, 1, '2024-01-02', 150.0),
(103, 2, '2024-01-03', 200.0),
(104, 3, '2024-01-04', 50.0),
(105, 4, '2024-01-05', 300.0),
(106, 5, '2024-01-06', 250.0),
(107, 6, '2024-01-07', 100.0),
(108, 7, '2024-01-08', 150.0),
(109, 8, '2024-01-09', 200.0),
(110, 9, '2024-01-10', 50.0),
(111, 10, '2024-01-11', 300.0),
(112, 1, '2024-01-12', 100.0),
(113, 2, '2024-01-13', 150.0),
(114, 3, '2024-01-14', 200.0),
(115, 4, '2024-01-15', 50.0),
(116, 5, '2024-01-16', 300.0),
(117, 6, '2024-01-17', 250.0),
(118, 7, '2024-01-18', 100.0),
(119, 8, '2024-01-19', 150.0),
(120, 9, '2024-01-20', 200.0);

-- 创建订单项表
CREATE TABLE order_items (
    order_item_id INT,
    order_id INT,
    product_id INT,
    quantity INT,
    price DOUBLE
);

-- 插入订单项数据
INSERT INTO order_items VALUES
(1001, 101, 1, 1, 50.0),
(1002, 101, 2, 1, 50.0),
(1003, 102, 3, 3, 50.0),
(1004, 103, 4, 2, 100.0),
(1005, 104, 5, 1, 50.0),
(1006, 105, 6, 2, 150.0),
(1007, 106, 7, 5, 50.0),
(1008, 107, 1, 2, 50.0),
(1009, 108, 2, 3, 50.0),
(1010, 109, 3, 1, 50.0),
(1011, 110, 4, 2, 100.0),
(1012, 111, 5, 1, 50.0),
(1013, 112, 6, 2, 150.0),
(1014, 113, 7, 5, 50.0),
(1015, 114, 1, 2, 50.0),
(1016, 115, 2, 3, 50.0),
(1017, 116, 3, 1, 50.0),
(1018, 117, 4, 2, 100.0),
(1019, 118, 5, 1, 50.0),
(1020, 119, 6, 2, 150.0),
(1021, 120, 7, 5, 50.0);

需求分析与实现

1.计算每个用户的总订单金额和订单数量，并根据总订单金额对用户进行排名

select
    user_id,
    total_amount,
    total_cnt,
    rank() over(order by total_amount desc) rk
from
    (select
        user_id,
        sum(amount) total_amount,
        count(order_id) total_cnt
    from
        orders
    group by
        user_id)t1;

在这里插入图片描述

解题思路

  1. 按用户ID进行分组，统计每个用户的总订单金额和订单数量；
  2. 使用 RANK() 窗口函数对用户进行排名。

2.按月统计每个用户的订单总金额和订单数量

select
    user_id,
    date_format(order_date,"yyyy-MM") order_month,
    sum(amount) total_amount,
    count(order_id) total_cnt
from
    orders
group by
    user_id,date_format(order_date,"yyyy-MM");

在这里插入图片描述

解题思路

  1. 使用 DATE_FORMAT() 函数按月提取订单日期；
  2. 按用户ID和月份进行分组，统计每个用户每月的订单总金额和订单数量。

3.分析每个用户最常购买的商品，并计算该商品的总购买次数和总金额

select
    user_id,
    product_id,
    total_amount,
    total_cnt
from
    (select
        user_id,
        product_id,
        total_amount,
        total_cnt,
        row_number() over(partition by user_id order by total_cnt desc) rn
    from
        (select
            user_id,
            product_id,
            sum(amount) total_amount,
            count(product_id) total_cnt
        from
            orders o
        join
            order_items oi
        on
            o.order_id = oi.order_id
        group by
            user_id,product_id)t1 )t2
where
    rn = 1;

在这里插入图片描述

解题思路

  1. 分组统计每个用户购买商品的次数和总金额；
  2. 使用 ROW_NUMBER() 窗口函数对每个用户购买的商品进行排序；
  3. 过滤出购买次数最多的商品。

4.分别找出平均每月订单金额最高与订单数量最高的用户

select
    user_id,
    order_month,
    avg_amount,
    order_cnt
from
    (select
        user_id,
        order_month,
        avg_amount,
        order_cnt,
        rank() over(order by avg_amount desc) rk_amount,
        rank() over(order by order_cnt desc) rk_cnt
    from
        (select
            user_id,
            date_format(order_date,"yyyy-MM") order_month,
            cast(avg(amount) as decimal(5,2)) avg_amount,
            count(order_id) order_cnt
        from
            orders
        group by
            user_id,
            date_format(order_date,"yyyy-MM"))t1 )t2
where
    rk_amount = 1 or rk_cnt = 1;

在这里插入图片描述

解题思路

  1. 统计每个用户每月的平均订单金额和订单数量；

  2. 排序并取出平均每月订单金额和订单数量最高的两个用户。

5.找出订单金额最高的前10名用户，并分析这些用户的年龄和性别分布

select
    u.user_id,
    total_amount,
    u.age,
    u.gender
from
    (select
        user_id,
        sum(amount) total_amount
    from
        orders
    group by
        user_id) o
join
    users u
on
    o.user_id = u.user_id
order by
    total_amount desc
limit
    10;

在这里插入图片描述

解题思路

  1. 分组统计每个用户的总订单金额；
  2. 联合 users 表，获取用户的年龄和性别信息；
  3. 排序并取出总订单金额最高的前10名用户。

6.找出在过去一年内注册的用户中，订单金额最高的前5名用户

select
    u.user_id,
    sum(amount) total_amount
from
    (select
        user_id
    from
        users
    where
        register_date >= date_sub(current_date(),365) )u
join
    orders o
on
    o.user_id = u.user_id
group by
    u.user_id
order by
    total_amount desc
limit
    5;

在这里插入图片描述

解题思路

  1. 筛选出过去一年内注册的用户；
  2. 联合 orders 表，统计这些用户的总订单金额；
  3. 通过排序获取出前 5 名用户，如果想要精准获取允许重复，则可以使用 rank 或者 row_number 进行窗口排序后过滤获取前 5。
月亮给我抄代码
关注
专栏目录
SQL 电商练习
wlbonnie的博客
02-10 473
create database ds; use ds; SET SQL_SAFE_UPDATES = 0; -- 建表导数--------------------------------------- -- UserInfo table create table userinfo( userid varchar(6) not null default '-', username ...
每天一道大厂SQL题【Day02】电商场景TopK统计_有50w个京东店铺,每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问(2)
2401_84181326的博客
05-04 447
一路走来，随着问题加深，发现不会的也愈来愈多。但底气着实足了不少，相信不少朋友和我一样，日积月累才是最有效的学习方式！
每天一道大厂SQL题【Day02】电商场景TopK统计_有50w个京东店铺,每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问(1)
2401_84181070的博客
05-16 461
不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！先通过 group by 按店铺名称进行分组，然后使用 count(distinct user_id) 计算每个店铺的访客数。先通过 group by 按店铺名称和用户id进行分组，然后使用 count(*) 计算每个用户在每个店铺的访问次数。参考答案适用HQL，SparkSQL，FlinkSQL，即大数据组件，其他SQL需自行修改。
SQL常见题目
Michael_Jay的博客
01-01 1654
汇总了牛客中SQL必知必会的错题
hive大数据——某电商平台数据分析(一)
ws19920726的博客
03-26 2291
hive数据库表结构 实现如下需求(由浅入深) 需求1：某次经营活动中，商家发起了"异性拼团购"，试着针对某个地区的用户进行推广，找出匹配用户。 分析：简单理解本需求，就是将某一个地区性别为男/女的所有用户找出。 select user_name from user_info where city='beijing' and sex='male'; 需求2：某天，发现食物类的商品卖的很好...
Hive SQL进阶：掌握这些SQL查询技巧，让你的数据报告更加精准和高效！
最新发布
击水三千里的专栏
08-05 915
这份文档提供了一系列的SQL查询示例，用于解决数据分析中的常见问题，如用户活跃度、留存率、销售统计等。通过这些示例，用户可以学习如何使用SQL进行高效的数据查询和分析。
尚硅谷数据仓库 用户行为分析
qq_44717177的博客
10-12 1511
这34个表以订单表、用户表、SKU商品表、活动表和优惠券表为中心，延伸出了优惠券领用表、支付流水表、活动订单表、订单详情表、订单状态表、商品评论表、编码字典表退单表、SPU商品表等，用户表提供用户的详细信息，支付流水表提供该订单的支付详情，订单详情表提供订单的商品数量等情况，商品表给订单详情表提供商品的详细信息。我们希望的是根据日志里面的实际时间，发往HDFS的路径，所以下面拦截器作用是获取日志中的实际时间。我们要收集和分析的数据主要包括页面数据、事件数据、曝光数据、启动数据和错误数据。
【电商数仓】数仓搭建之原始数据（Operation Data Store -- ODS）层（用户行为数据、业务数据）
weixin_43923463的博客
09-30 850
- 保持数据原貌不做任何修改，起到备份数据的作用。 - 数据采用Gzip压缩，减少磁盘存储空间。 - 创建分区表，防止后续的全表扫描，在企业开发中大量使用分区表。 - 创建外部表。在企业开发中，除了自己用的临时表，创建内部表外，绝大多数场景都是创建外部表。 27张业务数据表加1张用户行为数据表（log），共需要创建28张表，在mysql中有什么列，在这里就有什么列，直接复制过来。 log中的json数据直接存放为一行，所有的JSON体都作为line的一部分。
数据仓库之电商数仓-- 2、业务数据采集平台
FunnyPrince_的博客
10-04 771
目录 1). 2). 3). 4). 5). 6). 7). 8). 9).
以金融证券、游戏、电商等案例详解SQL强化
m0_57468722的博客
11-23 418
SQL强化 SQL执行顺序 --举例： select a.sex, b.city, count(1) as cnt, sum(salary) as sum1 fr...
【Hive+MySQL+Python】淘宝用户购物行为数据分析项目
weixin_46436010的博客
03-23 4118
user_data.csv是一份用户行为数据，时间区间为2017-11-25到2017-12-03，总计29132493条记录，大小为1.0G，包含5个字段。数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。
数据仓库Hive简述
戴着红领巾走世界
02-25 994
    Hive 是一个构建在 Hadoop 之上的数据仓库，它可以将HDFS数据文件映射成表，并提供类 SQL 查询功能，用于查询的 SQL 语句会被转化为 MapReduce 作业提交到 Hadoop 上运行。其实，Hive是用Java编写的一套基于HDFS分布式数据存储，将SQL编译为MapReduce任务进行分布式计算的数据仓库框架，提供了类似 sq...
HiveSQL练习题：计算近一个月活跃、连续活跃、沉默用户需求
qq_31412425的博客
07-01 1193
运算符筛选出符合条件的数据，即日期大于等于最近的 11 月 15 日前的 30 天。这行语句可以理解为统计最近 11 月 15 日前的 30 天内的用户数据。函数计算每个设备的唯一日期数，即活跃天数。本小节求出连续登录大于2天的数量，并进行汇总计算。列的值，这里是为了筛选出近一个月30天的数据。进行分组，并计算了每个分组中的行数，即。Step 4：对满足条件的分组再次进行了。这个需求比较简单，在需求一的基础上，增加对。对应的分组数,即为需求用户数。减去 30天的数据，并按。即为连续登录的日期。
自学SQL入门（2）
珎珎啊
05-04 632
1. FROM 和 JOINs FROM 或 JOIN会第一个执行，确定一个整体的数据范围. 如果要JOIN不同表，可能会生成一个临时Table来用于 下面的过程。总之第一步可以简单理解为确定一个数据源表（含临时表) 2. WHERE 我们确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行，所有的筛选col属性 只能来自FROM圈定的表. AS别名还不能在这个阶段使用，因为可能别名是一个还没执行的表达式 3. GROUP BY
大数据笔试之SQL必看篇（电商场景）
12-06 586
大数据笔试SQL之电商篇
SQL电商面试题：如何分析复杂业务？
前景
07-30 632
SQL电商面试题：如何分析复杂业务？
某电商销售数据分析 SQL 面试题解析
热门推荐
Tony.Dong的专栏
04-22 1万+
本文基于某电商销售数据分析 SQL 面试题，解析并给出了 MySQL 中的解决方法。依次使用了 GROUP BY 分组加聚合函数、HAVING 过滤和DISTINCT 去重、ORDER BY 排序加上 LIMIT 子句实现 Top-N 排行榜，以及窗口函数。
27、电商项目的代码及其SQL文件
叉叉不低头
12-05 1511
附上码云地址：https://gitee.com/Chakid/mmall_learning 其中： v1.0分支是当前第一阶段的代码， v2.0分支是阶段二的项目代码。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

月亮给我抄代码

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值