做题笔记:SQL Sever 方式做牛客SQL的题目--VQ

2023-12-13 03:56:40

----VQ 查询用户刷题日期和下一次刷题日期
现有牛客刷题记录表questions_pass_record ,请查询用户user_id,刷题日期date (每组按照date降序排列)和该用户的下一次刷题日期nextdate(若是没有则为None),组之间按照user_id升序排序,每组内按照date升序排列,查询返回结果名称和顺序为:
user_id|date|nextdate

表的创建及数据的添加:

drop table if exists questions_pass_record;
CREATE TABLE questions_pass_record
(
    user_id       int         NOT NULL,
    question_type varchar(32) NOT NULL,
    device        varchar(14) NOT NULL,
    question_id   int         NOT NULL,
    date          date        NOT NULL
);
INSERT INTO questions_pass_record
VALUES (101, 'java', 'app', 2, '2020-03-01');
INSERT INTO questions_pass_record
VALUES (102, 'sql', 'pc', 15, '2021-07-07');
INSERT INTO questions_pass_record
VALUES (102, 'python', 'pc', 9, '2021-04-09');
INSERT INTO questions_pass_record
VALUES (102, 'python', 'app', 3, '2022-03-17');
INSERT INTO questions_pass_record
VALUES (103, 'sql', 'pc', 60, '2018-08-15');
INSERT INTO questions_pass_record
VALUES (104, 'sql', 'pc', 20, '2019-05-15');
INSERT INTO questions_pass_record
VALUES (105, 'sql', 'pc', 550, '2022-07-25');
INSERT INTO questions_pass_record
VALUES (105, 'sql', 'pc', 299, '2020-05-16');
INSERT INTO questions_pass_record
VALUES (106, 'java', 'pc', 17, '2021-04-15');
INSERT INTO questions_pass_record
VALUES (106, 'java', 'pc', 20, '2021-04-15');

查询如下:

select q1.user_id, q1.date, q2.date as nextdate
 from (
		select  user_id, date, 
				row_number() over (partition by user_id order by date) as nextNum
        from questions_pass_record) q1
    left join
	  (
	    select  user_id, date, 
				row_number() over (partition by user_id order by date) as nextNum
        from questions_pass_record) q2
 on q1.user_id = q2.user_id and q1.nextNum + 1 = q2.nextNum

或者直接使用函数:

select user_id,date,
       lead(date, 1) over (partition by user_id order by date) as nextdate
from questions_pass_record

两个查询的执行结果都如下:

user_id	date		nextdate
101		2020-03-01	NULL
102		2021-04-09	2021-07-07
102		2021-07-07	2022-03-17
102		2022-03-17	NULL
103		2018-08-15	NULL
104		2019-05-15	NULL
105		2020-05-16	2022-07-25
105		2022-07-25	NULL
106		2021-04-15	2021-04-15
106		2021-04-15	NULL

在上述查询中:
ROW_NUMBER() 函数
在 SQL Server 中,ROW_NUMBER( ) 函数结合 OVER 子句用于为结果集中的行分配一个唯一的整数序号。
ROW_NUMBER( ) 函数的常见用法是将数据分组后,根据指定的排序方式为每个分组中的行分配一个序号。
语法:

ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2)

其中,PARTITION BY 指定了分组的列,而 ORDER BY 指定了排序的列。

row_number() over (partition by user_id order by date) as nextNum

上面的示例中,我们将结果集按 user_id 列进行分组,并按 date列进行排序。然后,为每个分组中的行分配一个唯一的整数序号,命名为 nextNum。

需要注意
<1> ROW_NUMBER( ) 函数是在 SELECT 查询的结果集上计算的,并不会对数据库中的实际数据进行更改。如果需要使用序号进行更新、删除等操作,可以将查询的结果作为子查询,并在外部查询中进行相关操作。
<2> ROW_NUMBER( ) 函数返回的序号是根据 ORDER BY 子句的排序顺序而确定的。如果 ORDER BY 子句中出现相同的值,结果可能会根据数据库引擎的具体实现而产生不确定的结果。如果需要确保排序的唯一性,请考虑在 ORDER BY 子句中使用更多的列来进行排序。

LEAD( ) 函数
在 SQL Server 中,LEAD( ) 函数用于在每一行中获取指定列的后一行的值。LEAD( ) 函数结合 OVER 子句可以对每个分组进行操作。

语法:

LEAD(column, offset, default value) OVER (PARTITION BY partition_column ORDER BY order_column)

column:指定要获取后一行值的列。
offset:可选参数,指定要获取的后一行的偏移量,默认为 1,即获取紧接着当前行的下一行的值。
default value:可选参数,在最后一行时用于提供默认值。

lead(date, 1) over (partition by user_id order by date) as nextdate

在查询中我们将结果集按 user_id 列进行分组,并按 date 列进行排序。然后使用 LEAD( ) 函数获取每个分组中当前行的下一个行的 date 列的值,将其命名为 nextdate。

需要注意
<1> LEAD( ) 函数的结果是基于指定的分组和排序顺序计算的。如果没有下一行或者超出了最后一行,LEAD( ) 函数根据提供的默认值返回 NULL。
<2>通过使用 LEAD( ) 函数,可以方便地获取指定列的后一行的值,这一点在很多情况都非常好用。

文章来源:https://blog.csdn.net/emmmheng/article/details/134823628
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。