GreenPlum数开手册
一、数据类型
1、基本数据类型
| 类型 | 长度 | 描述 | 范围 |
|---|---|---|---|
| bigint | 8字节 | 大范围整数 | -9223372036854775808 到 +9223372036854775807 |
| smallint | 2字节 | 小范围整数 | -32768到+32767 |
| integer(int) | 4字节 | 常用整数 | -2147483648 到 +2147483647 |
| decimal | 可变长 | 用户指定的精度，精确 | 小数点前 131072 位；小数点后 16383 位 |
| real | 4字节 | 可变精度，不准确 | 6位十进制数字精度 |
| double precision | 8字节 | 可变精度，不准确 | 15位十进制数字精度 |
| smallserial | 2字节 | 自增的小范围整数 | 1到32767 |
| serial | 4字节 | 自增整数 | 1到2147483647 |
| bigserial | 8字节 | 自增的大范围整数 | 1到9223372036854775807 |
| character | 别名char | 定长，不足补空白 | |
| character varying | 别名archar | 变长，有长度限制 | |
| text | 变长，无长度限制 | ||
| timestamp | 8字节 | 日期和时间，无时区 | 4713 BC到294276 AD |
| timestamp with time zone | 8字节 | 日期和时间，有时区 | 4713 BC到294276 AD |
| date | 4字节 | 只用于日期 | 4713 BC到5874897 AD |
| boolean | 1字节 | true/false | |
| money | 8字节 | 货币金额 | -92233720368547758.08 到 +92233720368547758.07 |
2、复杂数据类型
2.1、枚举类型
枚举类型是一个包含静态和值的有序集合的数据类型，类似于Java中的enum类型，需要使用create type命令创建。
-- 创建枚举类型
create type weeks as enum('Mon','Tue','Wed','Thu','Fri','Sat','Sun');
-- 建表字段使用枚举类型
create table user_schedule (
user_name varchar(100),
available_day weeks
);
-- 插入数据
insert into user_schedule (user_name, available_day) values ('Alice', 'Mon');
insert into user_schedule (user_name, available_day) values ('Bob', 'Fri');
insert into user_schedule (user_name, available_day) values ('Charlie', 'Sun');
-- 查询结果
select * from user_schedule;
2.2、几何类型
几何数据类型表示二维的平面物体。下表列出了GreenPlum支持的几何类型。
最基本的类型：点。它是其它类型的基础。
| 类型 | 大小 | 描述 | 表现形式 |
|---|---|---|---|
| point | 16字节 | 平面中的点 | (x,y) |
| line | 32字节 | 直线 | ((x1,y1),(x2,y2)) |
| lseg | 32字节 | 线段 | ((x1,y1),(x2,y2)) |
| box | 32字节 | 矩形 | ((x1,y1),(x2,y2)) |
| path | 16+16n字节 | 路径（与多边形相似） | ((x1,y1),…) |
| polygon | 40+16n字节 | 多边形 | ((x1,y1),…) |
| circle | 24字节 | 圆 | <(x,y),r> (圆心和半径) |
-- 建表 创建一个表 geometric_shapes，它包含点、线和多边形类型的列。
create table geometric_shapes (
id serial primary key,
point_col point,
lseg_col lseg,
polygon_col polygon
);
-- 插入数据
insert into geometric_shapes (point_col, lseg_col, polygon_col)
values
(point(1, 2), lseg '[(0,0),(1,1)]', polygon '((0,0),(1,0),(1,1),(0,1))');
-- 查询数据
--- 查询所有
select * from geometric_shapes;
--- 根据点查询
select * from geometric_shapes where point_col <-> point(1, 2) < 0.0001;
--- 根据线段查询
select * from geometric_shapes where lseg_col = lseg '[(0,0),(1,1)]';
--- 根据多边形查询
select * from geometric_shapes where polygon_col ~= polygon '((0,0),(1,0),(1,1),(0,1))';
2.3、网络地址类型
GreenPlum提供用于存储IPv4、IPv6、MAC地址的数据类型。用这些数据类型存储网络地址比用纯文本好，因为提供输入错误检查和特殊的操作和功能。
| 类型 | 描述 | 说明 |
|---|---|---|
| cidr | 7或19字节 | IPv4 或 IPv6 网络 |
| inet | 7或19字节 | IPv4 或 IPv6 主机和网络 |
| macaddr | 6字节 | MAC 地址 |
在对 inet 或 cidr 数据类型进行排序的时候， IPv4 地址总是排在 IPv6 地址前面。
-- 创建包含网络地址数据类型的表
create table network_addresses (
id serial primary key,
ip_address inet,
network cidr,
mac_address macaddr
);
-- 插入数据
insert into network_addresses (ip_address, network, mac_address)
values
('192.168.1.1/24', '192.168.1.0/24', '08:00:2b:01:02:03');
-- 查询数据
select * from network_addresses;
-- 查询特定的 IP 地址
select * from network_addresses where ip_address = inet '192.168.1.1';
select * from network_addresses where host(ip_address) = '192.168.1.1';
-- 查询特定的网络
select * from network_addresses where network = cidr '192.168.1.0/24';
-- 查询特定的 MAC 地址
select * from network_addresses where mac_address = macaddr '08:00:2b:01:02:03';
-- 更新数据
update network_addresses set ip_address = inet '192.168.1.2' where id = 1;
-- 删除数据
delete from network_addresses where id = 1;
2.4、JSON类型
json 数据类型可以用来存储 JSON（JavaScript Object Notation）数据， 这样的数据也可以存储为 text，但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值。
此外还有相关的函数来处理 json 数据：
-- 创建一个新表，名为 json_demo，包含一个 json 类型的列
create table json_demo (
id serial primary key,
data json
);
-- 向 json_demo 表插入 json 数据，注意 json 数据必须是单引号的字符串
-- 并且遵循 json 格式
insert into json_demo (data) values ('{"name": "张三", "age": 28, "city": "北京"}');
-- 查询 json_demo 表中的 json 数据
select * from json_demo;
-- 使用 ->> 运算符来提取 json 对象中的 name 字段
select data->>'name' as name from json_demo;
2.5、数组类型
GreenPlum允许将字段定义成变长的多为数组。数组可以是任何基本类型或用户定义类型，枚举类型或复合类型。
-- 创建一个新表，名为 array_demo，包含一个 int 类型的数组列
create table array_demo (
id serial primary key,
numbers int[] -- int 数组类型列
);
-- 向 array_demo 表插入数组数据
-- 数组使用花括号{}并且元素由逗号分隔
insert into array_demo (numbers) values ('{1,2,3,4,5}');
-- 查询 array_demo 表中的数组数据
select * from array_demo;
-- 使用数组下标来获取数组中的特定元素
-- 注意：Greenplum数组下标从1开始
select numbers[1] as first_element from array_demo;
-- 使用 unnest 函数来展开数组为一系列行
select unnest(numbers) as expanded_numbers from array_demo;
2.6、复合类型
复合类型表示一行或者一条记录的结构； 它实际上只是一个字段名和它们的数据类型的列表。GreenPlum允许像简单数据类型那样使用复合类型。比如，一个表的某个字段可以声明为一个复合类型。
定义复合类型，语法类似于create table，只是这里可以声明字段名字和类型。
-- 定义一个复合类型，名为 person_type，包含姓名、年龄和城市
create type person_type as (
name text,
age int,
city text
);
-- 创建一个新表，名为 composite_demo，包含一个复合类型的列
create table composite_demo (
id serial primary key,
person_info person_type -- 使用之前定义的复合类型作为列类型
);
-- 向 composite_demo 表插入复合类型数据
-- 复合类型数据使用括号，并且属性值由逗号分隔
insert into composite_demo (person_info) values (ROW('张三', 28, '北京'));
二、DDL
2.1、创建数据库
2.1.1、语法
CREATE DATABASE name [ [WITH] [OWNER [=] dbowner]
[TEMPLATE [=] template]
[ENCODING [=] encoding]
[TABLESPAC [=] tablespace]
[CONNECTIONE LIMIT [=] connlimit ] ]
CREATE DATABASE name：
CREATE DATABASE 是 SQL 命令，用于创建一个新的数据库。
name 是你要创建的数据库的名称。这个名称是必须的，并且在同一个数据库服务器上必须是唯一的。
[ [WITH] [OWNER [=] dbowner]：
这是一个可选项。
OWNER 指定了新数据库的所有者。如果未指定，新数据库的所有者默认是执行该命令的用户。
dbowner 是数据库所有者的用户名。
[TEMPLATE [=] template]：
这也是一个可选项。
TEMPLATE 指定了用于创建新数据库的模板。在 PostgreSQL 和 GreenPlum 中，通常有一个名为 template1 的默认模板。如果不指定，就会使用这个默认模板。
template 是模板数据库的名称。
[ENCODING [=] encoding]：
又一个可选项。
ENCODING 指定了新数据库的字符编码。这个设置决定了数据库可以存储哪些字符。
encoding 是字符编码的名称，例如 UTF8。
[TABLESPACE [=] tablespace]：
这是可选的。
TABLESPACE 指定了新数据库的存储位置。表空间是数据库中存储文件的物理位置。
tablespace 是表空间的名称。
[CONNECTION LIMIT [=] connlimit ]：
这也是可选的。
CONNECTION LIMIT 限制了可以同时连接到数据库的最大客户端数量。
connlimit 是允许的最大连接数。如果设置为 -1，则表示没有限制。
2.1.2、实例
create database my_db1
with owner gpadmin
encoding 'utf-8'
tablespace pg_default
connection limit 10;
2.1.3、创建schema
schema 本质上就是一种分组管理工具，它允许您将相关性质或类型的多个表和其他数据库对象（如视图、索引、存储过程等）组织在一起。也可以把 schema 看作是数据库内部的一个“文件夹”或“命名空间”，用于逻辑上组织和隔离数据，以实现更好的数据管理和安全控制。
一个 database 下可以有多个 schema。schema在 gp 中也叫做 namespace。
-- 连接创建完成的数据库
\c EmployeeDB
-- 创建schema
create schema sc_test;
2.2、查询数据库
-
显示数据库
-- 仅greenplum命令行客户端支持 \l --查看所有数据库 \dn --查看所有schema -- 可视化客户端中使用以下操作 -- 列出 PostgreSQL 实例中所有数据库的名称 select datname from pg_database; -- 查询schema SELECT schema_name FROM information_schema.schemata; -
切换当前数据库
-- 仅greenplum命令行客户端支持 \c db_gp
2.3、删除数据库
DROP DATABASE 会删除数据库的系统目录项并且删除包含数据的文件目录。
如果删除的数据库不存在，最好采用 if exists判断数据库是否存在。
drop database if exists db_gp;
2.4、创建表
2.4.1、建表语法
CREATE [EXTERNAL] TABLE table_name(
column1 datatype [NOT NULL] [DEFAULT] [CHECK] [UNIQUE],
column2 datatype,
column3 datatype,
.....
columnN datatype,
[PRIMARY KEY()]
)[ WITH ()]
[LOCATION()]
[FORMAT]
[COMMENT]
[PARTITION BY]
[DISTRIBUTE BY ()];
如果建表时会提示Table doesn’t have ‘DISTRIBUTED BY’ clause – Using column named ‘id’ as the Greenplum Database data distribution key for this table.这是提醒建表时指定分布键和分布策略，默认第一个字段（或逐渐）和hash策略。
2.4.2、字段解释说明
（1）create table创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；
（2）external关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（location）。
（3）comment：为表和列添加注释。
（4）distributed b****y 为表添加分布键，其必须为主键的子键。
（5）format 存储数据的文本类型。
（6）check 为表字段添加检查约束。
（7）unique 唯一约束，一个表中唯一和主键只能同时存在一个。
（8）primary key 主键设置,可以是一个或多个列。
（9）location：指定外部表数据存储位置。
（10）default：默认值。
（11）not null非空约束。
（12）with 可以添加数据追加方式，压缩格式，压缩级别，行列压缩等。
（13）partiton by 支持两种分区方式，范围分区（range）和列表分区（list）
2.4.3、内部表和外部表的介绍
内部表和外部表是两种不同类型的表，它们在数据存储和处理方式上有明显的区别。了解这些区别对于合理地设计和优化 GreenPlum 数据库是非常重要的。
-
内部表（Regular Tables）
**数据存储：**内部表的数据直接存储在 GreenPlum 数据库的数据文件中。这意味着数据被物理存储在数据库服务器上。
**事务管理：**内部表完全支持事务管理。这包括 ACID 属性（原子性、一致性、隔离性和持久性），确保数据完整性和可靠性。
**索引和约束：**你可以在内部表上创建索引和约束，这有助于提高查询性能和维护数据完整性。
**管理和维护：**内部表可以使用数据库的全部管理和维护功能，如备份和恢复。
**适用性：**适用于需要高性能查询和事务完整性的数据。
-
外部表（External Tables）
**数据存储：**外部表的数据存储在数据库外部，如在文件系统、Hadoop HDFS 或任何可通过 SQL/MED（SQL Management of External Data）访问的数据源。外部表仅存储数据的元数据和位置信息。
**事务管理：**外部表不支持事务管理。它们主要用于读取和加载操作，不保证 ACID 属性。
**索引和约束：**由于数据实际存储在外部，你不能在外部表上创建索引或强制执行数据库级别的约束。
**管理和维护：**外部表的管理相对简单，因为它们只是对外部数据源的引用。备份和恢复通常不适用于外部表本身，而是应用于数据源。
**适用性：**适用于 ETL（Extract, Transform, Load）操作，即从外部数据源提取数据，然后可能将其转换和加载到内部表中进行进一步处理。
案例实现：
一：内部表操作
-- 创建内部表
CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, name VARCHAR(100), department VARCHAR(100), hire_date DATE );
-- 插入数据
INSERT INTO employees (name, department, hire_date) VALUES ('John Doe', 'IT', '2020-01-01');
INSERT INTO employees (name, department, hire_date) VALUES ('Jane Smith', 'HR', '2020-02-01');
-- 查询结果
SELECT * FROM employees;
二：外部表操作
假设我们有一个 CSV 文件 employee_data.csv，它存储在文件系统中，格式如下：
employee_id,name,department,hire_date
1,John Doe,IT,2020-01-01
2,Jane Smith,HR,2020-02-01
-- 开启一个静态文件服务
gpfdist -d /home/gpadmin/software/datas/ -p 8081 &
-- 创建外部表
CREATE EXTERNAL TABLE ext_employees (
employee_id varchar(100),
name VARCHAR(100),
department VARCHAR(100),
hire_date varchar(100)
)
LOCATION ('gpfdist://hadoop102:8081/employee_data.csv')
FORMAT 'CSV';
SELECT * FROM ext_employees;
体现内外表区别的操作
内部表：
可以对内部表进行 INSERT, UPDATE, DELETE 等操作，因为数据存储在数据库内部。
支持事务，可以回滚未提交的更改。
可以创建索引以提高查询性能。
外部表：
通常只用于 SELECT 操作，用于读取外部数据源的数据。
不支持事务。
不支持索引创建。
内部表和外部表在操作和用途上的主要区别。内部表适合存储和管理数据库内的数据，而外部表适用于从外部数据源临时读取数据。
2.5、修改表
2.5.1、重命名表
1）语法
ALTER TABLE table_name RENAME TO new_table_name
2）实操案例
gpdw=# alter table arr_test rename to arr;
2.5.2、增加/修改/替换列信息
1）语法
（1）更新列
修改列明 ALTER TABLE tab_name RENAME old_name TO new_name;
修改列类型 ALTER TABLE tab_name ALTER COLUMN column_name TYPE column_type [USING column_name::column_type];
（2）增加/删除列
ALTER TABLE table_name ADD|DROP COLUMN col_name [column_type];
注：ADD是代表新增一个字段，需要添加相应的字段类型，DROP是代表删除一个字段。
2）实操案例
（1）查询表结构
gpdw=# \d arr
（2）添加列
gpdw=# gpdw=# alter table arr add column id int;
（3）更新列
gpdw=# alter table arr rename sal to salary;
gpdw=# alter table arr alter column id type double precision;
（4）删除列
gpdw=# alter table arr drop column id;
2.6、删除表
gpdw=# drop table arr;
2.7、清除表
注意：truncate只能删除内部表，不能删除外部表中数据。
gpdw=# truncate table arr;
三、DML
3.1 数据导入
3.1.1 向表中装载数据（COPY）
1）语法
COPY table_name FROM file_path DELIMITER ‘字段之间的分隔符’;
- （1）copy：表示加载数据，仅追加。
- （2）delimiter：表示读取的数据字段之间的分隔符。
2）实操案例
（0）准备一份文件 在hadoop102的/home/gpadmin/software/datas目录下新建文件
vim employees1.txt 内容如下：
John,IT,30
Jane,HR,25
（1）创建一张表
CREATE TABLE employees1 (
name VARCHAR(100),
department VARCHAR(50),
age INTEGER
);
（2）加载文件到表中
COPY employees1 FROM '/home/gpadmin/software/datas/employees1.txt' delimiter ',';
3.1.2 通过查询语句向表中插入数据（Insert）
1）创建一张表
CREATE TABLE employees2 (
name VARCHAR(100),
department VARCHAR(50),
age INTEGER
);
2）基本模式插入数据
insert into employees2 values(‘zhangsan’,'IT',18),(‘lisi’,'HR',20);
3）根据查询结果插入数据
insert into employees2 select * from employees1;
注意：insert不支持覆盖数据，仅追加。
3.1.3 查询语句中创建表并加载数据（As Select）
根据查询结果创建表（查询的结果会添加到新创建的表中）。
create table employees3 as select * from employees1 ;
3.2 数据更新和删除
3.2.1 数据更新
修改数据的通用SQL语法：
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
我们可以同时更新一个或多个字段，也可以在WHERE子句中指定任何条件。
update employees3 set name='haihai' where department = 'HR';
3.2.2 数据删除
删除指定数据的通用SQL语法：
DELETE FROM table_name WHERE [condition];
如果没有指定 WHERE 子句，GreenPlum对应表中的所有记录将被删除。
一般我们需要在 WHERE 子句中指定条件来删除对应的记录，条件语句可以使用 AND 或 OR 运算符来指定一个或多个。
delete from gp_test where id =1 or name = 'cangcang';
3.2.3 创建外部表时通过Location指定加载数据路径
1）数据存储在segment节点，不要在master节点
2）创建表，并指定文件位置
create external table gp_test3 (
id int,
name text
) location('file://hadoop103:6000/opt/greenplum/data1/primary/gp_test.txt')
format 'text' (delimiter ',');
3）查询数据
select * from gp_test3;
后续只需要文件修改，对应查出的数据即会随着改变。
3.3 数据导出
无法导出外部表的数据。
copy employees3 to '/home/gpadmin/software/datas/test.txt';
四、DQL
4.1 基础语法及执行顺序
1）查询语句语法：
SELECT [DISTINCT] colum1, column2, ...
FROM table_name -- 从什么表查
[WHERE condition] -- 过滤
[GROUP BY column_list] -- 分组查询
[HAVING column_list] -- 分组后过滤
[ORDER BY column_list] -- 排序
[LIMIT number]
4.2 基本查询（Select…From）
4.2.1 数据准备
（0）原始数据
①在/home/gpadmin/software/datas/路径上创建dept.txt文件，并赋值如下内容：
部门编号 部门名称 部门位置id
[atguigu@hadoop103 data]$ vim dept.txt
10,行政部,1700
20,财务部,1800
30,教学部,1900
40,销售部,1700
②在/home/gpadmin/software/datas/路径上创建emp.txt文件，并赋值如下内容：
员工编号 姓名 岗位 薪资 部门
[atguigu@hadoop103 data]$ vim emp.txt
7369,张三,研发,800.00,30
7499,李四,财务,1600.00,20
7521,王五,行政,1250.00,10
7566,赵六,销售,2975.00,40
7654,侯七,研发,1250.00,30
7698,马八,研发,2850.00,30
7782,金九,,2450.0,30
7788,银十,行政,3000.00,10
7839,小芳,销售,5000.00,40
7844,小明,销售,1500.00,40
7876,小李,行政,1100.00,10
7900,小元,讲师,950.00,30
7902,小海,行政,3000.00,10
7934,小红明,讲师,1300.00,30
（1）创建部门表
create external table dept (
deptno int, --部门编号
dname text, --部门名称
loc int --部门位置id
) location('gpfdist://hadoop102:8081/dept.txt')
format 'text' (delimiter ',');
（2）创建员工表
create external table emp (
empno int, -- 员工编号
ename text, -- 员工姓名
job text, -- 员工岗位（大数据工程师、前端工程师、java工程师）
sal double precision, -- 员工薪资
deptno int -- 部门编号
) location('gpfdist://hadoop102:8081/emp.txt')
format 'text' (delimiter ',');
4.2.2 全表和特定列查询
1）全表查询
select * from emp ;
2）选择特定列查询
select empno,ename from emp ;
4.2.3 列别名
1）重命名一个列
2）便于计算
3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’
4）查询名称和部门。
select
ename as name,
deptno dn
from emp;
4.2.4 Limit语句
典型的查询会返回多行数据。limit子句用于限制返回的行数。
select * from emp limit 2;
select * from emp limit 2 offset 3; --表示从第二行开始，向下抓取三行数据。
4.2.5 Where语句
1）使用where子句，将不满足条件的行过滤掉
2）where子句紧随from子句
3）查询出薪水大于1000的所有员工。
select * from emp where sal > 1000;
注意：where子句中不能使用字段别名。
4.2.6 关系运算函数
1）基本语法
如下操作符主要用于where、join…on和having语句中。
| 操作符 | 支持的数据类型 | 描述 |
|---|---|---|
| A=B | 基本数据类型 | 如果A等于B则返回true，反之返回false |
| A<=>B | 基本数据类型 | 如果A和B都为null，则返回true，如果一边为null，返回false |
| A<>B, A!=B | 基本数据类型 | A或者B为null则返回null；如果A不等于B，则返回true，反之返回false |
| A<B | 基本数据类型 | A或者B为null，则返回null；如果A小于B，则返回true，反之返回false |
| A<=B | 基本数据类型 | A或者B为null，则返回null；如果A小于等于B，则返回true，反之返回false |
| A>B | 基本数据类型 | A或者B为null，则返回null；如果A大于B，则返回true，反之返回false |
| A>=B | 基本数据类型 | A或者B为null，则返回null；如果A大于等于B，则返回true，反之返回false |
| A [not] between B and C | 基本数据类型 | 如果A，B或者C任一为null，则结果为null。如果A的值大于等于B而且小于或等于C，则结果为true，反之为false。如果使用not关键字则可达到相反的效果。 |
| A is null | 所有数据类型 | 如果A等于null，则返回true，反之返回false |
| A is not null | 所有数据类型 | 如果A不等于null，则返回true，反之返回false |
| in（数值1，数值2） | 所有数据类型 | 使用 in运算显示列表中的值 |
| A [not] like B | string 类型 | B是一个SQL下的简单正则表达式，也叫通配符模式，如果A与其匹配的话，则返回true；反之返回false。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母‘x’结尾，而‘%x%’表示A包含有字母‘x’,可以位于开头，结尾或者字符串中间。如果使用not关键字则可达到相反的效果。 |
| A rlike B, A regexp B | string 类型 | B是基于java的正则表达式，如果A与其匹配，则返回true；反之返回false。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。 |
2）案例实操（between/In/ Is null）
（1）查询出薪水等于5000的所有员工
select * from emp where sal = 5000;
（2）查询工资在500到1000的员工信息
select * from emp where sal between 500 and 1000;
（3）查询job为空的所有员工信息
select * from emp where job is null;
（4）查询工资是1500或5000的员工信息
select * from emp where sal in (1500,5000);
（5）查找名字以“明”结尾的员工信息
select * from emp where ename like '%明';
4.2.7 逻辑运算函数
1）基本语法（and/or/not）
| 操作符 | 含义 |
|---|---|
| and | 逻辑并 |
| or | 逻辑或 |
| not | 逻辑否 |
2）案例实操
（1）查询薪水大于1000，部门是30
select
*
from emp
where sal > 1000 and deptno = 30;
（2）查询薪水大于1000，或者部门是30
select
*
from emp
where sal>1000 or deptno=30;
（3）查询除了20部门和30部门以外的员工信息
select
*
from emp
where deptno not in(30, 20);
4.2.8 聚合函数
1）语法
count()，表示统计行数；
max()，求最大值，不包含null，除非所有值都是null；
min()，求最小值，不包含null，除非所有值都是null；
sum()，求和，不包含null。
avg()，求平均值，不包含null。
2）案例实操
（1）求总行数（count）
select count(*) from emp;
（2）求工资的最大值（max）
select max(sal) max_sal from emp;
（3）求工资的最小值（min）
select min(sal) min_sal from emp;
（4）求工资的总和（sum）
select sum(sal) sum_sal from emp;
（5）求工资的平均值（avg）
select avg(sal) avg_sal from emp;
4.3 分组
4.3.1 Group By语句
Group By语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
查询时如果SQL语句中含有group by，那么select后查询的字段只能包含出现在group by后面的字段。
1）案例实操：
（1）计算emp表每个部门的平均工资。
select
t.deptno,
avg(t.sal) avg_sal
from emp t
group by t.deptno;
（2）计算emp每个部门中每个岗位的最高薪水。
select
t.deptno,
t.job,
max(t.sal) max_sal
from emp t
group by t.deptno, t.job;
4.3.2 Having语句
1）having与where不同点
（1）where后面不能写分组聚合函数，而having后面可以且只能使用分组聚合函数，不可使用聚合函数的别名。
（2）having只用于group by分组统计语句。
2）案例实操
（1）求每个部门的平均薪水大于2000的部门
select
deptno,
avg(sal) avg_sal
from emp
group by deptno
having avg(sal) > 2000;
4.4 Join语句
4.4.1 等值Join
GreenPlum支持通常的sql join语句。
1）案例实操
（1）根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称。
select
e.empno,
e.ename,
d.dname
from emp e
join dept d
on e.deptno = d.deptno;
4.4.2 内连接
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。默认的join即为inner join。
select
e.empno,
e.ename,
d.deptno
from emp e
join dept d
on e.deptno = d.deptno;
4.4.3 左外连接
左外连接：join操作符左边表中所有记录将会被返回，右表匹配不到的置为null。
select
e.empno,
e.ename,
d.deptno
from emp e
left join dept d
on e.deptno = d.deptno;
4.4.4 右外连接
右外连接：join操作符右边表中所有记录将会被返回，左表匹配不到的置为null。
select
e.empno,
e.ename,
d.deptno
from emp e
right join dept d
on e.deptno = d.deptno;
4.4.5 满外连接
满外连接：将会返回所有表中所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用null值替代。
select
e.empno,
e.ename,
d.deptno
from emp e
full join dept d
on e.deptno = d.deptno;
4.4.6 多表连接
注意：连接n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。
数据准备，在/opt/module/data/下：vim location.txt
部门位置id 部门位置
[atguigu@hadoop102 datas]$ vim location.txt
1700,北京
1800,上海
1900,深圳
1）创建位置表
create external table location (
loc int, --部门位置id
loc_name text --部门位置
) location('gpfdist://hadoop102:8081/location.txt')
format 'text' (delimiter ',');
2）多表连接查询
select
e.ename,
d.dname,
l.loc_name
from emp e
join dept d
on d.deptno = e.deptno
join location l
on d.loc = l.loc;
4.4.7 笛卡尔积
1）笛卡尔集会在下面条件下产生
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
2）案例实操
select
empno,
dname
from emp, dept;
4.4.8 联合（union & union all）
1）union&union all上下拼接
union和union all都是上下拼接sql的结果，这点是和join有区别的，join是左右关联，union和union all是上下拼接，且前者会去重，后者不会进行去重。
union和union all在上下拼接sql结果时有两个要求：
（1）两个sql的结果，列的个数必须相同
（2）两个sql的结果，上下所对应列的类型必须一致
2）案例实操
将员工表30部门的员工信息和40部门的员工信息，利用union进行拼接显示。
select
*
from emp
where deptno=30
union
select
*
from emp
where deptno=40;
4.5 排序
Order By：全局排序。
1）使用Order By子句排序
asc（ascend）：升序（默认）
desc（descend）：降序
2）Order By子句一般放在在select语句的结尾，其后所跟字段可以是别名或者多个字段。
3）基础案例实操
（1）查询员工信息按工资升序，部门降序排列
select
deptno dn,
sal
from emp
order by dn desc,sal asc;
五、函数
5.1 函数简介
GreenPlum会将常用的逻辑封装成函数给用户进行使用，类似于Java中的方法。
**好处：**避免用户反复写逻辑，可以直接拿来使用。
**重点：**用户需要知道函数叫什么，能做什么。
提供了大量的内置函数，按照其特点可大致分为如下几类：单行函数、聚合函数(行转列)、炸裂函数(列转行)、窗口函数。
5.2 单行函数
单行函数的特点是一进一出，即输入一行，输出一行。
5.2.1 算术运算函数
| 运算符 | 描述 |
|---|---|
| A+B | A和B 相加 |
| A-B | A减去B |
| A*B | A和B 相乘 |
| A/B | A除以B |
| A%B | A对B取余 |
| A&B | A和B按位取与 |
| A|B | A和B按位取或 |
| A^B | A和B按位取异或 |
| ~A | A按位取反 |
案例实操：查询出所有员工的薪水后加1显示。
select sal + 1 from emp;
5.2.2 数学函数
| 函数 | 描述 |
|---|---|
| ceil | 不小于参数的最小整数 |
| floor | 不大于参数的最大整数 |
| round | 四舍五入 |
| round(a,b) | 保留b位小数的四舍五入 |
| random | 0到1之间的随机shu’zhi |
案例实操：保留一位小数。
select round(2.35,1);
5.2.3 字符串函数
1）substr或substring：截取字符串
语法一：substr(text A, int start)
说明：返回字符串A从start位置到结尾的字符串
语法二：substr(text A, int start, int len)
说明：返回字符串A从start位置开始，长度为len的字符串
案例实操：
（1）获取第二个字符以后的所有字符
select substr('helloworld',2);
输出：
elloworld
2）replace ：替换
语法：replace(text A, text B, text C)
说明：将字符串A中的子字符串B替换为C。
select replace('atguigu', 'a', 'A');
输出：
Atguigu
3）repeat：重复字符串
语法：repeat(textA, int n)
说明：将字符串A重复n遍。
select repeat('123', 3);
输出：
123123123
4）split_part ：字符串切割
语法：split_part(text str, text pat,int field)
说明：按照正则表达式pat匹配到的内容分割str，返回分割后的第field个字符。
select split_part('a-b-c-d','-',2);
输出：
b
5）concat ：拼接字符串
语法：concat(text A, text B, text C, ……)
说明：将A,B,C……等字符拼接为一个字符串
select concat('beijing','-','shanghai','-','shenzhen');
输出：
beijing-shanghai-shenzhen
6）concat_ws：以指定分隔符拼接字符串
语法：concat_ws(text A, text…)
说明：使用分隔符A拼接多个字符串，或者一个数组的所有元素。
select concat_ws('-','beijing','shanghai','shenzhen');
输出：
beijing-shanghai-shenzhen
5.2.4 日期函数
1）current_date：当前日期
select current_date;
输出：
2023-02-15
2）current_timestamp：当前的日期加时间，并且精确的毫秒
select current_timestamp;
输出：
2023-02-15 13:50:28.144287+08
3）date_part(text,timestamp)：根据输入的text获取对应的年月日时分秒
select date_part('hour',current_timestamp);
输出：
13
4）age：两个日期相差的天数（结束日期减去开始日期的天数）
语法：age(timestamp enddate, timestamp startdate) ,返回值使用年和月，不只是天数。
案例实操：
select age(timestamp '2024-03-16', timestamp '2023-02-15');
输出：
1 year 1 mon 1 day
5）to_char：转换日期格式
语法：to_char(timestamp startdate,text )
案例实操：
select to_char(now(),'yyyymmdd');
输出：
20230215
6）日期加减
对应的日期 + interval text
案例实操：
select timestamp '203-02-15' + interval '2 days';
输出：
2023-02-17 00:00:00
5.2.5 流程控制函数
1、case when：条件判断函数
语法：case when a then b [when c then d]* [else e] end
返回值：T
说明：如果a为true，则返回b；如果c为true，则返回d；否则返回 e
select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end;
mary
案例实现:
1）数据准备
| name | dept_id | sex |
|---|---|---|
| 悟空 | A | 男 |
| 大海 | A | 男 |
| 宋宋 | B | 男 |
| 凤姐 | A | 女 |
| 婷姐 | B | 女 |
| 婷婷 | B | 女 |
2）需求
求出不同部门男女各多少人。结果如下：
dept_Id 男 女
A 2 1
B 1 2
3）在/home/gpadmin/software/datas路径上创建emp_sex.txt，并导入数据
[gpadmin@hadoop103 datas]$ vim emp_sex.txt
悟空 A 男
大海,A,男
宋宋,B,男
凤姐,A,女
婷姐,B,女
婷婷,B,女
4）创建表并导入数据
-- 建表
create table emp_sex(
name varchar(100), --姓名
dept_id varchar(100), --部门id
sex varchar(10) --性别
);
-- 导入数据
copy emp_sex FROM '/home/gpadmin/software/datas/emp_sex.txt' delimiter '\t';
5）按需求查询数据
select
dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex
group by dept_id;
5.3 行列转换函数
5.3.1 行转列函数
有时候我们需要将一个列的字符串按照某个分隔符将其拼接起来，就用到了string_agg()函数。
语法：string_agg(column,parten)
案例实操：
select string_agg(ename,'-') from emp;
输出：
张三-李四-王五-赵六-侯七-马八-金九-银十-小芳-小明-小李-小元-小海-小红明
5.3.2 列转行函数
语法：regexp_split_to_table(column,parten)
案例实操：
select regexp_split_to_table('a-b-c-d','-');
输出：
a
b
c
d
5.3.3 案例演示
1.数据准备
1）表结构
| movie | category |
|---|---|
| 《疑犯追踪》 | 悬疑，动作，科幻，剧情 |
| 《Lie to me》 | 悬疑，警匪，动作，心理，剧情 |
| 《战狼2》 | 战争，动作，灾难 |
2）建表语句
create table movie_info(
movie varchar(100), --电影名称
category varchar(100) --电影分类
);
3）装载语句
insert into movie_info values ('《疑犯追踪》', '悬疑,动作,科幻,剧情'),
('《Lie to me》', '悬疑,警匪,动作,心理,剧情'),
('《战狼2》', '战争,动作,灾难');
2. 需求
1）需求说明
根据上述电影信息表，统计各分类的电影数量，期望结果如下：
| 剧情 | 2 |
|---|---|
| 动作 | 3 |
| 心理 | 1 |
| 悬疑 | 2 |
| 战争 | 1 |
| 灾难 | 1 |
| 科幻 | 1 |
| 警匪 | 1 |
2）答案
select regexp_split_to_table(category, ',') as category_name, count(*) as cnt
from movie_info
group by category_name
5.4 窗口函数
5.4.1 概述
窗口函数，能为每行数据划分一个窗口，然后对窗口范围内的数据进行计算，最后将计算结果返回给该行数据。
5.4.2 常用窗口函数
按照功能，常用窗口可划分为如下几类：聚合函数、跨行取值函数、排名函数。
1）聚合函数
max：最大值。
min：最小值。
sum：求和。
avg：平均值。
count：计数。
2）跨行取值函数
（1）lead和lag
注：lag和lead函数不支持自定义窗口。
（2）first_value和last_value
3）排名函数
注：rank 、dense_rank、row_number不支持自定义窗口。
5.4.3 案例实操
1）数据准备
（1）创建表
create table order_info
(
order_id varchar(100), --订单id
user_id varchar(100), -- 用户id
user_name varchar(100), -- 用户姓名
order_date varchar(100), -- 下单日期
order_amount int -- 订单金额
);
（2）插入语句
insert into order_info
values ('1', '1001', 'songsong', '2022-01-01', '10'),
('2', '1002', 'cangcang', '2022-01-02', '15'),
('3', '1001', 'songsong', '2022-02-03', '23'),
('4', '1002', 'cangcang', '2022-01-04', '29'),
('5', '1001', 'songsong', '2022-01-05', '46'),
('6', '1001', 'songsong', '2022-04-06', '42'),
('7', '1002', 'cangcang', '2022-01-07', '50'),
('8', '1001', 'songsong', '2022-01-08', '50'),
('9', '1003', 'huihui', '2022-04-08', '62'),
('10', '1003', 'huihui', '2022-04-09', '62'),
('11', '1004', 'linlin', '2022-05-10', '12'),
('12', '1003', 'huihui', '2022-04-11', '75'),
('13', '1004', 'linlin', '2022-06-12', '80'),
('14', '1003', 'huihui', '2022-04-13', '94');
2）需求
（1）统计每个用户截至每次下单的累积下单总额
select
order_id,
user_id,
user_name,
order_date,
order_amount,
sum(order_amount) over(partition by user_id order by order_date rows between unbounded preceding and current row) sum_so_far
from order_info;
（2）统计每个用户每次下单距离上次下单相隔的天数（首次下单按0天算）
select
order_id,
user_id,
user_name,
order_date,
order_amount,
case when age(cast(order_date as timestamp),cast(last_order_date as timestamp)) is null then '0'
else age(cast(order_date as timestamp),cast(last_order_date as timestamp))
end diff
from
(
select
order_id,
user_id,
user_name,
order_date,
order_amount,
lag(order_date,1,null) over(partition by user_id order by order_date) last_order_date
from order_info
)t1;
（3）查询所有下单记录以及每个下单记录所在月份的首/末次下单日期
select
order_id,
user_id,
user_name,
order_date,
order_amount,
first_value(order_date) over(partition by user_id,substring(order_date,1,7) order by order_date) first_date,
last_value(order_date) over(partition by user_id,substring(order_date,1,7) order by order_date rows between unbounded preceding and unbounded following) last_date
from order_info;
（5）为每个用户的所有下单记录按照订单金额进行排名
select
order_id,
user_id,
user_name,
order_date,
order_amount,
rank() over(partition by user_id order by order_amount desc) rk,
dense_rank() over(partition by user_id order by order_amount desc) drk,
row_number() over(partition by user_id order by order_amount desc) rn
from order_info;
六、其他
er by order_date rows between unbounded preceding and current row) sum_so_far
from order_info;
**（2）统计每个用户每次下单距离上次下单相隔的天数（首次下单按0天算）**
```sql
select
order_id,
user_id,
user_name,
order_date,
order_amount,
case when age(cast(order_date as timestamp),cast(last_order_date as timestamp)) is null then '0'
else age(cast(order_date as timestamp),cast(last_order_date as timestamp))
end diff
from
(
select
order_id,
user_id,
user_name,
order_date,
order_amount,
lag(order_date,1,null) over(partition by user_id order by order_date) last_order_date
from order_info
)t1;
（3）查询所有下单记录以及每个下单记录所在月份的首/末次下单日期
select
order_id,
user_id,
user_name,
order_date,
order_amount,
first_value(order_date) over(partition by user_id,substring(order_date,1,7) order by order_date) first_date,
last_value(order_date) over(partition by user_id,substring(order_date,1,7) order by order_date rows between unbounded preceding and unbounded following) last_date
from order_info;
（5）为每个用户的所有下单记录按照订单金额进行排名
select
order_id,
user_id,
user_name,
order_date,
order_amount,
rank() over(partition by user_id order by order_amount desc) rk,
dense_rank() over(partition by user_id order by order_amount desc) drk,
row_number() over(partition by user_id order by order_amount desc) rn
from order_info;
