2020-08-16PostgreSQL乱七八糟的总结

下周开始经理要安排我做一系列关于PSQL报表的工作，估计对于我这个新手来说会有很大的难度，慢点学习，在工作中理解与进步。接下来针对这两天查阅部分的资料进行一个简单的汇总。

generate_series(1, 10, 2)

生成一系列的数字，相信这里的参数基本上有过编程基础的都知道，不过多赘述。

这里有点像广播算法。

SQL常用数据类型总结

int、numeric、serial、varchar()、text、date。
常用于创建表时对于字段数据类型的定义，也可用于数据类型的转换。

再介绍下PSQL中内置的一些运算函数

• mod(8, 3) – 取余函数
• round(3.1415, 2) – 四舍五入
• ceil(-3.2) – 取右函数，输出 -3
• floor(2.3) – 取左函数， 输出 2

再介绍一个interval的时间函数，主要用于时间方面的计算，后面加的参数可为year、month、day、hour、minute。

注意interval后面加的是引号，而不是括号。

extract 提取时间函数的某一段

同样，extract中的hour可以换成year、month、minute、second、week等，其中week查出的是本年的第多少个星期。

数组那些事儿

数组array的数据类型创建

数组也是分类型的，比如字符串数组，数字数组等，创建的形式就是在后面加中括号[ ]，查询出来的结果就是花括号{ }。array[1, 2, 3] 表示{1, 2, 3}的数字型数组。‘{“1”， “2”， “3”}’表示字符型数组，创建条件很苛刻。同时数组还可以以[1]的形式对数组内的元素进行查看。例如select
array_tem[2] from dual; 数组中的元素是从1开始计数的。

数组类型的两种插入方法

方法一：利用array_append()函数，第一个参数必须是数组，第二个是数字，不能第一个是数字，也不能将两个数组进行运算，
例：select array_append(array[1, 2, 3], 4);
方法二：利用 || 拼接，这个用法就比较随意了。
例：select 4 || array[1, 2, 3];

数组移除特定值

select array_remove(array[1,2, 2, 3, 4, 5, 2], 2);将array中为2的元素全部移除。

数据类型的转换

方法一：cast方法，例：select cast(‘123’ as numeric(5, 2));可以转换为int、text、等。
方法二：使用 :: 例：select ‘123’ :: numeric(5, 2);
方法三：使用to_char(123, ‘999.99’);返回text格式，to_number(‘123’, ‘999.99’);返回numeric的格式，注意两个方法第二个参数中的数字必须为9，而且必须用引号括起来。

数据的批量插入

方法一：insert into table_name select * from temp; 切记不能加values
方法二：insert into table_name values (…)、(…)、…;注意插入的数据要按照table_name的各字段的顺序及限定的数据类型。

常用的聚合函数

avg()、sum()、min()、max()、count()。聚合函数多与group by联合使用，返回的是基于分类后每组的结果，将多行数据计算后返回一行。

string_agg()和array_agg()的对比

相同点

两者都是将某个字段的数据合并成一行作为输出。

不同点

string_agg()返回的是字符串，注意有两个参数，第一个参数类型必须是string或者text，只能接受字符串类型的输入，输出也为字符串。后面的参数是转换后的数据以什么类型的符合分开。


array_agg()返回是数组类型，注意有一个参数，输入类型没有限制，输出为array，也就是花括号，数组嘛，都是以逗号分割。

后来我发现，在需要合并的字段前加上distinct也可以用于去重。

string_agg和array_agg()之间的转换

array ==>> string，

这里补充一下array_to_string()的方法，注意有三个参数，第一个是array，第二个是转换后的字符串以什么样的符合进行分割，第三个是可选参数，就是针对array中的null值以什么样的元素进行替代，必须为string。

string ==>> array，

窗口函数

窗口函数与聚合函数

通常与聚合函数进行区分。
窗口函数和聚合函数都是基于合并后的结果集进行计算，不同的是聚合函数会将每组的结果集进行计算合并为一行作为输出，而窗口函数是将合并后的计算结果再次添加到结果集后面，不会改变样本数。
总的来说，group by+聚合函数改变了原表的行数，但是窗口函数不会改变，只是在原表的后面加上新的字段。
聚合函数后接over属性可转换为窗口函数。over中的partition by类似与group by，后面也可以加order by，为分组后的数据排序。
接下来以下表为例。

聚合函数见下

窗口函数见下

综上所述，聚合函数中使用group by中，部分字段是不能被查询的，而窗口函数则能查询到任何字段的元素。

聚合函数 + over() 构成窗口函数

row_number() over()

曾经想过将没有任何关联的表做连接，就想着每个表加一个相同的字段，用于做连接，这里就考虑用这个方法。

当然，也可以将分组后的数据进行排序，这就得看over(）中的参数如何写了。

总结后的甜点

瞎鸡儿起的名字，就是解答了自己的一个疑问。
将两张表做连接的时候，如果一个表中的一个样本对应另一个表的多个样本的时候，那么合并出来的就是多个样本。