sql - 如何摆脱游标？-6ren

sql - 如何摆脱游标？

转载作者：行者123 更新时间：2023-12-05 01:37:15

我编写了一个使用游标的 SQL 查询，我意识到它占用了服务器上的太多资源而且速度也很慢。

所以，这是表格:

Source

我需要计算 Status = Assigned 和 Status = In Progress 之间的时间差，其中 Assigned_group 或 prev_assigned_group 类似于 "%Hotline%"。

我也有一个序列，所以我选择不同的事件 ID 并按 Incident_Sequence 升序排列行，因为我必须从最小的序列号开始。

到目前为止一切顺利。

我从第一行开始，然后跳到下一行，直到我发现状态为“进行中”。找到状态后，我计算第一个分配的行与发现状态“进行中”的行之间的差异.

所以最大的问题是:我可以在没有光标的情况下执行此操作吗？如果是，那么如何？

SET NOCOUNT ON
DECLARE @day DATE

SET @day = '20160606'

CREATE TABLE #result(
[Assigned_Group] NVARCHAR(100),
[ProgressTime] INTEGER,
[Tickets] INTEGER,
[Avarage] FLOAT
)
INSERT INTO #result(Assigned_Group,ProgressTime,Tickets,Avarage)

SELECT DISTINCT Assigned_Group,0,0,0.0

       FROM [grs_dwh].[smt].[Aht]

   WHERE (Assigned_Group like 'CI-Hotline%' OR Prev_assigned_Group like 'CI-Hotline%')
      and CONVERT(DATE,Last_Modified_Date, 104) = @day

-- raw
SELECT [Incident_Sequence]
      ,[Incident_Id]
      ,[Assigned_Group]
      ,[Prev_assigned_Group] 
      ,[Status]
      ,[Last_Modified_Date]    
      ,[Service]

         INTO #rawData

  FROM [grs_dwh].[smt].[Aht]

WHERE (Assigned_Group like 'CI-Hotline%' OR Prev_assigned_Group like 'CI-Hotline%')
  and CONVERT(DATE,Last_Modified_Date, 104) = @day

  ORDER BY Incident_Sequence asc

  --CREATE TABLE #orderList(


  --)
  SELECT DISTINCT[Incident_id] INTO #incidentList FROM #rawData

  DECLARE cur0 CURSOR FOR SELECT incident_Id FROM #incidentList
  DECLARE @currentIncident NVARCHAR(15)

  OPEN cur0 

  FETCH next from cur0 INTO @currentIncident

  WHILE @@FETCH_STATUS = 0

       BEGIN
 -- PRINT @currentIncident
       SELECT * INTO #tmpTable FROM #rawData WHERE Incident_Id = @currentIncident ORDER BY Incident_Sequence

       DECLARE cur1 CURSOR FOR SELECT * FROM #tmpTable ORDER BY Incident_Sequence ASC

       DECLARE @incident_Sequence INTEGER
    DECLARE @incident_Id NVARCHAR(50)
    DECLARE @assigned_Group NVARCHAR(100)
       DECLARE @previous_Assiggned NVARCHAR(100)
    DECLARE @status NVARCHAR(50)
    DECLARE @last_Modified_Date DATETIME
    DECLARE @service NVARCHAR(50)

       DECLARE @progressFound BIT
       DECLARE @startProgressDate DATETIME
       DECLARE @ticketProgressTime INTEGER
       DECLARE @resultGroup NVARCHAR(100)
       SET @progressFound = 0

       OPEN cur1
       FETCH next from cur1

    INTO @incident_Sequence, @incident_Id, @assigned_Group, @previous_Assiggned, @status, @last_Modified_Date, @service

       WHILE @@FETCH_STATUS = 0

             BEGIN

                    IF @progressFound = 0 AND @status <> 'In Progress'
                    BEGIN

                    FETCH next from cur1 INTO @incident_Sequence, @incident_Id, @assigned_Group, @previous_Assiggned, @status, @last_Modified_Date, @service
                    CONTINUE
                    END

                    IF @progressFound = 0
                    BEGIN
                    SET @startProgressDate = @last_Modified_Date
                    SET @resultGroup = @assigned_Group
                    SET @progressFound = 1
                    FETCH next from cur1 INTO @incident_Sequence, @incident_Id, @assigned_Group, @previous_Assiggned, @status, @last_Modified_Date, @service
                    CONTINUE
                    END
                    ELSE
                    BEGIN
                           SET @ticketProgressTime = DATEDIFF(SECOND,  @startProgressDate,  @last_Modified_Date)


                           UPDATE #result SET ProgressTime = ProgressTime + @ticketProgressTime, Tickets = Tickets+1 WHERE Assigned_Group = @resultGroup
                           SET @ticketProgressTime = 0
                           SET @progressFound = 0
                    END
             FETCH next from cur1
             INTO @incident_Sequence, @incident_Id, @assigned_Group, @previous_Assiggned, @status, @last_Modified_Date, @service
             END
             CLOSE cur1
             DEALLOCATE cur1
             --IF @incident_Id = 'INC000010047798'
             --SELECT * FROM #tmpTable ORDER BY Incident_Sequence ASC
             DROP TABLE #tmpTable
             FETCH next from cur0 INTO @currentIncident
       END
  CLOSE cur0
  DEALLOCATE cur0
  SET NUMERIC_ROUNDABORT OFF
  UPDATE #result SET Avarage = CAST(ProgressTime AS float) / CASE WHEN Tickets = 0 THEN 1 ELSE CAST(Tickets AS float) END
   SELECT * FROM #result
   ORDER BY 1 asc
   DROP TABLE #result
   DROP TABLE #rawData
   DROP TABLE #incidentList

