SQL分类练习(一):同时在线人数

这篇具有很好参考价值的文章主要介绍了SQL分类练习(一):同时在线人数。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

题型:求同时在线人数

  1. SQL 163 每篇文章同一时刻最大在看人数
  2. SQL 179 各城市最大同时等车人数
  3. SQL189 牛客直播各科目同时在线人数

1. 每篇文章同一时刻最大在看人数

题目描述

用户行为日志表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:11

0

2

102

9001

2021-11-01 10:00:09

2021-11-01 10:00:38

0

3

103

9001

2021-11-01 10:00:28

2021-11-01 10:00:58

0

4

104

9002

2021-11-01 11:00:45

2021-11-01 11:01:11

0

5

105

9001

2021-11-01 10:00:51

2021-11-01 10:00:59

0

6

106

9002

2021-11-01 11:00:55

2021-11-01 11:01:24

0

7

107

9001

2021-11-01 10:00:01

2021-11-01 10:01:50

0

(uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到)

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

问题:统计每篇文章同一时刻最大在看人数,如果同一时刻有进入也有离开时,先记录用户数增加再记录减少,结果按最大人数降序。

输出示例:

示例数据的输出结果如下

artical_id

max_uv

9001

3

9002

2

解释:10点0分10秒时,有3个用户正在浏览文章9001;11点01分0秒时,有2个用户正在浏览文章9002。

解题思路

1. 进入时间 和离开时间分别记为 1 和 -1, 分别统计,最后用union all 连接

 -- in_time 和 out_time 都标记为 num,分别用 1 和-1 代表
 with t1 as (
     select artical_id, in_time dt,1 num from tb_user_log 
         where artical_id != 0
     union all
     select artical_id, out_time dt, -1 num from tb_user_log
         where artical_id != 0
 )

2. 使用窗口函数,sum(num)over()实现对同时在线人数的统计

select artical_id, 
    sum(num) over(partition by artical_id order by dt asc,num desc) as cnt
from
    t1

3. 按照article_id分组,求取每组的最大num

select artical_id, max(cnt) max_uv from
(
    select artical_id, 
    sum(num) over(partition by artical_id order by dt asc,num desc) as cnt
    from
        t1   
)

2. 各城市最大同时等车人数

题目描述

用户打车记录表tb_get_car_record

id

uid

city

event_time

end_time

order_id

1

108

北京

2021-10-20 08:00:00

2021-10-20 08:00:40

9008

2

118

北京

2021-10-20 08:00:10

2021-10-20 08:00:45

9018

3

102

北京

2021-10-20 08:00:30

2021-10-20 08:00:50

9002

4

106

北京

2021-10-20 08:05:41

2021-10-20 08:06:00

9006

5

103

北京

2021-10-20 08:05:50

2021-10-20 08:07:10

9003

6

104

北京

2021-10-20 08:01:01

2021-10-20 08:01:20

9004

7

105

北京

2021-10-20 08:01:15

2021-10-20 08:01:30

9019

8

101

北京

2021-10-20 08:28:10

2021-10-20 08:30:00

9011

(uid-用户ID, city-城市, event_time-打车时间, end_time-打车结束时间, order_id-订单号)

打车订单表tb_get_car_order

id

order_id

uid

driver_id

order_time

start_time

finish_time

mileage

fare

grade

1

9008

108

204

2021-10-20 08:00:40

2021-10-20 08:03:00

2021-10-20 08:31:00

13.2

38

4

2

9018

108

214

2021-10-20 08:00:45

2021-10-20 08:04:50

2021-10-20 08:21:00

14

38

5

3

9002

102

202

2021-10-20 08:00:50

2021-10-20 08:06:00

2021-10-20 08:31:00

10

41.5

5

4

9006

106

206

2021-10-20 08:06:00

2021-10-20 08:09:00

2021-10-20 08:31:00

8

25.5

4

5

9003

103

203

2021-10-20 08:07:10

2021-10-20 08:15:00

2021-10-20 08:31:00

11

41.5

4

6

9004

104

204

2021-10-20 08:01:20

2021-10-20 08:13:00

2021-10-20 08:31:00

7.5

22

4

7

9019

105

205

2021-10-20 08:01:30

2021-10-20 08:11:00

2021-10-20 08:51:00

10

39

4

8

9011

101

211

2021-10-20 08:30:00

2021-10-20 08:31:00

2021-10-20 08:54:00

10

35

5

(order_id-订单号, uid-用户ID, driver_id-司机ID, order_time-接单时间, start_time-开始计费的上车时间, finish_time-订单完成时间, mileage-行驶里程数, fare-费用, grade-评分)

