ServiceNowで複数テーブルの集計が思い通りにいかない理由と3つの突破口【GlideRecord / DB View 実装例付き】

import csv
import random
from datetime import date, timedelta

# ============================================================
# 設定
# ============================================================
OUTPUT_DIR = "/home/claude/"
START_DATE = date(2024, 4, 1)
END_DATE   = date(2026, 3, 31)

DEPARTMENTS = ["営業部", "マーケティング部", "人事部", "経理部", "開発部", "総務部", "購買部", "法務部"]
APPROVERS   = ["田中部長", "鈴木部長", "佐藤部長", "山田部長", "高橋部長"]
REQUESTERS  = ["山本太郎", "佐藤花子", "田中一郎", "鈴木明子", "高橋健二",
               "渡辺幸子", "伊藤誠", "中村美咲", "小林雄介", "加藤由美",
               "吉田亮", "松本奈々", "井上剛", "木村彩", "清水拓也"]

STATUSES = ["draft", "pending_approval", "approved", "rejected", "cancelled"]

def random_date(start, end):
    return start + timedelta(days=random.randint(0, (end - start).days))

def get_status_weights(req_date):
    if req_date >= date(2026, 1, 1):
        return [0.05, 0.35, 0.40, 0.15, 0.05]
    else:
        return [0.02, 0.05, 0.75, 0.15, 0.03]

# ============================================================
# 1. 仕入先マスター（x_xxx_supplier）
# ※ Pattern C用：製品マスターとはReference未定義、supplier_codeで紐づく
# ============================================================
suppliers = [
    {"supplier_code": "SUP001", "supplier_name": "東京オフィス機器株式会社",  "region": "関東", "rating": "A", "payment_terms": "30日"},
    {"supplier_code": "SUP002", "supplier_name": "大阪サプライズ商事",        "region": "関西", "rating": "B", "payment_terms": "60日"},
    {"supplier_code": "SUP003", "supplier_name": "名古屋テクノサービス",      "region": "中部", "rating": "A", "payment_terms": "30日"},
    {"supplier_code": "SUP004", "supplier_name": "福岡ITソリューションズ",    "region": "九州", "rating": "C", "payment_terms": "90日"},
    {"supplier_code": "SUP005", "supplier_name": "札幌オフィスサポート",      "region": "北海道","rating": "B", "payment_terms": "60日"},
    {"supplier_code": "SUP006", "supplier_name": "横浜プレミアムサプライ",    "region": "関東", "rating": "A", "payment_terms": "30日"},
    {"supplier_code": "SUP007", "supplier_name": "京都クリエイティブ商会",    "region": "関西", "rating": "B", "payment_terms": "60日"},
    {"supplier_code": "SUP008", "supplier_name": "仙台ビジネスマシン",        "region": "東北", "rating": "B", "payment_terms": "45日"},
]

with open(OUTPUT_DIR + "supplier.csv", "w", newline="", encoding="utf-8-sig") as f:
    writer = csv.DictWriter(f, fieldnames=["supplier_code","supplier_name","region","rating","payment_terms"])
    writer.writeheader()
    writer.writerows(suppliers)
print(f"supplier.csv: {len(suppliers)}件")

