SQL函數計算流量的95值

發布時間：2020-07-30 03:31:31 來源：網絡閱讀：610 作者：騎士救兵欄目：系統運維

我用的是Solarwinds系統，部分內容就結合Solarwinds系統一起寫了。最后落地也是通過系統的Report定時自動出報告并且可以發郵件。
不過計算方法是通過定義SQL函數，然后使用SQL查詢來獲取到的，這部分內容是通用的。

95th 計算方法

從 Solarwinds 官方網站搜索“95th”關鍵字能獲取到說明的文檔。
95th Percentile Calculations in the Orion Platform：
https://documentation.solarwinds.com/en/Success_Center/orionplatform/Content/Core-95th-Percentile-Calculations-sw80.htm

Over the 10 hours, the following 120 values were collected for inbound traffic (Mb/s):

0.149 0.623 0.281 0.136 0.024 0.042 0.097 0.185 0.198 0.243 0.274 0.390 0.971 0.633 0.238 0.142 
0.119 0.176 0.131 0.127 0.169 0.223 0.291 0.236 0.124 0.072 0.197 0.105 0.138 0.233 0.374 0.290 
0.871 0.433 0.248 0.242 0.169 0.116 0.121 0.427 0.249 0.223 0.231 0.336 0.014 0.442 0.197 0.125 
0.108 0.244 0.264 0.190 0.471 0.033 0.228 0.942 0.219 0.076 0.331 0.227 0.849 0.323 0.221 0.196 
0.223 0.642 0.197 0.385 0.098 0.263 0.174 0.690 0.571 0.233 0.208 0.242 0.139 0.186 0.331 0.124 
0.249 0.643 0.481 0.936 0.124 0.742 0.497 0.085 0.398 0.643 0.074 0.590 0.771 0.833 0.438 0.242 
0.092 0.376 0.231 0.627 0.249 0.663 0.181 0.636 0.224 0.342 0.697 0.285 0.108 0.211 0.074 0.490 
0.271 0.133 0.338 0.242 0.519 0.376 0.331 0.227

The values are reordered from high to low.

0.971 0.942 0.936 0.871 0.849 0.833 0.771 0.742 0.697 0.690 0.663 0.643 0.643 0.642 0.636 0.633 
0.627 0.623 0.590 0.571 0.519 0.497 0.490 0.481 0.471 0.442 0.438 0.433 0.427 0.398 0.390 0.385 
0.376 0.376 0.374 0.342 0.338 0.336 0.331 0.331 0.331 0.323 0.291 0.290 0.285 0.281 0.274 0.271 
0.264 0.263 0.249 0.249 0.249 0.248 0.244 0.243 0.242 0.242 0.242 0.242 0.238 0.236 0.233 0.233 
0.231 0.231 0.228 0.227 0.227 0.224 0.223 0.223 0.223 0.221 0.219 0.211 0.208 0.198 0.197 0.197 
0.197 0.196 0.190 0.186 0.185 0.181 0.176 0.174 0.169 0.169 0.149 0.142 0.139 0.138 0.136 0.133 
0.131 0.127 0.125 0.124 0.124 0.124 0.121 0.119 0.116 0.108 0.108 0.105 0.098 0.097 0.092 0.085 
0.076 0.074 0.074 0.072 0.042 0.033 0.024 0.014

The first 6 values are dropped, as these equal the top 5% of the values.

0.771 0.742 0.697 0.690 0.663 0.643 0.643 0.642 0.636 0.633 0.627 0.623 0.590 0.571 0.519 0.497 
0.490 0.481 0.471 0.442 0.438 0.433 0.427 0.398 0.390 0.385 0.376 0.376 0.374 0.342 0.338 0.336 
0.331 0.331 0.331 0.323 0.291 0.290 0.285 0.281 0.274 0.271 0.264 0.263 0.249 0.249 0.249 0.248 
0.244 0.243 0.242 0.242 0.242 0.242 0.238 0.236 0.233 0.233 0.231 0.231 0.228 0.227 0.227 0.224 
0.223 0.223 0.223 0.221 0.219 0.211 0.208 0.198 0.197 0.197 0.197 0.196 0.190 0.186 0.185 0.181 
0.176 0.174 0.169 0.169 0.149 0.142 0.139 0.138 0.136 0.133 0.131 0.127 0.125 0.124 0.124 0.124 
0.121 0.119 0.116 0.108 0.108 0.105 0.098 0.097 0.092 0.085 0.076 0.074 0.074 0.072 0.042 0.033 
0.024 0.014

