sqlover开窗函数和groupby的区别
  OVER⼦句⽤于为⾏为定义⼀个窗⼝（windows），以便进⾏特定的运算。可以把⾏的窗⼝简单地认为是运算将要操作的⼀个⾏的集合。例如，聚合函数和排名函数都是可以⽀持OVER⼦句的运算类型。由于OVER⼦句为这些函数提供了⼀个⾏的窗⼝，所以这些函数也称之为开窗函数。
  聚合函数的要点就是要对⼀组值进⾏聚合，聚合函数传统上⼀直以GROUP BY查询作为操作的上下⽂。在前⾯的“GROUP BY”⼦句的讨论中，我们知道在对数据进⾏分组以后，查询为每个组只返回⼀⾏；因此，也就是要限制所有的表达式为每个组只能返回⼀个值。
  聚合开窗函数使⽤OVER⼦句提供窗⼝作为上下⽂，对窗⼝中的⼀组值进⾏操作，⽽不是使⽤GROUP BY⼦句提供的上下⽂。这样就不必对数据进⾏分组，还能够在同⼀⾏中同时返回基础⾏的列和聚合列。为了理解OVER⼦句，现在考虑Sales.OrderValues视图，现在只要简单地把视图看作是⼀个表就可以了。Sales.OrderValues视图的每⼀⾏代表⼀个订单，包含订单ID（orderid），客户ID（custid）、雇员ID（empid）、承运⼈ID（shipperid）、订单⽇期（orderdate），以及订单价格（val）。
  带有空括号的OVER⼦句会提供所有⾏进⾏计算。这⾥的“所有⾏”并不⼀定是在FROM⼦句中出现的那些表中的所有⾏，⽽是在FROM、WHERE、GROUP BY，以及HAVING处理阶段完成后仍然可⽤的那些⾏。注意，只有在SELECT和ORDER BY处理阶段才允许使⽤OVER⼦句。这⾥只中点介绍在SELEC
T处理阶段如何使⽤OVER⼦句。例如，如果在对OrderValues视图进⾏查询的SELECT⼦句中指定了SUM(val) OVER()表达式，这个函数就会对SELECT阶段操作的所有⾏计算其总价格。
  如果查询在SELECT阶段之前没有过滤数据，也没有应⽤其他的逻辑阶段，这个表达式将返回OrderValues视图中所有⾏的总价格。如果想对⾏进⾏限制或分区，则可以使⽤PARTITION BY⼦句。例如，现在不想返回OrderValues中所有⾏的总价格，⽽只是想返回当前客户（和当前⾏具有相同custid的所有⾏）的总价格，则可以指定SUM(val) OVER(PARTITION BY custid)。
  以下查询可以返回OrderValues的所有⾏，并且演⽰了同时使⽤⾮分区和分区表达式的⽅法。此外，每⼀⾏除了基本列以外，查询还会返回所有⾏的总价格和当前客户的总价格：
1
SELECT
orderid,custid,val,
2
SUM
(val)
OVER
()
AS
totalvalue,
3
SUM
(val)
OVER
(PARTITION
BY
custid)
AS
custtotalvalue
4
FROM
Sales.OrderValues;
  这个查询返回以下结果：
  所有结果⾏的totalvalue列表⽰所有⾏的价格总数。custtotalvalue列表⽰所有⾏中与当前⾏具有相同c
ustid值的那些⾏的价格总数。
  OVER⼦句的⼀个优点就是能够在返回基本列的同时，在同⼀⾏对它们进⾏聚合；也可以在表达式中混合使⽤基本列和聚合值列。例如，以下查询为OrderValues的每⼀⾏计算当前价格占总价格的百分⽐，以及当前价格占客户总价格的百分⽐。
1
SELECT
orderid,custid,val,
2
100
.
*
val
/
SUM
(val)
OVER
()
AS
totalvalue,
3
100
.
*
val
/
SUM
(val)
OVER
(PARTITION
BY
custid)
AS
custtotalvalue
4
FROM
Sales.OrderValues;
  注意，在表达式中使⽤的是⼗进制实数100.（100后⾯加个点），⽽不是直接使⽤整数100，因为这样可以隐式地将整数数值val和SUM(val)转换成⼗进制实数值。否则，表达式中的除法将是“整数除法”，会截去数值的⼩数部分。
  这个查询返回以下结果：
  OVER⼦句也⽀持四种排名函数：ROW_NUMBER(⾏号)、RANK(排名)、DENSE_RANK(密集排名)，以及NTILE。以下查询演⽰了这些函数的⽤法：
