SQL练习--某书第七章（窗口函数

20190422更新重点在最后一题，有空看第一题

重要的事情说三遍

窗口函数只能自select和select顺序之后的命令中使用（orderby可以）但是where不可以

窗口函数只在orderby之前，但是在join where等的后面～

下面详细介绍一下sum以便于理解

pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号

pv2: 同pv1

pv3: 分组内(cookie1)所有的pv累加

pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号， 13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号

pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21

pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，14号=14号+15号+16号=2+4+4=10

如果不指定ROWS BETWEEN,默认为从起点到当前行;
如果不指定ORDER BY，则将分组内所有值累加;
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，
　　UNBOUNDED PRECEDING 表示从前面的起点，
　　UNBOUNDED FOLLOWING：表示到后面的终点
–其他AVG，MIN，MAX，和SUM用法一样。

举个例子

参考链接：https://www.zhihu.com/people/mu-zi-li-36-52/activities

max和min

https://www.cnblogs.com/yy3b2007com/p/8666927.html

MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 分区里面的从起点到当前行的最小值【有累积的含义】
MAX(pv) OVER(PARTITION BY cookieid) AS pv3 -- 分区里面的所有行的最小值

SELECT custid,orderid,qty,
      qty - LAG(qty) OVER(PARTITION BY custid
							  ORDER BY orderdate,orderid) AS diffprec,
	  qty - LEAD(qty) OVER(PARTITION BY custid
							  ORDER BY orderdate,orderid) AS diffprec,
  FROM dbo.Orders;
-- 自己做的 
SELECT empid,
         SUM(CASE WHEN YEAR(orderdate) = 2007 THEN 1 ELSE 0 END）AS cnt2007,
         SUM(CASE WHEN YEAR(orderdate) = 2008 THEN 1 ELSE 0 END）AS cnt2008,
         SUM(CASE WHEN YEAR(orderdate) = 2009 THEN 1 ELSE 0 END）AS cnt2009
  FROM dbo.Orders
 GROUP BY empid,
-- 答案做法
SELECT empid,
	   COUNT(CASE WHEN orderyear = 2007 THEN orderyear ELSE 0 END）AS cnt2007,
	   COUNT(CASE WHEN orderyear = 2007 THEN orderyear ELSE 0 END）AS cnt2007,
	   COUNT(CASE WHEN orderyear = 2007 THEN orderyear ELSE 0 END）AS cnt2007,
  FROM (
        SELECT empid,YEAR(orderdate) AS orderyear
          FROM dbo.Orders) AS D
 GROUP BY empid,

-- 注意sum和count，count只计数，只要满足条件不是null就计算1，不算具体的（有点类似exists

SELECT *
  FROM (SELECT empid,orderyear,
          CASE orderyear
            WHEN 2007 THEN cnt2007
            WHEN 2008 THEN cnt2008
            WHEN 2009 THEN cnt2009
		   END AS numorders
	      FROM dbo.EmpYearOrders
          CROSS JOIN (VALUES(2007),(2008),(2009)) AS YEARS(orderyear) AS D
  WHERE numorders <> 0;
       CASE WHEN cnt2007
-- 先外联结 从3行变成3*3行，
-- 之后 select变成想要的表
-- 之后 where过滤

后面的3个题都要看