商分SQL小菜一碟 - 方塘的半亩塘

本文最后更新于：2021年7月4日晚上

# 商分SQL小菜一碟

SQL增改查

(1)在登录信息表中插入以下数据：

INSERT INTO login(loginId, password) VALUES 
(40000,123456),(40001,123456),(40002,123456),(40003,123456),(40004,123456),(40005,123456),(40006,123456),(40007,123456),(40008,123456),(40009,123456);

(2) 修改以下用户的密码，密码值和 loginId 的值相同：

UPDATE login SET password=loginid 
WHERE (loginid>19999 AND loginid<20010);
-- 或者：UPDATE login SET password=loginId WHERE loginId LIKE'2000%';

(3) 查询操作后，登录信息表中的数据量(记录条数)。

SELECT count(*) FROM login;

(4) 查看用户基本信息表中，用户名、年龄、所在地、学校信息。

SELECT name,age,location,school FROM userbaseinfo;

(5) 统计用户基本信息表中，用户名含’服装’,‘穿搭’,‘汽车’,‘手机’,‘电影’,'音乐’关键字的用户数量及其比例并汇总。

SELECT name AS "类别",
COUNT(*) AS "用户数量",
COUNT(*)/(SELECT COUNT(*) FROM userbaseinfo) AS "比例"
FROM userbaseinfo WHERE name LIKE'%服装%' GROUP BY name;
-- 以此类推，其中 COUNT(*)/(SELECT COUNT(*)from userbaseinfo) 可以先运行一次计算总数再计算比例，可改成 COUNT(*)/20000
-- 即：SELECT name AS "类别",COUNT(*) AS "用户数量",COUNT(*)/20000 AS "比例" FROM userbaseinfo WHERE name LIKE'%服装%' GROUP BY name;
-- 或者：SELECT count(*),count(*)/20000 FROM userbaseinfo WHERE name LIKE'%服装%';

(6) 在用户基本信息表中，把用户名含’服装’,‘穿搭’,‘汽车’,‘手机’,‘电影’,'音乐’关键字的用户的用户名改为只含关键字。

UPDATE userbaseinfo
SET name='服装'
WHERE name LIKE'%服装%';
UPDATE userbaseinfo
SET name='穿搭'
WHERE name LIKE'%穿搭%';
UPDATE userbaseinfo
SET name='汽车'
WHERE name LIKE'%汽车%';
UPDATE userbaseinfo
SET name='手机'
WHERE name LIKE'%手机%';
UPDATE userbaseinfo
SET name='电影'
WHERE name LIKE'%电影%';
UPDATE userbaseinfo
SET name='音乐'
WHERE name LIKE'%音乐%';

(7) 统计用户基本信息表中，用户的性别数量及其比例并汇总。

SELECT sex AS "性别",
COUNT(*) AS "数量",
COUNT(*)/(SELECT COUNT(*) FROM userbaseinfo) AS "比例" 
FROM userbaseinfo GROUP BY sex;

(8) 统计用户基本信息表中，用户各年龄的数量及其比例并汇总。

SELECT age AS "年龄",
COUNT(*) AS "数量",
COUNT(*)/(SELECT COUNT(*) FROM userbaseinfo) AS "比例"
FROM userbaseinfo GROUP BY age;

(9) 统计用户基本信息表中，用户各年龄段{[0,20),[20,25),[25,30),[30,40),[40,50),[50,100)}的数量及其比例并汇总。

SELECT count(*) AS "[0,20)数量",
count(*)/(SELECT COUNT(*) FROM userbaseinfo) AS "比例"
FROM userbaseinfo WHERE age>=0 and age<20;
-- 以此类推，或着
SELECT count(*) AS "[20,25)数量",
count(*)/20000 AS "比例"
FROM userbaseinfo WHERE age>=20 and age<25;

SELECT count(*),count(*)/20000 FROM userbaseinfo WHERE age>=25 and age<30;
SELECT count(*),count(*)/20000 FROM userbaseinfo WHERE age>=30 and age<40;
SELECT count(*),count(*)/20000 FROM userbaseinfo WHERE age>=40 and age<50;
SELECT count(*),count(*)/20000 FROM userbaseinfo WHERE age>=50 and age<100;

(10) 统计用户基本信息表中，各所在地的用户总数量及其比例并汇总。

SELECT location AS "用户所在地",
count(*) AS "用户总数",
count(*)/(SELECT COUNT(*) FROM userbaseinfo) AS "比例" 
FROM userbaseinfo GROUP BY location;

(11) 在用户基本信息表中，所在地值为 0 的所有数据，修改为北京。

UPDATE userbaseinfo
SET location='北京'
WHERE location=0