# ============================================================
# 2. 製品マスター（x_xxx_product）
# ※ supplier_code は文字列フィールド（Referenceではない）→ Pattern C の素材
# ============================================================
products = [
    # OA機器
    {"product_code":"PRD001","product_name":"ノートPC（標準モデル）",   "category":"OA機器",   "unit_price":120000, "unit":"台",  "supplier_code":"SUP001"},
    {"product_code":"PRD002","product_name":"ノートPC（ハイスペック）", "category":"OA機器",   "unit_price":220000, "unit":"台",  "supplier_code":"SUP001"},
    {"product_code":"PRD003","product_name":"デスクトップPC",           "category":"OA機器",   "unit_price":150000, "unit":"台",  "supplier_code":"SUP003"},
    {"product_code":"PRD004","product_name":"モニター（24インチ）",     "category":"OA機器",   "unit_price": 35000, "unit":"台",  "supplier_code":"SUP001"},
    {"product_code":"PRD005","product_name":"モニター（32インチ）",     "category":"OA機器",   "unit_price": 65000, "unit":"台",  "supplier_code":"SUP006"},
    {"product_code":"PRD006","product_name":"プリンター（カラー）",     "category":"OA機器",   "unit_price": 80000, "unit":"台",  "supplier_code":"SUP003"},
    {"product_code":"PRD007","product_name":"プリンター（モノクロ）",   "category":"OA機器",   "unit_price": 45000, "unit":"台",  "supplier_code":"SUP003"},
    # オフィス用品
    {"product_code":"PRD008","product_name":"デスクチェア（エルゴノミクス）","category":"オフィス家具","unit_price": 85000,"unit":"脚","supplier_code":"SUP002"},
    {"product_code":"PRD009","product_name":"デスクチェア（スタンダード）", "category":"オフィス家具","unit_price": 35000,"unit":"脚","supplier_code":"SUP002"},
    {"product_code":"PRD010","product_name":"会議用テーブル",           "category":"オフィス家具","unit_price":120000,"unit":"台", "supplier_code":"SUP007"},
    {"product_code":"PRD011","product_name":"ホワイトボード（大）",     "category":"オフィス家具","unit_price": 28000,"unit":"枚", "supplier_code":"SUP007"},
    {"product_code":"PRD012","product_name":"コピー用紙（A4/500枚）",   "category":"消耗品",   "unit_price":   500, "unit":"箱",  "supplier_code":"SUP005"},
    {"product_code":"PRD013","product_name":"ボールペン（10本セット）", "category":"消耗品",   "unit_price":   800, "unit":"セット","supplier_code":"SUP005"},
    {"product_code":"PRD014","product_name":"トナーカートリッジ",       "category":"消耗品",   "unit_price":  8000, "unit":"個",  "supplier_code":"SUP003"},
    # ソフトウェア・サービス
    {"product_code":"PRD015","product_name":"Office365ライセンス（年間）","category":"ソフトウェア","unit_price": 18000,"unit":"ライセンス","supplier_code":"SUP001"},
    {"product_code":"PRD016","product_name":"セキュリティソフト（年間）","category":"ソフトウェア","unit_price":  5000,"unit":"ライセンス","supplier_code":"SUP004"},
    {"product_code":"PRD017","product_name":"クラウドストレージ（年間）","category":"ソフトウェア","unit_price": 12000,"unit":"アカウント","supplier_code":"SUP004"},
    # 外部サービス
    {"product_code":"PRD018","product_name":"社員研修サービス（1日）",  "category":"外部サービス","unit_price":200000,"unit":"回", "supplier_code":"SUP006"},
    {"product_code":"PRD019","product_name":"清掃サービス（月次）",     "category":"外部サービス","unit_price": 50000,"unit":"月", "supplier_code":"SUP008"},
    {"product_code":"PRD020","product_name":"設備保守契約（年間）",     "category":"外部サービス","unit_price":300000,"unit":"件", "supplier_code":"SUP008"},
]

with open(OUTPUT_DIR + "product.csv", "w", newline="", encoding="utf-8-sig") as f:
    writer = csv.DictWriter(f, fieldnames=["product_code","product_name","category","unit_price","unit","supplier_code"])
    writer.writeheader()
    writer.writerows(products)
print(f"product.csv: {len(products)}件")

# ============================================================
# 3. 購買申請ヘッダー（x_xxx_po_header2）
# ============================================================
headers = []
for i in range(1, 201):
    req_date   = random_date(START_DATE, END_DATE)
    status     = random.choices(STATUSES, weights=get_status_weights(req_date))[0]
    approval_date = ""
    approver      = ""
    if status in ("approved", "rejected"):
        approval_date = (req_date + timedelta(days=random.randint(3, 20))).strftime("%Y-%m-%d")
        approver      = random.choice(APPROVERS)
    headers.append({
        "po_number":     f"PO{str(i).zfill(5)}",
        "department":    random.choice(DEPARTMENTS),
        "requester":     random.choice(REQUESTERS),
        "request_date":  req_date.strftime("%Y-%m-%d"),
        "status":        status,
        "approver":      approver,
        "approval_date": approval_date,
        "purpose":       random.choice(["業務効率化", "設備更新", "新規プロジェクト対応", "定期補充", "緊急対応"]),
        "total_amount":  0,  # 明細集計後に更新
    })

with open(OUTPUT_DIR + "po_header.csv", "w", newline="", encoding="utf-8-sig") as f:
    writer = csv.DictWriter(f, fieldnames=["po_number","department","requester","request_date","status","approver","approval_date","purpose","total_amount"])
    writer.writeheader()
    writer.writerows(headers)
print(f"po_header.csv: {len(headers)}件")

# ============================================================
# 4. 申請明細（x_xxx_po_line2）
# ============================================================
lines = []
header_totals = {h["po_number"]: 0 for h in headers}
product_map   = {p["product_code"]: p for p in products}
line_seq = 1

