通过SQL计算同时在线问题-德赢Vwin官网网

本文是通过 SQL 计算同时在线问题，即求最高在线人数以及最高峰时间段。

0 需求分析

数据为主播ID，stt表示开播时间，edt表示下播时间。

idsttedt

10012021-06-14 12122021-06-14 1812

10032021-06-14 13122021-06-14 1612

10042021-06-14 13122021-06-14 2012

10022021-06-14 15122021-06-14 1612

10052021-06-14 15122021-06-14 2012

10012021-06-14 20122021-06-14 2312

10062021-06-14 21122021-06-14 2312

10072021-06-14 22122021-06-14 2312

求：

（1）该平台某一天主播同时在线人数最高为多少？

（2）出现最高峰的时间段是哪个时间？

1 数据准备

（1）数据

vim play.txt

idstt edt

10012021-06-14 1212 2021-06-14 1812

10032021-06-14 13122021-06-14 1612

10042021-06-14 13122021-06-14 2012

10022021-06-14 15122021-06-14 1612

10052021-06-14 15122021-06-14 2012

10012021-06-14 20122021-06-14 2312

10062021-06-14 21122021-06-14 2312

10072021-06-14 22122021-06-14 2312

（2）建表

create table if not exists play（

id string，

stt string，

edt string

）

row format delimitedfields terminated by ‘ ’

;

（3 ）加载数据

load data local inpath “/home/centos/dan_test/play.txt” into table play;

（4）查询数据

hive》 select * from play;

1001 2021-06-14 12:12:12 2021-06-14 18:12:121003 2021-06-14 13:12:12 2021-06-14 16:12:121004 2021-06-14 13:15:12 2021-06-14 20:12:121002 2021-06-14 15:12:12 2021-06-14 16:12:121005 2021-06-14 15:18:12 2021-06-14 20:12:121001 2021-06-14 20:12:12 2021-06-14 23:12:121006 2021-06-14 21:12:12 2021-06-14 23:15:121007 2021-06-14 22:12:12 2021-06-14 23:10:12

Time taken： 0.087 seconds， Fetched： 8 row（s）

2 数据分析

问题1分析：

本题如果直接从SQL本身很难下手，无从做起，不妨我们换个思路，假定我们拿到的是一条数据，现在用java程序怎么做？其实就是一个累加器的思想（如SPARK的累加器）。首先我们需要将这样一条记录进行拆分，分成不同的记录或数据流进入累加器，然后给每条记录进行标记，如果开播的话该条记录记为1，下播的话记为-1，此时的数据流按照时间顺序依次进入累加器，然后在累加器中进行叠加，其中累计的结果最大时候就是所求的结果。其实本质是利用累加器思想，但进入累加器的数据是按时间排好序的时序流数据（数据进入按时间先后顺序进入）。

上述思路总结如下：

（1）将数据切分（按起始时间和结束时间）

（2）数据进行标签，开播的记录为记为1，下播的记录记为-1用于累加

（2）将数据按时间进行排序

（3）数据进入累加器进行累加

（4）获取累加器中当前累加值最大的数值

有了以上思路后，我们将其转换为SQL求解思路。

（1）将数据切分：实际上就是将开播时间和下播时间转换成一条条记录。也就是列转行，我们用熟悉的UNION操作，进行转换。

select id，stt dt from play

unionselect id，edt dt from play

--------------------------------------------------------------------------------

1001 2021-06-14 12:12:121001 2021-06-14 18:12:121001 2021-06-14 20:12:121001 2021-06-14 23:12:121002 2021-06-14 15:12:121002 2021-06-14 16:12:121003 2021-06-14 13:12:121003 2021-06-14 16:12:121004 2021-06-14 13:15:121004 2021-06-14 20:12:121005 2021-06-14 15:18:121005 2021-06-14 20:12:121006 2021-06-14 21:12:121006 2021-06-14 23:15:121007 2021-06-14 22:12:121007 2021-06-14 23:10:12

Time taken： 20.502 seconds， Fetched： 16 row（s）

（2）数据标记。在上述SQL基础上直接进行标记即可。如果数据本来就是分开的则用case when进行标记。

select id，stt dt ， 1 flag from play

unionselect id，edt dt ，-1 flag from play

--------------------------------------------------------------------------------

1001 2021-06-14 12:12:12 11001 2021-06-14 18:12:12 -11001 2021-06-14 20:12:12 11001 2021-06-14 23:12:12 -11002 2021-06-14 15:12:12 11002 2021-06-14 16:12:12 -11003 2021-06-14 13:12:12 11003 2021-06-14 16:12:12 -11004 2021-06-14 13:15:12 11004 2021-06-14 20:12:12 -11005 2021-06-14 15:18:12 11005 2021-06-14 20:12:12 -11006 2021-06-14 21:12:12 11006 2021-06-14 23:15:12 -11007 2021-06-14 22:12:12 11007 2021-06-14 23:10:12 -1

Time taken： 7.408 seconds， Fetched： 16 row（s）

（3）数据按照时间排序，进入累加器进行累加（按时间排序是累加的关键）