场景逻辑说明:

  • 用户提交打车请求后,在用户打车记录表生成一条打车记录,订单号-order_id设为null;
  • 当有司机接单时,在打车订单表生成一条订单,填充接单时间-order_time及其左边的字段,上车时间及其右边的字段全部为null,并把订单号和接单时间(打车结束时间)写入打车记录表;若一直无司机接单、超时或中途用户主动取消打车,则记录打车结束时间。
  • 若乘客上车前,乘客或司机点击取消订单,会将打车订单表对应订单的订单完成时间-finish_time填充为取消时间,其余字段设为null。
  • 当司机接上乘客时,填充打车订单表中该订单的上车时间start_time。
  • 当订单完成时填充订单完成时间、里程数、费用;评分设为null,在用户给司机打1~5星评价后填充。

问题:请统计各个城市在2021年10月期间,单日中最大的同时等车人数。

注:   等车指从开始打车起,直到取消打车、取消等待或上车前的这段时间里用户的状态。

如果同一时刻有人停止等车,有人开始等车,等车人数记作先增加后减少。

结果按各城市最大等车人数升序排序,相同时按城市升序排序。

解题思路

回归主题: 最大同时等车人数 ,可以理解为最大同时在线人数。

1. 确定状态: 等车时间指的是 从开始打车起,直到取消打车、取消等待或上车前的这段时间里用户的状态。

打车开始时间:event_time

等车结束时间:三种情况:取消打车 | 取消等待 |上车

分别对应 order_id is null, start_time is null , start_time。

分类使用 case when ,构建临时表

with t1 as(
    select tcr.uid, city, event_time, 
    (case when tcr.order_id is null then end_time
                when start_time is null then finish_time
                when start_time is not null then start_time end) as e_time
    from tb_get_car_record tcr
    join tb_get_car_order tco
    using(order_id)
    where date(event_time) between '2021-10-1' and '2021-10-31'
)

2. 在获得临时表之后的操作就和求同时在线人数一样, 等车开始时间和等车结束时间分别做统计,区别是开始是1 , 结束是-1, 最后使用union all 联结。

SELECT city,event_time AS dt,1 AS num FROM t1 
UNION ALL 
SELECT city,e_time AS dt,- 1 AS num FROM t1 

3. 按照city,时间分区,按照时间升序,人数降序排序。

SELECT city,
sum( num ) over ( PARTITION BY city,date(dt) order by dt, num desc ) total_num
FROM
        (
        SELECT
            city,
            event_time AS dt,
            1 AS num 
        FROM
            t1 UNION ALL
        SELECT
            city,
            e_time AS dt,
            - 1 AS num 
        FROM
            t1 
        ) t2  

4. 按照city分组,统计求最大的同时在线人数

SELECT
    city,
    max( total_num ) max_wait_uv 
FROM
    (
    SELECT
        city,
        sum( num ) over ( PARTITION BY city,date(dt) order by dt, num desc ) total_num
    FROM
        (
        SELECT
            city,
            event_time AS dt,
            1 AS num 
        FROM
            t1 UNION ALL
        SELECT
            city,
            e_time AS dt,
            - 1 AS num 
        FROM
            t1 
        ) t2 
    ) t3 
GROUP BY
    city 
ORDER BY
    max_wait_uv,
    city

 3. 牛客直播各科目同时在线人数

问题描述

牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。

已知课程表course_tb如下(其中course_id代表课程编号,course_name表示课程名称,course_datetime代表上课时间):

course_id

course_name

course_datetime

1

Python

2021-12-1 19:00-21:00

2

SQL

2021-12-2 19:00-21:00

3

R

2021-12-3 19:00-21:00

上课情况表attend_tb如下(其中user_id表示用户编号、course_id代表课程编号、in_datetime表示进入直播间的时间、out_datetime表示离开直播间的时间):

user_id

course_id

in_datetime

out_datetime

100

1

2021-12-01 19:00:00

2021-12-01 19:28:00

100

1

2021-12-01 19:30:00

2021-12-01 19:53:00

101

1

2021-12-01 19:00:00

2021-12-01 20:55:00

102

1

2021-12-01 19:00:00

2021-12-01 19:05:00

104

1

2021-12-01 19:00:00

2021-12-01 20:59:00

101

2

2021-12-02 19:05:00

2021-12-02 20:58:00

102

2

2021-12-02 18:55:00

2021-12-02 21:00:00

104

2

2021-12-02 18:57:00

2021-12-02 20:56:00

107

2

2021-12-02 19:10:00

2021-12-02 19:18:00

100

3

2021-12-03 19:01:00

2021-12-03 21:00:00

102

3

2021-12-03 18:58:00

2021-12-03 19:05:00

108

3

2021-12-03 19:01:00

2021-12-03 19:56:00

请你统计每个科目最大同时在线人数(按course_id排序),以上数据的输出结果如下:

course_id

course_name

max_num

1

Python

4

2

SQL

4

3

R

3

解题思路

1. 确定开始和结束时间,in_datetime 和 out_datetime, 然后分别标记为1和-1

2. 使用窗口函数sum()对num进行求和

3. 课程表和上课情况表连接,可以使用笛卡尔积,也可以 情况表左连接课程表,然后按照course_id 和course_name 进行分组

注意: 只按照course_id会报错,意思是course_name不依赖于course_id

