【MySQL实战】基于100万电商用户的1亿条行为数据分析——自发秩序

已于 2023-09-25 20:57:27 修改
阅读量591 收藏 12
点赞数 4
分类专栏： SQL 文章标签： mysql
于 2023-02-27 21:19:46 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tossing_a_coin/article/details/129248185
版权

工具使用

kettle，Navicat，Tableau，xmind

数据集

来源：数据集-阿里云天池 (aliyun.com)

理解数据(数据预处理后)

user_id

用户ID

item_id

商品ID

category_id

商品类别ID

behavior_type

行为类型

pv：商品详情页pv，等价于点击

buy：商品购买

cart：将商品加入购物车

fav：收藏商品

timestamps

时间戳

datetimes/dates/times/hours

时间

导入一亿条数据

1.命令提示符

1.1在命令提示符中进入MySQL

1.2在MySQL中建立相应的数据库以及表

2.kettle

2.1新建转换——输入-CSV文件输入——输出-表输出

2.2打开CSV——浏览——选择数据集中的.CSV文件——取消"包含列头行"——获取字段——用命令提示符中的字段名称替换掉kettle中的名称并且修改字段的长度

2.3打开表输出——新建数据库连接——选择MYSQL——填写连接名称（connect mysql）、主机名称（localhost），数据库名称（taobao）,端口号、用户名、密码——点击测试

2.4打开连接池（表输出）——使用连接词——设置初始大小、最大空闲空间（皆为100）——将自动取消设置为false

2.5选项（表输出）——设置参数

2.6表输出——提交记录数量10000——勾选指定数据字段-点击获取字段

2.7主页将输入拖动到输出（主输入步骤）——表输出右键-改变开始复制的数量（10~20）

2.8表输出——浏览目标表——user_behavior——点击获取字段——填写数据库字段和字段流

2.9填写表输出的数据库字段中的流字段——运行-启动-命名

3.命令提示符（测试是否导入成功)

数据预处理

定义：数据预处理是指根据需求增减字段或者值，主要有应用在字段名、字段类型、字段约束、值的去空、去重、去异常

1.navicat查询中进行预处理


use taobao;
desc user_behavior;
-- desc 表名 查看表的结构
 
SELECT * 
FROM user_behavior
LIMIT 5;
 
-- timestamp同数据类型的timestamp重名，需修改
/* 
修改字段名
alter table 表名 change 旧字段名 新字段名 新数据类型
*/
ALTER TABLE user_behavior CHANGE timestamp timestamps int(14);
desc user_behavior;
 
-- 检查空值
SELECT *
FROM user_behavior
WHERE user_id IS NULL;
 
SELECT *
FROM user_behavior
WHERE item_id IS NULL;
 
SELECT *
FROM user_behavior
WHERE category_id IS NULL;
 
SELECT *
FROM user_behavior
WHERE behavior_type IS NULL;
 
SELECT *
FROM user_behavior
WHERE timestamps IS NULL;
 
-- 检查重复值（这里查询的是组合的重复值）
/*
重复值查询
select 检查的字段
from 表
group by 检查的字段
having count(检查字段)>1
*/
SELECT user_id,item_id,timestamps
FROM user_behavior
GROUP BY user_id,item_id,timestamps
HAVING COUNT(*)>1;
 
-- 去重
/*
添加一个字段并且居于首位
alter table 表名 add 字段名 字段类型 first
*/
ALTER TABLE user_behavior ADD id int FIRST;
SELECT * 
FROM user_behavior
LIMIT 5;
/*
修改字段为主键并且自增
    alter table 表名 modify 字段名 字段类型 primary key auto_increment
对已经存在的表，添加主键约束
    alter table 表名 modify 字段名 字段类型 primary key
自增
    auto_increment
*/
ALTER TABLE user_behavior MODIFY id INT PRIMARY KEY auto_increment;