for header in headers:
    num_lines = random.randint(1, 5)
    selected_products = random.sample(products, min(num_lines, len(products)))
    for prod in selected_products:
        qty      = random.randint(1, 20)
        unit_price = prod["unit_price"]
        # ±10%の価格ブレを加える（現実感のため）
        negotiated_price = int(unit_price * random.uniform(0.90, 1.10) / 100) * 100
        subtotal = qty * negotiated_price
        header_totals[header["po_number"]] += subtotal
        lines.append({
            "line_number":       f"LN{str(line_seq).zfill(6)}",
            "po_number":         header["po_number"],   # Header への参照キー
            "product_code":      prod["product_code"],  # Product への参照キー
            "quantity":          qty,
            "unit_price":        negotiated_price,
            "subtotal":          subtotal,
            "delivery_date":     (date.fromisoformat(header["request_date"]) + timedelta(days=random.randint(7, 45))).strftime("%Y-%m-%d"),
            "line_status":       random.choice(["pending", "confirmed", "delivered", "cancelled"]) if header["status"] == "approved" else "pending",
        })
        line_seq += 1

with open(OUTPUT_DIR + "po_line.csv", "w", newline="", encoding="utf-8-sig") as f:
    writer = csv.DictWriter(f, fieldnames=["line_number","po_number","product_code","quantity","unit_price","subtotal","delivery_date","line_status"])
    writer.writeheader()
    writer.writerows(lines)
print(f"po_line.csv: {len(lines)}件")

# ============================================================
# 5. ヘッダーの total_amount を明細集計で更新
# ============================================================
for h in headers:
    h["total_amount"] = header_totals[h["po_number"]]

with open(OUTPUT_DIR + "po_header.csv", "w", newline="", encoding="utf-8-sig") as f:
    writer = csv.DictWriter(f, fieldnames=["po_number","department","requester","request_date","status","approver","approval_date","purpose","total_amount"])
    writer.writeheader()
    writer.writerows(headers)

# ============================================================
# サマリー出力
# ============================================================
print("\n===== データ生成完了 =====")
print(f"supplier.csv  : {len(suppliers)}件（仕入先マスター）")
print(f"product.csv   : {len(products)}件（製品マスター）")
print(f"po_header.csv : {len(headers)}件（申請ヘッダー）")
print(f"po_line.csv   : {len(lines)}件（申請明細）")
print(f"\n総申請金額    : ¥{sum(h['total_amount'] for h in headers):,.0f}")
print(f"平均明細数    : {len(lines)/len(headers):.1f}件/申請")
print("\n===== テーブル間の結合キー =====")
print("po_line.po_number    → po_header.po_number  （Reference想定）")
print("po_line.product_code → product.product_code （Reference想定）")
print("product.supplier_code → supplier.supplier_code （Pattern C: Reference未定義）")

po_line2.po_number    → po_header2（申請ヘッダー）
po_line2.product_code → po_product（製品マスター）

var gr = new GlideRecord('x_XXXX_po_case_0_po_line2');
gr.setLimit(5);
gr.query();

while (gr.next()) {
    var lineNo   = gr.getValue('line_number');
    var dept     = gr.po_number.department.toString();      // po_header2.department
    var category = gr.product_code.category.toString();     // po_product.category
    var subtotal = gr.getValue('subtotal');

    gs.info(lineNo + ' | ' + dept + ' | ' + category + ' | ¥' + parseInt(subtotal).toLocaleString());
}

LN000001 | 総務部 | OA機器 | ¥519,200
LN000002 | 人事部 | ソフトウェア | ¥116,000
LN000003 | 総務部 | 外部サービス | ¥531,000
LN000004 | 営業部 | OA機器 | ¥741,600
LN000005 | 開発部 | オフィス家具 | ¥79,800

// NG例：groupBy にドットウォークを指定
var ga = new GlideAggregate('x_918153_po_case_0_po_line2');
ga.addAggregate('SUM', 'subtotal');
ga.groupBy('po_number.department');   // ドットウォーク指定
ga.groupBy('product_code.category');  // ドットウォーク指定
ga.query();

while (ga.next()) {
    var dept     = ga.getValue('po_number.department');
    var category = ga.getValue('product_code.category');
    var total    = ga.getAggregate('SUM', 'subtotal');
    gs.info(dept + ' | ' + category + ' | ¥' + total);
}

 |  | ¥500,281,100

var map = {};

var ga = new GlideAggregate('x_918153_po_case_0_po_line2');
ga.addAggregate('SUM', 'subtotal');
ga.groupBy('po_number');      // sys_id でグループ化
ga.groupBy('product_code');   // sys_id でグループ化
ga.query();