contains nonaggregated column 'c.course_name' which is not functionally dependent on columns in GROUP BY clause; 文章来源地址https://www.toymoban.com/news/detail-559705.html

select
    b.course_id,
    c.course_name,
    max(cnt) max_num
from(
    select course_id,sum(num) over (partition by course_id order by dt asc,num desc) cnt
from(
    select course_id,in_datetime dt,1 num from attend_tb
    union all
   select course_id,out_datetime dt,-1 num from attend_tb)
 as a
)as binner join course_tb c on c.course_id = b.course_id
    group by c.course_id,c.course_name
    order by c.course_id

到了这里,关于SQL分类练习(一):同时在线人数的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • GaussDB高斯数据库(SQL语法分类)

    日常查询中,最常用的是通过FROM子句实现的查询。 语法格式:使用方法: SELECT [ , ... ] FROM table_reference [ , ... ] SELECT之后和FROM子句之前出现的表达式称为SELECT项。SELECT项用于指定要查询的列,FROM指定要从哪个表中查询。如果要查询所有列,可以在SELECT后面使用*号,如

    2024年02月16日
    浏览(48)
  • 用MariaDB创建数据库,SQL练习,MarialDB安装和使用

    前言:MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可 MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。在存储引擎方面,使用XtraDB来代替MySQL的InnoDB。  Download MariaDB Server - MariaDB.org 注意:安装路径别选C盘 期间

    2024年02月13日
    浏览(41)
  • 数据库SQL语言实战(五)(数据库系统概念第三章练习题)

    目录 前言知识 一、 关系模式 二、 属性域 例子 介绍 作用 三、Select常数 举例 解释  四、集合差运算 本质 举例  结论 练习题 3.17 3.18  3.21  总结  注:本文的SQL语言适用的是 Oracle数据库 与mySQL可能存在略微不同 模式的定义 :模式则是指数据库中 所有关系模式 的集合,它

    2024年04月22日
    浏览(61)
  • 数据库系统概述——第三章 关系数据库标准语言SQL(知识点复习+练习题)

    🌟 博主: 命运之光 🦄 专栏: 离散数学考前复习(知识点+题) 🍓 专栏: 概率论期末速成(一套卷) 🐳 专栏: 数字电路考前复习 🦚 专栏: 数据库系统概述 ☀️ 博主的其他文章: 点击进入博主的主页​​​​​ 前言: 身为大学生考前复习一定十分痛苦,你有没有过

    2024年02月10日
    浏览(68)
  • MySql学习2:SQL分类、数据库操作、表操作、数据的增删改查

    SQL分类: DDL:数据定义语言,用来定义数据库对象(数据库、表、字段) DML:数据操作语言,用来对数据库表中的数据进行增删改 DQL:数据库查询语言,用来查询数据库表中的记录 DCL:数据控制语言,用来创建数据库用户、控制数据库的访问权限 查询所有数据库 查询当前

    2024年02月11日
    浏览(53)
  • sql在线练习

    SQLBolt - 学习 SQL - SQL 简介 https://sqlbolt.com/ 拿走不谢!!! UIUC什么乱七八糟的啊

    2024年02月08日
    浏览(31)
  • SQL语言的分类:DDL(数据库、表的增、删、改)、DML(数据的增、删、改)

    数据库管理系统(数据库软件)功能非常多,不仅仅是存储数据,还要包含:数据的管理、表的管理、库的管理、账户管理、权限管理等。 操作数据库的SQL语言,基于功能,划分为4类: 1、数据定义:DDL(Data Definition Language) 2、数据操纵:DML(Data Manipulation Language) 3、数据

    2024年02月09日
    浏览(48)
  • 推荐几个适合新手入门学习的SQL网站,在线就能练习

    这里整理推荐几个我自己学习时用过的在线学习网站,对新手非常友好,帮助初学者快速入门SQL,在交互式的环境里学习,既不用安装也不用导入数据,在线就能思考和练习。 1.自学SQL网 适合小白学习,这里由浅及深的介绍了SQL的知识,每一个章节是一组相关的SQL知识点且配备着

    2024年02月15日
    浏览(34)
  • SQL计算出每年在校人数

    以下是一个录取学生人数表的示例,记录了每年录取学生的人数和入学学制。  id year num stu_len 1 2018 101 3 2 2019 121 4 3 2020 91 2 4 2021 151 3 5 2021 141 2 6 2022 161 3 字段解释: id:记录的唯一标识符 year:学生入学年度 num:对应年度录取的学生人数 stu_len:录取学生的学制 举例说明:例

    2024年02月16日
    浏览(73)
  • 「SQL面试题库」 No_90 求团队人数

    「SQL面试题库」是由 不是西红柿 发起,全员免费参与的SQL学习活动。我每天发布1道SQL面试真题,从简单到困难,涵盖所有SQL知识点,我敢保证只要做完这100道题,不仅能轻松搞定面试,代码能力和工作效率也会有明显提升。 1.1 活动流程 整理题目 :西红柿每天无论刮风下雨

    2024年02月08日
    浏览(39)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包