PL/pgSQL初学之路（一）

参考资料：https://blog.csdn.net/yilulvxing/article/details/85098273
over partition by 和 group by 的区别
left join 和 left outer join 的区别

本系列为本人初学plpgsql语言的笔记，记录自己在实现算法、参考他人代码时遇到的简单语法。

（一）查询函数

一、row_number() over (partition by col1 order by col2)

1. 分组聚合：先分组再排序
2. 表示根据col1分组，在分组内部根据col2排序
3. ROW_NUMBER()函数将针对SELECT语句返回的每一行，从1开始编号，赋予其连续的编号。在查询时应用了一个排序标准后，只有通过编号才能够保证其顺序是一致的，当使用ROW_NUMBER函数时，也需要专门一列用于预先排序以便于进行编号
4. partition by关键字是分析性函数的一部分，它和聚合函数不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录，partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，分区函数一般与排名函数一起使用。

二、 count(*) over (partition by col1)

1. 展示每一行中，根据col1分组的数量
2. 示例

原始表score：
s_id 表是学生编号，c_id表是课程编号，s_score 表是学生对应的课程分数

要求:得出每门课程的学生成绩排序（升序）----因为是每门课程的结果，并且要排序，所以用row_number：

select * ,row_number() over (partition by c_id order by s_score) from score;

结果：

select * ,
	   row_number() over (partition by c_id order by s_score) from score，
	   count(1) over (partition by c_id);

结果：最后一列所有行都为6.

三、 ceil(row_number:: double precision / 6)

1. ceil()函数：向上取整
   ceil(2.86) = 3
   ceil(2.16) = 3
   ceil(-6.28) = -6
2. ceil函数传入的参数应该cast成所需要的数据类型，用parameter::type的形式声明，如果需要对该参数进行计算，则在声明类型后进行计算，如标题所示。

四、stddev

计算某一列数据的标准差，如：

stddev(price) over (partition by classification)

计算每一分类商品的价格的标准差。

五、avg(col1) over(partition by login_id, bucket_no)

1. avg(col1)：计算col1的平均值
2. over(partition by col2, col3)：按照col2和col3来分割，换句话说，如果相同的col3对应的条数小于相同的col2对应的条数，那么每col3条数据的avg(col1)这一列的数据是相同的
3. 计算的是col2和col3都相同的数据的col1的平均值

（二）group by 和 partition by的区别

1. group by：对检索结果的保留行进行单纯分组，返回聚合之后的组的数据统计值的记录。划分为多组，每组返回一行。
   partition by：返回分组里的每一条数据，在应用关联的开窗函数前确定行集的分区和排序，然后，开窗函数将计算窗口中的每一行的值。
   简单来说：group by每个类别只返回一行，over partition by每行都返回，同一类别返回的数据相同。
2. 示例
   数据：test表
   
   分别执行group by和over partition by

SELECT A,B,MAX（C） OVER( PARTITION BY A,B) FROM TEST;

结果：

SELECT A,B,MAX（C）FROM TEST GROUP BY 1,2;

结果：

（四）left outer join v.s. left join

一、left join

1. 语法
   A left join B 的连接的记录数与A表的记录数同
   A right join B 的连接记录数与B表的记录数同
   A left join B 等价于 B right join A
2. 示例
   

执行：

  select   a.Field_K,   a.Field_A,   b.Field_K,   b.Field_B    
  from   a   left   join   b   on   a.Field_K=b.Field_K  

结果：

执行：

  select   a.Field_K,   a.Field_A,   b.Field_K,   b.Field_B  
  from   a   right   join   b   on   a.Field_K=b.Field_K  

结果：

二、left outer join

left join 是left outer join的简写，没有left inner join的形式。