SQL优化、Oracle复杂查询练习

SQL优化

1. 执行计划

1. 性能优化：通过查看执行计划，可以识别出哪些操作耗费了大量资源，从而有针对性地进行优化。
2. 索引使用情况：了解数据库是否使用了适当的索引来加速查询。
3. 表连接顺序：确定数据库选择的表连接顺序是否最优。
4. 扫描方式：判断数据库使用的是全表扫描还是索引扫描等不同类型的扫描方式

mysql中使用explain命令来查看查询的执行计划，输出结果会显示每一步操作的详细信息，如表扫描方式、使用的索引等

Oracle使用explain plan for 语句生成执行计划

• type列显示了访问类型，ALL表示全表扫描，ref表示使用索引。
• key列显示了使用的索引名称。
• rows列显示了预计需要扫描的行数。
• Extra列提供了额外的信息，如Using where表示应用了过滤条件

2. sql语句优化

优先排查sql代码,尽量使用执行效率高的 sql代码 ,比如:

• 不使用select * 查询表
• 写列名的时候,所有表列都要带别名;
• 不使用 or 进行筛选条件连接 ;
• 多筛选条件时,合理排列筛选条件执行顺序,将能够缩小结果集的筛选条件,放在最后优先执行;
• 尽量在group by 之前 筛选数据, 尽量不使用having
• in 子句尽量使用exists替换,能用join 就不用exists;
• 能用union all 就不写union 提高执行效率

如果还是慢 就需要通过查看执行计划 ,对慢sql的原因进行排查

• 一般通过执行计划中的操作步骤 , io ,资源占用 来判断 慢sql原因
• 使数据扫描尽量走索引扫描 代码中防止索引失效的语句
• 对于几种join 尽量使用 内连接 以提高表连接效率

如果单纯是数据量 导致的慢sql

• 拆分sql 通过临时表 中间表等方式缓存sql结果
• 创建索引
• 进行分区表操作
• 进而进行 分库分表操作

3. 索引失效排查

（什么情况会索引失效）

• 索引列进行计算
• 索引列使用函数处理
• like语句 通配符在左 左模糊查询
• 复合索引不满足最左原则
• where筛选null值
• where筛选 <> != not 等
• 索引列进行计算
• 索引列使用函数处理
• like语句 通配符在左 左模糊查询
• 复合索引不满足最左原则
• where筛选null值
• where筛选 <> != not 等

Oracle复杂查询练习

1. 排序开窗

-- 1.排序开窗
-- 创建客户交易表
CREATE TABLE customer_transactions (
    customer_id VARCHAR2(10),
    tdate VARCHAR2(10),
    tamount NUMBER(10,2)
);

-- 插入示例数据
INSERT INTO customer_transactions (customer_id, tdate, tamount) VALUES ('A', '20210901', 100);
INSERT INTO customer_transactions (customer_id, tdate, tamount) VALUES ('A', '20210912', 200);
INSERT INTO customer_transactions (customer_id, tdate, tamount) VALUES ('B', '20210902', 100);
INSERT INTO customer_transactions (customer_id, tdate, tamount) VALUES ('B', '20210901', 101);
INSERT INTO customer_transactions (customer_id, tdate, tamount) VALUES ('B', '20210912', 102);
INSERT INTO customer_transactions (customer_id, tdate, tamount) VALUES ('C', '20210901', 200);
INSERT INTO customer_transactions (customer_id, tdate, tamount) VALUES ('C', '20210912', 300);

-- 提交事务
COMMIT;    

-- 实现题目
select dense_rank()over(order by customer_id) 序号1, 
customer_id 客户号, 
dense_rank()over(order by tdate) 序号2, 
tdate 交易时间, 
tamount 交易金额
from customer_transactions order by customer_id,tamount

实现效果

2. join

-- 创建表1
CREATE TABLE customer1 (
    customer_id VARCHAR2(10) PRIMARY KEY,
    customer_name VARCHAR2(50) NOT NULL,
    age NUMBER(3) NOT NULL
);

-- 插入表1数据
INSERT INTO customer1 (customer_id, customer_name, age) VALUES ('A', '张三', 20);
INSERT INTO customer1 (customer_id, customer_name, age) VALUES ('B', '李四', 21);
INSERT INTO customer1 (customer_id, customer_name, age) VALUES ('C', '王五', 22);

