首页 > 试题广场 >

某乎问答11月份日人均回答量

[编程题]某乎问答11月份日人均回答量

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

现有某乎问答创作者回答情况表answer_tb如下（其中answer_date表示创作日期、author_id指创作者编号、issue_id表示问题id、char_len表示回答字数）：

请你统计11月份日人均回答量（回答问题数量/答题人数），按回答日期排序，结果保留两位小数，以上例子的输出结果如下：

示例1

输入

drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);

输出

2021-11-01|1.40
2021-11-02|2.00
2021-11-03|1.00
2021-11-04|2.00
2021-11-05|1.25

算法知识视频讲解

Mysql

给个工作吧求求了

这题有bug，同一用户在一天内回答了同一个问题两次，竟然也算两个问题

发表于 2022-04-08 19:04:41 回复(3)

Mysql

凌12

知识点一：

round(number,digits)

number,要四舍五入的数，digits是要小数点后保留的位数
如果 digits 大于 0，则四舍五入到指定的小数位。
如果 digits 等于 0，则四舍五入到最接近的整数。
如果 digits 小于 0，则在小数点左侧进行四舍五入。
如果round函数只有参数number,等同于digits 等于 0。

https://blog.csdn.net/weixin_43591141/article/details/108624083

知识点二：

count( ):用于计数；

distinct( ):去重；

select answer_date,round(count(issue_id)/count(distinct author_id),2)
from answer_tb
group by answer_date

发表于 2021-11-30 15:15:34 回复(0)

Mysql

酸菜鱼土豆大侠

【难度】：简单

【场景】：日人均回答量

【分类】：分组查询、日期函数

分析思路

难点：

1.回答问题数量不去重；答题人数去重

（1）统计11月份日人均回答量（回答问题数量/答题人数），按回答日期排序

注：进入是增加一个在线人数，出去是减少一个在线人数

[条件]：month(answer_date) = 11
[使用]：count(); month()

最终结果

select 查询结果 [创作日期;日人均回答量]
from 从哪张表中查询数据 [创作者回答情况表]
where 查询条件 [11月份]
group by 分组条件 [创作日期]
order by 对查询结果排序 [创作日期];

求解代码

方法一

case when

select
    answer_date,
    round(count(issue_id)/count(distinct author_id),2) as per_num
from answer_tb
where month(answer_date) = 11
group by answer_date
order by answer_date

发表于 2022-11-01 16:17:30 回复(0)

Mysql

透亮的星

select answer_date,round(count(issue_id)/count(distinct author_id),2) per_num
from answer_tb
where month(answer_date) = 11
group by answer_date
order by answer_date
#人均，人是distinct的

发表于 2021-12-15 19:36:18 回复(0)

蒋点数分

求人均回答问题次数吧，一道题同一天答多次计算多次

发表于 2023-04-19 18:36:12 回复(0)

宝宝别生气啊！

select answer_date, round(count(issue_id)/count(distinct author_id),2) per_num
from answer_tb
group by answer_date
order by answer_date

发表于 2022-01-13 18:55:56 回复(0)

Mysql

牛客484876145号

select answer_date,round(count(author_id)/count(distinct author_id),2) as per_num

from answer_tb

group by answer_date

having date(answer_date) between '2021-11-01' and '2021-11-30'

order by answer_date

发表于 2025-06-30 08:57:32 回复(0)

kirua丶

select

answer_date

,round(count(1)/count(distinct author_id),2)

from answer_tb

group by 1

order by 1

发表于 2025-05-20 19:54:24 回复(0)

Mysql

等offer的萝卜

SELECT answer_date, ROUND(COUNT(*)/COUNT(DISTINCT author_id), 2) AS per_cnt

FROM answer_tb

GROUP BY answer_date;

发表于 2025-04-09 21:31:11 回复(0)

牛客775456359号

select
date(answer_date) answer_date
,round(count(issue_id)/count(distinct author_id),2) per_num
from answer_tb
where month(answer_date)='11'
group by answer_date
order by 1;

发表于 2025-03-13 11:49:58 回复(0)

牛客922835882号

select 
answer_date,
round(count(issue_id)/count(distinct author_id),2) per_num
from answer_tb
group by answer_date
order by answer_date;

发表于 2024-11-30 15:12:39 回复(0)

12347h

select
    answer_date,
    round(count(issue_id) / count(distinct author_id), 2) per_num
from
    answer_tb
where
    month (answer_date) = 11
group by
    answer_date
order by
    answer_date

发表于 2024-11-16 14:36:31 回复(0)

美丽的回笼觉觉主在刷代码

不是很理解为什么问题数量不用去重？

SELECT answer_date, 
ROUND(COUNT(issue_id) / COUNT(DISTINCT author_id),2) AS per_num
FROM answer_tb
GROUP BY answer_date
ORDER BY answer_date

发表于 2024-10-02 10:21:13 回复(0)

我真的蛮想拥有一个ofr的

select t1.answer_date,

round(t1.issue_num/t1.author_num,2) as per_num

from (select answer_date,

count(distinct author_id) as author_num,

count(issue_id) as issue_num #回答问题不查重，对回答过问题的人查重即可

from answer_tb

where month(answer_date)=11

group by answer_date) as t1

group by t1.answer_date

发表于 2024-09-11 21:58:30 回复(0)

爱刷美剧的柯基在许愿

select answer_date
,round(count(issue_id)/count(distinct author_id),2)
from answer_tb
group by 1
order by 1;

发表于 2024-08-04 19:46:07 回复(0)

炎煬

select answer_date,round(count(issue_id)/count(distinct author_id),2) as per_num

from answer_tb

where date(answer_date) between "2021-11-01" and "2021-11-30"

group by answer_date

order by answer_date

发表于 2024-07-14 21:43:37 回复(0)

小金桔_

select

answer_date,

round(count(issue_id)/count(distinct author_id),2) per_num

from answer_tb

where answer_date>='2021-11-01' and answer_date<='2021-11-30'

group by answer_date

order by answer_date

发表于 2024-07-12 11:25:19 回复(0)

Mysql

每天都很饿

select answer_date

,round(count( if(char_len>0,issue_id,null)) /count(distinct if(char_len>0,author_id,null)) ,2) per_num

from answer_tb

where date_format(answer_date ,"%Y-%m")="2021-11"

group by answer_date

order by answer_date

;

发表于 2024-07-11 17:10:21 回复(0)

幸运的LGGy

select
    answer_date,
    round(count(issue_id) / count(distinct author_id), 2) as per_num
from
    answer_tb
group by
    answer_date
order by
    answer_date

很多时候，要的是count(distinct id),但常常协程count(distinct dt,id)这种里面有冗余字段的情况。

实际上，count(distinct id)搭配上goup by dt 就能完成在每个dt下计数的作用

发表于 2024-06-17 18:06:50 回复(0)

风ii

select
    answer_date,
    round(count(author_id)/count(distinct (author_id)),2) as per_num
from
    answer_tb
where
    substring(answer_date, 1, 7)
group by
    answer_date
order by answer_date;

发表于 2024-05-02 15:08:25 回复(0)