次の例は、インシデント [incident] テーブルで GlideAggregate 関数を使用する方法を示しています。

var incidentGA = new GlideAggregate('incident');
incidentGA.groupBy('category');
incidentGA.orderByAggregate('COUNT', 'sys_mod_count');

incidentGA.addAggregate('AVG', 'sys_mod_count');
incidentGA.addAggregate('COUNT', 'sys_mod_count');
incidentGA.addAggregate('GROUP_CONCAT', 'sys_mod_count');
incidentGA.addAggregate('GROUP_CONCAT_DISTINCT', 'sys_mod_count');
incidentGA.addAggregate('MAX', 'sys_mod_count');
incidentGA.addAggregate('MIN', 'sys_mod_count');
incidentGA.addAggregate('STDDEV', 'sys_mod_count');
incidentGA.addAggregate('SUM', 'sys_mod_count');

incidentGA.query();

while (incidentGA.next()) {
gs.info('CATEGORY: ' + incidentGA.getValue('category'));
gs.info('AVG: ' + incidentGA.getAggregate('AVG', 'sys_mod_count'));
gs.info('COUNT: ' + incidentGA.getAggregate('COUNT', 'sys_mod_count'));
gs.info('GROUP_CONCAT: ' + incidentGA.getAggregate('GROUP_CONCAT', 'sys_mod_count'));
gs.info('GROUP_CONCAT_DISTINCT: ' + incidentGA.getAggregate('GROUP_CONCAT_DISTINCT', 'sys_mod_count'));
gs.info('MAX: ' + incidentGA.getAggregate('MAX', 'sys_mod_count'));
gs.info('MIN: ' + incidentGA.getAggregate('MIN', 'sys_mod_count'));
gs.info('STDDEV: ' + incidentGA.getAggregate('STDDEV', 'sys_mod_count'));
gs.info('SUM: ' + incidentGA.getAggregate('SUM', 'sys_mod_count'));
gs.info(' ');
}

出力。

CATEGORY: inquiry
AVG: 8.42424242424242424242424242424242424242E00
COUNT: 33
GROUP_CONCAT: 0,0,0,0,1,2,2,4,4,4,5,5,5,6,6,6,6,6,6,6,6,7,8,8,8,8,9,15,15,19,32,33,36
GROUP_CONCAT_DISTINCT: 0,1,2,4,5,6,7,8,9,15,19,32,33,36
MAX: 36
MIN: 0
STDDEV: 9.15843294125113629932710147135171494439E00
SUM: 278
  
CATEGORY: software
AVG: 21
COUNT: 13
GROUP_CONCAT: 3,4,5,5,6,7,9,10,10,11,14,94,95
GROUP_CONCAT_DISTINCT: 3,4,5,6,7,9,10,11,14,94,95
MAX: 95
MIN: 3
STDDEV: 3.27693962918655755532970326989852794087E01
SUM: 273
  
CATEGORY: Hardware
AVG: 6.8
COUNT: 10
GROUP_CONCAT: 2,5,5,6,6,6,7,7,9,15
GROUP_CONCAT_DISTINCT: 2,5,6,7,9,15
MAX: 15
MIN: 2
STDDEV: 3.39280283999985933622820108982884699755E00
SUM: 68
  
CATEGORY: network
AVG: 18
COUNT: 5
GROUP_CONCAT: 3,12,17,21,37
GROUP_CONCAT_DISTINCT: 3,12,17,21,37
MAX: 37
MIN: 3
STDDEV: 1.25698050899765347157025586536136512302E01
SUM: 90
  
CATEGORY: 
AVG: 9.5
COUNT: 4
GROUP_CONCAT: 8,8,10,12
GROUP_CONCAT_DISTINCT: 8,10,12
MAX: 12
MIN: 8
STDDEV: 1.91485421551267621995020382273964310607E00
SUM: 38
  
CATEGORY: database
AVG: 29
COUNT: 2
GROUP_CONCAT: 8,50
GROUP_CONCAT_DISTINCT: 8,50
MAX: 50
MIN: 8
STDDEV: 2.969848480983499602483546320840365965E01
SUM: 58

