SQL Server 游标运用:鼠标轨迹字符串分割

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 原文:SQL Server 游标运用:鼠标轨迹字符串分割一.本文所涉及的内容(Contents) 本文所涉及的内容(Contents) 背景(Contexts) 游标模板(Cursor Template) 鼠标轨迹字符串分割SQL脚本实现(SQL Codes) 补充说明(Addon) 二.背景(Contexts)   我们的系统中记录了用户的鼠标行为轨迹字符串,这些字符串的格式是:PosSet:[573,1103,2010-09-03 22:32:35],[864,110,2010-09-03 22:32:57],这个字段表示用户点击页面的X坐标,Y坐标,时间。
原文: SQL Server 游标运用:鼠标轨迹字符串分割

一.本文所涉及的内容(Contents)

  1. 本文所涉及的内容(Contents)
  2. 背景(Contexts)
  3. 游标模板(Cursor Template)
  4. 鼠标轨迹字符串分割SQL脚本实现(SQL Codes)
  5. 补充说明(Addon)

二.背景(Contexts)

  我们的系统中记录了用户的鼠标行为轨迹字符串,这些字符串的格式是:PosSet:[573,1103,2010-09-03 22:32:35],[864,110,2010-09-03 22:32:57],这个字段表示用户点击页面的X坐标,Y坐标,时间。现在要求对这样字符串进行分割。

  需要注意的是当字符串只有一个坐标的时候,如:PosSet:[513,1303,2010-09-03 22:34:35],你需要考虑这种情况的处理,因为这个时候就没有分隔字符在字符串了。

  还需要考虑字符串不规则的时候的异常处理;

wps_clip_image-25473

(Figure1:基础数据)

wps_clip_image-3548

(Figure2:实现效果图)

三.游标模板(Cursor Template)

  在正式解决问题之前我先提供一个游标的模板,它简单的实现了找出数据库中所有的数据库名,其实这个模板的目的是为了提供一个规范化的游标SQL代码模板。

--游标模板
DECLARE @databaseName VARCHAR(100)

DECLARE @itemCur CURSOR
SET @itemCur = CURSOR FOR 
    SELECT name FROM sys.databases 
        WHERE database_id > 4

OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @databaseName
WHILE @@FETCH_STATUS=0
BEGIN
    --逻辑处理
    PRINT @databaseName
    
    FETCH NEXT FROM @itemCur INTO @databaseName
END 

CLOSE @itemCur
DEALLOCATE @itemCur

wps_clip_image-27448

(Figure3:返回数据库名)

四.鼠标轨迹字符串分割SQL脚本实现(SQL Codes)

(一) 首先创建测试表VisiteLog,并插入测试数据:

