首页 > 试题广场 >

牛客每个人最近的登录日期(六)

[编程题]牛客每个人最近的登录日期(六)

热度指数：94540 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

牛客每天有很多人登录，请你统计一下牛客每个用户刷题情况，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。不存在没有登录却刷题的情况，但存在登录了没刷题的情况，不会存在刷题表里面，会存在提交代码没有通过的情况并记录在刷题表里，通过数目是0。
有登录(login)记录表，简况如下:

id	user_id	client_id	date
1	2	1	2020-10-12
2	3	2	2020-10-12
3	1	2	2020-10-12
4	1	3	2020-10-13
5	3	2	2020-10-13

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
......
第5行表示user_id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网

有刷题（passing_number)表，简况如下:

id	user_id	number	date
1	2	4	2020-10-12
2	3	1	2020-10-12
3	1	0	2020-10-13
4	3	2	2020-10-13

第1行表示user_id为2的用户在2020-10-12通过了4个题目。
......
第3行表示user_id为1的用户在2020-10-13提交了代码但是没有通过任何题目。
第4行表示user_id为3的用户在2020-10-13通过了2个题目

有用户(user)表，注：这里id字段对应user_id，简况如下:

id	name
1	tm
2	fh
3	wangchao

请你写出一个sql语句查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出，上面的例子查询结果如下:

u_n	date	ps_num
fh	2020-10-12	4
wangchao	2020-10-12	1
tm	2020-10-13	0
wangchao	2020-10-13	3

查询结果表明:
fh在2020-10-12为止，总共通过了4道题，输出为4
wangchao在2020-10-12为止，总共通过了1道题，总计为1
tm在2020-10-12为止只登陆了没有刷题，故没有显示出来
tm在2020-10-13为止刷了题，但是却没有通过任何题目，总计为0
wangchao在2020-10-13通过2道，但是加上前面2020-10-12通过1道，故在2020-10-13为止总共通过了3道题，总计为3

示例1

输入

