时间戳

首页 > 解决方案 > 从具有最大日期的多个连接中获取聚合

sql - 从具有最大日期的多个连接中获取聚合

问题描述

3张桌子

<h>DesktopGroup</h>
<table style="undefined;table-layout: fixed; width: 81px"><colgroup><col style="width: 25px"><col style="width: 55.5px"></colgroup><tr><th>id</th><th>name</th></tr><tr><td>1</td><td>group1</td></tr><tr><td>2</td><td>group2</td></tr></table>

<h>machines</h>
<table style="undefined;table-layout: fixed; width: 198px"><colgroup><col style="width: 31px"><col style="width: 67px"><col style="width: 99.5px"></colgroup><tr><th>id</th><th>name</th><th>Desktopgroupid</th></tr><tr><td>1</td><td>server1</td><td>1</td></tr><tr><td>2</td><td>server2</td><td>1</td></tr><tr><td>3</td><td>server3</td><td>2</td></tr></table>

<h>loadindex</h>
<table style="undefined;table-layout: fixed; width: 456px"><colgroup><col style="width: 28px"><col style="width: 61px"><col style="width: 89px"><col style="width: 277.5px"></colgroup><tr><th>id</th><th>load</th><th>machineid<br></th><th>Createdate</th></tr><tr><td>1</td><td>7654</td><td>1</td><td>2019-10-15 16:54:31.430</td></tr><tr><td>2</td><td>1235</td><td>1</td><td>2019-10-15 16:44:00.430</td></tr><tr><td>3</td><td>4443</td><td>1</td><td>2019-10-15 16:34:31.000</td></tr><tr><td>4</td><td>2345</td><td>1</td><td>2019-10-15 16:25:15.222</td></tr><tr><td>5</td><td>3456</td><td>2</td><td>2019-10-15 16:54:31.430</td></tr></table>
每个桌面组有 x 个服务器，每个服务器都有一个给定的负载时间点。（大约每 5 分钟一次）所以我试图为组中的所有计算机获取 desktopgroup.name, avg(load) 以获取最大负载（日期）。 

select loadvalues.avgload, dg.Name
from CitrixMonitoring.DesktopGroups as dg
join CitrixMonitoring.Machines as m on m.DesktopGroupId = dg.id
join (select LoadIndex.MachineId, avg(LoadIndex.EffectiveLoadIndex) as avgload, max(LoadIndex.[CreatedDate]) as maxdate
from CitrixMonitoring.LoadIndex group by MachineId) as [loadvalues] on m.id = loadvalues.MachineId
where dg.name like 'live - call center desktop'
group by dg.name, loadvalues.avgload

我明白了

3185    LIVE - Call Center Desktop
3236    LIVE - Call Center Desktop
3249    LIVE - Call Center Desktop
3263    LIVE - Call Center Desktop
3288    LIVE - Call Center Desktop
3295    LIVE - Call Center Desktop

预期结果应该是桌面组平均负载的一行

3185    LIVE - Call Center Desktop

标签： sqltsqlaggregate

解决方案

只是GROUP BY dg.name。当您包含loadvalues.avgload在GROUP BY子句中时，这意味着您希望为每个不同的值单独一行avgload。

但是由于您只是dg.name在WHERE子句中选择一个，因此根本不需要 for GROUP BY。所以它应该是。

select loadvalues.avgload, loadvalues.maxdate, dg.Name
from CitrixMonitoring.DesktopGroups as dg
join CitrixMonitoring.Machines as m on m.DesktopGroupId = dg.id
join (select LoadIndex.MachineId, avg(LoadIndex.EffectiveLoadIndex) as avgload, max(LoadIndex.[CreatedDate]) as maxdate
from CitrixMonitoring.LoadIndex group by MachineId) as [loadvalues] on m.id = loadvalues.MachineId
where dg.name like 'live - call center desktop'

推荐阅读