盘点4种计算数组中元素值为1的个数方法

大家好，我是热心读者。前几天在群里看到有人问了这样一道题，我觉得对一些新手了解窗口函数很有裨益，因此拿出来以飨读者。

至于为什么要拿窗口函数来说事儿呢？因为目前的数分面试，只要考sql，窗口函数是100%会问的。从另一个侧面来讲，窗口函数是检验你的SQL的试金石，一验一个准，比目前的核酸检测水平都高。

好了，闲言少叙，我们来解题。

窗口函数定义

按照分类来讲，一类是专门的窗口函数：row_number(),rank(),dense_rank()等;一类是聚合函数：sum(),avg()等。

按照功能来讲，窗口函数是在不损失行数的背景下，按照指定维度进行分组，按照指定维度进行排序的一种排序函数，聚合等作用的函数，窗口函数的熟练程度决定了你SQL的熟练程度，而在面试中是一道必考题，在业务实践中也是一道迈不过去的坎儿。

窗口函数表达式

这里以row_number()为例，来说明一下表达式的含义，因为万变不离其宗，对于基础我们要了然于心。

row_number() over([parition by 维度] order by 维度 asc [desc])

[partition by 维度]  该部分可以省略，表按照某指定维度进行分组

order by 维度 该部分不允许为空，表按照某维度进行升序(或降序)排序

row_number()函数是用来分组排序的，排序不重复，此处大家可以百度一下跟rank和dense_rank排序的区别。

我们先“由俭入奢”，从最常规的开始处理。题目被我稍作修改

窗口函数实战——基础版

如图：

[图片上传失败...(image-cc12b1-1740302603356)]

根据题意我们会发现，这里是完成的排序是按照id和cat字段分组，按照time字段进行排序，发现了这个规律我们就套用窗口函数的基本语法，即可完成题目的要求，下面是脚本：

  `#第一步 构造数据  insert into  test values('2020-10-02 12:30:45','A','AAA');  insert into  test values('2020-10-02 12:30:55','A','AAA');  insert into  test values('2020-10-02 14:39:45','A','BBB');  insert into  test values('2020-10-02 14:40:55','A','BBB');  insert into  test values('2020-10-02 15:30:05','A','AAA');  insert into  test values('2020-10-02 16:30:45','B','AAA');  insert into  test values('2020-10-02 17:04:45','B','BBB');    # 脚本  select   time,  id,  cat,  row_number() over(partition by id,cat order by time asc) as rnk  from test  order by time asc  # 加入order by 语句为了让数据展示的更清楚，别无他用` 

看下效果：

[图片上传失败...(image-9ca9b3-1740302603356)]

窗口函数实战——进阶版

具体的题目是这样的：

[图片上传失败...(image-ea40ef-1740302603356)]

具体的题目描述如下：

第一列是事件发生的时间，第二列是用户id，第三列是事件分组，第四列是我想要打的排序，如果事件分组和上一个事件不一致，或者用户id不一致的话，就要重新计数

根据图片和描述的情景，我们发现跟上一道题有一点点差别，就是数据顺序已经按照时间排好了序，如果id和cat相同，则进行顺序排序；如果id和cat不同，则要重新从1进行排序。

思路：

1、要想实现上述效果，也就是需要第三列辅助列跟id和cat一起进行分组，而第三列辅助列应该满足以下：

按照id和cat分组连续，则第三列辅助列应该为相同的值；如果id和cat发生了改变，则第三列辅助列应该随之改变，并且需要保证第三列辅助列在每个分组内的值唯一。

2、如果有了第三列辅助列，那么我们套用窗口函数公式即可将题意中顺序排出来。

以下为脚本：

  `# 1 构造数据 以题一中数据为例    # 2 脚本 a 构造连续排序  # 备注 原题中时间标记为1 2 3 ,而实际中时间肯定为标准的时间戳形式，因此需要通过连续数来构造  # 加with temp1 as这个伪脚本为了下面脚本引用方便而已，实际运行中请忽略  with temp1 as   select   time,  id,  category,  concat_ws('-',id,category) as add_col ,  row_number() over(order by time asc) as order_rnk  -- 按时间顺序计算连续排序，构造连续数序列  -- 此处省略partition by 说明实际中partition可以省略  from test` 

示意如图：

[图片上传失败...(image-d7af10-1740302603356)]

而我们通过这个连续数序列想要判断哪些数是连续的，哪些数又是不连续的，接着看脚本

  `with temp2 as   select  time,  id,  category,  add_col,  order_rnk,  order_rnk-lag(order_rnk,1,order_rnk-1) over(partition by add_col order by time asc) as order_rnk_lag1  from temp1  # 这里需要解释下几个知识点：  # 窗口函数lag可以理解为拖后的意思，即按照指定维度分组，指定维度排序，将某列向下平移n行，空值用第三个参数默认  # 因而本文的意思就是将order_rnk这个连续数序列按照add_col这个辅助列，组内向下平移1行，如果是空值，  # 用 order_rnk-1设置为默认值  # 为什么要这样处理：因为我们第一步配置的连续数序列，相邻两行作差，如果差值为1，则连续，否则说明组内出现了不连续的情况` 

示意如图：

[图片上传失败...(image-fda13c-1740302603356)]

通过红色框，我们可以看出来，我们把同一个add_col内的不连续区分了开来，此时，我们就可以有“山重水复疑无路，柳暗花明又一村”的感觉了，我们把add_col 和order_cnk_lag1再作为一个分组的依据，再排序，题意可解，脚本如下：

  `with temp3 as   select   time,  id,  catgory,  row_number() over(parition by concat(add_col,order_cnk_lag1) order by time asc) as rnk  from temp2` 

以上。

[图片上传失败...(image-ae4918-1740302603356)]

小伙伴们，快快用实践一下吧！如果在学习过程中，有遇到任何问题，欢迎加我好友，我拉你进Python学习交流群共同探讨学习。