一些SQL

SQL复习

一、窗口函数

窗口函数也称为OLAP（Online Anallytical Processing）函数，意思是对数据库数据进行实时分析处理。

窗口函数就是为了实现OLAP而添加的标准SQL功能。

窗口函数基本语法：

<窗口函数> over (partition by <用于分组的列名>  
                order by <用于排序的列名>) 

1.分类：

• 序号函数：row_number() / rank() / dense_rank()

  • ROW_NUMBER()不考虑并列，1、2、3、4
  • RANK()考虑并列，并列之后和ROW_NUMBER一样，1、2、2、4
  • DENSE_RANK()考虑并列，并列之后按下一个名次来，1、2、2、3

• 分布函数：percent_rank() / cume_dist()

  • percent_rank() ：rank()/rows
  • cume_dist()：累积分布，表示值小于或等于行的值除以总行数的行数

• 前后函数：lag(n) 前/ lead(n)后

  select *,
  lead(某个指标,offset,default_value) over(partition by 某个属性 order by 某个属性 desc) as lead_val
  from 表

  offset默认为1，default_value不设置就是NULL

• 头尾函数：first_val(expr) / last_val(expr)，得到分区中的第一个/最后一个（截至当前条记录）指定参数的值。

• 其他函数：nth_value(expr, n)，返回窗口中第N个expr的值

• 原有的聚合函数：sum()，avg()，count()，max()，min()

2.应用：

• topN

  select *
  from(select *,
       row_number() over(partition by 某个属性 order by 排序属性 desc)as ranking 
       from 表) as a 
  where ranking <=n

• 累计求和

  select *,
  sum(某个指标) over(oder by 某个属性) as current_sum
  from 表

• 滑动平均

  select *,
  avg(某个指标) over(order by 某个属性 rows 2 preceding) as current_avg
  from 表;

  使用 rows 和 preceding 这两个关键字，是“之前~行”的意思。得到的结果是自身纪录及前2行的平均。

参考：

二、日期处理

三、行和列的转换

四、题目

union去重

union all不去重