[CMU15445]:AdvancedSQL

---

聚集函数

当在sql语句中使用聚集函数时，如果列中同时出现了非聚合函数值，那么他们必须同时出现在GROUP BY子句中

嵌套查询(NESTED QUERIES)

嵌套查询形如这样

• 效率

  这种查询通常效率很低，因为嵌套查询有点像C++中的双重循环

• 声明问题

  与高级语言一样，内部查询可以使用外部查询的声明，但是外部查询看不见内部查询的声明

  

• 使用各种关键字或内置函数来辅助嵌套查询

  

窗口函数

窗口函数，也称OLAP(Online Anallytical Precessing),可以对数据库数据进行实时分析

• 基本语法

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

窗口函数有两种:

1. 专用的窗口函数,如rank(), row_num()等
2. 普通聚合函数

注:窗口函数只能使用在select子句中,因为它是对where子句或者GROUP BY子句返回的tuple进行处理

• 例子

  

  对这张表使用如下sql语句

  select *,
     rank() over (partition by 班级
                   order by 成绩 desc) as ranking
  from 班级表

  得到的结果如下

  

  最终得到的结果根据partition by子句的班级字段进行分组，组内按照成绩进行排序，然后使用rank()进行排序

  注意到该功能与GROUP BY子句功能十分相近，但是还是有些差别，下面是对GROUP BY班级的结果

  

  显然，GROUP BY 子句会减少原表的行数，而窗口函数不会

  注：专用窗口函数的()可以不加参数

通用表表达式(common table expression)

common table expression是一种临时表，它使用with命令，甚至可以执行递归查询

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a= cte2.c;

在上面的例子中创建了两张表，cte1,cte2，表名放在AS关键字之前

• 使用WITH + RECURSIVE关键字执行递归查询

  WITH RECURSIVE qn AS
     ( SELECT 1 AS a
       UNION ALL               /*UNION 关键字的作用是连接两行*/           
       SELECT 1+a FROM qn WHERE a<10
     )
  SELECT *FROM qn;

  最终得到的结果应该是这样