面试常问问题之SQL连续问题@SQL连续**问题编辑于2024-10-10在我们面试过程当中，经常会被问到经典的SQL连续问题，可能是连续10天活跃用户数，可能是连续十天登录的用户，也可能是计算用户的持仓天数。下面我们综合chatGPT和力扣来进行一下SQL连续问题的一个汇总。补充部分：问题2是实际面试题，大家有什么好的解法可以打在评论区。其他问题会存在两个解法，解法1是在力扣官网当时跑通的解法，不一定清晰，也不一定能覆盖所有情况，只能覆盖网站提供的用例。解法2是结合AI判题的改进版本，并且会考虑到实际业务中遇到的多样化情况，请各位斟酌判断后进行选择。问题1：sql连续十天活跃用户数怎么算要计算连续十天活跃用户数，我们可以使用SQL中的窗口函数（LEAD和SUM）来实现。以下是一个示例，假设我们有一个名为user_activity的表，其中包含两列：user_id和activity_dateSELECTCOUNT(DISTINCTuser_id)ASactive_usersFROM(SELECTuser_id,SUM(is_consecutive)OVER(PARTITIONBYuser_idORDERBYactivity_date)ASconsecutive_daysFROM(SELECTuser_id,activity_date,CASEWHENactivity_date=DATE_ADD(LAG(activity_date)OVER(PARTITIONBYuser_idORDERBYactivity_date),INTERVAL1DAY)THEN1ELSE0ENDASis_consecutiveFROMuser_activity)ASuser_activity_with_flag)ASusers_with_consecutive_daysWHEREconsecutive_days=9;但是上述代码，有个问题，就是SUM(is_consecutive) OVER (PARTITION BY user_id ORDER BY activity_date) AS consecutive_days的这一步会导致断开的部分依然在累加。所以进行改写：SELECTCOUNT(DISTINCTuser_id)ASactive_usersFROM(SELECTuser_id,DATE_SUB(activity_date,INTERVALROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYactivity_date)DAY)ASgrpFROMuser_activity)tGROUPBYuser_id,grpHAVINGCOUNT(*)=10;问题2：sql计算连续持仓小时数怎么算问题3：连续递增交易编写一个 SQL 查询，找出至少连续三天 amount 递增的客户。并包括 customer_id 、连续交易期的起始日期和结束日期。一个客户可以有多个连续的交易。表: Transactions+------------------+------+|字段名|类型|+------------------+------+|transaction_id|int||customer_id|int||transaction_date|date||amount|int|+------------------+------+transaction_id 是该表的主键。每行包含有关交易的信息，包括唯一的 (customer_id, transaction_date)，以及相应的 customer_id 和 amount。返回结果并按照 customer_id 升序 排列。查询结果的格式如下所示selectcustomer_id,min(transaction_date)asconsecutive_start,max(transaction_date)asconsecutive_endfrom(selectcustomer_id,transaction_date,sum(casewhenamountlast_amountthen0else1end)over(partitionbycustomer_idorderbytransaction_dateasc)asflag_amount,date_sub(transaction_date,intervalrnday)asflag_dayfrom(selecttransaction_id,customer_id,transaction_date,amount,lag(amount,1,0)over(partitionbycustomer_idorderbytransaction_dateasc)aslast_amount,row_number()over(partitionbycustomer_idorderbytransaction_dateasc)asrnfromTransactions)t1)t2groupbycustomer_id,flag_amount,flag_dayhavingcount(1)2orderbycustomer_idasc;1.lag(amount, 1,0) 意思是：“在当前行的前 1 行中，取 amount 列的值；如果前面没有行（比如分区内第一行），就返回默认值 0。”默认amount初始值是0，但是实际中可能存在初始值=0，则amount 0 为假，第一行会被误判为"非递增"，导致第一段被错误分割。2.另外，如果单日有多笔交易，order by transaction_date asc只有这一个排序是不够的，所以，需要新增交易ID作为排序字段。3.多层嵌套子查询不够清晰，清晰的表达式如下：WITHorderedAS(SELECTcustomer_id,transaction_date,amount,-- 取上一笔金额（无默认值，第一行自然为 NULL）LAG(amount)OVER(PARTITIONBYcustomer_idORDERBYtransaction_date,transaction_id)ASlast_amount,ROW_NUMBER()OVER(PARTITIONBYcustomer_idORDERBYtransaction_date,transaction_id)ASrnFROMTransactions),flaggedAS(SELECTcustomer_id,transaction_date,-- 金额段标识：下跌/持平时 +1SUM(CASEWHENamountlast_amountTHEN0ELSE1END)OVER(PARTITIONBYcustomer_idORDERBYtransaction_date,transaction_id)ASamt_grp,-- 日期连续性标识DATE_SUB(transaction_date,INTERVALrnDAY)ASdate_grpFROMordered)SELECTcustomer_id,MIN(transaction_date)ASconsecutive_start,MAX(transaction_date)ASconsecutive_endFROMflaggedGROUPBYcustomer_id,amt_grp,date_grpHAVINGCOUNT(*)2-- 至少连续 3 天ORDERBYcustomer_id;解析：用户发生连续交易通用解法，使用 日期 - row_number做标识即可连续交易的几天中交易金额持续增长1.逐日判断当日金额是否大于前一天金额，大于则标识0否则标识1。目的是为了使用1作为“不连续”情况的分界线2.开窗累加标识，当存在不连续情况的“分界线”时累计值会发生变化，即可识别金额的连续增长情况。问题4：连续空余座位表: Cinema+-------------+------+|ColumnName|Type|+-------------+------+|seat_id|int||free|bool|+-------------+------+Seat_id 是该表的自动递增主键列。在 PostgreSQL 中，free 存储为整数。请使用 ::boolean 将其转换为布尔格式。该表的每一行表示第 i 个座位是否空闲。1 表示空闲，0 表示被占用。查找电影院所有连续可用的座位。返回按 seat_id 升序排序 的结果表。测试用例的生成使得两个以上的座位连续可用。selectdistinctseat_idfrom(selectseat_id,free,sum(free)over(rowsbetween1precedingandcurrentrow)asflag1,sum(free)over(rowsbetweencurrentrowand1following)asflag2fromCinema)awhereflag11orflag21orderbyseat_id;解析：当flag1等于2时，此座位连续，但是无法识别连续座位的第一个，因为上一个是0，这一个是1，之和为1。当flag2等于2时，此座位连续，但是无法识别连续座位的最后一个，因为下一个是0，这一个是1，之和为1。所以通过同时判断flag1和flag2，可以得到连续座位。问题5：连续空余座位2SQL SchemaPandas Schema表：Cinema+-------------+------+|ColumnName|Type|+-------------+------+|seat_id|int||free|bool|+-------------+------+seat_id 是这张表中的自增列。这张表的每一行表示第 i 个作为是否空余。1 表示空余，而 0 表示被占用。编写一个解决方案来找到电影院中 最长的空余座位 的 长度。注意：保证 最多有一个 最长连续序列。如果有 多个 相同长度 的连续序列，将它们全部输出。返回结果表以 first_seat_id 升序排序。selectmin(seat_id)asfirst_seat_id,max(seat_id)aslast_seat_id,count(seat_id)asconsecutive_seats_lenfrom(selectseat_id,free,flag1,flag2from(selectseat_id,free,sum(free)over(rowsbetween1precedingandcurrentrow)asflag1,sum(free)over(rowsbetweencurrentrowand1following)asflag2fromCinema)awhereflag11orflag21)b解析：非常规做法，根据连续空余座位得出思路，先找到连续的座位，然后再找连续座位中最小的就是开始座位，最大的就是结束座位，count计数一共有多少个连续的座位。风险当多次连续时可能无法计算。WITHt1AS(# 找出空闲座位SELECTseat_idFROMCinemaWHEREfree=1),t2AS(# 计算seat_id与row_number之差，num相同的代表连续空闲SELECTseat_id,seat_id-ROW_NUMBER()OVER(ORDERBYseat_idASC)ASnumFROMt1),t3AS(SELECTMIN(seat_id)ASfirst_seat_id,# 连续空闲开始的seat_idMAX(seat_id)ASlast_seat_id,# 连续空闲结束的seat_idCOUNT(*)ASconsecutive_seats_len,# 空闲座位数DENSE_RANK()OVER(ORDERBY
面试常问问题之SQL连续问题@SQL连续**问题编辑于2024-10-10在我们面试过程当中，经常会被问到经典的SQL连续问题，可能是连续10天活跃用户数，可能是连续十天登录的用户，也可能是计算用户的持仓天数。下面我们综合chatGPT和力扣来进行一下SQL连续问题的一个汇总。补充部分：问题2是实际面试题，大家有什么好的解法可以打在评论区。其他问题会存在两个解法，解法1是在力扣官网当时跑通的解法，不一定清晰，也不一定能覆盖所有情况，只能覆盖网站提供的用例。解法2是结合AI判题的改进版本，并且会考虑到实际业务中遇到的多样化情况，请各位斟酌判断后进行选择。问题1：sql连续十天活跃用户数怎么算要计算连续十天活跃用户数，我们可以使用SQL中的窗口函数（LEAD和SUM）来实现。以下是一个示例，假设我们有一个名为user_activity的表，其中包含两列：user_id和activity_dateSELECTCOUNT(DISTINCTuser_id)ASactive_usersFROM(SELECTuser_id,SUM(is_consecutive)OVER(PARTITIONBYuser_idORDERBYactivity_date)ASconsecutive_daysFROM(SELECTuser_id,activity_date,CASEWHENactivity_date=DATE_ADD(LAG(activity_date)OVER(PARTITIONBYuser_idORDERBYactivity_date),INTERVAL1DAY)THEN1ELSE0ENDASis_consecutiveFROMuser_activity)ASuser_activity_with_flag)ASusers_with_consecutive_daysWHEREconsecutive_days=9;但是上述代码，有个问题，就是SUM(is_consecutive) OVER (PARTITION BY user_id ORDER BY activity_date) AS consecutive_days的这一步会导致断开的部分依然在累加。所以进行改写：SELECTCOUNT(DISTINCTuser_id)ASactive_usersFROM(SELECTuser_id,DATE_SUB(activity_date,INTERVALROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYactivity_date)DAY)ASgrpFROMuser_activity)tGROUPBYuser_id,grpHAVINGCOUNT(*)=10;问题2：sql计算连续持仓小时数怎么算问题3：连续递增交易编写一个 SQL 查询，找出至少连续三天 amount 递增的客户。并包括 customer_id 、连续交易期的起始日期和结束日期。一个客户可以有多个连续的交易。表: Transactions+------------------+------+|字段名|类型|+------------------+------+|transaction_id|int||customer_id|int||transaction_date|date||amount|int|+------------------+------+transaction_id 是该表的主键。每行包含有关交易的信息，包括唯一的 (customer_id, transaction_date)，以及相应的 customer_id 和 amount。返回结果并按照 customer_id 升序 排列。查询结果的格式如下所示selectcustomer_id,min(transaction_date)asconsecutive_start,max(transaction_date)asconsecutive_endfrom(selectcustomer_id,transaction_date,sum(casewhenamountlast_amountthen0else1end)over(partitionbycustomer_idorderbytransaction_dateasc)asflag_amount,date_sub(transaction_date,intervalrnday)asflag_dayfrom(selecttransaction_id,customer_id,transaction_date,amount,lag(amount,1,0)over(partitionbycustomer_idorderbytransaction_dateasc)aslast_amount,row_number()over(partitionbycustomer_idorderbytransaction_dateasc)asrnfromTransactions)t1)t2groupbycustomer_id,flag_amount,flag_dayhavingcount(1)2orderbycustomer_idasc;1.lag(amount, 1,0) 意思是：“在当前行的前 1 行中，取 amount 列的值；如果前面没有行（比如分区内第一行），就返回默认值 0。”默认amount初始值是0，但是实际中可能存在初始值=0，则amount 0 为假，第一行会被误判为"非递增"，导致第一段被错误分割。2.另外，如果单日有多笔交易，order by transaction_date asc只有这一个排序是不够的，所以，需要新增交易ID作为排序字段。3.多层嵌套子查询不够清晰，清晰的表达式如下：WITHorderedAS(SELECTcustomer_id,transaction_date,amount,-- 取上一笔金额（无默认值，第一行自然为 NULL）LAG(amount)OVER(PARTITIONBYcustomer_idORDERBYtransaction_date,transaction_id)ASlast_amount,ROW_NUMBER()OVER(PARTITIONBYcustomer_idORDERBYtransaction_date,transaction_id)ASrnFROMTransactions),flaggedAS(SELECTcustomer_id,transaction_date,-- 金额段标识：下跌/持平时 +1SUM(CASEWHENamountlast_amountTHEN0ELSE1END)OVER(PARTITIONBYcustomer_idORDERBYtransaction_date,transaction_id)ASamt_grp,-- 日期连续性标识DATE_SUB(transaction_date,INTERVALrnDAY)ASdate_grpFROMordered)SELECTcustomer_id,MIN(transaction_date)ASconsecutive_start,MAX(transaction_date)ASconsecutive_endFROMflaggedGROUPBYcustomer_id,amt_grp,date_grpHAVINGCOUNT(*)2-- 至少连续 3 天ORDERBYcustomer_id;解析：用户发生连续交易通用解法，使用 日期 - row_number做标识即可连续交易的几天中交易金额持续增长1.逐日判断当日金额是否大于前一天金额，大于则标识0否则标识1。目的是为了使用1作为“不连续”情况的分界线2.开窗累加标识，当存在不连续情况的“分界线”时累计值会发生变化，即可识别金额的连续增长情况。问题4：连续空余座位表: Cinema+-------------+------+|ColumnName|Type|+-------------+------+|seat_id|int||free|bool|+-------------+------+Seat_id 是该表的自动递增主键列。在 PostgreSQL 中，free 存储为整数。请使用 ::boolean 将其转换为布尔格式。该表的每一行表示第 i 个座位是否空闲。1 表示空闲，0 表示被占用。查找电影院所有连续可用的座位。返回按 seat_id 升序排序 的结果表。测试用例的生成使得两个以上的座位连续可用。selectdistinctseat_idfrom(selectseat_id,free,sum(free)over(rowsbetween1precedingandcurrentrow)asflag1,sum(free)over(rowsbetweencurrentrowand1following)asflag2fromCinema)awhereflag11orflag21orderbyseat_id;解析：当flag1等于2时，此座位连续，但是无法识别连续座位的第一个，因为上一个是0，这一个是1，之和为1。当flag2等于2时，此座位连续，但是无法识别连续座位的最后一个，因为下一个是0，这一个是1，之和为1。所以通过同时判断flag1和flag2，可以得到连续座位。问题5：连续空余座位2SQL SchemaPandas Schema表：Cinema+-------------+------+|ColumnName|Type|+-------------+------+|seat_id|int||free|bool|+-------------+------+seat_id 是这张表中的自增列。这张表的每一行表示第 i 个作为是否空余。1 表示空余，而 0 表示被占用。编写一个解决方案来找到电影院中 最长的空余座位 的 长度。注意：保证 最多有一个 最长连续序列。如果有 多个 相同长度 的连续序列，将它们全部输出。返回结果表以 first_seat_id 升序排序。selectmin(seat_id)asfirst_seat_id,max(seat_id)aslast_seat_id,count(seat_id)asconsecutive_seats_lenfrom(selectseat_id,free,flag1,flag2from(selectseat_id,free,sum(free)over(rowsbetween1precedingandcurrentrow)asflag1,sum(free)over(rowsbetweencurrentrowand1following)asflag2fromCinema)awhereflag11orflag21)b解析：非常规做法，根据连续空余座位得出思路，先找到连续的座位，然后再找连续座位中最小的就是开始座位，最大的就是结束座位，count计数一共有多少个连续的座位。风险当多次连续时可能无法计算。WITHt1AS(# 找出空闲座位SELECTseat_idFROMCinemaWHEREfree=1),t2AS(# 计算seat_id与row_number之差，num相同的代表连续空闲SELECTseat_id,seat_id-ROW_NUMBER()OVER(ORDERBYseat_idASC)ASnumFROMt1),t3AS(SELECTMIN(seat_id)ASfirst_seat_id,# 连续空闲开始的seat_idMAX(seat_id)ASlast_seat_id,# 连续空闲结束的seat_idCOUNT(*)ASconsecutive_seats_len,# 空闲座位数DENSE_RANK()OVER(ORDERBY
)
