笔记：index/match函数及窗口函数

读书笔记

数据分析师手记

EXCEL函数 index和match

=index （查找的区域，区域内第几行，区域内第几列）
=match（查找指定的值，查找所在区域，查找方式的参数）

窗口函数

over(partition by xxxx order by xxxx rows | range  between xxxx and xxxx)

其中 partition by 为按照什么分组，order by表述按照什么排序，rows/range between表示窗口范围的定义。

ROWS选择前／后几行，例如ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING表示当前行往前3行到往后3行，一共7行数据（或小于7行，如果碰到了边界）​。所以ROWS是通过排序后的前后位置选取窗口范围。

RANGE选择数据范围，例如RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING表示选取取值在[c-3,c+3]范围内的行，其中c为当前行的值。所以RANGE是通过数值的大小选取窗口范围。

窗口函数的分类

• 聚合：avg() count() min() max() sum()
• 排序：rank() dense_rank() row_number()
• 极值： first_value() last_value()
• 位移： lead() lag()
• 分箱：ntile()
  解释：
  • ROW_NUMBER从1开始，按照ORDER BY的顺序，值相等时排名不出现并列。
  • RANK与ROW_NUMBER类似，只是值相等时排名会并列，并会在名次中跳过并列排名继续排名。
  • DENSE_RANK与ROW_NUMBER类似，只是值相等时排名会并列，并会在名次中紧接着并列的排名继续排名。
  • FIRST_VALUE()是该分组内截至当前行的第一条记录。
  • LAST_VALUE()是该分组内截至当前行的最后一条记录。
  • 如果我们要取当前记录的前／后几位的数据，需要用到位移函数LAG或LEAD。LAG或LEAD是按照排序规则，取前多少位或后多少位，参数有3个：第1个是要取出来的列；第2个是移动多少位；第3个是如果取不到赋予的值，默认为NULL。
  • NTILE(N)可以将数据按照顺序划分成N组，编号从1开始，对于每一行，返回此行所属的组的编号，NTILE(3)表示将表切分为3组，NTILE也可以在PARTITION BY分组后分箱，表示在当前的组内进行分箱。

实例一

实例二

实例三


实例四


实例五