--创建测试表[VisiteLog]
CREATE TABLE [dbo].[VisiteLog](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [PosSet] [varchar](500) NULL,
 CONSTRAINT [PK_VisiteLog] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--插入测试数据
SET IDENTITY_INSERT [dbo].[VisiteLog] ON
INSERT [dbo].[VisiteLog] ([Id], [PosSet]) VALUES (1, N'[573,1103,2010-09-03 22:32:35],[864,110,2010-09-03 22:32:57]')
INSERT [dbo].[VisiteLog] ([Id], [PosSet]) VALUES (2, N'[513,1303,2010-09-03 22:34:35]')
SET IDENTITY_INSERT [dbo].[VisiteLog] OFF

--查看数据
SELECT * FROM [dbo].[VisiteLog]

clip_image001

(Figure4:基础数据)

 

(二) 接着创建表PosSetInfo,这个表是用来保存鼠标轨迹字符串分割后的数据:

--创建表[PosSetInfo]
CREATE TABLE [dbo].[PosSetInfo](
    [ID] [int] NULL,
    [PosSet_x] [int] NULL,
    [PosSet_y] [int] NULL,
    [PosTime] [datetime] NULL
) ON [PRIMARY]
GO

 

(三) 根据鼠标轨迹字符串:[573,1103,2010-09-03 22:32:35],[864,110,2010-09-03 22:32:57]。它坐标点与坐标点之间的分隔符是“,”,X坐标与Y坐标同样适用分隔符“,”,这给我们的分割带来了一些不便,所以我们创建了一个函数,它的作用是把字符串@str以@split分隔符进行分隔,返回第@index次匹配的元素。如下图所示:

-- =============================================
-- Author:      <听风吹雨>
-- Create date: <2010/05/27>
-- Description: <把字符串@str以@split分隔符进行分隔,返回第@index次匹配的元素>
-- =============================================
CREATE FUNCTION [dbo].[Get_StrArrayStrOfIndex]
(
    @str VARCHAR(5000),  --要分割的字符串
    @split VARCHAR(10),  --分隔符号
    @index INT --取第几个元素
)
RETURNS VARCHAR(5000)
AS
BEGIN
    DECLARE @location INT
    DECLARE @start INT
    DECLARE @next INT
    DECLARE @seed INT
    SET @str=LTRIM(RTRIM(@str))
    SET @start=1
    SET @next=1
    SET @seed=LEN(@split)
  
    SET @location=CHARINDEX(@split,@str)
    WHILE @location<>0 and @index>@next
    BEGIN
        SET @start=@location+@seed
        SET @location=CHARINDEX(@split,@str,@start)
        SET @next=@next+1
    END
    IF @location =0 SELECT @location =LEN(@str)+1 
    RETURN SUBSTRING(@str,@start,@location-@start)
END

--测试函数
SELECT DBO.[Get_StrArrayStrOfIndex]('AB,CDE,FG,HIJK',',',3);

wps_clip_image-11407

(Figure5:函数测试效果)

 

(四) 下面的SQL脚本就是对VisiteLog表的数据进行分割,再把分割后的字符串保存到PosSetInfo表中,这个脚本的主要做法是先把[573,1103,2010-09-03 22:32:35],[864,110,2010-09-03 22:32:57]以“]”做为分隔符,把数据分割成两段:[573,1103,2010-09-03 22:32:35]和[864,110,2010-09-03 22:32:57],再以“,”做为分割符,找出X坐标、Y坐标和时间;

--鼠标轨迹字符串分割
DECLARE @ID int,@PosSet VARCHAR(2000)
DECLARE @splitlen INT
DECLARE @split NVARCHAR(10)
DECLARE @temp VARCHAR(100)
DECLARE @PosSet_x INT
DECLARE @PosSet_y INT
DECLARE @PosTime DATETIME
SET @split = ']'
DECLARE @itemCur CURSOR
SET @itemCur = CURSOR FOR 
    SELECT ID,PosSet FROM [VisiteLog]
        WHERE PosSet <> ''

OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @ID,@PosSet
WHILE @@FETCH_STATUS=0
BEGIN
    SET @splitlen=LEN(@split+'a')-2
    WHILE CHARINDEX(@split,@PosSet)>0
    BEGIN
        SET @Temp = LEFT(@PosSet,CHARINDEX(@split,@PosSet)-1)
        SET @Temp = replace(replace(@Temp,',[',''),'[','')
        SET @PosSet_x = dbo.Get_StrArrayStrOfIndex(@Temp,',',1)
        SET @PosSet_y = dbo.Get_StrArrayStrOfIndex(@Temp,',',2)
        SET @PosTime = dbo.Get_StrArrayStrOfIndex(@Temp,',',3)
        INSERT PosSetInfo VALUES(@ID,@PosSet_x,@PosSet_y,@PosTime)
        SET @PosSet=STUFF(@PosSet,1,CHARINDEX(@split,@PosSet)+@splitlen,'')
    END

    FETCH NEXT FROM @itemCur INTO @ID,@PosSet
END 

CLOSE @itemCur
DEALLOCATE @itemCur