(12) 统计用户基本信息表中，所在地值为 1 并且性别取值为 0 的数据量。

SELECT count(*) FROM userbaseinfo WHERE location=1 and sex=0

(13) 统计用户基本信息表中，各学校的用户总数量及其比例。

SELECT school,count(*),count(*)/20000 FROM userbaseinfo GROUP BY school;

(14) 删除用户基本信息表中的简介属性。

Alter TABLE userbaseinfo DROP intro;

(15) 添加用户基本信息表中的简介属性，并将所有记录简介属性的值设置为”简介”。

ALTER TABLE userbaseinfo ADD intro VARCHAR(500);
UPDATE userbaseinfo
SET intro='简介';

(16) 在用户基本信息表中，用户名含有’服装’关键字的所有用户的名字改为’服装类 up 主’。

UPDATE userbaseinfo
SET name='服装类up主'
WHERE name LIKE'%服装%';

(17) 在用户获赞关注粉丝统计信息表中，统计获赞数超过 500000 并且粉丝数超过 10000 的用户数量。

SELECT count(*) FROM userstatistics WHERE love>500000 and fans>10000;

(18) 在用户喜欢的作品表中，查询 userId 值为 20000 的用户喜欢的作品(workId)。

SELECT workid FROM userlikeworks WHERE userId='20000';

(19) 查询 userId 值为 20000 的用户喜欢的作品的发布用户(userId)。

SELECT userId 
FROM userworks 
WHERE workId IN(SELECT workId 
		FROM userlikeworks 
		WHERE userId='20000');

(20) 查询 userId 值为 20000 的用户喜欢的作品的发布用户的用户类型(用户名name视为用户类型)。

SELECT name 
FROM userbaseinfo 
WHERE userId IN
		(SELECT userId 
		FROM userworks 
		WHERE workId IN(SELECT workId 
				FROM userlikeworks 
				WHERE userId='20000'));

(21) 查询 userId 值为 15000 的用户发布的作品(workId)。

SELECT workId FROM userworks WHERE userId='15000';

(22) 查询 userId 值为 15000 的用户发布的作品的信息。

SELECT * FROM works WHERE workId IN(SELECT workId FROM userworks WHERE userId='15000');

(23) 在作品信息表中，统计时长分别在 0min-5min，5min-10min,10min-15min,15min-20min 区间的作品数量及比例。

SELECT COUNT(*),COUNT(*)/100000 FROM works WHERE totalDuration>'00:00:00' AND totalDuration<'00:05:00';

SELECT COUNT(*),COUNT(*)/100000 FROM works WHERE totalDuration>'00:05:00' AND totalDuration<='00:10:00';

SELECT COUNT(*),COUNT(*)/100000 FROM works WHERE totalDuration>'00:10:00' AND totalDuration<='00:15:00';

SELECT COUNT(*),COUNT(*)/100000 FROM works WHERE totalDuration>'00:15:00' AND totalDuration<='00:20:00';

(24) 用户名含有(‘服装’,‘穿搭’,‘汽车’,‘手机’,‘电影’,‘音乐’)关键字的关键字视为作品类型，统计时长在 10min-15min 的各作品类型的比例。

SELECT name,count(*)/100000 FROM userbaseinfo WHERE userId IN(
SELECT userId FROM userworks WHERE workId IN(
SELECT workId FROM works WHERE totalDuration>'00:10:00' AND totalDuration<='00:15:00')) GROUP BY name;

(25) 查询 userId 值为 12345 的用户的粉丝数量及粉丝类型(用户名视为类型名)。

SELECT name,COUNT(*) FROM userbaseinfo WHERE userId IN(SELECT userId2 FROM userfans WHERE userId1='12345') GROUP BY name;

字段设计

根据抖音服装带货类用户的作品，基于流量指标，转化率指标，搭建数据库。

数据库包含以下基本表：

表名	字段	含义
Login	(loginId,password)	登录用户信息表
UserBaseInfo	(userId,name,intro,age,sex,birthday,location,school)	用户基本信息表
LoginIdUserId	(loginId,userId)	登录信息与用户信息关联基本表
Works	(workId,description,category,love,commentNum,shareNum,viewtime,totalDuration)	作品信息表
UserActionInfo	(userId,workId,viewDate)	用户浏览行为信息表
ProcessEfficiency	(userId,loginDate,exitDate)	流程效率表
flowStatistics	(workId,viewNum,visitorNum,revisitorNum,onlineVisitorNum,dayFlow)	流量指标统计表
conversionStatistics	(workId,collectRate,attentionRate,buyRate)	转化率指标统计表
workStatistics	(workId,downloadRate,activateUserNum,ADtransfromRate)	作品数据指标统计表

