首页 > 试题广场 >

2021年11月每天的人均浏览文章时长

[编程题]2021年11月每天的人均浏览文章时长
  • 热度指数:85689 时间限制:C/C++ 1秒,其他语言2秒 空间限制:C/C++ 256M,其他语言512M
  • 算法知识视频讲解
用户行为日志表tb_user_log
id uid artical_id in_time out_time sign_cin
1 101 9001 2021-11-01 10:00:00 2021-11-01 10:00:31 0
2 102 9001
2021-11-01 10:00:00 2021-11-01 10:00:24 0
3 102 9002
2021-11-01 11:00:00 2021-11-01 11:00:11 0
4 101 9001 2021-11-02 10:00:00 2021-11-02 10:00:50 0
5 102 9002
2021-11-02 11:00:01 2021-11-02 11:00:24
0
(uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到)


场景逻辑说明artical_id-文章ID代表用户浏览的文章的ID,artical_id-文章ID0表示用户在非文章内容页(比如App内的列表页、活动页等)。

问题:统计2021年11月每天的人均浏览文章时长(秒数),结果保留1位小数,并按时长由短到长排序。

输出示例
示例数据的输出结果如下

dt avg_viiew_len_sec
2021-11-01 33.0
2021-11-02 36.5

解释:
11月1日有2个人浏览文章,总共浏览时长为31+24+11=66秒,人均浏览33秒;
11月2日有2个人浏览文章,总共时长为50+23=73秒,人均时长为36.5秒。
示例1

输入

DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:31', 0),
  (102, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:24', 0),
  (102, 9002, '2021-11-01 11:00:00', '2021-11-01 11:00:11', 0),
  (101, 9001, '2021-11-02 10:00:00', '2021-11-02 10:00:50', 0),
  (102, 9002, '2021-11-02 11:00:01', '2021-11-02 11:00:24', 0);

输出

2021-11-01|33.0
2021-11-02|36.5
select DATE_FORMAT(in_time,'%Y-%m-%d') dt,
round(sum(timestampdiff(second,in_time,out_time))/count(DISTINCT uid),1) avg_view_len_sec from tb_user_log
where DATE_FORMAT(in_time,'%Y-%m')='2021-11' and artical_id != 0
group by dt
order by avg_view_len_sec

#主要注意uid每个人可能看几次,所以不能用avg
发表于 2021-12-08 14:39:56 回复(0)
select date(in_time) as dt,
round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) as avg_viiew_len_sec
from tb_user_log
where year(in_time)=2021 and month(in_time)=11 and artical_id >0
group by date(in_time)
order by avg_viiew_len_sec

中规中矩的题目

发表于 2022-08-25 14:09:08 回复(1)
select substring(in_time,1,10) as dt,
round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) as avg_view_len_sec
from tb_user_log
where substring(in_time,1,7)='2021-11' and artical_id !=0
group by substring(in_time,1,10)
order by avg_view_len_sec 

发表于 2022-01-04 11:13:36 回复(0)

【场景】:每天人均

【分类】:嵌套子查询、日期函数、month、year、date、date_format

分析思路

难点:

1.难点:计算人均浏览时长时,可能一个人当天浏览多条记录,所以需要对用户去重后统计个数

(1)统计2021年11月每天的人均浏览文章时长(秒数),结果保留1位小数,并按时长由短到长排序。

​ [条件]:where artical_id <> '0' and month(in_time) = 11 and year(in_time) = 2021

​ [使用]:group by dt;order by avg_viiew_len_sec;年月日使用date();两个日期相减得到秒使用timestampdiff(second,expr1,expr2)

最终结果

select 查询结果 [日期;人均浏览文章时长]
from 从哪张表中查询数据 [用户行为日志表]
where 查询条件 [2021年11月;文章ID不为0]
group by 分组条件 [日期]
order by 对查询结果排序 [时长升序];

求解代码