The 95th percentile is 0.771.

SQL 函數

系統上安裝的工具是：SQL Server Management Studio

新建函數的命令模板

新建內聯表值函數：

-- ================================================
-- Template generated from Template Explorer using:
-- Create Inline Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
(   
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <Data_Type_For_Param1, , int>, 
    <@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT 0
)
GO

新建多語句表值函數：

-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <data_type_for_param1, , int>, 
    <@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
    -- Add the column definitions for the TABLE variable here
    <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
    <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set

    RETURN 
END
GO

新建標量值函數：

-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
    -- Declare the return variable here
    DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

    -- Add the T-SQL statements to compute the return value here
    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN <@ResultVar, sysname, @Result>

END
GO

Solarwinds系統使用的計算95th的函數

通過標量值函數來實現的。
一共有3個，一個In方向GetInBps95th，一個Out方向GetOutBps95th。
還有一個單個時間點的In和Out方向取大值計算結果。
函數已經在系統中了，這里是修改函數的命令模板。

計算In方向的95th

USE [SolarwindsOrion]
GO
/****** Object:  UserDefinedFunction [dbo].[GetInBps95th]    Script Date: 2019/11/14 11:07:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[GetInBps95th] 
(
    @InterfaceId int,
    @StartDate DateTime,
    @EndDate DateTime
)
RETURNS real
AS
BEGIN
    DECLARE @ResultVar real

    SELECT @ResultVar = MAX(In_Maxbps)
    FROM (
        SELECT TOP 95 PERCENT In_Maxbps
        FROM dbo.InterfaceTraffic WITH (NOLOCK)
        WHERE InterfaceID = @InterfaceId AND DateTime >= @StartDate AND DateTime <= @EndDate
        ORDER BY In_Maxbps ASC
    ) AS AA

    RETURN @ResultVar

END

Out方向的只是換一個字段，其他都一樣。

計算雙向的95th

USE [SolarwindsOrion]
GO
/****** Object:  UserDefinedFunction [dbo].[GetMaxBps95th]    Script Date: 2019/11/14 11:08:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[GetMaxBps95th] 
(
    @InterfaceId int,
    @StartDate DateTime,
    @EndDate DateTime
)
RETURNS real
AS
BEGIN
    DECLARE @ResultVar real

    SELECT @ResultVar = MAX(Maxbps)
    FROM (
        SELECT TOP 95 PERCENT Maxbps
        FROM (SELECT (CASE WHEN Out_Maxbps > In_Maxbps THEN Out_Maxbps ELSE In_Maxbps END) AS Maxbps
            FROM dbo.InterfaceTraffic WITH (NOLOCK)
            WHERE InterfaceID = @InterfaceId AND DateTime >= @StartDate AND DateTime <= @EndDate) AS MaxbpsSet
        ORDER BY Maxbps ASC
    ) AS AA

    RETURN @ResultVar

END

多了一層子查詢，使用WHEN子句用來判斷取大的值。

自定義函數

因為原有的函數不能滿足需求，需要改一下，增加一個時間段篩選的變量。需求就是一周或者一個月，每天只取工作時間的數據。
這里按時間的小時數 'DATEPART(hh,DateTime)' 只要是9到16這些數值就表示每天的9點到17點了。
另外還有一個時間段的需求是每天有2段時間，考慮到通用性，這里用了數組的方式來設置，把需要的小時的數值定義在數組里。實際沒有用數組，用字符串來模擬了。

創建自定義函數

創建一個自定義函數，在原有的函數的基礎上，增加時間段的篩選：

USE [SolarwindsOrion]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Steed Xu
-- Create date: 2019/11/14
-- Description: Base on GetInBps95th, add BusyHours.
-- =============================================
CREATE FUNCTION [dbo].[GetInBps95thBusyHours] 
(
    @InterfaceId int,
    @StartDate DateTime,
    @EndDate DateTime,
    @BusyHours VarChar(64)
)
RETURNS real
AS
BEGIN
    DECLARE @ResultVar real

    SELECT @ResultVar = MAX(In_Maxbps)
    FROM (
        SELECT TOP 95 PERCENT In_Maxbps
        FROM dbo.InterfaceTraffic WITH (NOLOCK)
        WHERE InterfaceID = @InterfaceId 
            AND DateTime >= @StartDate AND DateTime <= @EndDate
            AND ','+RTRIM(@BusyHours)+',' LIKE '%,'+CAST(DATEPART(hh,DateTime) AS varchar)+',%'
        ORDER BY In_Maxbps ASC
    ) AS AA

    RETURN @ResultVar

END
GO

執行上面的語句后，就會添加到數據庫中。
注意：這里定義字符串變量的時候要指定大小，否則雖然可以成功執行，但是結果會和預期的不一樣。

解決數組的問題

篩選方式使用取時間的小時數，一天里哪幾個時段是需要的，就定義在數組里。
使用的時候這樣定義：

DECLARE @BusyHours VarChar(64)
SET @BusyHours = '9,10,11,12,13,14,15,16'

這樣就是要取9點到17點的數據。

對應的WHERE可以這樣寫：

WHERE ','+RTrim(@BusyHours)+',' LIKE '%,'+CAST(datepart(hh,DateTime) AS VarChar)+',%'

基本用字符串模擬出了數組的效果，在這里是夠用了。

修改自定義函數

如果創建的時候寫錯了，則可以用下面的模板進行修改，更新到數據庫中。

USE [SolarwindsOrion]
GO
/****** Object:  UserDefinedFunction [dbo].[GetInBps95thBusyHours]    Script Date: 2019/11/14 13:43:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Steed Xu
-- Create date: 2019/11/14
-- Description: Base on GetInBps95th, add BusyHours.
-- =============================================
ALTER FUNCTION [dbo].[GetInBps95thBusyHours]
(
    @InterfaceId int,
    @StartDate DateTime,
    @EndDate DateTime,
    @BusyHours VarChar(64)
)
RETURNS real
AS
BEGIN
    DECLARE @ResultVar real

    SELECT @ResultVar = MAX(In_Maxbps)
    FROM (
        SELECT TOP 95 PERCENT In_Maxbps
        FROM dbo.InterfaceTraffic WITH (NOLOCK)
        WHERE InterfaceID = @InterfaceId 
            AND DateTime >= @StartDate AND DateTime <= @EndDate 
            AND ','+RTRIM(@BusyHours)+',' LIKE '%,'+CAST(DATEPART(hh,DateTime) AS varchar)+',%'
        ORDER BY In_Maxbps ASC
    ) AS AA

    RETURN @ResultVar

END

在SQL中調用函數

供應商給的使用模板

完整的SQL查詢語句：

完整的查詢語句如下：
SET NOCOUNT OFF
SET ROWCOUNT 0

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = CAST((ROUND(CAST(GetDate() - 7 AS FLOAT), 0, 1)) as datetime)
SET @EndDate = GetDate()

SELECT Interfaces.InterfaceId,
    Nodes.NodeID,
    Nodes.Caption AS NodeName,
    Nodes.VendorIcon AS Vendor_Icon,
    Interfaces.Caption AS Interface_Caption,
    Interfaces.InterfaceIcon AS Interface_Icon,
    Maxbps_In95,
    Maxbps_Out95,
    Maxbps_95
FROM Nodes
INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN (
    SELECT InterfaceID,
        dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,
        dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,
        dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95
    FROM InterfaceTraffic
    WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate
    GROUP BY InterfaceID
) TrafficStat
ON Interfaces.InterfaceID = TrafficStat.InterfaceID
WHERE (1=1)
AND  
(
    (Nodes.Vendor = 'Cisco') AND
    (Interfaces.Comments = 'MT')
)
ORDER BY 
Maxbps_In95 desc,
Maxbps_Out95 desc

按需求修改的查詢語句

需求是要計算一周的數據，這里是獲取上一周的數據，從周一開始。
完整的查詢語句：

SET NOCOUNT OFF
SET ROWCOUNT 0

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
DECLARE @BusyHours VarChar(64)

SET @StartDate = DATEADD(week,-1,DATEADD(week,DATEDIFF(week,0,getdate()),0))
SET @EndDate = DATEADD(week,DATEDIFF(week,0,getdate()),0)
SET @BusyHours = '9,10,11,12,13,14,15,16'

SELECT Interfaces.InterfaceId,
    Nodes.NodeID,
    Nodes.Caption AS NodeName,
    Nodes.VendorIcon AS Vendor_Icon,
    Nodes.IP_Address,
    Interfaces.InterfaceName AS Interface_Name,
    Interfaces.InterfaceAlias AS Description,
    Interfaces.InterfaceIcon AS Interface_Icon,
    Maxbps_In95,
    Maxbps_Out95,
    @StartDate AS StartDate,
    @EndDate AS EndDate,
    @BusyHours AS BusyHours,
    Interfaces.Status AS Interfaces_Status
FROM Nodes
INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN (
    SELECT InterfaceID,
        dbo.GetInBps95thBusyHours(InterfaceID, @StartDate, @EndDate, @BusyHours) AS Maxbps_In95,
        dbo.GetOutBps95thBusyHours(InterfaceID, @StartDate, @EndDate, @BusyHours) AS Maxbps_Out95
    FROM InterfaceTraffic
    WHERE InterfaceTraffic.DateTime >= @StartDate
        AND InterfaceTraffic.DateTime <= @EndDate
        AND ','+RTRIM(@BusyHours)+',' LIKE '%,'+CAST(DATEPART(hh,DateTime) AS varchar)+',%'
    GROUP BY InterfaceID
) TrafficStat
ON Interfaces.InterfaceId = TrafficStat.InterfaceId
WHERE (1=1)
    AND Nodes.IP_Address IN ('172.16.6.1','172.16.6.2','172.16.5.3','172.16.10.1','172.16.10.2','172.16.5.4')
    AND Interfaces.InterfaceAlias <> ''
    AND Interfaces.Status IN (1,2)
ORDER BY Interfaces.InterfaceId

注意：這里定義字符串變量的時候要指定大小，否則雖然可以成功執行，但是結果會和預期的不一樣。

驗證計算結果

在寫一個查詢語句，把一個端口下的所有的流量數據都導出。驗證函數的結果是否符合預期。
完整的查詢語句：

SET NOCOUNT OFF
SET ROWCOUNT 0

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
DECLARE @BusyHours VarChar(64)
DECLARE @InterfaceId int

SET @StartDate = DATEADD(week,-1,DATEADD(week,DATEDIFF(week,0,getdate()),0))
SET @EndDate = DATEADD(week,DATEDIFF(week,0,getdate()),0)
SET @BusyHours = '9,10,11,12,13,14,15,16'
SET @InterfaceId = 28993

SELECT TOP 95 PERCENT 
    DateTime, InterfaceID, In_Maxbps, Out_Maxbps, 
    @StartDate AS StartDate,
    @EndDate AS EndDate,
    @BusyHours AS BusyHours
FROM InterfaceTraffic
WHERE InterfaceTraffic.DateTime >= @StartDate
    AND InterfaceTraffic.DateTime <= @EndDate
    AND ','+RTrim(@BusyHours)+',' LIKE '%,'+CAST(datepart(hh,DateTime) AS VarChar)+',%'
    AND InterfaceID = @InterfaceId
ORDER BY In_Maxbps ASC

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

SQL函數計算流量的95值

95th 計算方法

SQL 函數

新建函數的命令模板

Solarwinds系統使用的計算95th的函數

自定義函數

創建自定義函數

解決數組的問題

修改自定義函數

在SQL中調用函數

供應商給的使用模板

按需求修改的查詢語句

驗證計算結果

猜你喜歡

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

SQL函數計算流量的95值

95th 計算方法

SQL 函數

新建函數的命令模板

Solarwinds系統使用的計算95th的函數

自定義函數

創建自定義函數

解決數組的問題

修改自定義函數

在SQL中調用函數

供應商給的使用模板

按需求修改的查詢語句

驗證計算結果

猜你喜歡

最新資訊

相關推薦

相關標簽