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

本文涉及的产品
检索分析服务 Elasticsearch 版,2核4GB开发者规格 1个月
大数据开发治理平台 DataWorks,不限时长
实时计算 Flink 版,5000CU*H 3个月
简介: 作为一名数据分析师,你需要分析电商平台的用户行为和订单数据。你有三张表:`users`(用户信息),`orders`(订单信息)和`order_items`(订单商品信息)。任务包括计算用户总订单金额和数量,按月统计订单,找出最常购买的商品,找到平均每月最高订单金额和数量的用户,以及分析高消费用户群体的年龄和性别分布。通过SQL查询,你可以实现这些分析,例如使用`GROUP BY`、`JOIN`和窗口函数来排序和排名。

需求描述

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

数据准备

我们有三张表,表的字段信息如下:

users

记录用户的信息

字段名 字段类型 备注
user_id int 用户ID
name string 用户姓名
age int 用户年龄
gender string 用户性别
register_date string 注册日期

示例数据:

user_id name age gender register_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_id int 订单ID
user_id int 用户ID
order_date string 订单日期
amount double 订单金额

示例数据:

order_id user_id order_date amount
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

order_items

记录订单中商品的信息

字段名 字段类型 备注
order_item_id int 订单项ID
order_id int 订单ID
product_id int 商品ID
quantity int 商品数量
price double 商品单价

示例数据:

order_item_id order_id product_id quantity price
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

数据集

-- 创建用户表
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;

image.png

解题思路

  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");

image.png

解题思路

  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;

image.png

解题思路

  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;

image.png

解题思路

  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;

image.png

解题思路

  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;

image.png

解题思路

  1. 筛选出过去一年内注册的用户;
  2. 联合 orders 表,统计这些用户的总订单金额;
  3. 通过排序获取出前 5 名用户,如果想要精准获取允许重复,则可以使用 rank 或者 row_number 进行窗口排序后过滤获取前 5
相关文章
|
3天前
|
SQL DataWorks 关系型数据库
DataWorks产品使用合集之数据集成时源头提供数据库自定义函数调用返回数据,数据源端是否可以写自定义SQL实现
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
1天前
|
SQL 分布式计算 DataWorks
MaxCompute操作报错合集之使用sql查询一个表的分区数据时遇到报错,该如何解决
MaxCompute是阿里云提供的大规模离线数据处理服务,用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时,可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。
|
11天前
|
SQL 数据库
【SQL】已解决:SQL分组去重并合并相同数据
【SQL】已解决:SQL分组去重并合并相同数据
22 1
|
15天前
|
SQL 分布式计算 关系型数据库
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
24 2
|
1天前
|
SQL 存储 Oracle
TDengine 3.3.2.0 发布:新增 UDT 及 Oracle、SQL Server 数据接入
**TDengine 3.3.2.0 发布摘要** - 开源与企业版均强化性能,提升WebSocket、stmt模式写入与查询效率,解决死锁,增强列显示。 - taos-explorer支持geometry和varbinary类型。 - 企业版引入UDT,允许自定义数据转换。 - 新增Oracle和SQL Server数据接入。 - 数据同步优化,支持压缩,提升元数据同步速度,错误信息细化,支持表名修改。 - 扩展跨平台支持,包括麒麟、Euler、Anolis OS等。
9 0
|
4天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用问题之如何使用Flink SQL连接带有Kerberos认证的Hive
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
11天前
|
SQL 测试技术 数据库
【SQL】已解决:SQL错误(15048): 数据兼容级别有效值为100、110或120
【SQL】已解决:SQL错误(15048): 数据兼容级别有效值为100、110或120
19 0
|
18天前
|
SQL 存储 大数据
SQL中DISTINCT关键字的使用与性能影响分析
SQL中DISTINCT关键字的使用与性能影响分析
|
2月前
|
SQL 数据采集 数据挖掘
大数据行业应用之Hive数据分析航班线路相关的各项指标
大数据行业应用之Hive数据分析航班线路相关的各项指标
142 1
|
5天前
|
SQL 分布式计算 大数据
大数据处理平台Hive详解
【7月更文挑战第15天】Hive作为基于Hadoop的数据仓库工具,在大数据处理和分析领域发挥着重要作用。通过提供类SQL的查询语言,Hive降低了数据处理的门槛,使得具有SQL背景的开发者可以轻松地处理大规模数据。然而,Hive也存在查询延迟高、表达能力有限等缺点,需要在实际应用中根据具体场景和需求进行选择和优化。