/*
数据的去重
1 找到重复值，并命名
        1.1 用group by 将字段组合起来，会将重复的值压缩为一条
        1.2 用count(*)查找重复的数量
        1.3 将重复的字段展现出来，并且找到重复值中最小的id号
        1.4 将找到的重复值作为子查询命名
2 去重
        2.1 用where筛选
        2.2 子查询的字段和外查询的字段相同时删除数据，必须同时满足数据
        2.3 user_behavior.id > t2.id （删除重复值的范围，只保留group by中他的最小id的数据）
delete 后直接跟表，是因为我只需要删除user_behavior的数据，而非t2的数据
*/
DELETE user_behavior 
FROM user_behavior,
(
SELECT user_id,item_id,timestamps,MIN(id) id
FROM user_behavior
GROUP BY user_id,item_id,timestamps
HAVING COUNT(*)>1
) t2
WHERE user_behavior.user_id = t2.user_id
AND user_behavior.item_id = t2.item_id
AND user_behavior.timestamps = t2.timestamps
AND user_behavior.id > t2.id;
 
-- 新增字段：date time hour
-- 更改buffer值（更改缓冲值的大小）
 
-- 查看缓冲值现在的大小
/*
查看缓冲池，并对他进行修改
1 show variables 查看系统变量及其值
2 like模糊查询
3 set global 全局变量的设置，后面跟赋值公式
*/
SHOW VARIABLES LIKE '%_buffer%';
-- 设缓冲值为10G
SET GLOBAL innodb_buffer_pool_size = 10700000000;
 
-- TIMESTAMP(0) 0的意思是秒后面的位数为0
-- 添加一个时间字段，并且秒后面的位数为0
ALTER TABLE user_behavior ADD datetimes TIMESTAMP(0);
-- int型的TIMESTAMP改为日期型的TIMESTAMP（天啦，运行了5个小时，我要减少一下数据量了）
/*
将int型的TIMESTAMP改为日期型的TIMESTAMP
1 更新已有数据
update 表名 set 字段=值
2 int的时间戳修改为日期型
from_unixtime(时间戳)
*/
UPDATE user_behavior SET datetimes = FROM_UNIXTIME(timestamps);
 
-- 数据过多删除某些行,只保留50w数据(难以运行)
DELETE FROM user_behavior
WHERE id > 500000;
 
-- 插入dates、times、hours
-- 添加 日期、时间、小时的字段，并设置数据类型和长度
ALTER TABLE user_behavior ADD dates char(10);
ALTER TABLE user_behavior ADD times char(8);
ALTER TABLE user_behavior ADD hours char(2);
 
/* SUBSTRING（str，start, length）：str：代表字符串，start：代表是从第几位开始截取，length：代表截取几位数
*/
UPDATE user_behavior set dates = SUBSTRING(datetimes,1,10),
times = SUBSTRING(datetimes,12,8),
hours = SUBSTRING(datetimes,12,2);
 
-- 去异常——时间范围的异常（2017.11.25-12.3）
SELECT MAX(datetimes),MIN(datetimes) 
FROM user_behavior;
 
-- 异常的去除方法
DELETE FROM user_behavior
WHERE datetimes < "2017-11-25 00:00:00"
OR datetimes > "2017-12-03 23:59:59";
 
-- 数据概览
DESC user_behavior;
SELECT * 
FROM user_behavior
LIMIT 5;
SELECT COUNT(*) FROM user_behavior; #499779条数据

获客情况


-- 创建临时表，临时表的字段名和原表相同，但是无数据
/*
创建临时表格，并复制原表的字段名，不复制数据
create table 临时表名 like 原表
*/
CREATE TABLE temp_behavior 
LIKE user_behavior;
 
-- 截取原表中的100000条数据插入临时表中
/*
截取原表的数据插入临时表中
insert into 临时表 select 列 from 表 limit 数据量
*/
INSERT INTO temp_behavior 
SELECT * 
FROM user_behavior 
LIMIT 100000;
 
-- 查看临时表数据
SELECT *
FROM temp_behavior;
 
-- PV 页面浏览量 每天页面的点击数
SELECT dates,COUNT(*) "pv"
FROM temp_behavior
WHERE behavior_type = "pv"
GROUP BY dates
ORDER BY dates;
 
-- UV 独立的访客数(当日的客户数量) 去除每天相同客户的点击
SELECT dates,COUNT(DISTINCT user_id) "uv"
FROM temp_behavior
WHERE behavior_type = "pv"
GROUP BY dates
ORDER BY dates;
 
-- uv、pv每天顾客浏览了多少次
/* 
round(a,b)  
a：要处理的数
b：保留几位小数
*/
SELECT dates,
    COUNT(*) "pv",
    COUNT(DISTINCT user_id) "uv",
    ROUND(COUNT(*)/COUNT(DISTINCT user_id),1) "pv/uv"
