sql - Subquery a value for math function - Stack Overflow-软件玩家

admin管理员组
文章数量:1391995

I'm trying to create a query that will dynamically sum the bandwidth of sub interfaces and then be able to calculate the percentage provisioned by the speed of the physical interface. I currently do this statically but its a pain as some LAG interfaces are different speeds so I'm trying to figure out how I can do this more dynamically. I don't know SQL well enough to come up with a way to do it though.

Here is an example of the table I'm working with.

Name	Speed	MAC	Link Classification
ae5	200000000000	20ED47CC8885	UNI-direct-P
ae5.100	100000000	20ED47CC8885	UNI-direct-L
ae5.101	200000000	20ED47CC8885	UNI-direct-L
ae5.103	200000000	20ED47CC8885	UNI-direct-L
ae5.104	20000000	20ED47CC8885	UNI-direct-L

Here is an example of the table I'm working with.

Name	Speed	MAC	Link Classification
ae5	200000000000	20ED47CC8885	UNI-direct-P
ae5.100	100000000	20ED47CC8885	UNI-direct-L
ae5.101	200000000	20ED47CC8885	UNI-direct-L
ae5.103	200000000	20ED47CC8885	UNI-direct-L
ae5.104	20000000	20ED47CC8885	UNI-direct-L

This is an example of what I currently do where I use substrings to just sum the sub interfaces and then manually determine how to divide it for how we have some overprovisioning rules (1:1, 1:4, etc) based on if its a 1g or 10g interface. The issue is the LAGs (ae) interface they can vary.

SELECT 
  CASE WHEN SUBSTRING(I.Name, 1,2) = 'ae' THEN SUBSTRING(I.Name, 1,3) 
       WHEN SUBSTRING(I.Name, 9,1) = ':' THEN SUBSTRING(I.Name, 1,10) 
       ELSE REPLACE(SUBSTRING(I.Name, 1,9),'.','')
  END AS [Interface],

  CASE WHEN SUM(I.Speed) <= 999999999 THEN Concat(ROUND(SUM(I.Speed)/1000000,2), ' Mbps') 
       WHEN SUM(I.Speed) > 999999999 THEN Concat(ROUND(SUM(I.Speed)/1000000000,2), ' Gbps')
  END AS [Prov_Total],

  CASE WHEN SUBSTRING(I.Name, 1,2) = 'ge' AND I.CustomProperties.LINK_CLASSIFICATION = 'UNI-direct-L' THEN ROUND(SUM(I.Speed)/1000000000 * 100,0)
       WHEN SUBSTRING(I.Name, 1,2) = 'xe' AND I.CustomProperties.LINK_CLASSIFICATION = 'UNI-direct-L' THEN ROUND(SUM(I.Speed)/10000000000 * 100,0)
       WHEN SUBSTRING(I.Name, 1,2) = 'ae' AND I.CustomProperties.LINK_CLASSIFICATION = 'UNI-direct-L' THEN ROUND(SUM(I.Speed)/20000000000 * 100,0)
  END AS [Percent_Used],
  I.CustomProperties.LINK_CLASSIFICATION
FROM Orion.NPM.Interfaces I

WHERE I.NodeID = 1596
  AND I.CustomProperties.LINK_CLASSIFICATION IN ('UNI-direct-L')

GROUP BY (
  I.Node.Vendor, 
  I.CustomProperties.LINK_CLASSIFICATION, 
  CASE WHEN SUBSTRING(I.Name, 1,2) = 'ae' THEN SUBSTRING(I.Name, 1,3) 
       WHEN SUBSTRING(I.Name, 9,1) = ':' THEN SUBSTRING(I.Name, 1,10)  
       ELSE REPLACE(SUBSTRING(I.Name, 1,9),'.','')
  END,
  SUBSTRING(I.Name, 1,2)
)

This is an example of the end result table that just shows the total and percentage.

Interface	Prov_Total	Percent_Used	Link_Classification
ae5	133.84 Gbps	669	UNI-direct-L
ge-0/0/3	1.31 Gbps	131	UNI-direct-L
ge-0/0/7	2.1 Gbps	210	UNI-direct-L
ge-0/0/9	1 Gbps	100	UNI-direct-L

So basically I would like to take the 200000000000 from Speed for ae5 physical interface and be able to put it in this line like a variable so it just fills in whatever the actual speed is rather than statically making a bunch of CASE statements to account for all the different variations we have. For this percentage calculations specifically:ROUND(SUM(I.Speed)/20000000000 * 100,0)

SUBSTRING(I.Name, 1,2) = 'ae' AND I.CustomProperties.LINK_CLASSIFICATION = 'UNI-direct-L' THEN ROUND(SUM(I.Speed)/20000000000 * 100,0)

