oracle学习第四天-DDL/DML

合集 - oracle数据库学习(4)

1.oracle-select第一天2024-07-302.oracle学习第二天--函数2024-07-313.oracle学习第三天--多表关联2024-08-05

4.oracle学习第四天-DDL/DML2024-08-05

收起

用得到的表

emp表

dept表

salgrade表

开窗函数准备数据

create table employees (
  id number primary key,
  name varchar(20),
  groupname varchar(20),
  salary number
);
insert into employees values
(1,'小明','开发部',7000);
insert into employees values
(4,'小张','行政部',7600);
insert into employees values
(5,'小白','开发部',7000);
insert into employees values
(8,'小王','财务部',8500);
insert into employees values
(9, '小赵','行政部',6900);
insert into employees values
(15,'小刘','财务部',6000);
insert into employees values
(16,'小高','行政部',4500);
insert into employees values
(18,'小王','行政部',6000);
insert into employees values
(23,'小李','开发部',7500);
insert into employees values
(29,'小吴','财务部',4700);

一、开窗函数

1、开窗函数概述

原理：开窗函数是一种用于执行窗口操作的函数，可以在查询结果集的特定窗口内进行计算。
作用：开窗函数通常与聚合函数一起使用，可以对每一行进行计算，并返回结果集中的每一行的一个值。
语法：

函数名 over([partition by 列明] [order by 排序列] [窗口子句])
-- partition by：进行分组
-- order by：进行排序
-- 窗口子句：进行范围圈定

注意：函数名表示要使用的开窗函数、聚合函数，列名表示要进行分区（分组）的列，排序列表示用于排序的列，窗口子句定义了窗口的范围。

2、常见的开窗函数类型

2.1 排名函数

原理：排名函数用于计算行的排名，常见的排名函数包括RANK()、DENSE_RANK()和ROW_NUMBER()。
 a、RANK():函数计算行的排名，如果有相同的值，则会跳过下一个排名，例：123 44 66 8 9
 b、DENSE_RANK():函数计算行的排名，如果有相同的值，则会跳过下一个排名，但不会跳过相同的排名，例：123 44 5 66 78
 c、ROW_NUMBER():函数为每一行分配一个唯一的行号。
案例：未分组

 select 
id, 
name, 
groupname, 
salary,
 rank() over(order by salary desc) as rank,
 dense_rank() over(order by salary desc) as dense_rank,
 row_number() over(order by salary desc) as row_num
 from employees;

分组后：

 SELECT id,
 name,
 groupname,
 salary,
 ROW_NUMBER() OVER(PARTITION BY groupname ORDER BY salary DESC)
rank_group,
 SUM(salary) OVER(PARTITION BY groupname) sum_salary_group,
 -- 分组后对
每组薪资求和
SUM(salary) OVER() sum_salary
-- 薪资总和
FROM employees

2.2累计函数

原理：累计函数用于计算累计值，常见的累计函数包括SUM()、AVG()、COUNT()等
函数：
 sum(列名):函数计算指定列的累计求总和。
 count(列名): 函数计算指定列的累计计数。
 avg(列名):函数计算指定列的累计平均值。
 max(列名):函数计算指定列累计的最大值。
 min(列名):函数计算指定列累计的最小值。
案例：

 SELECT id,
 name,
 groupname,
 salary,
 SUM(salary) OVER(ORDER BY salary DESC) AS cumulative_sum,
 AVG(salary) OVER(ORDER BY salary DESC) AS cumulative_avg,
 COUNT(id) OVER(ORDER BY salary DESC) AS cumulative_count
 FROM employees;

2.3分析函数

原理：分析函数用于在查询结果集中执行分析操作，常见的分析函数包括LEAD()、LAG()、FIRST_VALUE()、LAST_VALUE()。
函数：
 LEAD(列名,n,默认值):函数用于获取指定列的后n行的值。=> 速记：往后第n行数据
 LAG(列名,n,默认值):函数用于获取指定列的前n行的值。 => 往前第n行数据
 FIRST_VALUE(列名,n,默认值):函数用于获取指定列的第一行的值。
 LAST_VALUE(列名,n,默认值):函数用于获取指定列的最后一行的值。需要注意的是：last_value() 返回的排序
中指定列的最后一行的值
案例：

 select id,
 name,
 groupname,
 salary,
 lead(salary, 1) over(partition by groupname order by salary desc) as 
next_salary,
 lag(salary, 1) over(partition by groupname order by salary desc) as 
previous_salary,
 first_value(salary) over(partition by groupname order by salary desc) 
as first_salary,
 last_value(salary) over(partition by groupname order by salary desc) 
as last_salary
 from employees

2.4 滑动窗口

原理：移动平均是一种常见的数据分析技术，可以用于平滑时间序列数据。也就是给结果集添加一个范围。
案例：

select id,
 name,
 groupname,
 salary,
 avg(salary) over(order by id rows between 2 preceding and current row)as moving_average
 from employees;

参数：
 a、current row：当前行
 b、preceding：往前n行
 c、following：往后n行
 d、unbounded preceding:表示从前面的起点 => 第1行
 e、unbounded following:表示到后面的终点 => 最后1行
详细例子：

 select 
deptno,
 ename,
 hiredate,
 sal,
 sum(sal) over() as s1, --所有行相加
sum(sal) over(partition by deptno) as s2, --按deptno分组，组内数据相加
sum(sal) over(partition by deptno order by hiredate) as s3, --按deptno分组，组
内数据累加
sum(sal) --和s3效果相同，由起点到当前行聚合
over(partition by deptno order by hiredate rows between unbounded preceding 
and current row) as s4, 
sum(sal) --当前行和前面1行聚合
over(partition by deptno order by hiredate rows between 1 preceding and 
current row) as s5, 
sum(sal) --前1行，当前行及后1行聚合
over(partition by deptno order by hiredate rows between 1 preceding and 1 
following) as s6, 
sum(sal) --当前行到最后一行聚合
over(partition by deptno order by hiredate rows between current row and 
unbounded following) as s7 
from emp

二、oracle常用的数据类型

数据类型	描述	案例
char(max_length)	存储固定长度的字符串，
max_length指定长度，最大 2000
字节。
如果存储的字符串长度小于
max_length用空格填充。	char(11) 表示可以存储 11 字节的
内容。
varchar2(length)	存储可变长度的字符串，length指
定了该字符串的最大长度，默认长
度是1，最长不超过4000字节。	varchar2(3)：表示最长存储 3 个字
母，或者一个中文字符（前提是
UTF8 编码）；如果存储一个英文
字母实际占用空间 1 个字节。
number(p, s)	既可以存储小数，也可以存储整
数，p表示数字的最大位数(如果是
小数包括整数部分和小数部分,p默
认是38)，s是指定小数位数。	number(10,2) 表示最长整数位 8
位，2 位小数
date	存储日期和时间，存储4位年、
月、日、时、分、秒。只能精确到
秒	date 存生日，注册时间，最后登陆
的时间
timestamp	时间戳，精确到微秒
long	长文本，最长2GB

注意：各种数据类型的表达形式:
 a、字符型用单引号加字符表示，例如：'ABC'
 b、数字型直接用阿拉伯数字表示，例如：123 3.14
 c、日期型不能直接表示，必须使用函数转换
  例如：2022年08月04日14时00分01秒 select to_date('20220804140001','YYYYMMDD
HH24:MI:SS') as test_time from dual;