//Number of incidents varies depending on the current state
//of the incident table
var count = new GlideAggregate('incident');
count.addEncodedQuery('active=true');
count.addAggregate('COUNT');
count.query();
var incidents = 0;
if (count.next())
   incidents = count.getAggregate('COUNT');
gs.info(incidents);

//Number of incidents varies depending on the current state
//of the incident table
var count = new GlideAggregate('incident');
count.addQuery('active', '=','true');
count.addAggregate('COUNT', 'category');
count.query();  
while (count.next()) {
   var category = count.category;
   var categoryCount = count.getAggregate('COUNT', 'category');
   gs.info("There are currently " + categoryCount + " incidents with a category of " + category);
}

There are currently 1 incidents with a category of database
There are currently 5 incidents with a category of hardware
There are currently 42 incidents with a category of inquiry
There are currently 4 incidents with a category of network
There are currently 4 incidents with a category of request
There are currently 7 incidents with a category of software

var count = new GlideAggregate('incident');
  count.addNotNullQuery('short_description');
  count.query();   // Issue the query to the database to get all records
  while (count.next()) {   
     // add code here to process the aggregate
  }

var count = new GlideAggregate('incident');
  count.addNullQuery('short_description');
  count.query();   // Issue the query to the database to get all records
  while (count.next()) {   
     // add code here to process the aggregate
  }

var ga = new GlideAggregate('incident');
ga.addAggregate('COUNT'); // Count all incidents opened each quarter
ga.addTrend('opened_at', 'quarter');
ga.query();
while(ga.next()) {
 gs.info([ga.getValue('timeref'), ga.getAggregate('COUNT')]);
}

3/2018, 9
4/2018, 2
1/2019, 38
2/2019, 310

インシデントテーブルのレコード数を返す集計を表示します。

var count = new GlideAggregate('incident');
count.addAggregate('COUNT');
count.query();
var incidents = 0;
if (count.next()) {
  incidents = count.getAggregate('COUNT');
}
//Number of incidents varies depending on the current state
//of the incident table
gs.info('Number of incidents: ' + incidents);

出力:インシデントの数:63。

FX 通貨フィールドのアグリゲーションを表示します。

var ga = new GlideAggregate('laptop_tracker');
ga.addAggregate('SUM', 'cost');
ga.groupBy('name');
ga.query();
while (ga.next()) {
  gs.info('Aggregate results ' + ga.getValue('name') + ' => ' + ga.getAggregate('SUM', 'cost'));
}

出力:

*** Script: Aggregate results Apple MacBook Air => USD;1651.784280000000
*** Script: Aggregate results Apple MacBook Pro => USD;1651.784280000000
*** Script: Aggregate results Dell XPS => USD;470.852672000000
*** Script: Aggregate results LG =>
*** Script: Aggregate results Samsung Galaxy => USD;225.320000000000
*** Script: Aggregate results Surface3 => USD;2895.560369520000
*** Script: Aggregate results Toshiba => USD;9385.202875800000

var count = new GlideAggregate('incident');
count.addAggregate('MIN', 'sys_mod_count');
count.groupBy('category');
count.query();  
while (count.next()) {
    gs.info(count.getAggregateEncodedQuery());
}

category=database
category=hardware
category=inquiry
category=network
category=request
category=software

var count = new GlideAggregate('incident');
count.addAggregate('MIN', 'sys_mod_count');
count.addAggregate('MAX', 'sys_mod_count');
count.addAggregate('AVG', 'sys_mod_count');
count.groupBy('category');
count.query();
gs.info(count.getEncodedQuery());

ORDERBYcategory^GROUPBYcategory