Share Improve this question edited Mar 12 at 17:42 asked Mar 12 at 16:56 jajao555 355 bronze badges

Please tag the question with the specific RDBMS you're using -- MySQL, SQL-Server, Oracle-Database, SQLite, etc. – Barmar Commented Mar 12 at 17:24
1 The square brackets around aliases suggests SQL-Server. – Barmar Commented Mar 12 at 17:24
Why are you joining the table with itself? FROM Orion.NPM.Interfaces I JOIN Orion.NPM.Interfaces I. If you do need a self-join, you have to give them different aliases. – Barmar Commented Mar 12 at 17:26
1 My thinking at first was to some how join the subinterfaces by their MAC since the sub interfaces inherit the physical interface's MAC and the link classification UNI-direct-P property we use to identify them within the Orion monitoring tool. Also the parent would never have the .123 number added to it if that helps. Those are all VLANs/subinterfaces. So I was thinking the logic: WHERE I.MAC = Iphy.MAC AND Iphy.CustomProperties.LINK_CLASSIFICATION = UNI-direct-P I can't really wrap my head around how to do something like that though. – jajao555 Commented Mar 12 at 17:56
1 If I'm understanding you correctly, here's a fiddle to show what I mean. It's obviously a massive over-simplification, but hopefully it's helpful. – Andrew Commented Mar 12 at 18:13

| Show 4 more comments

1 Answer 1

Sorted by: Reset to default 1

Maybe using analytic functions could help make it less complicated ...

--      S a m p l e    D a t a :
create table interfaces ( name varchar(100), speed bigint, mac varchar(20), link_classification varchar(100) );

insert into interfaces values('ae5',200000000000,'20ED47CC8885','UNI-direct-P');
insert into interfaces values('ae5.100',100000000,'20ED47CC8885','UNI-direct-L');
insert into interfaces values('ae5.101',200000000,'20ED47CC8885','UNI-direct-L');
insert into interfaces values ('ae5.103',200000000,'20ED47CC8885','UNI-direct-L');
insert into interfaces values ('ae5.104',20000000,'20ED47CC8885','UNI-direct-L');

... create a cte that geenerates row numbers and fetches the speed of 'UNI-direct-P'

WITH 
  grid as
    ( Select   Row_Number() Over(Partition By i.mac Order By i.name) as rn, 
               i.*, 
               Max(speed) Over(Partition By mac) as p_speed
      From       interfaces i
    )

... now using analytic functions Sum() Over() you can get your total and percentage ...

--      M a i n    S Q L : 
SELECT  x.*
FROM  ( Select     g.*,
                   Sum(Case When rn > 1
                            Then speed 
                       End) Over( Partition By mac ) as l_speed_total, 
                  Sum(Case When rn > 1 
                            Then speed 
                       End) Over( Partition By mac ) * 100.0 / p_speed as l_speed_pct
        From       grid g
     ) x
WHERE   x.rn = 1

R e s u l t :

rn	name	speed	mac	link_classification	p_speed	l_speed_total	l_speed_pct
1	ae5	200000000000	20ED47CC8885	UNI-direct-P	200000000000	520000000	0.260000000000000

OR
... running totals and percentages if you include windowing clause (Rows Between ...) in analytic functions ....

--      M a i n    S Q L : 
Select     g.*,
           Sum(Case When rn > 1
                    Then speed 
               End) Over(Partition By mac Order By rn
                    Rows Between Unbounded Preceding And Current Row) as l_speed_running_total, 
          Sum(Case When rn > 1 
                    Then speed 
               End) Over(Partition By mac Order By rn
                    Rows Between Unbounded Preceding And Current Row) * 100.0 / p_speed l_speed_running_pct
From       grid g

R e s u l t :

rn	name	speed	mac	link_classification	p_speed	l_speed_running_total	l_speed_running_pct
1	ae5	200000000000	20ED47CC8885	UNI-direct-P	200000000000	null	null
2	ae5.100	100000000	20ED47CC8885	UNI-direct-L	200000000000	100000000	0.050000000000000
3	ae5.101	200000000	20ED47CC8885	UNI-direct-L	200000000000	300000000	0.150000000000000
4	ae5.103	200000000	20ED47CC8885	UNI-direct-L	200000000000	500000000	0.250000000000000
5	ae5.104	20000000	20ED47CC8885	UNI-direct-L	200000000000	520000000	0.260000000000000

fiddle

本文标签： sqlSubquery a value for math functionStack Overflow

版权声明：本文标题：sql - Subquery a value for math function - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人，转载请联系作者并注明出处：http://www.betaflare.com/web/1744738566a2622476.html，本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。

编程频道|软件玩家 - 软件改变生活！

sql - Subquery a value for math function - Stack Overflow

1 Answer 1

更多相关文章