首页 > 试题广场 >

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

[编程题]2021年11月每天的人均浏览文章时长

热度指数：88724 时间限制：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-文章ID为0表示用户在非文章内容页（比如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

算法知识视频讲解

Mysql

祖拜尔实习生

注意排除浏览非文章页面的时长以及用户去重

select
    date_format(in_time,'%Y-%m-%d') dt,
    round(sum(case when artical_id > 0 then timestampdiff(second,in_time,out_time)else null end)/
        count(distinct case when artical_id > 0 then uid else null end),1) avg_viiew_len_sec
from
    tb_user_log
where
    date_format(in_time,'%Y-%m-%d') between '2021-11-01' and '2021-11-31'
group by date_format(in_time,'%Y-%m-%d')
order by avg_viiew_len_sec

发表于 2024-04-04 10:04:00 回复(0)

Mysql

求你们别卷了2

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)

Mysql

你滴铁牛

select DATE_FORMAT(in_time,'%Y-%m-%d')as times,round(sum(TIME_TO_SEC(out_time) - TIME_TO_SEC(in_time))/count(distinct uid),1)as avgs
from tb_user_log
where year(in_time)='2021' and month(in_time)='11'
and artical_id <> '0'
group by times
order by avgs

发表于 2023-08-23 15:54:45 回复(0)

Mysql

牛客675725041号

（1）count里可以用distinct （2）where那里可以用like和通配符

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 in_time LIKE '2021-11%' and artical_id != 0
group by dt
order by avg_viiew_len_sec

发表于 2023-08-14 17:58:09 回复(0)

Mysql

济楚

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 a
where
year(in_time)=2021 and month(in_time)=11 and artical_id!=0
group by
dt
order by
avg_viiew_len_sec

有日期限制,有页面限制,有分母限制

发表于 2023-08-12 14:10:07 回复(0)

Mysql

War张

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)

Mysql

Quinlan201803241302548

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

发表于 2023-07-06 19:53:33 回复(0)

Mysql

大炑炑他大姨

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

发表于 2023-06-14 17:39:34 回复(0)

Mysql

beaverh

SELECT
    DATE_FORMAT(in_time,'%Y-%m-%d') AS dt,
    ROUND(SUM(IF(artical_id=0,0,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'
GROUP BY 1
ORDER BY 2

发表于 2023-06-02 17:25:36 回复(0)

Mysql

快乐柯基

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

发表于 2023-05-21 16:01:51 回复(0)

Mysql

长白山啊

#好几个坑
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 YEAR(in_time) = '2021' AND MONTH(in_time) = '11'
    AND  artical_id != '0'
group by
    dt
order by
    avg_viiew_len_sec

发表于 2023-05-06 11:15:24 回复(0)

Mysql

Kalina2023

select
    substr(out_time,1,10) 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
-- 2021年11月
and substr(out_time,1,7) = '2021-11'
group by substr(out_time,1,10)
order by round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1)

发表于 2023-03-27 22:41:29 回复(0)

Mysql

黑板与粉笔

两个时间不能直接相减

利用

TIMESTAMPDIFF(SECOND,in_time,out_time)

函数获取秒的时间差

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 year(in_time) = 2021 AND month(in_time) = 11 AND artical_id > 0
GROUP BY
dt
ORDER BY
avg_viiew_len_sec

发表于 2023-03-23 16:53:52 回复(0)

Mysql

爱给不给

select 
dt,
round(avg(sum_viiew_len_sec),1) as avg_viiew_len_sec
from(
select 
date_format(in_time,'%Y-%m-%d') as dt,
uid,
round(sum(timestampdiff(second,in_time,out_time)),1) as sum_viiew_len_sec
from  tb_user_log
where date_format(in_time,'%Y-%m') = '2021-11'
and artical_id != 0    
group by dt,uid
) a
group by dt
order by avg_viiew_len_sec;

发表于 2023-03-18 17:17:35 回复(0)

Mysql

我叫谢小盟，叫我查尔斯好了

# Y
select dt, round(sum(view_time)/count(distinct uid),1) as avg_view_len_sec

from 

(select 
uid,
date_format(in_time,"%Y-%m-%d") as dt,
abs(timestampdiff(second, in_time, out_time)) as view_time
from tb_user_log
where artical_id != 0) as t1

where month(dt) = "11"
group by dt
order by avg_view_len_sec asc

发表于 2023-03-04 04:06:40 回复(0)

Mysql

Einstein...

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 artical_id<>0 AND DATE_FORMAT(in_time,'%Y-%m')='2021-11'
GROUP BY DATE_FORMAT(in_time,'%Y-%m-%d')
ORDER BY avg_viiew_len_sec

发表于 2023-02-21 00:47:20 回复(0)

Mysql

牛客264648632号

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 artical_id<>0

and date_format(in_time,'%Y-%m')='2021-11'

group by dt

order by avg_viiew_len_sec

发表于 2023-02-10 14:26:45 回复(0)

Mysql

小宏的大牛之路

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 left(in_time,7)='2021-11' and artical_id!=0
group by date(in_time)
order by avg_viiew_len_sec;

发表于 2023-01-17 16:23:50 回复(0)