var count = new GlideAggregate('incident');
  count.addAggregate('MIN', 'sys_mod_count');
  count.addAggregate('MAX', 'sys_mod_count');
  count.addAggregate('AVG', 'sys_mod_count');
  count.groupBy('category');
  count.query();
  gs.info(count.getRowCount());
  while (count.next()) {  
     var min = count.getAggregate('MIN', 'sys_mod_count');
     var max = count.getAggregate('MAX', 'sys_mod_count');
     var avg = count.getAggregate('AVG', 'sys_mod_count');
     var category = count.category.getDisplayValue();
     gs.info(category + " Update counts: MIN = " + min + " MAX = " + max + " AVG = " + avg);
  }

6
Database Update counts: MIN = 8 MAX = 48 AVG = 28.0000
Hardware Update counts: MIN = 4 MAX = 14 AVG = 6.6250
Inquiry / Help Update counts: MIN = 0 MAX = 34 AVG = 6.5714
Network Update counts: MIN = 3 MAX = 37 AVG = 18.6000
Request Update counts: MIN = 5 MAX = 39 AVG = 13.4000
Software Update counts: MIN = 4 MAX = 98 AVG = 24.0000

var count = new GlideAggregate('incident');
count.addAggregate('MIN', 'sys_mod_count');
count.addAggregate('MAX', 'sys_mod_count');
count.addAggregate('AVG', 'sys_mod_count');
count.groupBy('category');
count.query();
gs.info(count.getTableName());

var count = new GlideAggregate('incident');
count.addAggregate('MAX', 'sys_mod_count');
count.groupBy('category');
count.query();   
while (count.next()) {  
  gs.info(count.getValue('category') + " category had " + count.getAggregate('MAX', 'sys_mod_count') + " updates");  
}

出力:

category had 12 updates
hardware category had 15 updates
inquiry category had 36 updates
network category had 37 updates
software category had 95 updates

var count = new GlideAggregate('incident');
count.addAggregate('MIN', 'sys_mod_count');
count.addAggregate('MAX', 'sys_mod_count');
count.addAggregate('AVG', 'sys_mod_count');
count.groupBy('category');
count.query();   
while (count.next()) {  
     var min = count.getAggregate('MIN', 'sys_mod_count');
     var max = count.getAggregate('MAX', 'sys_mod_count');
     var avg = count.getAggregate('AVG', 'sys_mod_count');
     var category = count.category.getDisplayValue();
     gs.info(category + " Update counts: MIN = " + min + " MAX = " + max + " AVG = " + avg);
}

Database Update counts: MIN = 8 MAX = 48 AVG = 28.0000
Hardware Update counts: MIN = 4 MAX = 14 AVG = 6.6250
Inquiry / Help Update counts: MIN = 0 MAX = 34 AVG = 6.5714
Network Update counts: MIN = 3 MAX = 37 AVG = 18.6000
Request Update counts: MIN = 5 MAX = 39 AVG = 13.4000
Software Update counts: MIN = 4 MAX = 98 AVG = 24.0000

var agg = new GlideAggregate('incident');
agg.addAggregate('AVG', 'sys_mod_count');
agg.groupBy('category');
agg.query();
while (agg.hasNext()) {
    agg.next();
    var avg = agg.getAggregate('AVG', 'sys_mod_count');
    var category = agg.category.getDisplayValue();
    gs.info(category + ': AVG = ' + avg);
}

Database: AVG = 32.5000
Hardware: AVG = 12.0000
Inquiry / Help: AVG = 7.6667
Network: AVG = 24.0000
Request: AVG = 16.4000
Software: AVG = 27.0833

var count = new GlideAggregate('incident');
count.addAggregate('COUNT');
count.query();
var incidents = 0;
if (count.next()) {
   incidents = count.getAggregate('COUNT');
   gs.info(incidents);
}

var agg = new GlideAggregate('incident');
agg.addAggregate('count', 'category'); 
agg.orderBy('category'); 
agg.query(); 
while (agg.next()) { 
  var category = agg.category;
  var count = agg.getAggregate('count', 'category');
  var agg2 = new GlideAggregate('incident');   
  agg2.addAggregate('count', 'category');
  agg2.orderBy('category');
  gs.info(category + ": Current number of incidents:" + count);
}

database: Current number of incidents:2
hardware: Current number of incidents:8
inquiry: Current number of incidents:28
network: Current number of incidents:5
request: Current number of incidents:5
software: Current number of incidents:11