1
SELECT
orderid,custid,val,
2
ROW_NUMBER()
OVER
(
ORDER
BY
val)
AS
rownum,
3
RANK()
OVERgroupby是什么函数
(
ORDER
BY
val)
AS
rank,
4
DENSE_RANK()
OVER
(
ORDER
BY
val)
AS
dense_rank,
5
NTILE(
10
)
OVER
(
ORDER
BY
val)
AS
ntile
6
FROM
Sales.OrderValues
7
ORDER
BY
val;
  该查询会⽣成以下输出：
  ROW_NUMBER函数⽤于为查询的结果集中的各⾏分配递增的序列号，其逻辑顺序通过OVER⼦句中的ORDER BY语句进⾏指定。在我们的查询例⼦中，逻辑顺序基于的是val列；因此，从输出中可以看到，随着订单价格的增加，⾏号也随之增加。不过，即使订单价格没有增加，⾏号也会依然增加。所以，如果ROW_NUMBER函数的ORDER BY不能唯⼀确定⾏的顺序（如前⾯这个例⼦所⽰），查询
结果就是不确定的。也就是说，查询可能返回多个正确的结果。例如，可以看到订单价格为36.00的两⾏，它们的⾏号分别是7和8。如果这些⾏的顺序发⽣了变化，结果都可以认为是正确的。如果想让⾏号计算值是确定的，则必须在ORDER BY列表中添加元素，让它具有唯⼀性；也就是说，要让ORDER BY⼦句中列出的元素能够唯⼀地确定各个⾏。例如，可以在ORDER BY列表中增加orderid作为附加属性，这样，⾏号计算结果就成为确定的。
  前⾯提过，即使⾏的排序值相同，ROW_NUMBER函数也⼀定为其⽣成唯⼀的⾏号值。如果想以同样的⽅式对排序值中的相同值进⾏更多的处理，可以考虑使⽤RANK或DENSE_RANK函数。这两个函数与ROW_NUMBER类似，但它们为具有相同逻辑排序值的所有⾏⽣成同样的排名。RANK和DENSE_RANK的区别是：RANK表⽰之前有多少⾏具有更低的排序值，⽽DENSE_RANK则表⽰之前有多少个更低的排序值。例如，在我们的查询例⼦中，rank列为9表⽰前⾯有8⾏具有更⼩的排序值，dense_rank列为9表⽰前⾯有8个更⼩的不同排序值。
  NTILE函数可以把结果中的⾏关联到组（tile，相当于由⾏组成的指定数⽬的组），并为每⼀⾏分配⼀个所属的组的编号。NTILE函数接受⼀个表⽰组的数量的输⼊参数，并要在OVER⼦句中指定逻辑顺序。我们的查询例⼦有830⾏，请求将其分成10组；因此，组的⼤⼩就是83（830除以10）。逻辑排序基于的是val列，这意味着价格最低的83⾏将分在第1组，接下来的83⾏将分在第2组，在接下来的83⾏将分在第3组，以此类推。NTILE函数在逻辑上需要依赖于ROW_NUMBER函数。整个过程是先根
据对val的排序结果，为每⼀⾏分配⾏号；在基于前⾯计算好的组的⼤⼩83⾏，将第1⾏到第83⾏分配到第1组，将第84⾏到第166⾏分配到第2组，以此类推。如果组数⽆法整除表的⾏数，余数（remainder）中的每⼀⾏会被分配到最前⾯的每个组。例如，假设有102⾏，请求分成5组，那么前两组将有21⾏⽽不是20⾏。
  和聚合开窗函数类似，排名函数也⽀持在OVER⼦句中使⽤PARTITION BY语句。以排名计算作为背景，理解PARTITION BY⼦句的含义可能要更容易些。例如，与在整个集合中分配⾏号不同，表达式ROW_NUMBER() OVER(PARTITION BY custid ORDER BY val)为各⾏中具有相同custid的⼦集独⽴地分配⾏号。在以下查询中使⽤了这个表达式：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。