vlambda博客
学习文章列表

2.1.1 连续题目

1、一个表中有uid、视频id、视频类型、访问时间，求连续4次刷同一类型视频的用户

uid，videoid、video_type、time

with a as 
(
    select
        uid,videoid,video_type,time,row_number() over(partition by uid order by time ) as rn 
    from
        v 
)

select
 a.uid 
from 
a 
inner join 
 a as b 
on 
 a.uid = b.uid and a.rn = b.rn- 1 and a,video_type = b.video_type
inner join 
 a as c 
on 
 b.uid = c.uid and b.rn = c.rn - 1 and b.video_type = c.video_type 
inner join 
 a as d 
on 
 c.uid = d.uid and c.rn = b.rn - 1 and c.video_type = d.video_type
group by 
 a.uid 

select
uid 
from
(
    select
        uid,video_id,video_type,time,lead(video_type,1) over(partition by uid order by time) as lead_video_type1,
        lead(video_type,2) over(partition by uid order by time) as lead_video_type2,
        lead(video_type,3) over(partition by uid order by time) as lead_video_type3
    from
        v
) as a 
where
 video_type = lead_video_type1 and video_type = lead_video_type2
 and video_type = lead_video_type3
group by 
uid

180. 连续出现的数字

SQL架构

表：Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id 是这个表的主键。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

查询结果格式如下面的例子所示：

示例 1:

输入：
Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
输出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
解释：1 是唯一连续出现至少三次的数字。

• 使用lead函数

# Write your MySQL query statement below
select
    num as ConsecutiveNums
from
(
    select  
        id,num,lead(num,1) over() as lead_num1,
        lead(num,2) over() as lead_num2
    from
        logs 
) as a 
where
    num - lead_num1 = 0 and num - lead_num2 = 0
group by 
    num

• 正常解法

# Write your MySQL query statement below
select
    a.num as ConsecutiveNums
from
    logs as a 
inner join 
    logs as b 
on 
    a.num = b.num and a.id = b.id - 1
inner join 
    logs as c 
on  
    b.num = c.num and b.id = c.id -1
group by 
    a.num 

3、统计连续登陆的三天数和以上的用户以及他们的首次登录和最后登陆时间

select
 uid,min(dt) as min_dt,
 max(dt) as max_dt,
 count(1) as cnt 
from
(
    select
        uid,dt,rn,date_sub(dt,rn) as dt_rn
    from
    (
        select
            uid,dt,row_number() over(partition by uid order by dt) as rn
        from
            a 
    ) as b 
) as tmp 
group by 
 uid,dt_rn 
having
 count(1) >= 3

33. 搜索旋转排序数组

整数数组 nums 按升序排列，数组中的值 互不相同 。

在传递给函数之前，nums 在预先未知的某个下标 k（0 <= k < nums.length）上进行了 旋转，使数组变为 [nums[k], nums[k+1], ..., nums[n-1], nums[0], nums[1], ..., nums[k-1]]（下标 从 0 开始 计数）。例如， [0,1,2,4,5,6,7] 在下标 3 处经旋转后可能变为 [4,5,6,7,0,1,2] 。

给你 旋转后 的数组 nums 和一个整数 target ，如果 nums 中存在这个目标值 target ，则返回它的下标，否则返回 -1 。

示例 1：

输入：nums = [4,5,6,7,0,1,2], target = 0

输出：4

示例 2：

输入：nums = [4,5,6,7,0,1,2], target = 3

输出：-1

示例 3：

输入：nums = [1], target = 0

输出：-1

提示：

1 <= nums.length <= 5000

-10^4 <= nums[i] <= 10^4

nums 中的每个值都 独一无二

题目数据保证 nums 在预先未知的某个下标上进行了旋转

-10^4 <= target <= 10^4

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/search-in-rotated-sorted-array

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

class Solution: def search(self, nums: List[int], target: int) -> int: if not nums: return -1 left,right = 0,len(nums) - 1
 while left <= right: mid = left + (right - left) // 2 if nums[mid] == target: return mid   elif nums[mid] < nums[right]: # 折点在左侧 if nums[mid] < target <= nums[right]: left = mid + 1 else: right = mid - 1 else: # 这点在右侧 if nums[left] <= target < nums[mid]: right = mid - 1 else: left = mid + 1 return -1