-- 创建户表2
CREATE TABLE customer2 (
    customer_id VARCHAR2(10) PRIMARY KEY,
    customer_name VARCHAR2(50) NOT NULL,
    age NUMBER(3) NOT NULL
);

-- 插入表2数据
INSERT INTO customer2 (customer_id, customer_name, age) VALUES ('A', '张三', 20);
INSERT INTO customer2 (customer_id, customer_name, age) VALUES ('Z', '小李', 23);
INSERT INTO customer2 (customer_id, customer_name, age) VALUES ('Q', '小王', 24);

-- 提交事务
COMMIT; 

--实现题目1
select customer_id 客户号, customer_name 客户名, age 年龄 from customer1
union
select customer_id 客户号, customer_name 客户名, age 年龄 from customer2
order by 年龄;  

-- 实现题目2
select 
    c1.customer_id 客户号,
    c1.customer_name 客户名,
    c1.age 年龄,
    case when c2.customer_id is not null then '是' else '否' end 是否在表2
from customer1 c1
left join customer2 c2 on c1.customer_id = c2.customer_id

-- 实现题目3
select 
    c1.customer_id 客户号1,
    c1.customer_name 客户名1,
    c1.age 年龄1,
    c2.customer_id 客户号2,
    c2.customer_name 客户名2,
    c2.age 年龄2
from customer2 c2
full join customer1 c1 on c1.customer_id = c2.customer_id

3. case when

-- 创建表1
CREATE TABLE cd1 (
    CD NUMBER(10) PRIMARY KEY,
    CD_DESC VARCHAR2(50) NOT NULL
);

-- 插入表1数据
INSERT INTO cd1 (CD, CD_DESC) VALUES (1, '普通');
INSERT INTO cd1 (CD, CD_DESC) VALUES (2, '潜力');
INSERT INTO cd1 (CD, CD_DESC) VALUES (3, '优质');
INSERT INTO cd1 (CD, CD_DESC) VALUES (4, '财富');
INSERT INTO cd1 (CD, CD_DESC) VALUES (5, '私行');

-- 创建表2
CREATE TABLE cd2 (
    CID VARCHAR2(10) PRIMARY KEY,
    CD NUMBER(10) NOT NULL,
    FOREIGN KEY (CD) REFERENCES cd1(CD)
);

-- 插入表2数据
INSERT INTO cd2 (CID, CD) VALUES ('A', 1);
INSERT INTO cd2 (CID, CD) VALUES ('B', 2);
INSERT INTO cd2 (CID, CD) VALUES ('C', 3);
INSERT INTO cd2 (CID, CD) VALUES ('D', 4);
INSERT INTO cd2 (CID, CD) VALUES ('E', 5);
INSERT INTO cd2 (CID, CD) VALUES ('F', 4);

-- 提交事务
COMMIT; 

-- 实现题目
-- 方法1：内连接
select cid 客户号, cd_desc from cd1 join cd2 on cd1.cd=cd2.cd

-- 方法2：case when 
select cid 客户号, case cd 
when 1 then '普通'
when 2 then '潜力'
when 3 then '优质'
when 4 then '财富'
when 5 then '私行'
else null end CD_DESC from cd2

4. listagg

-- 建表
CREATE TABLE customer_purchase (
    cid VARCHAR2(10),
    product VARCHAR2(50),
    pdate DATE
);

