SQL语法

--1.窗口函数进阶

/*CREATE DATABASE Study;

\c Study;*/

--学习准备
/*
CREATE TABLE Student (
    Number      numeric(8,0) PRIMARY KEY NOT NULL,
    Name        varchar(10) NOT NULL,
    Sex         char(4) CHECK (Sex IN ('男','女')) DEFAULT '男' NOT NULL,
    Age     int CHECK (Age > 18 AND Age < 25) NOT NULL
);

INSERT INTO Student(Number,Name,Sex,Age) VALUES(20163000,'Mike','男',21),
(20163001,'Lily','女',20),(20163002,'Coco','女',22),(20153200,'Hallen','女',19),(20163340,'Jessile','女',20),(20163167,'Tom','男',23);
*/
--窗口函数与GROUP BY很像,但是窗口灵活性更大,因为窗口函数可以将得到的窗口集合能进一步划分
--个人理解:窗口函数是使得具有某种性质可大可小的窗口形成结果集的一行
----------:GROUP BY是通过把具有某种性质的不易伸缩的窗口形成结果集的一行
--窗口函数像是一个集合被拆成多个小集合,这几个小集合可有交叉
--GROUP BY则相反
--举例1:利用聚合函数算出不同学生的总年龄

SELECT sum(Age) FROM Student GROUP BY Sex;

SELECT DISTINCT sum(Age) OVER (PARTITION BY Sex) FROM Student;

--举例2:利用聚合函数算出按姓名排序并且按性别分组算出从上到下累计年龄
--这时候使用GROUP BY明显力不从心了

SELECT Name,Sex,Age,sum(Age) OVER (PARTITION BY Sex ORDER BY Name) AS 累计年龄 FROM Student;

--举例3:算出每两个一组的平均年龄

SELECT Name,Sex,Age,avg(Age) OVER (ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS TG FROM Student;

--例2也可以用[range row]改写

SELECT Name,Sex,Age,sum(Age) OVER (PARTITION BY Sex ORDER BY Name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 累计年龄 FROM Student;

--不按年龄排序和性别分组
--注:如果窗口分区未排序别用RANGE代替ROWS，因为未排序窗口分区会被认为全区重复的
SELECT Name,Sex,Age,sum(Age) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 累计年龄 FROM Student;


--由下向上计算sum(Age)

SELECT Name,Sex,Age,sum(Age) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS 累计年龄 FROM Student;

--总结:
--CURRENT ROW: 表示当前行
--UNBOUNDED PRECEDING:表示分区第一行
--UNBOUNDED FOLLOWING:表示分区最后一行
--[number of pos] PRECEDING:当前行的之前[number of pos]行(只能用于ROWS)
--[number of pos] FOLLOWING:当前行的之后[number of pos]行(只能用于ROWS)

--2.类型转换
--类型转换有多种语法,有postgresql特有的,也有sql标准语法
--1.CAST (_expression_ AS _type_) --推荐方式
--2.函数风格的语法 --有很大的局限性不予讨论
--3._expression_::_type_ --postgresql历史用法

--选出学号的前4位
SELECT substring(CAST(Number AS text) FROM 1 FOR 4) FROM Student;
SELECT substring(Number::text FROM 1 FOR 4) FROM Student;
--选出学号的后4位
SELECT substring(CAST(Number AS text) FROM 5 FOR 8) FROM Student;
SELECT substring(Number::text FROM 5 FOR 8) FROM Student;
--也可以使用CREATE CAST自定义类型转换的行为

--3.排序规则表达式
--通过对表达式应用不同的排序规则能得到不同的结果集
SELECT Number,Name,Sex FROM Student WHERE Name > 'Marco' COLLATE "C";
SELECT Number,Name,Sex FROM Student WHERE Name > 'Marco' COLLATE "zh_CN";

--排序规则应用于字符串,所有有时我们需要进行类型转换.

--4.数组构造器
--一个数组构造器是一个表达式,它从自身成员元素构造一个数组. 
--一个简单数组的构造由ARRAY关键字指定ARRAY[ele1,ele2.....]
--数组必须要有类型,当数组构造时不明确时必须要指定类型
SELECT ARRAY[1,2,4,6,8];

--使用类型转换将元素指定为精确的类型

SELECT ARRAY[1,2,5,6]::integer[];
SELECT CAST(ARRAY[1,2,5,6] AS integer[]);

--多维数组

SELECT ARRAY[ARRAY[1,2],ARRAY[3,4],ARRAY[5,6]];

--可以将查询结构构造成数组 ARRAY(SELECT子句)

SELECT ARRAY(SELECT Number FROM Student);
--通过数组将列构造了行

--5.行构造器

SELECT ROW(1,2.5,'this is a test');

--通过外部对象构造行

SELECT ROW(Student.*,'student') FROM Student;

--6.调用函数
CREATE FUNCTION lower_or_upper_name(name text,islow boolean DEFAULT TRUE) RETURNS text AS $$SELECT CASE WHEN $2 THEN LOWER($1) ELSE UPPER($1) END;$$ LANGUAGE SQL IMMUTABLE STRICT;
--位置表示法:通过位置将实参与参数列表一一对应
SELECT lower_or_upper_name(Name) FROM Student;
--名称表示法:通过arg_name := real_arg将实参与参数一一对应
SELECT lower_or_upper_name(name:=Name,islow:=FALSE) FROM Student;
--混合表示法:综合位置和名称的信息将实参与参数列表一一对应
SELECT lower_or_upper_name(Name,islow:=FALSE) FROM Student;