-- 创建数据库
CREATE DATABASE newTiktok;
USE newTiktok;

-- 创建登录信息表
CREATE TABLE Login
(
loginId VARCHAR(20) PRIMARY KEY, 
password VARCHAR(20)
);

-- 创建用户基本信息表
CREATE TABLE UserBaseInfo
(
usertId VARCHAR(20) PRIMARY KEY,
name VARCHAR(20)  NOT NULL,
intro VARCHAR(500), 
age int, 
birthday DATE, 
location VARCHAR(20), 
school VARCHAR(20)
)

-- 创建登录信息与用户基本信息关联表
CREATE TABLE LoginIdUserId
(
loginId VARCHAR(20), 
usertId VARCHAR(20),
foreign key(loginId) references Login(loginId),
foreign key(usertId) references UserBaseInfo(usertId)
);

-- 创建作品信息表
CREATE TABLE Works
(
workId VARCHAR(20) PRIMARY KEY,
description VARCHAR(500),
category VARCHAR(5),
love int, 
commentNum int, 
shareNum int, 
viewtime TIMESTAMP, 
totalDuration time
)

-- 创建用户浏览行为信息表
CREATE TABLE useractioninfo
(
userId VARCHAR(20),
workid VARCHAR(20),
viewtotalDuration time,
viewDate datetime,
foreign key(userId) references UserBaseInfo(usertId),
foreign key(workId) references works(workId)
)

-- 创建流程效率表
CREATE TABLE ProcessEfficiency
(
userId VARCHAR(20),
loginDate datetime,
exitDate datetime,
foreign key(userId) references UserBaseInfo(usertId)
)

-- 创建流程指标统计表
CREATE TABLE flowStatistics
(
workId VARCHAR(20),
viewNum int,
visitorNum int,
revisitorNum int,
onlineVisitorNum int,
dayFlow int,
foreign key(workId) references works(workId)
)


-- 创建转化率指标统计表
CREATE TABLE conversionStatistics
(
workId VARCHAR(20),
collectRate FLOAT(4),
attentionRate FLOAT(4),
buyRate FLOAT(4),
foreign key(workId) references works(workId)
)

-- 创建作品指标统计表
CREATE TABLE workStatistics
(
workId VARCHAR(20),
downloadRate FLOAT(4),
activeUserNum int,
ADtransfromRate FLOAT(4),
foreign key(workId) references works(workId)
)

数据清洗

1.完成为数据添加字段‘user_id’,‘item_id’, ‘category_id’,‘behavior’,‘timestamps’,并清洗数据
2.转换时间数据类型，并添加datentime，dates，hours三个字段，将转换好的数据放进去
3.检查日期是否在规定范围内：2017年11月25日至2017年12月3日，检查时间是否正确，并将不符合规定的数据删除

-- 新增字段
ALTER TABLE UserBehavior ADD COLUMN datentime TIMESTAMP(0) NULL;
UPDATE UserBehavior
SET datentime = FROM_UNIXTIME(timestamps);

ALTER TABLE UserBehavior ADD COLUMN dates CHAR(10) NULL;
UPDATE UserBehavior
SET dates = SUBSTRING(datentime FROM 1 FOR 10);

ALTER TABLE UserBehavior ADD COLUMN hours CHAR(10) NULL;
UPDATE UserBehavior
SET hours = SUBSTRING(datentime FROM 12 FOR 2);
-- 检查范围
SELECT MAX(timestamps),
       MIN(timestamps),
       MAX(datentime),
       MIN(datentime)
FROM UserBehavior;
-- 删数据
DELETE FROM UserBehavior
WHERE datentime < '2017-11-25 00:00:00'
OR datentime > '2017-12-04 00:00:00';

方法二：

-- 扩大缓存，否则可能用不了
SET GLOBAL  tmp_table_size =1024*1024*1024;
SET GLOBAL innodb_buffer_pool_size=3*1024*1024*1024;
-- 添加字段
ALTER TABLE UserBehavior ADD COLUMN datentime datetime;
ALTER TABLE UserBehavior ADD COLUMN dates date;
ALTER TABLE UserBehavior ADD COLUMN hours int;
-- 更新数据
UPDATE UserBehavior SET datentime = FROM_UNIXTIME(timestamps);
UPDATE UserBehavior SET dates = FROM_UNIXTIME(timestamps,'%Y-%m-%d');
UPDATE UserBehavior SET hours = FROM_UNIXTIME(timestamps,'%H');
#剔除不符合数据
DELETE FROM userbehavior WHERE dates<'2017-11-25' or dates>'2017-12-03';

数据分析

SQL 数据分析

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

MySQL踩坑记录及解决方案上一篇

数据统计与分析竞赛简单复盘下一篇