一.窗口函数有什么用
聚合函数对结果集进行计算并且通常只返回一行，只会返回聚合的结果和聚合的那列。如果需要返回聚合列之外的列的结果则可以使用窗口函数。窗口函数也是基于结果集计算，与聚合函数不同的是窗口函数会把计算后的结果合并到原来的结果集上，返回多行并保持与结果集相同的行数。
可以使用窗口函数的业务场景：

排名问题：比如每个部门按业绩来排名

topN问题：比如找出每个部门排名前N的员工进行奖励

二.窗口函数语法
除了pg内置的窗口函数rank()，dense_rank()， row_numbe()，lag()等，聚合函数或自定函数接OVER也可作为窗口函数。
窗口函数需要放在SELECT子句中，SQL中语法如下：
<窗口函数> OVER(PARTITION BY <分组列名> ORDER BY <排序列名>)

SQLAlchemy 下可以这样使用：


三.窗口函数怎么用
1.排名问题

如下学生成绩表（stu_score）：




stu_id
class
score




0001
1
90


0002
1
65


0003
2
70


0004
1
65


0005
2
80


0006
3
75


0007
3
95


使用窗口函数取每个班级内按成绩的排名：
SELECT 
	*, 
  RANK() OVER(PARTITION BY class ORDER BY score DESC) AS ranking 
 FROM stu_score;

结果为：




stu_id
class
score
ranking




0001
1
90
1


0002
1
65
2


0004
1
65
2


0005
2
80
1


0003
2
70
2


0007
3
95
1


0006
3
75
2


2.topN问题

取学生成绩表每个班成绩排名第一的学生：
SELECT * 
FROM
	(SELECT *, ROW_NUMBER() OVER (
    PARTITION BY class ORDER BY score DESC
  ) AS ranking FROM stu_score) t1 
WHERE ranking = 1;

结果：




stu_id
class
score
ranking




0001
1
90
1


0005
2
80
1


0007
3
95
1


3.举一个实际中遇到的问题是怎么用窗口函数解决的

场景是，现在有一个表有user_id（用户id）、log_date（记录日期）和其他信息，需要把一批用户最新的记录日期的那条记录取出来并且能获取其他信息。

有内味了，这不就是topN问题。具体思路就是按user_id分组，log_date排倒叙取第一条。解决如下：
SELECT * 
FROM
	(SELECT *, ROW_NUMBER() OVER (
    PARTITION BY user_id ORDER BY log_date DESC
  ) AS log_date_rank FROM TABLE) t1 
WHERE log_date_rank = 1;

3.rank、dense_rank、row_number区别

rank 是正常排序，当并列时行号重复，并且产生行号间隙。

dense_rank 是紧密排序，当并列时行号重复，但是不产生间隙，依然按自然数顺延。

row_number 是行数排序，不考虑并列的情况，按照行数依次排序。


四.总结
1）窗口函数同时具有分组（partition by）和排序（order by）的功能。

2）不会改变结果集的行数，可以取到所有列的结果。

#每天一点点#

python 空气质量AQI数据分析与预测
点击可以查看 分析：用到的库，数据集，数据清洗
点击可以查看 分析：相关系数矩阵
#四：数据分析

#1：空气质量最好/最差的5个城市

#最好的5个城市

#sort_values对列进行排序
t = data[['City','AQI']].sort_values('AQI')  #只取城市和空气质量，按照空气质量排序
display(t.iloc[:5]) #取最好的前5个
sns.barplot(x = 'City',y = 'AQI',data = t.iloc[:5]) #柱状图

输出结果 ??????????



输出结果???????????
#最差的5个城市
display(t.iloc[-5:]) #最差的5个，字段可以执行了看下
sns.barplot(x= 'City',y = 'AQI',data = t.iloc[-5:])

输出结果 ??????????



输出结果???????????
#2：临海城市是否空气质量优于内陆城市？

#首先统计下临海城市与内陆城市的数量

#根据 "Coastal	是否沿海 "字段来统计城市数量
display(data['Coastal'].value_counts()) 
sns.countplot(x = 'Coastal',data = data) #直方图

输出结果 ??????????


输出结果???????????
#然后观察临海城市和内陆城市的散点图
sns.swarmplot(x = 'Coastal',y = 'AQI',data = data)

输出结果 ??????????



输出结果???????????
#分组计算空气质量的均值
display(data.groupby ('Coastal')['AQI'].mean())  #均值
sns.barplot(x = 'Coastal',y = 'AQI',data = data)  #柱状图

输出结果 ??????????



输出结果???????????
#柱形图仅仅能进行均值比较，再用箱线图显示更多信息
sns.boxplot(x = 'Coastal',y = 'AQI',data = data)

输出结果 ??????????

输出结果???????????
#可以绘制小提琴图，看出分布的密度
sns.violinplot(x = 'Coastal', y = 'AQI',data = data)

输出结果 ??????????