方法一:

使用month、year、date

select
    date(in_time) as dt,
    round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) as avg_viiew_len_sec
from tb_user_log
where artical_id != 0
and month(in_time) = 11
and year(in_time) = 2021
group by dt
order by avg_viiew_len_sec

方法二:

使用date_format、date

select
    date(in_time) as dt,
    round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) as avg_viiew_len_sec
from tb_user_log
where artical_id != 0
and date_format(in_time,'%Y%m') = '202111'
group by dt
order by avg_viiew_len_sec
发表于 2022-11-21 09:58:17 回复(0)
注意坑:
1. 人均浏览时长,不是时长的平均值,avg。应该是总时长/去重的用户数
2. 筛选条件中要考虑artical_id!=0
3. 按天分组不能忘

select
    date_format(in_time,"%Y-%m-%d") as dt,
    round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) as avg_viiew_len_sec
from tb_user_log
where date_format(in_time,"%Y-%m") = "2021-11"
and artical_id!=0
group by dt
order by avg_viiew_len_sec


发表于 2022-09-04 09:53:16 回复(2)
SELECT 
    DATE(in_time) as dt,ROUND(sum(TIMESTAMPDIFF(second,in_time,out_time)) / count(DISTINCT uid),1) as avg_view_len_sec
FROM
    tb_user_log
where substring(in_time,1,7)='2021-11' and artical_id !=0
GROUP by DATE(in_time)
order by avg_view_len_sec ASC
发表于 2022-02-13 20:19:42 回复(0)
SELECT date_format(in_time, '%Y-%m-%d') dt, 
round(sum(TIMESTAMPDIFF(second, in_time, out_time))/count(DISTINCT uid), 1) avg_view_len_sec
from tb_user_log
where artical_id != 0
and DATE_FORMAT(in_time,'%Y-%m')='2021-11'
group by date_format(in_time, '%Y-%m-%d')
order by avg_view_len_sec

发表于 2021-12-02 09:53:40 回复(0)
select date(in_time) dt,
round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) avg_viiew_len_sec 
from tb_user_log 
where date(in_time) between '2021-11-01' and '2021-11-30' and artical_id<>0
group by 1
order by 2
artical_id!=0
日期筛选也可
year(in_time)=2021 and month(in_time)=11或
in_time like '2021-11%'或
in_time like '2021-11-%'或·
date_format(in_time,'%Y-%m')='2021-11' 注意必须用引号括起2021-11

编辑于 2024-02-20 18:18:55 回复(0)
SELECT SUBSTRING(in_time,1,10) dt,
       ROUND(SUM(ABS(TIMESTAMPDIFF(SECOND,out_time,in_time)))/COUNT(DISTINCT uid),1) avg_viiew_len_sec
FROM tb_user_log
WHERE SUBSTRING(in_time,1,7)='2021-11'
AND artical_id<>'0'
GROUP BY 1
ORDER BY 2
注意不能用avg求平均,每个人能看多篇文章,注意
artical_id<>'0'
发表于 2023-09-05 11:39:22 回复(0)
select
    date(in_time) dt,
    round(sum(if(artical_id = 0,0,timestampdiff(second,in_time,out_time))) / count(distinct uid),1) avg_viiew_len_sec
from tb_user_log
where date_format(in_time,'%Y-%m') = '2021-11'
group by dt
order by avg_viiew_len_sec;