FROM temp_behavior
WHERE behavior_type = "pv"
GROUP BY dates
ORDER BY dates;
 
-- 创建获客情况表格
/*
创建表格并定义字段
create table 表名（字段1 字段类型1 ,字段2 字段类型2）
decimal型（十进制小数型）比float和double计算精度高，使用空间少，常用于金融运算。
1 浮点型会四舍五入，decimal则不会
2 decimal(a,b)
    a：一共保留几位数 
    b:小数点后保留几位数
*/
 
CREATE TABLE pv_uv_puv(
dates char(10),
pv int(9),
uv int(9),
puv decimal(10,1)
);
 
-- 取原表中的数据插入获客情况表
/*
round(a,b)  
        a：要处理的数
        b：保留几位小数
*/
INSERT INTO pv_uv_puv
SELECT dates,
    COUNT(*) "pv",
    COUNT(DISTINCT user_id) "uv",
    ROUND(COUNT(*)/COUNT(DISTINCT user_id),1) "pv/uv"
FROM user_behavior
WHERE behavior_type = "pv"
GROUP BY dates
ORDER BY dates;
 
-- 查看获客情况表
SELECT *
FROM pv_uv_puv
ORDER BY dates;
 
-- *本次获客情况表无空值，若出现空值，可使用以下办法
DELETE FROM pv_uv_puv WHERE dates IS NULL;

留存情况


SELECT * FROM user_behavior WHERE dates IS NULL;
 
SELECT *
FROM temp_behavior;
-- 留存率：活跃用户留存率，计算次日留存率
 
-- 用户活跃日期
SELECT user_id,dates
FROM temp_behavior
GROUP BY user_id,dates
ORDER BY user_id;
 
 
-- 自关联(隐式连接)
SELECT * FROM
(SELECT user_id,dates
FROM temp_behavior
GROUP BY user_id,dates
) a
,(SELECT user_id,dates
FROM temp_behavior
GROUP BY user_id,dates)
b
WHERE a.user_id=b.user_id;
 
 
-- 筛选
SELECT * FROM
(SELECT user_id,dates
FROM temp_behavior
GROUP BY user_id,dates
) a
,(SELECT user_id,dates
FROM temp_behavior
GROUP BY user_id,dates)
b
WHERE a.user_id=b.user_id AND a.dates < b.dates;
 
 
--datediff函数返回两个日期之间的天数,对时间差值的计算方式为date1-date2的差值。 
-- 留存数，以日期为单位的顾客数量
SELECT a.dates,
COUNT(if(datediff(b.dates,a.dates)=0,b.user_id,NULL)) rentention0,
COUNT(if(datediff(b.dates,a.dates)=1,b.user_id,NULL)) rentention1
FROM
(SELECT user_id,dates
FROM temp_behavior
GROUP BY user_id,dates
) a
,(SELECT user_id,dates
FROM temp_behavior
GROUP BY user_id,dates)
b
WHERE a.user_id=b.user_id AND a.dates <= b.dates
GROUP BY a.dates
 
-- 留存率
 
SELECT a.dates,
COUNT(if(datediff(b.dates,a.dates)=1,b.user_id,NULL))/COUNT(if(datediff(b.dates,a.dates)=0,b.user_id,NULL)) rentention1
FROM
(SELECT user_id,dates
FROM temp_behavior
GROUP BY user_id,dates
) a
,(SELECT user_id,dates
FROM temp_behavior
GROUP BY user_id,dates)
b
WHERE a.user_id=b.user_id AND a.dates <= b.dates
GROUP BY a.dates
 
 
 
-- 保存结果
CREATE TABLE retention_rate
(
dates CHAR(10),
retention_1 FLOAT);
 

INSERT INTO retention_rate
SELECT a.dates,
COUNT(if(datediff(b.dates,a.dates)=1,b.user_id,NULL))/COUNT(if(datediff(b.dates,a.dates)=0,b.user_id,NULL)) rentention1
FROM
(SELECT user_id,dates
FROM user_behavior
GROUP BY user_id,dates
) a
,(SELECT user_id,dates
FROM user_behavior
GROUP BY user_id,dates)
b
WHERE a.user_id=b.user_id AND a.dates <= b.dates
GROUP BY a.dates;
 
