目录
1.使用环境
数据库:MySQL 8.0.30
客户端:Navicat 15.0.12
2.视图的概念和作用
什么是视图?数据库中的视图是一种虚拟的表,是根据对一个或多个实际表的查询结果来创建的。视图看起来就像真实的表,但实际上并不存储数据。用户可以像操作普通表一样对视图进行查询、更新和删除操作,而不必直接访问源表。
视图的作用包括简化复杂查询、提供安全性、隐藏表的结构和限制数据访问等。通过视图,可以将复杂的查询逻辑封装起来,使用户能够通过简单的接口访问数据,同时也能保护源表的数据完整性和安全性。
如果视图引用的原始数据发生了变化,视图引用的数据也会随之发生变化。
3.视图操作
创建视图:
create view 视图名称 [ (视图字段列表) ] as select语句;
create view t_view as select * from student;
create view t_view(sname,gender) as select sname,gender from student;
查询视图:
select * from 视图名称;
修改视图:
alter view 视图名称 as SQL语句;
删除视图:
drop view 视图名称;
4.视图应用
这里使用RFM模型来举栗子,可参考之前文章中的用户分层(RFM模型)小节:
按用户价值分层—RFM模型:
- 最近一次消费-R:客户最近一次交易时间的间隔,上一次消费时间离现在越近。即 R 值越小,用户的活跃度越大,用户的价值就越高。
- 消费频率-F:客户在最近一段时间内交易的次数。购买频率越高,说明用户对品牌产生一定的信任和情感维系。即 F 值越大,用户的忠诚度就越大,用户的价值就越高。
- 消费金额-M:客户在最近一段时间内交易的金额。M值越大,表示客户价值越高,反之则表示客户价值越低。
我们在test数据库中创建表RFM:
create table rfm(order_id int(10) PRIMARY KEY,
date_time date,
customer_name varchar(50),
count int(10),
pay_money int(10));
> OK
> 时间: 0.007s
填充数据:
insert into rfm values
(1001 , '2024-01-01' ,'张三', 8 , 109),
(1002 , '2024-01-01' ,'李四', 11 , 121),
(1003 , '2024-01-01' ,'王五', 35 , 409),
(1004 , '2024-01-01' ,'赵六', 26 , 301),
(1005 , '2024-01-02' ,'陈七', 45 , 531),
(1006 , '2024-01-02' ,'孙八', 66 , 779),
(1007 , '2024-01-02' ,'王五', 42 , 521),
(1008 , '2024-01-03' ,'赵六', 33 , 411),
(1009 , '2024-01-03' ,'陈七', 58 , 655),
(10010 , '2024-01-03' ,'孙八', 65 , 781),
(10011 , '2024-01-03' ,'张三', 77 , 935),
(10012 , '2024-01-04' ,'李四', 98 , 1115),
(10013 , '2024-01-04' ,'王五', 87 , 1065),
(10014 , '2024-01-04' ,'赵六', 25 , 267),
(10015 , '2024-01-04' ,'王五', 18 , 190);
> Affected rows: 15
> 时间: 0.002s
计算R:
计算时间间隔:
select date_time,current_date() as currentdate,datediff(current_date(),r.date_time) as 'interval' from rfm r;
创建视图v_r(计算R指标的sql):
create view v_r as (select a.customer_name as name1,min(a.interval) as R from
(select customer_name,datediff(current_date(),date_time) as 'interval'
from rfm) as a group by a.customer_name);
> OK
> 时间: 0.002s
将计算出R的表和原始rfm表的数据进行汇总:
select * from rfm as r inner join v_r on v_r.name1 = r.customer_name;
为汇总后的表创建视图v_r_new:
create view v_r_new as
(select * from rfm as r inner join v_r on v_r.name1 = r.customer_name);
> OK
> 时间: 0.002s
根据用户的名字分组,统计每组用户出现的次数,即每个用户消费的次数F:
select customer_name, count(1) as F from rfm group by customer_name;
创建视图v_f:
create view v_f as(select customer_name as name2, count(1) as F from rfm group by customer_name);
> OK
> 时间: 0.001s
将v_f和v_r_new进行汇总:
select * from v_r_new inner join v_f on v_r_new.name1 = v_f.name2;
将汇总后的表创建为视图v_f_new:
create view v_f_new as ( select * from v_r_new inner join v_f on v_r_new.name1 = v_f.name2);
> OK
> 时间: 0.002s
计算M:
计算每位客户的平均消费金额:
select customer_name,avg(pay_money) as M from rfm group by customer_name;
创建视图v_m:
create view v_m as (select customer_name as name3,avg(pay_money) as M from rfm group by customer_name);
> OK
> 时间: 0.002s
汇总数据:
select * from v_f_new inner join v_m on v_m.name3 = v_f_new.customer_name;