SQL分析——常见问题三

最新推荐文章于 2024-08-07 16:28:39 发布

吴声子夜歌

最新推荐文章于 2024-08-07 16:28:39 发布

阅读量525

点赞数 2

分类专栏： SQL 文章标签： sql

本文链接：https://blog.csdn.net/cold___play/article/details/124134039

版权

SQL 专栏收录该内容

33 篇文章 10 订阅

订阅专栏

一、直播间人气

create table kbjl
(
	author_id INTEGER COMMENT '主播id',
	live_id INTEGER COMMENT '直播间id',
	live_duration INTEGER COMMENT '开播时长'
) COMMENT '开播记录表';

insert into kbjl values 
(1, 1, 60),
(2, 2, 120),
(3, 3, 60);

create table gkjl
(
	user_id INTEGER COMMENT '观众id',
	live_id INTEGER COMMENT '直播间id',
	watching_duration INTEGER COMMENT '观看时长'
) COMMENT '观看记录表';

insert into gkjl values 
(11, 1, 60),
(12, 1, 30),
(13, 1, 60),
(12, 2, 30),
(14, 2, 90);

/*
要求：计算直播间的人气值，输出结果格式如下：
		主播id|直播间id|acu
acu为平均同时在线人数，计算方式为：观众观看时长 / 某场直播的开播时长 ，没有人观看的时候显示为0
*/

select a.author_id, a.live_id, ifnull(sum(b.watching_duration/ a.live_duration), 0) acu 
from kbjl a 
left join gkjl b
on a.live_id = b.live_id
group by a.author_id, a.live_id

在这里插入图片描述

二、累计销售金额

create table xsjl 
(
	user_id INTEGER COMMENT '用户id',
	sell_day date COMMENT '销售日期',
	amount numeric COMMENT '销售金额' 
) comment '销售记录表';

insert into xsjl values 
(1, '2021-01-01', 100),
(1, '2021-01-30', 100),
(1, '2021-02-01', 100),
(2, '2021-01-10', 200),
(2, '2021-01-11', 200),
(2, '2021-01-12', 200);

/*
要求：计算每个用户首次销售日期后30天内累计销售金额，输出结果格式如下：
		用户id|首次销售日期|30内累计销售金额
*/

方案一：

select a.user_id, a.first_date, sum(amount) amount_total
from (
	select *, first_value(sell_day) over(partition by user_id order by sell_day) first_date
	from xsjl
) a
where TIMESTAMPDIFF(DAY,a.first_date,a.sell_day) <= 30
group by a.user_id, a.first_date

方案二：

select s.user_id, s.first_date, sum(t2.amount)
from (
	select user_id, min(sell_day) first_date
	from xsjl t1
	group by user_id 
) s
join xsjl t2
on (
	s.user_id = t2.user_id 
	and 
	t2.sell_day between s.first_date and s.first_date + INTERVAL '30' DAY 
)
group by s.user_id, s.first_date

三、销量占比

create table mhjl
(
	seller_id INTEGER COMMENT '卖家id',
	buyer_id INTEGER COMMENT '买家id',
	item_id INTEGER COMMENT '商品id',
	order_cnt INTEGER COMMENT '销量',
	price NUMERIC COMMENT '单价',
	source_type TINYINT COMMENT '商品渠道 1：自建商品 0：其它'
) COMMENT '卖货记录表';

insert into mhjl values 
(1, 11, 1, 5, 9.9, 1),
(1, 12, 2, 15, 16.9, 0),
(2, 13, 3, 10, 89.0, 0),
(2, 14, 4, 1, 1999, 0);

/*
	要求：计算每个商家的自建商品销售占总销量的比例，结果格式如下：
		卖家id|自建商品销量占比
*/

select a.seller_id, ifnull(b.temp / a.total, 0) '占比'
from (
	select seller_id, sum(order_cnt) total
	from mhjl
	group by seller_id
) a
left join (
	select seller_id, sum(order_cnt) temp
	from mhjl
	where source_type = 1
	group by seller_id
) b
on a.seller_id = b.seller_id