-- 插入数据
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('A', '基金', TO_DATE('20210901', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('A', '理财', TO_DATE('20210902', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('A', '保险', TO_DATE('20210903', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('A', '理财2', TO_DATE('20210904', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('A', '基金2', TO_DATE('20210905', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('A', '基金2', TO_DATE('20210905', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('A', '理财', TO_DATE('20210906', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('A', '理财', TO_DATE('20210907', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('A', '理财3', TO_DATE('20210908', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('A', '国债', TO_DATE('20210909', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('B', '理财A', TO_DATE('20210904', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('B', '理财B', TO_DATE('20210905', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('B', '理财C', TO_DATE('20210905', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('B', '理财A', TO_DATE('20210906', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('B', '基金2', TO_DATE('20210907', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('B', '基金2', TO_DATE('20210908', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('B', '保险', TO_DATE('20210909', 'YYYYMMDD'));
INSERT INTO customer_purchase (cid, product, pdate) VALUES ('B', '保险', TO_DATE('20210910', 'YYYYMMDD'));

-- 提交事务
COMMIT;    
select * from customer_purchase

-- 实现题目   
select 
    cid as 客户,
    listagg(product, '，') within group (order by min_pdate) as 购买产品
from (
    select cid,product,min(pdate) as min_pdate  -- 将每个产品的日期统一为最早购买日期（方便去重并用于排序）
    from customer_purchase group by cid, product   -- 按客户和产品去重
) group by cid;

5. 偏移开窗

-- 建表
CREATE TABLE ct5 (
    cid VARCHAR2(10),
    tdate DATE
);

-- 插入数据
INSERT INTO ct5 (cid, tdate) VALUES ('A', TO_DATE('20210101', 'YYYYMMDD'));
INSERT INTO ct5 (cid, tdate) VALUES ('A', TO_DATE('20210201', 'YYYYMMDD'));
INSERT INTO ct5 (cid, tdate) VALUES ('A', TO_DATE('20210301', 'YYYYMMDD'));
INSERT INTO ct5 (cid, tdate) VALUES ('B', TO_DATE('20210101', 'YYYYMMDD'));
INSERT INTO ct5 (cid, tdate) VALUES ('B', TO_DATE('20210301', 'YYYYMMDD'));
INSERT INTO ct5 (cid, tdate) VALUES ('C', TO_DATE('20210101', 'YYYYMMDD'));
INSERT INTO ct5 (cid, tdate) VALUES ('C', TO_DATE('20210301', 'YYYYMMDD'));
INSERT INTO ct5 (cid, tdate) VALUES ('C', TO_DATE('20210315', 'YYYYMMDD'));
INSERT INTO ct5 (cid, tdate) VALUES ('D', TO_DATE('20210101', 'YYYYMMDD'));
INSERT INTO ct5 (cid, tdate) VALUES ('D', TO_DATE('20210221', 'YYYYMMDD'));
INSERT INTO ct5 (cid, tdate) VALUES ('D', TO_DATE('20210301', 'YYYYMMDD'));
INSERT INTO ct5 (cid, tdate) VALUES ('D', TO_DATE('20210315', 'YYYYMMDD'));

-- 提交事务
COMMIT;    

-- 实现题目
with m as (
    -- 按客户和月份统计交易，去重
    select cid 客户, trunc(tdate, 'mm') 交易月份
    from ct5 group by cid, trunc(tdate, 'mm')
),
m3 as (
    -- 使用窗口函数计算每个月之前两个月的月份
    select 客户,交易月份,
        lag(交易月份,2) over (partition by 客户 order by 交易月份) m1
    from m
)
-- 查询结果，判断是否连续三个月有交易
select 
    TO_CHAR(交易月份,'yyyymm') 日期, 客户, case 
    when add_months(交易月份,-2) = m1 then '是' 
    else '否' end 是否连续三个月交易
from m3
where 交易月份 = to_date('202103', 'yyyymm')
order by 客户;  

6. 同环比

--6. 同环比
CREATE TABLE table6 (
    sdate DATE,
    sal NUMBER(20,10)
);

-- 插入数据
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202001', 'YYYYMM'), 749.7997074);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202002', 'YYYYMM'), 989.1732764);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202003', 'YYYYMM'), 275.7157704);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202004', 'YYYYMM'), 472.2227195);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202006', 'YYYYMM'), 222.9311331);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202007', 'YYYYMM'), 37.03906155);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202008', 'YYYYMM'), 729.9000341);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202009', 'YYYYMM'), 120.9572041);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202010', 'YYYYMM'), 441.8720002);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202012', 'YYYYMM'), 454.0350884);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202101', 'YYYYMM'), 292.4960738);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202102', 'YYYYMM'), 244.9181963);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202103', 'YYYYMM'), 772.5048729);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202105', 'YYYYMM'), 778.8382229);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202106', 'YYYYMM'), 53.41619373);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202107', 'YYYYMM'), 0);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202108', 'YYYYMM'), 567.6163141);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202110', 'YYYYMM'), 708.7802233);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202111', 'YYYYMM'), 250.4268242);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202112', 'YYYYMM'), 893.4930988);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202201', 'YYYYMM'), 268.8781552);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202203', 'YYYYMM'), 570.5022024);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202204', 'YYYYMM'), 126.6045656);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202205', 'YYYYMM'), 905.5077431);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202206', 'YYYYMM'), 365.6966979);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202207', 'YYYYMM'), 804.5220551);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202208', 'YYYYMM'), 880.6164334);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202210', 'YYYYMM'), 656.1069145);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202211', 'YYYYMM'), 366.0299052);
INSERT INTO table6 (sdate, sal) VALUES (TO_DATE('202212', 'YYYYMM'), 994.1196283);

-- 提交事务
COMMIT;    
select * from table6

-- 计算同环比
select 
    to_char(t1.sdate, 'yyyymm') 月份,
    t1.sal,
    -- 计算环比
    case 
        when t2.sal is not null and t2.sal <> 0 
        then concat(round((t1.sal - t2.sal) / t2.sal * 100, 2),'%') 
        else null 
    end as 环比,
    -- 计算同比
    case 
        when t3.sal is not null and t3.sal <> 0 
        then concat(round((t1.sal - t3.sal) / t3.sal * 100, 2),'%') 
        else null 
    end as 同比
from table6 t1
-- 获取上月数据
left join table6 t2 
    on t2.sdate = add_months(t1.sdate, -1)
-- 获取去年同月数据
left join table6 t3 
    on t3.sdate = add_months(t1.sdate, -12)
order by t1.sdate;

7. 健康码

-- 原始表
CREATE TABLE green (
    sno NUMBER(10),
    code VARCHAR2(10)
);

-- 黄码表
CREATE TABLE yellow (
    sno NUMBER(10),
    code VARCHAR2(10)
);

-- 红码表
CREATE TABLE red (
    sno NUMBER(10),
    code VARCHAR2(10)
);

-- 白名单
CREATE TABLE white (
    sno NUMBER(10),
    code VARCHAR2(10)
);

-- 向绿码表（green）插入数据
INSERT INTO green (sno, code) VALUES (1, '绿码');
INSERT INTO green (sno, code) VALUES (2, '绿码');
INSERT INTO green (sno, code) VALUES (3, '绿码');
INSERT INTO green (sno, code) VALUES (4, '绿码');
INSERT INTO green (sno, code) VALUES (5, '绿码');
INSERT INTO green (sno, code) VALUES (6, '绿码');
INSERT INTO green (sno, code) VALUES (7, '绿码');
INSERT INTO green (sno, code) VALUES (8, '绿码');
INSERT INTO green (sno, code) VALUES (9, '绿码');
INSERT INTO green (sno, code) VALUES (10, '绿码');
INSERT INTO green (sno, code) VALUES (11, '绿码');
INSERT INTO green (sno, code) VALUES (12, '绿码');
INSERT INTO green (sno, code) VALUES (13, '绿码');
INSERT INTO green (sno, code) VALUES (14, '绿码');
INSERT INTO green (sno, code) VALUES (15, '绿码');

-- 向黄码表（yellow）插入数据
INSERT INTO yellow (sno, code) VALUES (1, '黄码');
INSERT INTO yellow (sno, code) VALUES (4, '黄码');
INSERT INTO yellow (sno, code) VALUES (5, '黄码');
INSERT INTO yellow (sno, code) VALUES (6, '黄码');
INSERT INTO yellow (sno, code) VALUES (8, '黄码');
INSERT INTO yellow (sno, code) VALUES (9, '黄码');
INSERT INTO yellow (sno, code) VALUES (15, '黄码');

-- 向红码表（red）插入数据
INSERT INTO red (sno, code) VALUES (3, '红码');
INSERT INTO red (sno, code) VALUES (4, '红码');
INSERT INTO red (sno, code) VALUES (6, '红码');
INSERT INTO red (sno, code) VALUES (7, '红码');
INSERT INTO red (sno, code) VALUES (14, '红码');

-- 向白名单（white）插入数据
INSERT INTO white (sno, code) VALUES (1, '绿码');
INSERT INTO white (sno, code) VALUES (2, '绿码');
INSERT INTO white (sno, code) VALUES (3, '绿码');

-- 提交事务
COMMIT;

-- 最终结果
select sno,
	case 
        when sno in (select sno from white) then '绿码'  -- 白名单优先
        when sno in (select sno from red) then '红码'    -- 其次红码
        when sno in (select sno from yellow) then '黄码' -- 然后黄码
        else '绿码'                                   -- 最后绿码
    end 最终结果
from green

-- 方法2
select a.sno 学号, case a.sno 
when d.sno then '绿码'
when c.sno then '红码'
when b.sno then '黄码'
else '绿码' end 最终结果
from green a left join yellow b on a.sno=b.sno
left join red c on a.sno=c.sno
left join white d on a.sno=d.sno
order by 学号 asc

8. 聚合开窗

--1. 学号	姓名	年龄	性别	课程	成绩	课程数	总成绩	平均分	是否学的课程数>5门	所有学员的总成绩	所有学员的平均分	平均分与所有学员的平均分比较值	同一课程所有学员的平均分	成绩与“同一课程所有学员的平均分”的比较值
select sc.sno 学号,s.sname 姓名,s.sage 年龄,s.ssex 性别,c.cno 课程号,c.cname 课程名称,sc.score 成绩,
count(sc.cno)over(partition by sc.sno) 课程数,
sum(sc.score)over(partition by sc. sno) 总成绩,
avg(sc.score)over(partition by sc.sno) 平均分,
case when count(sc.cno)over(partition by sc.sno)>=5 then '是' else '否' end  课程数是否大于5门,
sum(sc.score)over() 所有学员的总成绩,
round(avg(sc.score)over(),2)  所有学员的平均分,
round(avg(sc.score)over(partition by sc.sno)/avg(sc.score)over(),2) 平均分比较值,
round(avg(sc.score)over(partition by sc.cno),2) 该课程平均分,
round(sc.score/avg(sc.score)over(partition by sc.cno),2)  成绩与平均分比较值
from sc join student s on s.sno=sc.sno
join course c on c.cno=sc.cno

--2. 学号	姓名	年龄	性别	科目1	科目2 ...科目10	总成绩	平均分	已学科目	未学科目	超过平均分科目数	不及格科目数 	及格科目数
with psc as (
    -- 行转列：将科目成绩转为列
    select * from (
        select sno, cno, score from sc
    )
    pivot (
        max(score)  -- 取成绩（无成绩则为null）
        for cno in (
            'c001' as 科目1, 'c002' as 科目2, 'c003' as 科目3, 'c004' as 科目4, 'c005' as 科目5,
            'c006' as 科目6, 'c007' as 科目7, 'c008' as 科目8, 'c009' as 科目9, 'c010' as 科目10
        )
    )
),
tmp as (
    -- 计算每个学生的统计指标
    select 
        s.sno,
        count(sc.cno) as 已学科目数,
        (select count(*) from course) - count(sc.cno) as 未学科目数,
        sum(sc.score) as 总成绩,
        avg(sc.score) as 平均分,
        -- 超过平均分的科目数（子查询关联当前学生平均分）
        sum(case when sc.score > (select avg(score) from sc where sno = s.sno) then 1 else 0 end) as 超过平均分科目数,
        sum(case when sc.score < 60 then 1 else 0 end) as 不及格科目数,
        sum(case when sc.score >= 60 then 1 else 0 end) as 及格科目数
    from student s
    left join sc on s.sno = sc.sno
    group by s.sno
)
-- 关联所有信息
select 
    s.sno as 学号,
    s.sname as 姓名,
    s.sage as 年龄,
    s.ssex as 性别,
    p.科目1, p.科目2, p.科目3, p.科目4, p.科目5,
    p.科目6, p.科目7, p.科目8, p.科目9, p.科目10,
    t.总成绩,
    round(t.平均分, 2) as 平均分,
    t.已学科目数 as 已学科目,
    t.未学科目数 as 未学科目,
    t.超过平均分科目数,
    t.不及格科目数,
    t.及格科目数
from student s
left join psc p on s.sno = p.sno
left join tmp t on s.sno = t.sno
order by s.sno;