while (ga.next()) {
    var dept     = ga.po_number.department.toString();
    var category = ga.product_code.category.toString();
    var total    = parseInt(ga.getAggregate('SUM', 'subtotal'));
    var key      = dept + '||' + category;

    map[key] = (map[key] || 0) + total;
}

// 結果出力
for (var key in map) {
    var parts = key.split('||');
    gs.info(parts[0] + ' | ' + parts[1] + ' | ¥' + map[key].toLocaleString());
}

総務部 | OA機器        | ¥25,755,700
総務部 | 外部サービス   | ¥34,611,800
人事部 | OA機器        | ¥38,884,200
人事部 | ソフトウェア   | ¥869,500
開発部 | 外部サービス   | ¥40,434,800
開発部 | OA機器        | ¥36,162,800
マーケティング部 | OA機器 | ¥30,181,000
...

po_header2（1）
    ↑ po_line2.po_number（Reference）で逆引き
po_line2（N）

// Pattern B：po_header2 → po_line2 の逆引き（1:N）
var header = new GlideRecord('x_xxx_po_header2');
header.addQuery('status', 'approved');  // 承認済みのみ
header.query();

while (header.next()) {
    var poNumber = header.getValue('po_number');
    var dept     = header.getValue('department');

    // 明細を逆引き
    var lineTotal = 0;
    var lineCnt   = 0;

    var line = new GlideRecord('x_xxx_po_line2');
    line.addQuery('po_number', header.getValue('sys_id'));
    line.query();
    while (line.next()) {
        lineTotal += parseInt(line.getValue('subtotal'));
        lineCnt++;
    }

    gs.info(
        poNumber + ' | ' + dept +
        ' | 明細' + lineCnt + '件' +
        ' | ¥' + lineTotal.toLocaleString()
    );
}

PO00006 | 総務部         | 明細3件 | ¥2,047,400
PO00046 | マーケティング部 | 明細3件 | ¥1,537,100
PO00060 | 総務部         | 明細2件 | ¥345,600
PO00072 | マーケティング部 | 明細2件 | ¥827,500
PO00109 | 開発部         | 明細2件 | ¥414,700
...

Scheduled Script Job（夜間バッチ）
    ↓
Pattern B のロジックで集計
    ↓
集計用 テーブル（単一テーブル）に書き込み
    ↓
GlideAggregate で高速集計・Report/PA で可視化

po_product.supplier_code  →  String（文字列）
po_supplier.supplier_code →  String（PK）

※ SNow 上で Reference 関係は未定義
※ ドットウォーク不可

po_product（prod）
    ↓ prod.supplier_code = sup.supplier_code で JOIN
po_supplier（sup）
    ↓
u_po_product_supplier_view（仮想テーブル）
    → GlideRecord / GlideAggregate で通常テーブルとして扱える

// Pattern C：DB View を GlideRecord で参照
var gr = new GlideRecord('u_po_product_supplier_view');
gr.setLimit(5);
gr.query();

while (gr.next()) {
    gs.info(
        gr.getValue('prod_category')     + ' | ' +
        gr.getValue('prod_product_name') + ' | ' +
        gr.getValue('sup_supplier_name') + ' | ' +
        gr.getValue('sup_region')
    );
}

OA機器      | プリンター（モノクロ）       | 名古屋テクノサービス   | 中部
外部サービス | 設備保守契約（年間）         | 仙台ビジネスマシン     | 東北
消耗品      | ボールペン（10本セット）      | 札幌オフィスサポート   | 北海道
OA機器      | デスクトップPC              | 名古屋テクノサービス   | 中部
ソフトウェア | セキュリティソフト（年間）   | 福岡ITソリューションズ | 九州

// Pattern C：DB View に GlideAggregate で集計
// 製品カテゴリ × 仕入先地域 ごとの製品数
var ga = new GlideAggregate('u_po_product_supplier_view');
ga.addAggregate('COUNT');
ga.groupBy('prod_category');
ga.groupBy('sup_region');
ga.orderBy('prod_category');
ga.query();

while (ga.next()) {
    gs.info(
        ga.getValue('prod_category') + ' | ' +
        ga.getValue('sup_region')    + ' | ' +
        ga.getAggregate('COUNT')     + '件'
    );
}

OA機器      | 中部   | 3件
OA機器      | 関東   | 4件
オフィス家具 | 関西   | 4件
ソフトウェア | 九州   | 2件
ソフトウェア | 関東   | 1件
外部サービス | 東北   | 2件
外部サービス | 関東   | 1件
消耗品      | 中部   | 1件
消耗品      | 北海道 | 2件