SQL连续登录、活跃、持仓天数问题

青卿呀

已于 2024-10-14 20:25:47 修改

阅读量635

点赞数 12

文章标签： sql 数据库

于 2024-10-10 10:27:31 首次发布

本文链接：https://blog.csdn.net/imalady/article/details/142814032

版权

面试常问问题之SQL连续问题@SQL连续**问题
编辑于2024-10-10
在我们面试过程当中，经常会被问到经典的SQL连续问题，可能是连续10天活跃用户数，可能是连续十天登录的用户，也可能是计算用户的持仓天数。下面我们综合chatGPT和力扣来进行一下SQL连续问题的一个汇总。

问题1：sql连续十天活跃用户数怎么算

要计算连续十天活跃用户数，我们可以使用SQL中的窗口函数（LEAD和SUM）来实现。以下是一个示例，假设我们有一个名为user_activity的表，其中包含两列：user_id和activity_date

SELECT 
    COUNT(DISTINCT user_id) AS active_users
FROM (
    SELECT 
        user_id, 
        SUM(is_consecutive) OVER (PARTITION BY user_id ORDER BY activity_date) AS consecutive_days
    FROM (
        SELECT 
            user_id, 
            activity_date, 
            CASE 
                WHEN activity_date = DATE_ADD(LAG(activity_date) OVER (PARTITION BY user_id ORDER BY activity_date), INTERVAL 1 DAY) 
                THEN 1 
                ELSE 0 
            END AS is_consecutive
        FROM 
            user_activity
    ) AS user_activity_with_flag
) AS users_with_consecutive_days
WHERE 
    consecutive_days = 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 升序排列。

查询结果的格式如下所示

select 
    customer_id,
    min(transaction_date) as consecutive_start,
    max(transaction_date) as consecutive_end
from
(    select 
			customer_id,
      transaction_date,
      sum(case when amount > last_amount then 0 else 1 end) over(partition by customer_id order by transaction_date asc) as flag_amount,
			date_sub(transaction_date,interval rn day) as flag_day
		from (
			select 
		    transaction_id,
		    customer_id,
		    transaction_date,
        amount,
        lag(amount,1,0) over(partition by customer_id order by transaction_date asc) as last_amount,
		    row_number() over(partition by customer_id order by transaction_date asc) as rn 
			from Transactions
	) t1
) t2 
group by customer_id,flag_amount,flag_day 
having count(1) > 2
order by customer_id asc
;

解析：

用户发生连续交易
通用解法，使用日期 - row_number做标识即可
连续交易的几天中交易金额持续增长
1.逐日判断当日金额是否大于前一天金额，大于则标识0否则标识1。目的是为了使用1作为“不连续”情况的分界线
2.开窗累加标识，当存在不连续情况的“分界线”时累计值会发生变化，即可识别金额的连续增长情况。

问题4：连续空余座位

表: Cinema

+-------------+------+
| Column Name | Type |
+-------------+------+
| seat_id     | int  |
| free        | bool |
+-------------+------+

Seat_id 是该表的自动递增主键列。
在 PostgreSQL 中，free 存储为整数。请使用 ::boolean 将其转换为布尔格式。
该表的每一行表示第 i 个座位是否空闲。1 表示空闲，0 表示被占用。

查找电影院所有连续可用的座位。
返回按 seat_id 升序排序的结果表。
测试用例的生成使得两个以上的座位连续可用。

select distinct seat_id
from(select seat_id,free,
    sum(free) over(rows between 1 preceding and current row) as flag1,
    sum(free) over(rows between current row and 1 following) as flag2
from Cinema) a
where flag1>1 or flag2>1
order by seat_id;

解析：当flag1等于2时，此座位连续，但是无法识别连续座位的第一个，因为上一个是0，这一个是1，之和为1。
当flag2等于2时，此座位连续，但是无法识别连续座位的最后一个，因为下一个是0，这一个是1，之和为1。
所以通过同时判断flag1和flag2，可以得到连续座位。

问题5：连续空余座位2

SQL Schema
Pandas Schema
表：Cinema

+-------------+------+
| Column Name | Type |
+-------------+------+
| seat_id     | int  |
| free        | bool |
+-------------+------+

seat_id 是这张表中的自增列。
这张表的每一行表示第 i 个作为是否空余。1 表示空余，而 0 表示被占用。
编写一个解决方案来找到电影院中最长的空余座位的长度。

注意：

保证最多有一个最长连续序列。
如果有多个相同长度的连续序列，将它们全部输出。
返回结果表以 first_seat_id 升序排序。

select min(seat_id) as first_seat_id,
        max(seat_id) as last_seat_id,
        count(seat_id) as consecutive_seats_len
from(select seat_id,free,flag1,flag2
from(select seat_id,free,
    sum(free) over(rows between 1 preceding and current row) as flag1,
    sum(free) over(rows between current row and 1 following) as flag2
 from Cinema) a
 where flag1 > 1 or flag2 > 1) b

解析：非常规做法，根据连续空余座位得出思路，先找到连续的座位，然后再找连续座位中最小的就是开始座位，最大的就是结束座位，count计数一共有多少个连续的座位。风险当多次连续时可能无法计算。

 WITH t1 AS(          # 找出空闲座位
    SELECT seat_id
    FROM Cinema
    WHERE free = 1 
),t2 AS(            # 计算seat_id与row_number之差，num相同的代表连续空闲
    SELECT
        seat_id,
        seat_id - ROW_NUMBER() OVER(ORDER BY seat_id ASC) AS num
    FROM t1
),t3 AS(           
    SELECT
        MIN(seat_id) AS first_seat_id,   # 连续空闲开始的seat_id
        MAX(seat_id) AS last_seat_id,    # 连续空闲结束的seat_id
        COUNT(*) AS consecutive_seats_len,    # 空闲座位数
        DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) rnk  # 根据空闲座位数降排列
    FROM t2
    GROUP BY num  # 根据num分组，同组连续空闲
)
SELECT
    first_seat_id,
    last_seat_id,
    consecutive_seats_len
FROM t3
WHERE rnk = 1               # 连续空闲最大座位数
ORDER BY first_seat_id ASC
;

解析常规做法

问题6：连续出现的数字

表：Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+

在 SQL 中，id 是该表的主键。
id 是一个自增列。

找出所有至少连续出现三次的数字。
返回的结果表中的数据可以按任意顺序排列。

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

解析常规做法：相当于自连接，但是没有join可能产生的笛卡尔积，因此执行速度更快。ID连续即ID的差值是1，连续出现三次即值相等。

问题7：报告系统状态的连续日期

表：Failed

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| fail_date    | date    |
+--------------+---------+

该表主键为 fail_date (具有唯一值的列)。
该表包含失败任务的天数.

表： Succeeded

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| success_date | date    |
+--------------+---------+

该表主键为 success_date (具有唯一值的列)。
该表包含成功任务的天数.

系统每天运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。
编写解决方案找出 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。

最后结果按照起始日期 start_date 排序