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

## 数据准备

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;


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

|
3天前
|
SQL DataWorks 关系型数据库
DataWorks产品使用合集之数据集成时源头提供数据库自定义函数调用返回数据，数据源端是否可以写自定义SQL实现
DataWorks作为一站式的数据开发与治理平台，提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案，帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述，涵盖数据处理的各个环节。
18 7
|
1天前
|
SQL 分布式计算 DataWorks
MaxCompute操作报错合集之使用sql查询一个表的分区数据时遇到报错，该如何解决
MaxCompute是阿里云提供的大规模离线数据处理服务，用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时，可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。
10 2
|
11天前
|
SQL 数据库
【SQL】已解决：SQL分组去重并合并相同数据
【SQL】已解决：SQL分组去重并合并相同数据
22 1
|
15天前
|
SQL 分布式计算 关系型数据库
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

14 0
|
11天前
|
SQL 测试技术 数据库
【SQL】已解决：SQL错误(15048): 数据兼容级别有效值为100、110或120
【SQL】已解决：SQL错误(15048): 数据兼容级别有效值为100、110或120
19 0
|
18天前
|
SQL 存储 大数据
SQL中DISTINCT关键字的使用与性能影响分析
SQL中DISTINCT关键字的使用与性能影响分析
13 0
|
2月前
|
SQL 数据采集 数据挖掘

142 1
|
5天前
|
SQL 分布式计算 大数据

20 6