SELECT *
FROM retention_rate
 
 
-- 跳失率，是指显示顾客通过相应入口进入，只访问了一个页面就离开的访问次数占该页面总访问次数的比例。
 
-- 跳失用户 
--  10
 
SELECT count(*)
FROM
(
SELECT user_id 
FROM user_behavior
GROUP BY user_id
HAVING COUNT(behavior_type) = 1
) a
 
-- 448998
SELECT SUM(pv) FROM pv_uv_puv
 
-- 10/448998

时间序列分析

1.在sql中创建date_hour_behavior


--统计日期一小时行为
SELECT dates,hours,
            COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) 'pv',
            COUNT(IF(behavior_type = 'cart',behavior_type,NULL)) 'cart',
            COUNT(IF(behavior_type = 'fav',behavior_type,NULL)) 'fav',
            COUNT(IF(behavior_type = 'buy',behavior_type,NULL)) 'buy'
            
FROM temp_behavior
GROUP BY dates,hours
ORDER BY dates,hours
 
 
-- 存储
CREATE TABLE date_hour_behavior AS
(SELECT dates,hours,
            COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) 'pv',
            COUNT(IF(behavior_type = 'cart',behavior_type,NULL)) 'cart',
            COUNT(IF(behavior_type = 'fav',behavior_type,NULL)) 'fav',
            COUNT(IF(behavior_type = 'buy',behavior_type,NULL)) 'buy'
            
FROM user_behavior
GROUP BY dates,hours
ORDER BY dates,hours)
 
 
-- 检查
SELECT * FROM date_hour_behavior

2.tableau

2.1 分析用户的周内行为

11月25日、26日，12月2日、3日是周末，用户在周末的各种行为均高于工作日

2.2 分析用户的小时行为

2.3 共同分析用户的周内行为和小时行为

2.4 分析用户下单购买的概率

用户转化率分析


-- 统计各类行为用户数
SELECT behavior_type,
            COUNT(DISTINCT user_id) use_num
FROM temp_behavior
GROUP BY behavior_type
ORDER BY behavior_type DESC
 
-- 存储
CREATE TABLE behavior_user_num AS
SELECT behavior_type,
            COUNT(DISTINCT user_id) use_num
FROM user_behavior
GROUP BY behavior_type
ORDER BY behavior_type DESC    
 
SELECT * FROM behavior_user_num
 
-- 0.6631(购买概率)
SELECT 3444/5194
 
-- 统计各类行为的数量
SELECT behavior_type,
        COUNT(*) behavior_count_num
FROM temp_behavior
GROUP BY behavior_type
ORDER BY behavior_type DESC
 
-- 储存数据
CREATE TABLE behavior_num AS
SELECT behavior_type,
           COUNT(*) behavior_count_num
FROM user_behavior
GROUP BY behavior_type
ORDER BY behavior_type DESC
 
SELECT * FROM behavior_num
 
-- buy/pv 0.0228
SELECT 10226/448998
-- (fav+cart)/pv 0.0903
SELECT (13013+27542)/448998

行为路径分析


-- 删除后面使用temp_behavior创建的视图，并使用原表进行视图的再次创建
DROP VIEW user_behavior_view
DROP VIEW user_behavior_standard
DROP VIEW user_behavior_path
DROP VIEW path_count
 
-- 在查询中对字段内的内容进行筛选可用if
-- 用户对商品行为类型的计算
CREATE VIEW user_behavior_view AS
SELECT user_id,item_id,
        COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) pv,
        COUNT(IF(behavior_type = 'cart',behavior_type,NULL)) cart,
        COUNT(IF(behavior_type = 'fav',behavior_type,NULL)) fav,
        COUNT(IF(behavior_type = 'buy',behavior_type,NULL)) buy        
FROM temp_behavior
GROUP BY user_id,item_id
 
 
-- 用户行为标准化
CREATE VIEW user_behavior_standard AS
SELECT user_id,item_id,
            (CASE WHEN pv>0 THEN 1 ELSE 0 END) '浏览了',
            (CASE WHEN fav>0 THEN 1 ELSE 0 END) '收藏了',     
            (CASE WHEN cart>0 THEN 1 ELSE 0 END) '加购了',
            (CASE WHEN buy>0 THEN 1 ELSE 0 END) '购买了' 