输出结果???????????
#将散点与箱线图或小提琴图组合在一起
sns.violinplot(x= 'Coastal',y='AQI',data = data,inner = None)
sns.swarmplot(x = 'Coastal',y = 'AQI',color = 'g',data = data)

输出结果 ??????????



输出结果???????????

oracle分析函数是对分组后的数据进行排序
这里用emp表和dept表做例子。
题目：求每个部门工资在前四名的员工信息

有三种情况：
1.用rank(),排名不连续，跳跃性。
select * from(      
   select e.*,
          rank() over (partition by e.deptno order by e.sal desc) drank 
          from emp e) where drank<=3
 数据结果：


 
2.使用dense_rank()，排名连续
   select * from(      
   select e.*,
          dense_rank() over (partition by e.deptno order by e.sal desc) drank 
          from emp e) where drank<=4
 数据结果

 
 
3.使用 row_number()。排名相同则按员工编号升序。连续。
   select * from(      
   select e.*,
          row_number() over (partition by e.deptno order by e.sal desc,e.empno asc) drank 
          from emp e) where drank<=4
 数据结果


 
 
总结，sql server也有这样的函数。
插一句：行转列参考：[url]http://www.iteye.com/topic/1112111[/url]
下面给出一个通用的写法，不用数据库自己提供的函数。不过没有分析函数这么方便
 
select * from 表名 a where (select count(1) from 表名 where 分组字段=a.分组字段   
and 排序字段>=a.排序字段)<=每组前几名 order by 分组字段 ,
排序字段desc



select * from(
select deptno,sal
,(select count(*) from emp where deptno=e.deptno and sal>=e.sal) rk
from emp e
order by deptno,sal desc) where rk<=5;


--一个行转列的例子，使用decode函数
如
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为 
语文 数学 英语
student1 80 70 60
student2 90 80 100

--sql语句
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student

这是一种方法：你基本上重写你的排序函数来获取排序函数列表，每个排序函数比较你想要测试的属性，在每个排序测试中，你看，看看cmp函数是否返回非零返回如果是这样打破并发送返回值 . 您可以通过调用Lambda列表函数的Lambda来调用它 .
它的优点是它可以单独传递数据，而不是像其他方法那样的前一种数据 . 另一件事是它排序到位，而排序似乎复制 .
我用它来编写一个排名函数，它对每个对象在一个组中的类列表进行排序，并且有一个得分函数，但是你可以添加任何属性列表 . 注意非lambda式，虽然hackish使用lambda来调用setter . 排名部分不适用于列表数组，但排序将 .
#First, here's  a pure list version
my_sortLambdaLst = [lambda x,y:cmp(x[0], y[0]), lambda x,y:cmp(x[1], y[1])]
def multi_attribute_sort(x,y):
r = 0
for l in my_sortLambdaLst:
r = l(x,y)
if r!=0: return r #keep looping till you see a difference
return r
Lst = [(4, 2.0), (4, 0.01), (4, 0.9), (4, 0.999),(4, 0.2), (1, 2.0), (1, 0.01), (1, 0.9), (1, 0.999), (1, 0.2) ]
Lst.sort(lambda x,y:multi_attribute_sort(x,y)) #The Lambda of the Lambda
for rec in Lst: print str(rec)
这是一种对对象列表进行排名的方法
class probe:
def __init__(self, group, score):
self.group = group
self.score = score
self.rank =-1
def set_rank(self, r):
self.rank = r
def __str__(self):
return '\t'.join([str(self.group), str(self.score), str(self.rank)])
def RankLst(inLst, group_lambda= lambda x:x.group, sortLambdaLst = [lambda x,y:cmp(x.group, y.group), lambda x,y:cmp(x.score, y.score)], SetRank_Lambda = lambda x, rank:x.set_rank(rank)):
#Inner function is the only way (I could think of) to pass the sortLambdaLst into a sort function
def multi_attribute_sort(x,y):
r = 0
for l in sortLambdaLst:
r = l(x,y)
if r!=0: return r #keep looping till you see a difference
return r
inLst.sort(lambda x,y:multi_attribute_sort(x,y))
#Now Rank your probes
rank = 0
last_group = group_lambda(inLst[0])
for i in range(len(inLst)):
rec = inLst[i]
group = group_lambda(rec)
if last_group == group:
rank+=1
else:
rank=1
last_group = group
SetRank_Lambda(inLst[i], rank) #This is pure evil!! The lambda purists are gnashing their teeth
Lst = [probe(4, 2.0), probe(4, 0.01), probe(4, 0.9), probe(4, 0.999), probe(4, 0.2), probe(1, 2.0), probe(1, 0.01), probe(1, 0.9), probe(1, 0.999), probe(1, 0.2) ]
RankLst(Lst, group_lambda= lambda x:x.group, sortLambdaLst = [lambda x,y:cmp(x.group, y.group), lambda x,y:cmp(x.score, y.score)], SetRank_Lambda = lambda x, rank:x.set_rank(rank))
print '\t'.join(['group', 'score', 'rank'])
for r in Lst: print r