本文转载请联系舒哥公众号。在上一期的SQL专题中，我们以连续活跃度为例，介绍了超前滞后错位减法的用法。但是如果不使用位错减法，其实也可以达到这个目的。这次我们先说说最大连续活跃天数的第二种解法，再说说另一个常见的面试SQL题。该方法是利用row_number为每一行生成一个连续的序号。这样，在连续活跃的时间段内，就有了比较值，通过日期和行号就可以得到差值。在连续活动的时间段内，差值是一样的，那么我们可以根据这个差值分组，进一步计算本次连续活动了多少天。SQL如下WITHactiveAS(SELECT100UID,'2021-04-01'dtUNIONALLSELECT101UID,'2021-04-01'dtUNIONALLSELECT102UID,'2021-04-01'dtUNIONALLSELECT103UID,'2021-04-01'dtUNIONALLSELECT100UID,'2021-04-02SELECT10UID10UID10UNIONALL,'2021-04-02'dtUNIONALLSELECT102UID,'2021-04-02'dtUNIONALLSELECT103UID,'2021-04-02'dtUNIONALLSELECT104UID,'2021-04-02'dtUNIONALLSELECT100UID,'2021-04-03'LETUNIONALLSELECT100UID-,'2021-03'LETUNIONALLSELECT100UID,'2021-04-03'LETUNIONALLSELECT100UID-03'dtUNIONALLSELECT101UID,'2021-04-04'dtUNIONALLSELECT102UID,'2021-04-04'dtUNIONALLSELECT103UID,'2021-04-04'dtUNIONALL-04-SELECT102UID04'dtUNIONALLSELECT105UID,'2021-04-04'dtUNIONALLSELECT102UID,'2021-04-03'dt)SELECTUID,max(days)daysFROM(SELECTdate_sub(dt,rn),UID,count(DISTINCTdt)daysFROM(SELECTUID,dt,row_number)()over(PARTITIONBYUIDORDERBYdt)rnFROMactive)xGROUPBYdate_sub(dt,rn),UID)yGROUPBYUID结果如下：至此，连续活动的问题告一段落。再来看一个常见的问题：2.行列转换(1)对于一个直播产品，live中有liveid(直播id)和invite_list(邀请名单，字符串，用户id用“,”分隔)字段直播记录表直播。观看记录表记录中有uid（用户id）、liveid（直播id）、duration（观看时长）。尝试找出邀请列表中所有用户的观看时间。由于存在邀请用户没有进入房间观看的问题，我们直接从查看记录开始可能不一定包括列表中的所有用户，所以需要参考直播记录中的邀请列表。这就需要使用explode和lateralview把这个字符串类型转换成行记录。还是用with构造了两个简单的临时表，lives和records，详细SQL如下：withlivesas(select100liveid,'A01,A02,A03,A04'invite_listunionallselect101liveid,'A05,A06,A07,A08'invite_list),recordas(select100liveid,'A01'uid,30durationunionallselect100liveid,'A02'uid,50durationunionallselect100liveid,'A03'uid,15durationunionallselect101liveid,'A07'uid,20durationunionallselect101liveid,'A08'uid,60duration)selectcordlist.liveidis,list.reduration),duration(selectliveid,ulistfromliveslateralviewexplode(split(invite_list,','))uidasulist)listleftjoinrecordonlist.liveid=record.liveidandlist.ulist=record.uid这里简单解释一下：侧视图是调用原表的explode函数，Decompose将一个数组或映射字段分成多行并用作临时表，然后将其与其他字段组合。(2)APP用户访问页面记录记录，包含uid(用户id)和pageid(页面id)字段，在一个字段中输出每个用户的访问路径。上一题是columntorow，这道题是rowspecialcolumn。使用collect_set将group下某个字段的所有记录聚合成一个列表，然后使用concat_ws函数将数组转换为字符串，并用“>”连接起来。withRecordas（select100uid，'a01'pageIdunionAllSelect100UID，'a02'pageIdunionAllSelect100UID，'a03'pageidunionallect101uid，'a07'pageidunionallect101uid留存率在任何时候都是重中之重。所以在数据分析的面试中，关于留存率的SQL题出现频率也是极高的。所谓的“留存率”通常定义了一个初始行为和一个后续行为。N天后满足初始条件的用户，剩下的比例就是留存率。在发散方面，回访、重看等概念也与留存类似。例如：一个APP新注册的用户表user包含字段uid（用户id）和dt（注册日期），一个active表active有字段uid（用户id）和dt（激活日期）。求每天新增用户的第二天留存率，后2天留存率，后3天留存率...后7天留存率本题以新注册为初始行为，并积极作为后续行为。显然，我们可以将初始行为和后续行为做一个左连接，然后根据注册日期进行聚合，找到对应的新用户和接下来N天的留存用户数。WITHuseras(SELECT100UID,'2021-01-01'dtUNIONALLSELECT101UID,'2021-01-01'dtUNIONALLSELECT102UID,'2021-01-01'dtUNIONALLSELECT103UID,'2021-01-02'dtUNIONALLSELECT104UID,'2021-01-02'dtUNIONALLSELECT12021-01-02'dtUNIONALLSELECT106UID,'2021-01-02'dt),activeas(select100UID,'2021-01-01'dtunionallselect100UID,'2021-01-02'dtunionallselect100UID,'2021-01-03'dtunionallselect100UID,'2021-01-05'dtunionallselect100UID,'2021-01-07'dtunionallselect101UID,'2021-01-01'dtunionallselect101UID,'2021-01-07'dtunionallselect102UID,'2021-01-01'dtunionallselect103UID,'2021-01-01'dtunionallselect103UID,'2021-01-02'dtunionallselect103UID,'2021-01-03'dtunionallselect103UID,'2021-01-05'dtunionallselect104UID,'2021-01-02'dtunionallselect104UID,'2021-01-03'dtunionallselect103UID,'2021-01-03'dtunionallselect104UID,-01-04'dtunionallselect105UID,'2021-01-02'dtunionallselect105UID,'2021-01-03'dtunionallselect105UID,'2021-01-04'dtunionallselect105UID,'2021-01-05'dtunionallselect105UID,'2021-01-07'国民党select106UID,'2021-01-02'dtunionallselect106UID,'2021-01-04'dt)selectuser.dt,count(distinctuser.uid)new_user,count(distinctif(datediff(active.dt,user.dt)=1,活动.uid,null))retain1,count(distinctif(datediff(active.dt,user.dt)=2,active.uid,null))retain2,count(distinctif(datediff(active.dt,user.dt)=3,active.uid,null))retain3,count(distinctif(datediff(active.dt,user.dt)=4,active.uid,null))retain4,count(distinctif(datediff(active.dt,user.dt))=5,active.uid,null))retain5,count(distinctif(datediff(active.dt,user.dt)=6,active.uid,null))retain6,count(distinctif(datediff(active.dt,user.dt)=7,active.uid,null))retain7fromuserleftjoinactiveonuser.uid=active.uidgroupbyuser.dt结果如下：这种方法可能比较麻烦，需要为每个retentionindicator单独写一个字段，省事儿子，你可以单独计算每天的新增人数，然后将新增表与活跃表连接起来，找到下图中每个注册日期的活跃数wingdays，然后将新号与活跃号相关联，从而得到可以保留任意期间。