drop table if exists login;
drop table if exists passing_number;
drop table if exists user;
drop table if exists client;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `passing_number` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`number` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `user` (
`id` int(4) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`));


INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,1,2,'2020-10-12'),
(4,1,3,'2020-10-13'),
(5,3,2,'2020-10-13');

INSERT INTO passing_number VALUES
(1,2,4,'2020-10-12'),
(2,3,1,'2020-10-12'),
(3,1,0,'2020-10-13'),
(4,3,2,'2020-10-13');

INSERT INTO user VALUES
(1,'tm'),
(2,'fh'),
(3,'wangchao');

输出

fh|2020-10-12|4
wangchao|2020-10-12|1
tm|2020-10-13|0
wangchao|2020-10-13|3

算法知识视频讲解

SunburstRun

select user_id,date,sum(number) over(partition by user_id order by date) from passing_number

与

select pn1.user_id,pn1.date,sum(pn2.number) 
from passing_number pn1,passing_number pn2 where pn1.user_id=pn2.user_id and pn1.date>=pn2.date 
group by pn1.user_id,pn1.date;

这2种方法，均可以得到某一天，id为多少的用户，在这一天和这一天之前，通过的题目总数，id和日期，然后和登录(login)表，用户(user)表连接，再按照题目要求的东西排序，就可以得到最后的结果了:

select user.name as u_n, login.date, p1.ps_num
from login 
join (select user_id, date ,sum(number) over(partition by user_id order by date) ps_num from passing_number) p1
on p1.user_id=login.user_id and p1.date=login.date
join user on login.user_id=user.id
order by login.date, user.name

与以下语句等价:

select user.name as u_n, login.date, p1.ps_num
from login 
join (select pn1.user_id,pn1.date,sum(pn2.number) as ps_num
from passing_number pn1,passing_number pn2 where pn1.user_id=pn2.user_id and pn1.date>=pn2.date 
group by pn1.user_id,pn1.date) p1
on p1.user_id=login.user_id and p1.date=login.date
join user on login.user_id=user.id
order by login.date,user.name;

编辑于 2020-11-04 12:10:25 回复(2)

Mysql

djkfjdxc

select c.name u_n,a.date,
sum(b.number) over(partition by a.user_id order by a.date) ps_num
from login a 
join passing_number b on a.user_id=b.user_id and a.date=b.date
left join user c on a.user_id=c.id
order by 2,1;

发表于 2022-04-22 14:44:02 回复(0)

Tina要成为数据分析师呀

注意最后排序是日期，姓名，分数

select u.name as u_n,p.date as date,
sum(p.number)over(partition by p.user_id order by p.date) as ps_num 
from passing_number p
join user u
on p.user_id=u.id
order by p.date,u_n,ps_num

发表于 2021-01-07 17:16:57 回复(0)

Moonfisher

/*解题关键：如何表示每个用户，截止到某天，累计总共通过了多少题。
联想到使用sum()窗口函数，对number进行累加，按user_id进行分组，按date进行排序
*/

select a.u_name as u_n, a.c_name as c_n, p.date as date,
sum(p.number) over (partition by p.user_id order by p.date) as ps_num
from (select u.id as u_id, u.name as u_name, c.id as c_id, c.name as c_name, l.date as l_date
from user as u inner join login as l
on u.id = l.user_id inner join client as c
on l.client_id = c.id) as a
inner join passing_number as p
on a.u_id = p.user_id and a.l_date = p.date
order by date asc, u_n asc;

发表于 2020-09-10 19:03:51 回复(0)

我是哈哈哈.jpg

select u.name, c.name, pn1.date, sum(pn2.number)
from login l, passing_number pn1, user u, client c, passing_number pn2
where l.user_id = pn1.user_id
  and l.user_id = u.id
  and l.client_id = c.id
  and l.date = pn1.date
  and pn1.user_id = pn2.user_id
  and pn1.date >= pn2.date
group by pn1.user_id, pn1.date
order by pn1.date, u.name

本题难度在于求截止到某天，累计总共通过了多少题，我这里直接复用了一次passing_number表，自连接一次，然后通过分组求出最后的答案

发表于 2020-09-25 11:02:28 回复(1)

全麦面包秋招中

最简单的解法：

本题不需要使用到第一个login表，采用sum函数进行开窗处理，将user_id进行分区，再通过时间升序排序，进而实现了在每个user_id分区中以升序日期排序的通过题数的逐个递加（在这一步中，user_id的排序不重要，date的排序才重要），即“截止到某天，累计总共通过了多少题”的题意。

select
name as u_n,date,
sum(number) over(partition by user_id order by date) as ps_num
from passing_number p
left join user u
on p.user_id=u.id
order by date,u_n

编辑于 2020-11-04 14:16:06 回复(16)

xwsummer

题目容易有歧义，

“请你写出一个sql语句查询每一天的刷题通过数据”

这句话很容易被理解为

“每天通过了多少题？”

而题目的本意应该是“截止到某天，累计总共通过了多少题？”

这样写意思也许不容易歧义。

发表于 2020-09-01 15:03:20 回复(3)

多吃早睡

本题主要是一个窗口函数的另一妙用：组内累加

select user.name as u_n, a.date, a.ps_num
from user 
inner join (
    select user_id, date, sum(number) over (partition by user_id order by date asc) ps_num
    from passing_number 
) a
on user.id=a.user_id
order by date asc, u_n asc

另一种方法，自连接，然后对大于某日期的数量做累加

select u.name u_n, a.date, sum(b.number) ps_num
from passing_number as a 
inner join passing_number as b
on a.user_id=b.user_id and a.date>=b.date
inner join user as u
on a.user_id=u.id
group by u_n, a.date
order by a.date asc, u_n asc

编辑于 2020-11-13 17:03:46 回复(8)

牛客231462763号

这个题目比较简单，就是一个简单求滚动sum，而且题目出题不仔细，忽略了每天有人可以用很多不同设备登陆的情况，更降低了题目的难度。

/* 每个人的滚动sum */
select u.name as u_n, c.name as c_n, pn.date as date, sum(number) over (partition by pn.user_id order by pn.date) as ps_num
from passing_number pn
inner join user u on pn.user_id = u.id
inner join login l on pn.user_id = l.user_id and pn.date = l.date
inner join client c on l.client_id = c.id
order by pn.date, u.name

发表于 2020-08-21 18:01:09 回复(5)

Mysql

LilOwer

挺简单的

select
	u.name u_n,
	l.date,
	sum(number) over(partition by `name` order by date) ps_num
from login l
	join passing_number p
on l.user_id=p.user_id and l.date=p.date
  join `user` u
on l.user_id=u.id
order by date,u_n

发表于 2021-11-14 17:37:02 回复(0)

牛客421553514号

select name, date, sum(number) over(partition by user_id order by date)
from passing_number join user
on passing_number.user_id = user.id
order by date, name

发表于 2021-02-21 21:34:19 回复(0)

Mysql

一个好懒的人

select
name as u_n,
date,
sum(number) over(partition by name order by date) as ps_num
from passing_number pn
left join user u on pn.user_id=u.id
order by date,name

发表于 2022-06-25 17:45:48 回复(1)

Stzolo

# 思路，开窗函数累加和

select b.name, a.date, sum(a.number) over(partition by a.user_id order by a.user_id, a.date) as number
from passing_number a
join user b on a.user_id = b.id
order by a.date, b.name

发表于 2022-04-18 16:24:35 回复(0)

Mysql

mmk必翻身！

SELECT name as u_n , date , ps_num
FROM  (
    SELECT sum(number)over(PARTITION BY name order by date ) as ps_num 
    , name , date
    FROM (
        SELECT p.user_id,p.date , number ,name 
        from user u 
        JOIN passing_number p
        ON p.user_id = u.id 
    ) a 
) b
ORDER BY date , u_n

题目说登录没做题的不记录，意思login的表可以不用join啊直接join user和passing——number就好了

破大防了家人们

发表于 2022-03-10 09:42:26 回复(0)

Mysql

牛客168328177号

select name,pn.date
,sum(number) over (partition by name order by pn.date) ps_num
from user u
right join passing_number pn
on u.id = pn.user_id
order by pn.date,name;

发表于 2021-11-10 21:49:18 回复(0)

晓_

select
   u.name,p.date,sum(p.number) over(partition by user_id order by date)
from
    passing_number p
left join
    user u
on
    p.user_id=u.id
order by
    p.date,u.name

发表于 2021-07-13 11:52:45 回复(0)

Victiny

这个方法好像没看到有人提过

SELECT u.name, a.date, CAST(a.tpn AS SIGNED)
FROM user AS u,
(SELECT p.user_id, p.date, p.number, IF(@pre_user = p.user_id, @pn := @pn + p.number, @pn := p.number) AS tpn, @pre_user := p.user_id
FROM passing_number AS p, (SELECT @pn := 0, @pre_user := 0) AS r
ORDER BY p.user_id, p.date) AS a
WHERE u.id = a.user_id
ORDER BY a.date, u.name

这里直接输出a.tpn 为三位小数，我用FLOOR, CEIL, ROUND都不能转化成整数是为什么？

发表于 2021-03-09 17:28:15 回复(1)

数字乐章

select u.name u_n, a.date, sum(b.number) ps_num
from passing_number a, passing_number b, user u
where a.date>=b.date and a.user_id=b.user_id and a.user_id = u.id
group by a.user_id, u_n, a.date
order by a.date, u_n

发表于 2020-10-28 00:23:21 回复(0)

Mysql

不懂业务数据的运营不是好产品

窗口函数求和

select
    t2.name as u_n,
    t1.date,
    sum(t3.number) over(partition by t2.name ORDER BY t1.date) as ps_num
    -- 通过窗口函数 对每个用户在每个日期的刷题记录进行累计
from  
    login t1
    join user t2  on t1.user_id =t2.id  -- 为每个登录记录匹配用户昵称,
    join passing_number  t3 on (t1.user_id =t3.user_id and t1.date=t3.date )  
    -- 获得每次登陆的刷题数量；同时通过inner join 也能消除掉有登录但未刷题的记录
order by
    `date` asc,u_n asc

发表于 2024-07-09 19:32:19 回复(0)

Mysql

吃过饭去看星星吧

select
    t.name u_n,
    t.date,
    sum(t.number) over (
        partition by
            t.name
        order by
            t.date
    ) ps_num
from
    (
        select
            a.user_id,
            b.name,
            a.date,
            a.number
        from
            passing_number a
            left join user b on a.user_id = b.id
    ) t
order by
    t.date asc

发表于 2024-05-24 10:39:43 回复(0)