执行完上面运用游标的SQL脚本,现在查看PosSetInfo表的数据,返回的结果如下图所示:

--查看数据
SELECT * FROM [dbo].[PosSetInfo]

clip_image001[4]

(Figure6:分割后数据)

五.补充说明(Addon)

(一) 如果需要循环每个数据库进行操作,你可以使用下面的SQL脚本:

--循环每个数据库
EXEC sp_MSForEachDB 'USE [?];
SELECT * FROM sys.database_files'

wps_clip_image-20534

(Figure7:sp_MSForEachDB效果图)

更多sp_MSForEachDB和sp_MSforeachtable的运用,可以参考:

SQL Server 游标运用:查看一个数据库所有表大小信息(Sizes of All Tables in a Database)

SQL Server 游标运用:查看所有数据库所有表大小信息(Sizes of All Tables in All Database)

 

(二) 步骤四中处理SQL代码可能性能不是最优的,因为就函数Get_StrArrayStrOfIndex的调用就已经非常频繁的,如果一个PosSet字段的值非常多,你可以考虑用一个表值函数返回一个表。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
打赏
0
0
0
0
217
分享
相关文章
MyBatis动态SQL字符串空值判断,这个细节99%的程序员都踩过坑!
本文深入探讨了MyBatis动态SQL中字符串参数判空的常见问题。通过具体案例分析,对比了`name != null and name != &#39;&#39;`与`name != null and name != &#39; &#39;`两种写法的差异,指出后者可能引发逻辑混乱。为避免此类问题,建议在后端对参数进行预处理(如trim去空格),简化MyBatis判断逻辑,提升代码健壮性与可维护性。细节决定成败,严谨处理参数判空是写出高质量代码的关键。
314 0
【SQL周周练】:利用行车轨迹分析犯罪分子作案地点
【SQL破案系列】第一篇: 如果监控摄像头拍下了很多车辆的行车轨迹,那么如何利用这些行车轨迹来分析车辆运行的特征,是不是能够分析出犯罪分子“踩点”的位置
80 15
Lindorm Ganos 一条 SQL 计算轨迹
Lindorm Ganos 针对轨迹距离计算场景提供了内置函数 ST_Length_Rows,结合原生时空二级索引和时空聚合计算下推技术,能够高效过滤数据并并行执行运算任务。该方案通过主键索引和时空索引快速过滤数据,并利用多Region并行计算轨迹点距离,适用于车联网等场景。具体步骤包括根据车辆识别代码和时间戳过滤数据、范围过滤轨迹点以及并行计算距离。使用限制包括只支持点类型列聚合运算及表中轨迹点需按顺序排列等。测试结果显示,Lindorm Ganos 在不同数据量下均能实现秒级响应。
84 3
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
11月前
|
在 SQL Server 中使用字符串转义
【8月更文挑战第5天】
956 7
在 SQL Server 中使用字符串转义
SQL语句中的引号使用技巧:正确处理字符串与标识符
在编写SQL语句时,引号的使用是一个基础且重要的环节
1267 0
|
12月前
|
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
333 13
|
12月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
210 9
SQL字符串查询有哪些坑?
本文通过创建一个包含不同格式姓名数据的表格,探讨了MySQL中字符排序规则(Collation)的影响。通过使用不区分大小写和空格的查询条件,文章演示了如何获取所有插入的记录,并解释了排序规则中&quot;_ci&quot;、&quot;_cs&quot;及&quot;_bin&quot;的区别。此外,还强调了在数据处理过程中,应考虑大小写敏感性和字符串前后空格的问题,以防导致统计或比较上的错误。最后,提供了Go语言中处理这类问题的方法,如使用`strings.EqualFold()`进行不区分大小写的字符串比较,以及使用`strings.TrimSpace()`去除字符串两端的空白字符。
121 1
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等

登录插画

登录以查看您的控制台资源

管理云资源
状态一览
快捷访问