发表于 2023-07-19 17:53:14 回复(0)
大佬们 为啥我1号的数据是错的,而在自测运行的时候是对的
select dt, round(a/uid,1) as avg_viiew_len_sec
from (

select distinct dt, sum(viiew_len_sec) as a,count(distinct uid) as uid
from (
select  date(out_time) as dt,timestampdiff(second,in_time,out_time) as viiew_len_sec,uid
from tb_user_log
where month(out_time) = '11' and month(in_time) ='11'
)t1
group by dt
) t2
发表于 2023-02-06 17:55:52 回复(2)
错点1:子查询,执行顺序
错点2:date(),timestampdiff(),date_format(),指定11月
错点3:artical_id != 0
错点4:distinct uid 去重
发表于 2022-08-24 19:06:02 回复(0)
为什么会考虑到 artical_id != 0 的情况,为什么要考虑artical_id != 0 的情况???🤣😫,那为什么不是考虑 artical_id 为空值的情况??我是题目那个点没看明白吗?
SELECT DATE_FORMAT(in_time,"%Y-%m-%d") dt,
ROUND(SUM(TIMESTAMPDIFF(SECOND,in_time,out_time))/COUNT(DISTINCT uid),1) avg_viiew_len_sec
FROM tb_user_log
WHERE YEAR(in_time) = 2021 AND MONTH(in_time) = 11
AND artical_id != 0       #没想到!!
GROUP BY dt
ORDER BY avg_viiew_len_sec


发表于 2022-04-18 12:43:34 回复(2)
select substring(in_time,1,10)dt,
round(sum(TIMESTAMPDIFF(SECOND,in_time,out_time))/count(distinct uid),1) avg_view_len_sec
from tb_user_log
where in_time like '2021-11%'
and artical_id !='0'
group by dt
order by avg_view_len_sec

发表于 2021-12-01 17:53:44 回复(0)
select
    date(in_time) dt,
    round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) avg_viiew_len_sec
from tb_user_log 
where 
    artical_id!=0 and 
    date_format(in_time,'%Y-%m')='2021-11'
group by dt
order by avg_viiew_len_sec

编辑于 2024-01-23 15:13:30 回复(0)
有没有大佬能看看这个出什么问题了,一直显示答案错误,不知道怎么改
select date_format(in_time,"%y-%m-%d")dt,
round (sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1)avg_viiew_len_sec
from tb_user_log
where month(in_time)= 11 and artical_id != 0
group by dt
order by avg_viiew_len_sec
编辑于 2024-01-19 11:02:01 回复(0)
注意细节:
1. artical_id !=0
2. order by avg_viiew_len_sec asc
逻辑上没有难度
发表于 2023-12-13 16:08:19 回复(0)
select date(in_time) as dt,
round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) as avg_viiew_len_sec 
from tb_user_log 
where year(in_time)=2021 and month(in_time)=11 and artical_id>0
group by dt
order by avg_viiew_len_sec

发表于 2023-11-21 11:29:46 回复(0)
select date_format(tul.in_time,'%Y-%m-%d') ym,
       round(sum(time_to_sec(timediff(tul.out_time,tul.in_time)))/count(distinct uid),1) avg_time
from tb_user_log tul
where tul.in_time like '2021-11-%' and tul.artical_id <> 0
group by ym
order by avg_time

发表于 2023-11-13 14:28:30 回复(0)
SELECT
    date (in_time) AS dt,
    CAST(
        sum(TIMESTAMPDIFF (SECOND, in_time, out_time)) / count(DISTINCT uid) AS DECIMAL(10, 1)
    ) AS avg_viiew_len_sec
FROM
    tb_user_log
WHERE
    artical_id <> 0
    AND YEAR (in_time) = 2021
    AND MONTH (in_time) = 11
GROUP BY
    dt
ORDER BY
    avg_viiew_len_sec

我们可以使用DATE(date)函数直接将时间转化为标准的yyyy-MM-dd格式,使用TIMESTAMPDIFF(type,date1,date2)计算出浏览文章的时长,使用CAST(expr as type)可以将计算出来的结果转化为DECIMAL类型并指定小数位,使用YEAR(date)获取时间的年份,MONTH(date)获取时间的月份。
发表于 2023-10-20 17:38:52 回复(0)
  • 二维码

    扫描二维码,关注牛客网

  • 二维码

    下载牛客APP,随时随地刷题