更新变量为一个专责在 Oracle 中的每一行

标签: SQLServer Oracle
发布时间: 2017/4/15 19:01:03
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我通常使用 MS SQL。我有一段代码,我可能要转化到 Oracle SQL。在这段代码我更新两个变量的每个行以查找并发用户的最大值。

我的问题是,如果有可能做什么都是在下面在 Oracle 中的 MS SQL 代码相同吗?

-- The creation of this test table is not relevant to my question.
-- It is just there to make a working example
SELECT {ts '2012-09-03 10:12:00'} AS eventTime, 1 AS change
INTO #EVENTS
UNION ALL SELECT {ts '2012-09-03 10:24:00'}, 1
UNION ALL SELECT {ts '2012-09-03 10:25:00'}, 1
UNION ALL SELECT {ts '2012-09-03 11:02:00'}, -1
UNION ALL SELECT {ts '2012-09-03 11:25:00'}, 1
UNION ALL SELECT {ts '2012-09-03 11:34:00'}, 1
UNION ALL SELECT {ts '2012-09-03 12:15:00'}, -1
UNION ALL SELECT {ts '2012-09-03 13:50:00'}, -1
UNION ALL SELECT {ts '2012-09-03 14:20:00'}, -1
UNION ALL SELECT {ts '2012-09-03 14:44:00'}, -1

DECLARE @count int, @maxUsers int
SET @count = 0
SET @maxUsers = 0

-- This part is what is in question. Can you do something similar in Oracle?
-- Having calculation done for each row and store it in a variable for
-- the next row.
SELECT
    @count = @count + change,
    @maxUsers = CASE WHEN @count > @maxUsers THEN @count ELSE @maxUsers END
FROM #EVENTS
ORDER BY eventTime ASC

PRINT @maxUsers -- Prints the value 4

DROP TABLE #EVENTS

解决方法 1:

Oracle 已经可以用于获取运行总计之类的解析函数。

SELECT MAX(CURRENTUSERS) FROM
(
  SELECT SUM(CHANGE) OVER (ORDER BY eventTime) AS CURRENTUSERS 
  FROM EVENTS
)
官方微信
官方QQ群
31647020