select id

，dt

，sum（flag） over（order by dt） as cur_cnt

from（

select id，stt dt ， 1 flag from play

union

select id，edt dt ，-1 flag from play

） t

--------------------------------------------------------------------------------

1001 2021-06-14 12:12:12 11003 2021-06-14 13:12:12 21004 2021-06-14 13:15:12 31002 2021-06-14 15:12:12 41005 2021-06-14 15:18:12 51002 2021-06-14 16:12:12 31003 2021-06-14 16:12:12 31001 2021-06-14 18:12:12 21001 2021-06-14 20:12:12 11004 2021-06-14 20:12:12 11005 2021-06-14 20:12:12 11006 2021-06-14 21:12:12 21007 2021-06-14 22:12:12 31007 2021-06-14 23:10:12 21001 2021-06-14 23:12:12 11006 2021-06-14 23:15:12 0

Time taken： 8.133 seconds， Fetched： 16 row（s）

（4）获取累加器中当前时刻累加的最大值，即为同时开播最多的人数

select max（cur_cnt）

from（

select id

，dt

，sum（flag） over（order by dt） as cur_cnt

from（

select id，stt dt ， 1 flag from play

union

select id，edt dt ，-1 flag from play

） t

） m

--------------------------------------------------------------------------------

Time taken： 13.087 seconds， Fetched： 1 row（s）

问题2分析：

第二问求的是出现高峰时的时间段，也就是高峰时间的起始时间及结束时间，或持续时长。

借鉴第一问的结果进行分析：

select *，max（cur_cnt） over（）

from（

select id

，dt

，sum（flag） over（order by dt） as cur_cnt

from（

select id，stt dt ， 1 flag from play

union

select id，edt dt ，-1 flag from play

） t

） m

通过上图我们可以看出当由峰值出的记录时间到下一条记录人数减少的时候这一段时间即为峰值持续的时间，或高峰的时间段，也就是求出峰值的下一条记录的时间与峰值对应记录的时间即为高峰时间段，因此利用lead（）函数很容易求出问题的答案。SQL如下：

select max_cur_cnt

，dt as start_time

，lead_dt as end_time

from（

select *

，lead（dt，1，dt） over（order by dt） lead_dt

from（

select *，max（cur_cnt） over（） as max_cur_cnt

from（

select id

，dt

，flag

，sum（flag） over（order by dt） as cur_cnt

from（

select id，stt dt ， 1 flag from play

union

select id，edt dt ，-1 flag from play

） t

） m

） n

） p

where cur_cnt=max_cur_cnt

计算结果如下：

--------------------------------------------------------------------------------

5 2021-06-14 15:18:12 2021-06-14 16:12:12

Time taken： 17.513 seconds， Fetched： 1 row（s）

3 小结

本文针对SQL统计同时在线人数问题进行了分析，利用累加器思想对该问题进行求解，最终划归为时序数据，进行时序数据分析（常用技巧：打标签，形成序列，多序列进行分析），最后利用sum（） over（）对标签进行累加求出当前在线人数本题最关键的点在于转换为时序数据及累加器的思想，望读者能够掌握。

事实上该问题的分析在业务上具有重要的意义，我们能够实时跟踪随着时间变化的在线人数，了解服务器的负载变化情况，服务器的实时并发数等。该问题在不同业务场景下，有不同意义，比如某个游戏的同时在线人数，比如某个服务器的实时并发数，比如某个仓库的货物积压数量，某一段时间内的同时处于服务过程中的最大订单量等。实际上求最大在线人数和求实时在线人数是一回事，最大人数依赖于当前在线人数表，只有先求出当前在线人数表，才能求出最大同时在线人数。

不谋全局者，不足以谋一域。

不谋万世者，不足以谋一时。

作者：石榴公子YYDS

https://blog.csdn.net/godlovedaniel/article/details/118651811

责任编辑：haq

声明：本文内容及配图由入驻作者撰写或者入驻合作网站授权转载。文章观点仅代表作者本人，不代表德赢Vwin官网网立场。文章及其配图仅供工程师学习之用，如有内容侵权或者其他违规问题，请联系本站处理。举报投诉

数据

数据

+关注

关注
8

文章
7002

浏览量
88938
SQL

SQL

+关注

关注
1

文章
762

浏览量
44114

原文标题：3 小结

文章出处：【微信号：DBDevs，微信公众号：数据分析与开发】欢迎添加关注！文章转载请注明出处。

SQL错误代码及解决方案

在SQL数据库开发和管理中，常见的错误代码及其解决方案可以归纳如下：一、语法错误（Syntax Errors）错误代码：无特定代码，但通常会在错误消息中明确指出是语法错误。原因：SQL语句

发表于 11-19 10:21 •1322次阅读

常用SQL函数及其用法

的 SQL 函数及其用法：一、聚合函数（Aggregate Functions）聚合函数对一组值执行计算，并返回单个值。 COUNT() 用途：返回匹配指定条件的行数。示例： SELECT

发表于 11-19 10:18 •227次阅读