ga.addAggregate('COUNT', 'category');
ga.orderByAggregate('count', 'category');
ga.query();
while(ga.next()) {
  gs.info('Category: ' + ga.category + ' ' + ga.getAggregate('COUNT', 'category'));
}

出力:

Category: inquiry 18
Category: software 11
Category: hardware 7
Category: network 5
Category: request 5
Category:  4
Category: database 2

var agg = new GlideAggregate('incident');
agg.addAggregate('count', 'category'); 
agg.orderByDesc('category'); 
agg.query(); 
while (agg.next()) { 
  var category = agg.category;
  var count = agg.getAggregate('count', 'category');
  var agg2 = new GlideAggregate('incident');   
  agg2.addAggregate('count', 'category');
  agg2.orderBy('category');
  gs.info(category + ": Current number of incidents:" + count);
}

software: Current number of incidents:11
request: Current number of incidents:5
network: Current number of incidents:5
inquiry: Current number of incidents:28
hardware: Current number of incidents:8
database: Current number of incidents:2

var count = new GlideAggregate('incident');
count.addAggregate('COUNT');
count.query();
var incidents = 0;
if (count.next()) {
   incidents = count.getAggregate('COUNT');
}
gs.info('Number of incidents: ' + incidents);

インシデント [incident] テーブルの最初の 10 レコードの各カテゴリの数を出力します。

var incidentGroup = new GlideAggregate('incident');
incidentGroup.addAggregate('COUNT', 'category');
incidentGroup.setAggregateWindow(0, 10);
incidentGroup.query();
while (incidentGroup.next()) {
   var incidentCount = incidentGroup.getAggregate('COUNT', 'category');
   gs.info('{0} count: {1}', [incidentGroup.getValue('category'), incidentCount]);
}

出力：

database count: 1
Hardware count: 1
inquiry count: 7
software count: 1

次の例は、集計クエリのビジネスルールを有効にする方法を示しています。

var incidentGA = new GlideAggregate('incident');
incidentGA.setAggregateWorkflow(true); // activate business rules for this aggregate
incidentGA.addAggregate('COUNT', 'sys_mod_count');
incidentGA.query();

glide.businessrule.callstack プロパティがシステムプロパティ [sys_properties] テーブルにあり、true に設定されている場合、出力にはビジネスルール実行ログが含まれます。パフォーマンスへの影響を避けるために、不要な場合はこのプロパティを false に設定します。

BUSINESS RULE - About to execute business rule 'incident query' on incident:<span class = "session-log-bold-text"> </span>
BUSINESS RULE - Finished executing business rule 'incident query' on incident:<span class = "session-log-bold-text"> </span>

var ga = new GlideAggregate('incident');
ga.addAggregate('COUNT', 'category');
 
ga.setGroup(true);
ga.groupBy("category");
 
ga.query();
 
while(ga.next()) {
  gs.info('Category ' + ga.category + ' ' + ga.getAggregate('COUNT', 'category'));
  }

出力:

Category database 2
Category hardware 7
Category inquiry 18
Category network 5
Category request 5
Category software 11

次の例は、過去 6 か月間に作成されたインシデントをカウントする方法を示しています。インシデントは曜日ごとに分けられますが、年は含まれません。たとえば、木曜日のデフォルトの結果には年が含まれます ( 2023 年 16 日木曜日など)。

var incidentGroup = new GlideAggregate('incident');
incidentGroup.addEncodedQuery("sys_created_onRELATIVEGT@month@ago@6");
incidentGroup.addTrend('sys_created_on', 'dayofweek');
incidentGroup.addAggregate('COUNT');
incidentGroup.setIntervalYearIncluded(false);
incidentGroup.query();
while (incidentGroup.next()) {
    gs.info(incidentGroup.getValue('timeref') + ': ' + incidentGroup.getAggregate('COUNT'))};

出力：

Sunday: 1
Monday: 15
Tuesday: 1
Wednesday: 7
Thursday: 16
Saturday: 1