最佳答案

您可以使用 ROW_NUMBER跟踪序列并进行自连接以计算这些值的函数:

;WITH   Data    AS
(       -- Sample data (http://i.stack.imgur.com/TfzL7.png)
        SELECT  Id, Incident_Id Incident, Incident_Sequence Sequence,
                Prev_Assigned_Group Previous, Assigned_Group GroupName,
                Status, CAST(Last_Modified_Date AS DATETIME) Modified,
                ROW_NUMBER() OVER (PARTITION BY Incident_Id ORDER BY Id) RowNumber  -- Start over the count for every Incident_Id
        FROM    (   VALUES
                    (164293, 05, 'INC000010047798', 'Eastern Europe1'   , 'CI-Hotline North America', 'Assigned'   , '2016-06-04 12:28:46'),
                    (171241, 07, 'INC000010047798', 'CI-Hotline'        , 'Eastern Europe1'         , 'Assigned'   , '2016-06-06 06:42:16'),
                    (171919, 09, 'INC000010047798', 'CI-Hotline'        , 'Eastern Europe1'         , 'In Progress', '2016-06-06 06:46:19'),
                    (172138, 10, 'INC000010047798', 'CI-Hotline Romania', 'CI-Hotline'              , 'Assigned'   , '2016-06-06 06:46:35'),
                    (172483, 12, 'INC000010047798', 'CI-Hotline Romania', 'CI-Hotline'              , 'In Progress', '2016-06-06 07:11:53'),
                    (173003, 15, 'INC000010047798', 'Austria Adria3'    , 'CI-Hotline Romania'      , 'Assigned'   , '2016-06-06 07:15:36'),
                    (208011, 17, 'INC000010047798', 'Austria Adria3'    , 'CI-Hotline Romania'      , 'Resolved'   , '2016-06-10 12:14:05')
                )   AS X(Id, Incident_Sequence, Incident_Id, Assigned_Group, Prev_Assigned_Group, Status, Last_Modified_Date)
        WHERE   Assigned_Group LIKE '%HOTLINE%' OR 
                Prev_Assigned_Group LIKE '%HOTLINE%'
)
SELECT  Assigned.Incident,
        Assigned.Status + ' » ' + InProgress.Status,
        DATEDIFF(second, InProgress.Modified, Assigned.Modified) / 60.0 / 60.0 Hours
        --,Assigned.*, InProgress.*
FROM    Data Assigned
JOIN    Data InProgress
    ON  Assigned.Incident  = InProgress.Incident AND
        Assigned.RowNumber = InProgress.RowNumber + 1

下次，请以文本格式发送您的样本数据;-)

编辑:计算不同于'In Progress'的任何状态与下一个'In Progress'状态之间的时间差(或最后可用的状态)，使用此代码:

;WITH   Data    AS
(       -- Sample data (http://i.stack.imgur.com/TfzL7.png)
        SELECT  Id, Incident_Id Incident, Incident_Sequence Sequence,
                Prev_Assigned_Group Previous, Assigned_Group GroupName,
                Status, CAST(Last_Modified_Date AS DATETIME) Modified,
                ROW_NUMBER() OVER
                (   PARTITION BY Incident_Id
                    ORDER BY Incident_Sequence
                )   RowNumber       -- Start over the count for every Incident_Id
        FROM    [aht_data_one_day]
        WHERE   -- Incident_Id IN ('INC000010164572') AND
                (Assigned_Group LIKE '%HOTLINE%' OR Prev_Assigned_Group LIKE '%HOTLINE%')
)
SELECT      Assigned.Id, Assigned.Incident,
            CAST(Assigned.Sequence AS VARCHAR(5)) + ' » ' + 
            CAST(InProgress.Sequence AS VARCHAR(5)) Transition,
            DATEDIFF(second, Assigned.Modified, InProgress.Modified) TotalSeconds
            --, Assigned.*, InProgress.*
FROM        Data Assigned
JOIN        Data InProgress
        ON  Assigned.Status NOT IN ('In Progress') AND
            InProgress.Id = ISNULL(
                (   -- Try to locate the next 'In Progress' status
                    SELECT  MIN(Id)
                    FROM    Data
                    WHERE   Status IN ('In Progress') AND
                            Data.Incident = Assigned.Incident AND
                            Data.RowNumber > Assigned.RowNumber -- That's the trick
                ),
                (   -- If not found, get the latest status
                    SELECT  MAX(Id)
                    FROM    Data
                    WHERE   Data.Incident = Assigned.Incident
                ))
ORDER BY    Assigned.Incident, Assigned.Id

编辑:在您的数据更新中，我保留了之前的代码并稍作改动。下面是一个大型事件的逻辑验证和查询返回的数据:

Id          Incident        Transition    TotalSeconds
----------- --------------- ------------- ------------
172090      INC000010164572 10 » 13       1877
172939      INC000010164572 15 » 25       6578
173241      INC000010164572 17 » 25       4045
173597      INC000010164572 20 » 25       3616
173949      INC000010164572 23 » 25       1125
174298      INC000010164572 27 » 34       981
174468      INC000010164572 30 » 34       287
174647      INC000010164572 33 » 34       100
174773      INC000010164572 36 » 36       0

编辑:最后一次尝试

SELECT      InProgress.Id, InProgress.Incident,
            CAST(InProgress.Sequence AS VARCHAR(5)) + ' » ' + 
            CAST(NextStatus.Sequence AS VARCHAR(5)) Transition,
            DATEDIFF(second, InProgress.Modified, NextStatus.Modified) TotalSeconds
            -- , InProgress.*, NextStatus.*
FROM        Data InProgress
JOIN        Data NextStatus
        ON  InProgress.Status IN ('In Progress') AND
            InProgress.Incident = NextStatus.Incident AND
            NextStatus.Id = ISNULL(
                (   -- Try to locate the next status different than 'In Progress'
                    SELECT  MIN(Id)
                    FROM    Data
                    WHERE   Status NOT IN ('In Progress') AND
                            Data.Incident = InProgress.Incident AND
                            Data.RowNumber > InProgress.RowNumber -- That's the trick
                ),
                (   -- If not found, get the latest status
                    SELECT  MAX(Id)
                    FROM    Data
                    WHERE   Data.Incident = InProgress.Incident
                ))
ORDER BY    InProgress.Incident, InProgress.Id

输出:

Id          Incident        Transition    TotalSeconds
----------- --------------- ------------- ------------
172564      INC000010164572 13 » 15       236
174123      INC000010164572 25 » 27       688
174689      INC000010164572 34 » 36       77

祝你好运。

关于sql - 如何摆脱游标？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/37784921/

文章推荐： common-lisp - 将数字强制转换为普通 lisp 中的列表

文章推荐： chef-infra - 如何使用 apache2 chef cookbook 安装 apache2？

文章推荐： swing - 删除子项后在 JTree 节点上显示 'expand' 控件？

文章推荐： angular - 如何在用户单击链接时隐藏 Angular Material Sidenav

SQL Server - 游标
我正在尝试使用游标遍历表: DEClARE @ProjectOID as nvarchar (100) DECLARE @TaskOID as nvarchar (100) DECLARE TaskO
java - JOptionPane 游标
使用 JOprionPane 时，光标出现了一些问题。我将光标设置到 pharent 框架，然后使用这个显示一个对话框: Object[] possibilities = {"ham", "spam"
SQL Server 游标
我想将数据从一个表(原始数据，所有列都是 VARCHAR)复制到另一个表(使用相应的列格式进行格式化)。为了将数据从 rawdata 表复制到 formatted 表中，我使用游标来识别受影响的行。
SQL 游标...您会捍卫任何用例？
我先走了我 100% 属于集合运算阵营。但是当设置逻辑时会发生什么在整个所需的输入域上进行检索会导致如此大的检索，以至于查询显着减慢，变得缓慢，或者基本上需要无限的时间？在这种情况下，我将使用可能
MySql 工作台 - 游标
为什么我不能这样做？我想从 TABLEA 中搜索大于光标值的最接近的值，对两者执行平均函数并将结果放入 test3 中。我收到错误代码 1054 未知列“Xnearest in 'field list
MySQL 游标/获取
我希望以下存储例程返回一系列行，但它只返回 1: CREATE PROCEDURE example() BEGIN DECLARE current_id INT;
更新表的 PostgreSQL 游标
我有一张代表患者体检的表，它有检查 ID 和患者 ID。我想逐行浏览表格并获取每个患者 ID 并比较其不同的咨询，看看它是否被视为“new_attack”。我正在处理疟疾疾病，我们认为每个在过去 6
带参数的 PostgreSQL 游标
如文档所述here ，我需要声明一个在打开时接受参数的游标。我的查询类似于: DECLARE cur CURSOR (argName character varying) FOR SELECT *
sql - PostgreSQL 游标
我正在尝试使用 PostgreSQL 学习基本游标。这是我的脚本: DECLARE cur_employees CURSOR FOR SELECT * FROM employee CLOS
当前的 MYSQL 游标
*DELIMITER // create procedure test(OUT l_out INT) begin DECLARE done INT DEFAULT FALSE; declare l_s
Python psycopg2 游标
来自 psycopg2 文档: When a database query is executed, the Psycopg cursor usually fetches all the record
java - 游标 while 循环返回除最后一个值之外的每个值
我正在使用 while 循环遍历游标，然后输出数据库中每个点的经度和纬度值。出于某种原因，它没有返回光标中的最后一组(或第一个取决于我是否使用 Cursor.MoveToLast)经度和纬度值。这
PHPStorm 4 游标
不知道有没有人试过全新的PHPStorm 4 , 但我遇到了这个新版本的问题，而我以前的主要版本 (PHPStorm 3) 没有。基本上，当我单击代码 View 空白处的任意位置时，光标会设置在该位
mysql的存储过程、游标、事务实例详解
mysql的存储过程、游标、事务实例详解下面是自己曾经编写过的mysql数据库存储过程，留作存档，以后用到的时候拿来参考。其中，涉及到了存储过程、游标（双层循环）、事务。【说明】：代码
mysql存储过程游标循环使用介绍
Mysql的存储过程是从版本5才开始支持的，所以目前一般使用的都可以用到存储过程。今天分享下自己对于Mysql存储过程的认识与了解。一些简单的调用以及语法规则这里就不在赘述，网上有许多例子。这里
sql - 游标/递归是这里唯一可能的选择吗？基于某些滞后条件的集合逻辑
我正在使用 SQL Server，我有一个包含 3 列(时间序列)的表 data ，带日期，hour开始，AwardStatus . 大部分奖励状态是随机生成的。有两种选择，授予或未授予。但是，业务
sql - 为什么人们如此讨厌 SQL 游标？
就目前情况而言，这个问题不太适合我们的问答形式。我们希望答案得到事实、引用资料或专业知识的支持，但这个问题可能会引发辩论、争论、民意调查或扩展讨论。如果您觉得这个问题可以改进并可能重新开放，visit
oracle - 具有最终记录副本的 Oracle 游标
Why am getting duplicate records ? pls correct me.Thanks in Advance. declare clazzes_rec clazzes%r
oracle - 具有最终记录副本的 Oracle 游标
Why am getting duplicate records ? pls correct me.Thanks in Advance. declare clazzes_rec clazzes%r
sql - sql - 设置顺序值而不循环/游标
我需要在数据表中设置一个非唯一标识符。这在组内是连续的，即。对于每个组，ID 应从 1 开始，并以 1 为增量递增，直到该组的最后一行。下表对此进行了说明。 “新 ID”是我需要填充的列。 Uniq

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - 如何摆脱游标？