FROM user_behavior_view
 
-- 购买路径类型
-- CONCAT拼接函数
CREATE VIEW user_behavior_path AS
SELECT *,
CONCAT(浏览了,收藏了,加购了,购买了) 购买路径类型
FROM user_behavior_standard AS a
WHERE a.购买了> 0
 
-- 统计各类购买行为数量
CREATE VIEW path_count AS
SELECT 购买路径类型,count(*) 数量
FROM user_behavior_path
GROUP BY 购买路径类型
ORDER BY 数量 DESC
 
-- 解释表
CREATE TABLE explanation(
path_type char(4),
description VARCHAR(40)
)
 
INSERT INTO explanation
VALUES('0001','直接购买'),
            ('1001','浏览后购买'),
            ('0011','加购后购买'),
            ('1011','浏览加购后购买'),
            ('0101','收藏后购买'),
            ('1101','浏览收藏后购买'),
            ('0111','收藏加购后购买'),
            ('1111','浏览收藏加购后购买')
 
SELECT * FROM explanation
 
SELECT * FROM explanation e
JOIN path_count pc
ON pc.购买路径类型 = e.path_type
ORDER BY 数量 DESC
 
-- 使用原始表
 
-- 在查询中对字段内的内容进行筛选可用if
CREATE VIEW user_behavior_view AS
SELECT user_id,item_id,
        COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) pv,
        COUNT(IF(behavior_type = 'cart',behavior_type,NULL)) cart,
        COUNT(IF(behavior_type = 'fav',behavior_type,NULL)) fav,
        COUNT(IF(behavior_type = 'buy',behavior_type,NULL)) buy        
FROM user_behavior
GROUP BY user_id,item_id
 
 
-- 用户行为标准化
CREATE VIEW user_behavior_standard AS
SELECT user_id,item_id,
            (CASE WHEN pv>0 THEN 1 ELSE 0 END) '浏览了',
            (CASE WHEN fav>0 THEN 1 ELSE 0 END) '收藏了',     
            (CASE WHEN cart>0 THEN 1 ELSE 0 END) '加购了',
            (CASE WHEN buy>0 THEN 1 ELSE 0 END) '购买了' 
FROM user_behavior_view
 
-- 路径类型
CREATE VIEW user_behavior_path AS
SELECT *,
CONCAT(浏览了,收藏了,加购了,购买了) 购买路径类型
FROM user_behavior_standard AS a
WHERE a.购买了> 0
 
-- 统计各类购买行为数量
CREATE VIEW path_count AS
SELECT 购买路径类型,count(*) 数量
FROM user_behavior_path
GROUP BY 购买路径类型
ORDER BY 数量 DESC
 
-- 解释表
CREATE TABLE explanation(
path_type char(4),
description VARCHAR(40)
)
 
INSERT INTO explanation
VALUES('0001','直接购买'),
            ('1001','浏览后购买'),
            ('0011','加购后购买'),
            ('1011','浏览加购后购买'),
            ('0101','收藏后购买'),
            ('1101','浏览收藏后购买'),
            ('0111','收藏加购后购买'),
            ('1111','浏览收藏加购后购买')
 
SELECT * FROM explanation
 
SELECT * FROM explanation e
JOIN path_count pc
ON pc.购买路径类型 = e.path_type
ORDER BY 数量 DESC
 
CREATE TABLE path_result AS
SELECT path_type,description,数量
FROM explanation e
JOIN path_count pc
ON pc.购买路径类型 = e.path_type
ORDER BY 数量 DESC
 
 
SELECT *
FROM path_result
 
 
-- 购买但没有收藏加购的购买数量 7785
SELECT sum(buy)
FROM user_behavior_view
WHERE buy>0 AND fav=0 AND cart=0
-- 总购买量 10226
-- 收藏加购后购买量 2441
-- 收藏加购 13013+27542
-- 收藏加购后购买的概率 0.0602
SELECT 2441/(13013+27542)

RFM模型


-- 最近购买的时间
SELECT user_id,MAX(dates) '最近购买时间'
FROM user_behavior
WHERE behavior_type = "buy"
GROUP BY user_id
ORDER BY 2 DESC;
#ORDER BY后面的数字分别代表SELECT后面的第几个字段，当查询的列发生改变，容易忘记修改ORDER BY列表。