或

select seller_id, sum(case source_type when 1 then order_cnt else 0 end) / sum(order_cnt) '占比'
from mhjl
group by seller_id

四、畅销商品分析

create table mhjlb
(
	seller_id int COMMENT '卖家id',
	buyer_id int COMMENT '买家id',
	item_id int COMMENT 'item_id',
	num int COMMENT '物品数量' 
) COMMENT '卖货记录表';

insert into mhjlb values 
(1, 11, 1, 100),
(1, 12, 1, 200),
(1, 12, 2, 300),
(2, 11, 3, 100),
(2, 12, 4, 200);

/*
	要求：计算每个卖家销量最高的商品，输出信息包含以下内容
		卖家id|物品id|物品总销量
*/

select seller_id, item_id, total
from (
	select seller_id, item_id, total, rank() over(partition by seller_id order by total desc) rk 
	from (
		select seller_id, item_id, sum(num) total
		from mhjlb
		group by seller_id, item_id
	) a
) b 
where b.rk = 1

在这里插入图片描述

五、行列转换

create table score 
(
	sname varchar(10) COMMENT '学生姓名',
	cname varchar(10) COMMENT '课程名称',
	grade int COMMENT '成绩'
)COMMENT '学生成绩表';

insert into score values 
('张三', '语文', 80),
('李四', '语文', 77),
('王五', '语文', 91),
('张三', '数学', 85),
('李四', '数学', 90),
('王五', '数学', 60),
('张三', '英语', 81),
('李四', '英语', 69),
('王五', '英语', 82);

-- 要求：以每个学生一行数据的形式创建以下报表：
		姓名|语文|数学|英语

select sname as '姓名', 
			 sum(case cname when '语文' then grade end) as '语文',
			 sum(case cname when '数学' then grade end) as '数学',
			 sum(case cname when '英语' then grade end) as '英语'
 from score 
 group by sname

在这里插入图片描述

六、数据透视表

create table sales_date
(
	saledate date comment '日期',
	product varchar(10) comment '商品',
	channel varchar(10) comment '渠道',
	amount decimal(10, 2) comment '金额'
) comment '销售数据表';

insert into sales_date values 
('2019-01-01', '橘子', '淘宝', 1864.00),
('2019-01-01', '橘子', '京东', 1329.00),
('2019-01-01', '橘子', '店面', 1736.00),
('2019-01-01', '香蕉', '淘宝', 1573.00),
('2019-01-01', '香蕉', '京东', 1364.00),
('2019-01-01', '香蕉', '店面', 1178.00),
('2019-01-01', '苹果', '淘宝', 511.00),
('2019-01-01', '苹果', '京东', 568.00),
('2019-01-01', '苹果', '店面', 847.00),
('2019-02-01', '橘子', '淘宝', 1864.00),
('2019-02-01', '橘子', '京东', 1329.00),
('2019-02-01', '橘子', '店面', 1736.00),
('2019-02-01', '香蕉', '淘宝', 1573.00),
('2019-02-01', '香蕉', '京东', 1364.00),
('2019-02-01', '香蕉', '店面', 1178.00),
('2019-02-01', '苹果', '淘宝', 511.00),
('2019-02-01', '苹果', '京东', 568.00),
('2019-02-01', '苹果', '店面', 847.00),
('2019-03-01', '橘子', '淘宝', 1864.00),
('2019-03-01', '橘子', '京东', 1329.00),
('2019-03-01', '橘子', '店面', 1736.00),
('2019-03-01', '香蕉', '淘宝', 1573.00),
('2019-03-01', '香蕉', '京东', 1364.00),
('2019-03-01', '香蕉', '店面', 1178.00),
('2019-03-01', '苹果', '淘宝', 511.00),
('2019-03-01', '苹果', '京东', 568.00),
('2019-03-01', '苹果', '店面', 847.00),
('2019-04-01', '橘子', '淘宝', 1864.00),
('2019-04-01', '橘子', '京东', 1329.00),
('2019-04-01', '橘子', '店面', 1736.00),
('2019-04-01', '香蕉', '淘宝', 1573.00),
('2019-04-01', '香蕉', '京东', 1364.00),
('2019-04-01', '香蕉', '店面', 1178.00),
('2019-04-01', '苹果', '淘宝', 511.00),
('2019-04-01', '苹果', '京东', 568.00),
('2019-04-01', '苹果', '店面', 847.00),
('2019-05-01', '橘子', '淘宝', 1864.00),
('2019-05-01', '橘子', '京东', 1329.00),
('2019-05-01', '橘子', '店面', 1736.00),
('2019-05-01', '香蕉', '淘宝', 1573.00),
('2019-05-01', '香蕉', '京东', 1364.00),
('2019-05-01', '香蕉', '店面', 1178.00),
('2019-05-01', '苹果', '淘宝', 511.00),
('2019-05-01', '苹果', '京东', 568.00),
('2019-05-01', '苹果', '店面', 847.00),
('2019-06-01', '橘子', '淘宝', 1864.00),
('2019-06-01', '橘子', '京东', 1329.00),
('2019-06-01', '橘子', '店面', 1736.00),
('2019-06-01', '香蕉', '淘宝', 1573.00),
('2019-06-01', '香蕉', '京东', 1364.00),
('2019-06-01', '香蕉', '店面', 1178.00),
('2019-06-01', '苹果', '淘宝', 511.00),
('2019-06-01', '苹果', '京东', 568.00),
('2019-06-01', '苹果', '店面', 847.00);