SQL与NoSQL的区别

景。 SQL数据库 SQL数据库，也称为关系型数据库管理系统（RDBMS），是一种基于关系模型的数据库。它使用表格、行和列来组织数据，并通过SQL语言进行数据的查询和管理。特点结构

发表于 11-19 10:15 •153次阅读

KM振动在线监测系统助力设备焕新升级#振动在线监测#振动监测#在线监测

在线监测

KM预测性维护专家

发布于 :2024年10月23日 14:59:48

大数据从业者必知必会的Hive SQL调优技巧

不尽人意。本文针对Hive SQL的性能优化进行深入研究，提出了一系列可行的调优方案，并给出了相应的优化案例和优化前后的SQL代码。通过合理的优化策略和技巧，能够显著提升Hive SQL

发表于 09-24 13:30 •234次阅读

数据库数据恢复—SQL Server数据库出现823错误的数据恢复案例

SQL Server数据库故障： SQL Server附加数据库出现错误823，附加数据库失败。数据库没有备份，无法通过备份恢复数据库。 SQL Server数据库出现823错误的

发表于 09-20 11:46 •338次阅读

IP 地址在 SQL 注入攻击中的作用及防范策略

SQL 注入是通过将恶意的 SQL 代码插入到输入参数中，欺骗应用程序执行这些恶意代码，从而实现对数据库的非法操作。例如，在一个登录表单中，如果输入的用户名被直接拼接到 SQL 查询

发表于 08-05 17:36 •296次阅读

恒讯科技分析：sql数据库怎么用？

。 2、安装数据库软件：在您的服务器或本地计算机上安装所选的数据库软件。 3、配置数据库服务器：根据需要配置数据库服务器设置，包括内存分配、存储位置、网络配置等。 4、创建数据库：使用SQL命令或数据库管理工具创建一个新的数据库实例。 5、设计数据库模式：设计数据

发表于 07-15 14:40 •343次阅读

什么是 Flink SQL 解决不了的问题？

简介在实时数据开发过程中，大家经常会用 Flink SQL 或者 Flink DataStream API 来做数据加工。通常情况下选用2者都能加工出想要的数据，但是总会有 Flink SQL

发表于 07-09 20:50 •292次阅读

甲骨文发布AI编程助手，助用户编写Java、SQL程序

据悉，Oracle Code Assist基于甲骨文先进的云计算平台——Oracle Cloud Infrastructure （OCI）构建，重点优化各项Java、SQL程序以及OCI应用开发环节。

发表于 05-13 10:19 •534次阅读

SQL全外连接剖析

SQL中的全外连接是什么？在SQL中，FULLOUTERJOIN组合左外连接和右外连接的结果，并返回连接子句两侧表中的所有（匹配或不匹配）行。接下面sojson给大家详细讲解。图解：SQL全

发表于 03-19 18:28 •2219次阅读

为什么需要监控SQL服务器？

服务器是存储、处理和管理数据的关系数据库管理系统 (RDBMS) 工具或软件，例如Microsoft的MSSQL、Oracle DB和PostgreSQL。此外，服务器执行SQL查询和命令来操作关系数据库。实际上，SQL服务器将托管数据库和

发表于 02-19 17:19 •467次阅读

如何用Rust过程宏魔法简化SQL函数呢？

这是 RisingWave 中一个 SQL 函数的实现。只需短短几行代码，通过在 Rust 函数上加一行过程宏，我们就把它包装成了一个 SQL 函数。

发表于 01-23 09:43 •939次阅读

分布式无纸化交互系统如何支持多用户同时在线编辑和协作？

分布式无纸化交互系统通过以下方式支持多用户同时在线编辑和协作：实时同步技术：系统使用实时同步技术，确保多个用户在同时编辑同一份文档时，每个人都能看到其他人的操作，并能够实时互动和协作。版本控制

发表于 01-15 15:33 •404次阅读

labview与sql数据库连接5种方法

连接LabVIEW和SQL数据库是一种常见的需求，可以通过多种方法实现。本文将介绍五种连接LabVIEW和SQL数据库的方法。方法一：使用ADO.NET连接数据库 ADO.NET是一个用于访问

发表于 01-07 16:01 •4824次阅读

搜索历史

通过SQL计算同时在线问题

评论

SQL错误代码及解决方案

常用SQL函数及其用法

SQL与NoSQL的区别

KM振动在线监测系统助力设备焕新升级#振动在线监测#振动监测#在线监测

大数据从业者必知必会的Hive SQL调优技巧

数据库数据恢复—SQL Server数据库出现823错误的数据恢复案例

IP 地址在 SQL 注入攻击中的作用及防范策略

恒讯科技分析：sql数据库怎么用？

什么是 Flink SQL 解决不了的问题？

甲骨文发布AI编程助手，助用户编写Java、SQL程序

SQL全外连接剖析

为什么需要监控SQL服务器？

如何用Rust过程宏魔法简化SQL函数呢？

分布式无纸化交互系统如何支持多用户同时在线编辑和协作？

labview与sql数据库连接5种方法