-- 购买次数
SELECT user_id,count(user_id) "购买次数"
FROM user_behavior
WHERE behavior_type = "buy"
GROUP BY user_id
ORDER BY 2 DESC;

-- 统一
SELECT user_id,count(user_id) "购买次数",MAX(dates) "最近购买时间"
FROM user_behavior
WHERE behavior_type = "buy"
GROUP BY user_id
ORDER BY 2 DESC,3 DESC;

-- 创建表之前判断表是否存在，如果存在则删除已有表
-- DROP table IF EXISTS rfm_model
-- 储存
CREATE TABLE RFM_model AS
SELECT user_id,count(user_id) "购买次数",MAX(dates) "最近购买时间"
FROM user_behavior
WHERE behavior_type = "buy"
GROUP BY user_id
ORDER BY 2 DESC,3 DESC;


-- 根据购买的次数对用户进行分层
ALTER TABLE rfm_model ADD fscore int;

UPDATE rfm_model
SET fscore = CASE 
    WHEN 购买次数 BETWEEN 50 AND 60 THEN 5
    WHEN 购买次数 BETWEEN 40 AND 59 THEN 4
    WHEN 购买次数 BETWEEN 25 AND 39 THEN 3
    WHEN 购买次数 BETWEEN 15 AND 24 THEN 2
    ELSE 1
    END
    
SELECT * FROM rfm_model

-- 根据最近购买时间对用户进行分层
ALTER TABLE rfm_model ADD rscore INT;

UPDATE rfm_model
SET rscore = CASE 
    WHEN 最近购买时间 = '2017-12-03' THEN 5
    WHEN 最近购买时间 IN ('2017-12-01','2017-12-02') THEN 4
    WHEN 最近购买时间 IN ('2017-11-29','2017-11-30') THEN 3
    WHEN 最近购买时间 IN ('2017-11-27','2017-11-28') THEN 2
    ELSE 1
    END


-- 定义两个变量，存取f和r的平均值
SET @r_avg = NULL;
SET @f_avg = NULL;
SELECT AVG(fscore) INTO @f_avg FROM rfm_model;
SELECT AVG(rscore) INTO @r_avg FROM rfm_model;

-- 根据平均数将用户分类
SELECT *,
(CASE 
WHEN fscore>@f_avg AND rscore>@r_avg THEN '价值用户'
    WHEN fscore>@f_avg AND rscore<@r_avg THEN '保持用户'
    WHEN fscore<@f_avg AND rscore>@r_avg THEN '发展用户'
    WHEN fscore<@f_avg AND rscore<@r_avg THEN '挽留用户'
    END) class
FROM rfm_model

-- 插入 
-- COLUMN关键字是可选的，因此您可以省略它。
ALTER TABLE rfm_model ADD COLUMN class VARCHAR(40);
UPDATE rfm_model
SET class = CASE 
WHEN fscore>@f_avg AND rscore>@r_avg THEN '价值用户'
WHEN fscore>@f_avg AND rscore<@r_avg THEN '保持用户'
WHEN fscore<@f_avg AND rscore>@r_avg THEN '发展用户'
WHEN fscore<@f_avg AND rscore<@r_avg THEN '挽留用户'
END

-- 统计各分区的用户数
SELECT class,COUNT(user_id)
FROM rfm_model
GROUP BY class

商品按照热度分类


-- 商品类别浏览量排名
SELECT category_id,
    COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) '品类浏览量'
FROM user_behavior  u
GROUP BY u.category_id
ORDER BY 2 DESC
LIMIT 10
 
-- 商品浏览量排名
SELECT item_id,
    COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) '商品浏览量'
FROM user_behavior  u
GROUP BY item_id
ORDER BY 2 DESC
LIMIT 10

-- 品类中商品的浏览量排名
SELECT category_id,item_id,品类中的商品浏览量
FROM 
(
SELECT item_id,
    category_id,
    COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) '品类中的商品浏览量',
    RANK()over(PARTITION by category_id ORDER BY COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) DESC ) r