要求：通过SQL查询实现一下数据透视表
在这里插入图片描述

select coalesce(product, '总计') as '产品',
			 coalesce(channel, '--') as '渠道',
			 sum(case EXTRACT(MONTH from saledate) when 1 then amount end) as '1月',
			 sum(case EXTRACT(MONTH from saledate) when 2 then amount end) as '2月',
			 sum(case EXTRACT(MONTH from saledate) when 3 then amount end) as '3月',
			 sum(case EXTRACT(MONTH from saledate) when 4 then amount end) as '4月',
			 sum(case EXTRACT(MONTH from saledate) when 5 then amount end) as '5月',
			 sum(case EXTRACT(MONTH from saledate) when 6 then amount end) as '6月',
			 sum(amount) as '合计'
from sales_date sd 
group by product, channel with ROLLUP

七、查找优秀员工

create table excellent_emp
(
	year int comment '年份',
	emp_id int comment '员工id',
	emp_name varchar(10) comment '员工姓名'
) comment '优秀员工表';

insert into excellent_emp values 
(2019, 9, '赵云'),
(2019, 11, '关平'),
(2020, 9, '赵云'),
(2020, 8, '马超'),
(2021, 8, '马超'),
(2021, 7, '司马光');

-- 统计2019和2020年连续两年获得优秀称号的员工

select emp_id, emp_name 
from excellent_emp
where year = 2020 and emp_id in (
	select emp_id
	from excellent_emp
	where year = 2019
)

八、应用系统用户权限设计

/*
用户权限管理系统通常采用基于角色的访问控制方式（RBAC）。角色拥有权限，用户拥有角色。另外用户也可以直接拥有权限。
*/

-- 创建权限表t_permission
CREATE TABLE t_permission (
	permission_id INTEGER NOT NULL PRIMARY KEY,
	permission_name VARCHAR(100) NULL UNIQUE,
	parent_permission INTEGER NULL,
	sort_order INTEGER NULL DEFAULT 100
);
INSERT INTO t_permission VALUES (1, '系统管理', NULL, 1);
INSERT INTO t_permission VALUES (2, '员工管理', NULL, 11);
INSERT INTO t_permission VALUES (3, '查看员工信息', 2, 12);
INSERT INTO t_permission VALUES (4, '修改员工信息', 2, 13);