FROM user_behavior  u
GROUP BY category_id,item_id
ORDER BY 3 DESC
) a
WHERE a.r = 1
ORDER BY a.品类中的商品浏览量 DESC
LIMIT 10

CREATE TABLE popular_categories(
category_id INT,
pv INT
);
CREATE TABLE popular_items(
item_id INT,
pv INT
);
CREATE TABLE popular_cateitems(
category_id INT,
item_id INT,
pv INT
)

INSERT INTO popular_categories
SELECT category_id,
    COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) '品类浏览量'
FROM user_behavior  u
GROUP BY u.category_id
ORDER BY 2 DESC
LIMIT 10;

INSERT INTO popular_items
SELECT item_id,
    COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) '商品浏览量'
FROM user_behavior  u
GROUP BY item_id
ORDER BY 2 DESC
LIMIT 10;

INSERT INTO popular_cateitems
SELECT category_id,item_id,品类中的商品浏览量
FROM 
(
SELECT item_id,
    category_id,
    COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) '品类中的商品浏览量',
    RANK()over(PARTITION by category_id ORDER BY COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) DESC ) r
FROM user_behavior  u
GROUP BY category_id,item_id
ORDER BY 3 DESC
) a
WHERE a.r = 1
ORDER BY a.品类中的商品浏览量 DESC
LIMIT 10;

SELECT * FROM popular_categories

商品转化率分析


-- 特点商品转化率
SELECT item_id,
        COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) pv,
        COUNT(IF(behavior_type = 'cart',behavior_type,NULL)) cart,
        COUNT(IF(behavior_type = 'fav',behavior_type,NULL)) fav,
        COUNT(IF(behavior_type = 'buy',behavior_type,NULL)) buy,
      COUNT(DISTINCT IF(behavior_type = 'buy',user_id,NULL)) / COUNT(DISTINCT user_id) '商品转化率'
FROM user_behavior
GROUP BY item_id
ORDER BY 商品转化率 DESC

-- 保存
CREATE TABLE item_detail(
item_id INT,
pv INT,
fav INT,
cart INT,
buy INT,
user_buy_rate FLOAT
);

INSERT INTO item_detail
SELECT item_id,
        COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) pv,
        COUNT(IF(behavior_type = 'cart',behavior_type,NULL)) cart,
        COUNT(IF(behavior_type = 'fav',behavior_type,NULL)) fav,
        COUNT(IF(behavior_type = 'buy',behavior_type,NULL)) buy,
      COUNT(DISTINCT IF(behavior_type = 'buy',user_id,NULL)) / COUNT(DISTINCT user_id) '商品转化率'
FROM user_behavior
GROUP BY item_id
ORDER BY 商品转化率 DESC;

SELECT * FROM item_detail;


-- 品类转化率
CREATE TABLE category_detail(
category_id INT,
pv INT,
fav INT,
cart INT,
buy INT,
user_buy_rate FLOAT
);

INSERT INTO category_detail
SELECT category_id,
        COUNT(IF(behavior_type = 'pv',behavior_type,NULL)) pv,
        COUNT(IF(behavior_type = 'cart',behavior_type,NULL)) cart,
        COUNT(IF(behavior_type = 'fav',behavior_type,NULL)) fav,
        COUNT(IF(behavior_type = 'buy',behavior_type,NULL)) buy,
      COUNT(DISTINCT IF(behavior_type = 'buy',user_id,NULL)) / COUNT(DISTINCT user_id) '品类转化率'
FROM user_behavior
GROUP BY category_id
ORDER BY 品类转化率 DESC;

SELECT * FROM category_detail;

商品特征分析(Tableau)

点击数高、购买数高 = 刚需产品

点击数少、购买数少 = 存在替代品，客户很难在一个产品大量购买

点击数高、购买数少 = 商品需求弹性较大

注：基于项目的SQL知识点

1.修改字段名

alter table 表名 change 旧字段名 新字段名 新数据类型

举例:


ALTER TABLE user_behavior CHANGE timestamp timestamps int(14);

2.重复值查询

select 检查的字段

from 表

group by 检查的字段

having count(检查字段)>1

举例：


SELECT user_id,item_id,timestamps
FROM user_behavior
GROUP BY user_id,item_id,timestamps
HAVING COUNT(*)>1;

3.添加一个字段

3.1 添加一个字段并且居于首位