-- 创建用户表t_user
CREATE TABLE t_user (
	user_id INTEGER NOT NULL PRIMARY KEY,
	user_name VARCHAR(50) NOT NULL UNIQUE,
	password VARCHAR(50) NOT NULL,
	email VARCHAR(200) NOT NULL,
	last_login timestamp
);
INSERT INTO t_user VALUES (1, 'Admin', 'e10adc3949ba59abbe56e057f20f883e', 'admin@shuguo.com', NULL);
INSERT INTO t_user VALUES (2, 'Tony', 'eee7ac208064d408e84ab5e26d24b278', 'tony@shuguo.com', NULL);

-- 创建用户权限表t_user_permission
CREATE TABLE t_user_permission (
	user_id INTEGER NOT NULL,
	permission_id INTEGER NOT NULL,
	CONSTRAINT pk_user_permission PRIMARY KEY (user_id, permission_id),
	CONSTRAINT fk_user_permission_user FOREIGN KEY (user_id) REFERENCES t_user(user_id),
	CONSTRAINT fk_user_permission_permission FOREIGN KEY (permission_id) REFERENCES t_permission(permission_id)
);
INSERT INTO t_user_permission VALUES (2, 2);
INSERT INTO t_user_permission VALUES (2, 3);

-- 创建角色表t_role
CREATE TABLE t_role (
	role_id INTEGER NOT NULL PRIMARY KEY,
	role_name  VARCHAR(50) NOT NULL UNIQUE
);
INSERT INTO t_role VALUES (1, '系统管理员');
INSERT INTO t_role VALUES (2, '人力资源专员');

-- 创建角色权限表t_role_permission
CREATE TABLE t_role_permission (
	role_id INTEGER NOT NULL,
	permission_id INTEGER NOT NULL,
	CONSTRAINT pk_role_permission PRIMARY KEY (role_id, permission_id),
	CONSTRAINT fk_role_permission_user FOREIGN KEY (role_id) REFERENCES t_role(role_id),
	CONSTRAINT fk_role_permission_permission FOREIGN KEY (permission_id) REFERENCES t_permission(permission_id)
);
INSERT INTO t_role_permission VALUES (1, 1);
INSERT INTO t_role_permission VALUES (2, 2);
INSERT INTO t_role_permission VALUES (2, 3);
INSERT INTO t_role_permission VALUES (2, 4);

-- 创建用户角色表t_user_role
CREATE TABLE t_user_role (
	user_id INTEGER NOT NULL,
	role_id INTEGER NOT NULL,
	CONSTRAINT pk_user_role PRIMARY KEY (user_id, role_id),
	CONSTRAINT fk_user_role_user FOREIGN KEY (user_id) REFERENCES t_user(user_id),
	CONSTRAINT fk_user_role_role FOREIGN KEY (role_id) REFERENCES t_role(role_id)
);
INSERT INTO t_user_role VALUES (1, 1);
INSERT INTO t_user_role VALUES (2, 2);


-- 要求：查询用户对应的权限

在这里插入图片描述

select tp.permission_id, tp.permission_name, tp.parent_permission
from t_user_permission tup 
join t_permission tp on tp.permission_id = tup.permission_id
where tup.user_id = 2
UNION
select tp.permission_id, tp.permission_name, tp.parent_permission
from t_role_permission trp 
join t_permission tp on tp.permission_id = trp.permission_id 
join t_user_role tur on tur.role_id = trp.role_id
where tur.user_id = 2

-- 获取完成的权限

select tp.permission_id, tp.permission_name, tp.parent_permission, COALESCE(p.has_permission, 'N') has_permission
from t_permission tp
left join (
	select tp.permission_id, tp.permission_name, tp.parent_permission,'Y' has_permission
	from t_user_permission tup 
	join t_permission tp on tp.permission_id = tup.permission_id
	where tup.user_id = 2
	UNION
	select tp.permission_id, tp.permission_name, tp.parent_permission,'Y' has_permission
	from t_role_permission trp 
	join t_permission tp on tp.permission_id = trp.permission_id 
	join t_user_role tur on tur.role_id = trp.role_id
	where tur.user_id = 2
) p 
on p.permission_id = tp.permission_id