alter table 表名 add 字段名 字段类型 first


ALTER TABLE user_behavior ADD id int FIRST;

3.2 添加一个时间字段，并秒后面的位数为0


ALTER TABLE user_behavior ADD datetimes TIMESTAMP(0);

3.3 添加 日期、时间、小时的字段，并设置数据类型和长度


ALTER TABLE user_behavior ADD dates char(10);
ALTER TABLE user_behavior ADD times char(8);
ALTER TABLE user_behavior ADD hours char(2);

4.修改字段为主键并且自增

alter table 表名 modify 字段名 字段类型 primary key auto_increment

4.1 对已经存在的表，添加主键约束

alter table 表名 modify 字段名 字段类型 primary key

4.2自增

auto_increment

举例：


ALTER TABLE user_behavior MODIFY id INT PRIMARY KEY auto_increment;

5.数据的去重

5.1 找到重复值，并命名

5.1.1 用group by 将字段组合起来，会将重复的值压缩为一条

5.1.2 用count(*)查找重复的数量

5.1.3 将重复的字段展现出来，并且找到重复值中最小的id号

5.1.4 将找到的重复值作为子查询命名

5.2 去重

5.2.1 用where筛选

5.2.2 子查询的字段和外查询的字段相同时删除数据，必须同时满足数据

5.2.3 user_behavior.id > t2.id （删除重复值的范围，只保留group by中他的最小id的数据）

注意：delete from后跟子查询，就不应使用DELETE FROM 表，而应该DELETE 表 FROM 表，


DELETE user_behavior FROM user_behavior,
(
SELECT user_id,item_id,timestamps,MIN(id) id
FROM user_behavior
GROUP BY user_id,item_id,timestamps
HAVING COUNT(*)>1
) t2
WHERE user_behavior.user_id = t2.user_id
AND user_behavior.item_id = t2.item_id
AND user_behavior.timestamps = t2.timestamps
AND user_behavior.id > t2.id;

6.查看缓冲池，并对他进行修改

6.1 show variables 查看系统变量及其值

6.2 like模糊查询

6.3 set global 全局变量的设置，后面跟赋值公式


SHOW VARIABLES LIKE '%_buffer%';
SET GLOBAL innodb_buffer_pool_size = 10700000000;

7.将int型的TIMESTAMP改为日期型的TIMESTAMP

7.1 更新已有数据

update 表名 set 字段=值

7.2 int的时间戳修改为日期型

from_unixtime(时间戳)


UPDATE user_behavior SET datetimes = FROM_UNIXTIME(timestamps);

8.截取子串并补充日期、时间、小时的数据

8.1 截取子串

SUBSTRING（str，start, length）

str：代表字符串，start：代表是从第几位开始截取，length：代表截取几位数


UPDATE user_behavior 
SET dates = SUBSTRING(datetimes,1,10),
times = SUBSTRING(datetimes,12,8),
hours = SUBSTRING(datetimes,12,2);

9.创建临时表格，并复制原表的字段名，不复制数据

create table 临时表名 like 原表


CREATE TABLE temp_behavior LIKE user_behavior;

10.截取原表的数据插入临时表中

insert into 临时表 select 列 from 表 limit 数据量


INSERT INTO temp_behavior SELECT * FROM user_behavior LIMIT 100000;

11.项目中pv，uv查看


SELECT dates,
    COUNT(*) "pv",
    COUNT(DISTINCT user_id) "uv",
    ROUND(COUNT(*)/COUNT(DISTINCT user_id),1) "pv/uv"
FROM temp_behavior
WHERE behavior_type = "pv"
GROUP BY dates
ORDER BY dates;

11.1 round(a,b)

a：要处理的数

b：保留几位小数

12.创建表格并定义字段

create table 表名（字段1 字段类型1 ,字段2 字段类型2）


CREATE TABLE pv_uv_puv(
dates char(10),
pv int(9),
uv int(9),
puv decimal(10,1)
);

12.1 decimal型（十进制小数型）比float和double计算精度高，使用空间少，常用于金融运算。

12.1.1 浮点型会四舍五入，decimal则不会

12.1.2 decimal(a,b)

a：一共保留几位数

b:小数点后保留几位数

tossing_a_coin
关注
  • 4
    点赞
  • 踩
  • 12
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值