0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@pmc-ko(K Ohtsuka)
in

プリザンター内部で動くSQL文を調べてみる[第9回：クロス集計編]

0
Posted at

はじめに

第 8 回 ではカンバンの SQL を解説しました。
本記事（第 9 回）では クロス集計（Crosstab） の SQL を解説します。GROUP BY を使った DB 側集計と、datetime 型 X 軸の日付グループ化式が特徴です。

バージョン 1.5.9.0 を対象にしています

回 タイトル
第 1 回 アーキテクチャ編 ── SQL を組み立てる仕組みの全体像
第 2 回 一覧編 ── フィルタとソートの WHERE・ORDER BY
第 3 回 編集編 ── SELECT・INSERT・UPDATE の実装
第 4 回 カレンダー編 ── 日付範囲クエリの実装
第 5 回 ガントチャート編 ── GanttUtilities.Where() と GanttLimit
第 6 回 バーンダウンチャート編 ── 現在 + 履歴 UNION クエリ
第 7 回 時系列チャート編 ── HorizontalAxis と NormalAndHistory
第 8 回 カンバン編 ── KambanLimit・全件取得・カード移動
第 9 回（本記事） クロス集計編 ── GROUP BY と日付軸グループ化式
第 10 回 画像ライブラリ編 ── Binaries INNER JOIN クエリ
第 11 回 分析チャート編 ── 分析パーツごとの現在＋履歴 SQL

処理フロー

GET /items/{siteId}/crosstab
 └─ IssueUtilities.Crosstab()
     └─ CrosstabDataRows()                         ← L9454 IssueUtilities.cs
         ├─（X 軸が datetime 以外）
         │   └─ Rds.SelectIssues(X+Y グループ列 + 集計列,
         │           GROUP BY: X + Y)
         │       ① SELECT（GROUP BY あり）
         └─（X 軸が datetime 型）
             ├─ CrosstabUtilities.DateGroup()       日付グループ化式を生成
             ├─ CrosstabUtilities.Where()           表示期間 WHERE を生成
             └─ Rds.SelectIssues(日付式 + Y + 集計列,
                     GROUP BY: 日付式 + Y)
                 ② SELECT（日付グループ化 GROUP BY）

CrosstabColumns() による集計 SELECT 列の組み立て

クロス集計の SELECT 句（集計列部分）は CrosstabColumns() 拡張メソッドで生成されます。集計タイプ（Count / Total / Average / Min / Max）と Y 軸設定によって SELECT 列が変わります。

Models/Issues/IssueUtilities.cs
private static SqlColumnCollection CrosstabColumns(
    this SqlColumnCollection self,
    Context context, SiteSettings ss, View view,
    Column groupByY, List<Column> columns,
    Column value, string aggregateType)
{
    // Y 軸が "Columns"（列ごとに横展開）の場合
    if (view.CrosstabGroupByY == "Columns")
    {
        // columns に含まれる各列を集計関数付きで SELECT に追加
        columns.ForEach(column =>
            self.Add(
                context: context,
                column: column,
                _as: column.ColumnName,
                function: Sqls.Function(aggregateType)));  // SUM / COUNT / AVG など
        return self;
    }

    // Y 軸が通常列（ステータス・担当者など）の場合
    // Y 軸グループ列を追加
    self.WithItemTitleCoalesced(context, ss, column: groupByY);

    // 集計値列を追加（COUNT なら IssueId で COUNT、他は value 列で集計）
    if (aggregateType == "Count")
    {
        self.Add(context, column: ss.GetColumn(context, "IssueId"),
            _as: "Value",
            function: Sqls.Functions.Count);  // COUNT("Issues"."IssueId")
    }
    else
    {
        self.Add(context, column: value,
            _as: "Value",
            function: Sqls.Function(aggregateType));  // SUM / AVG / MIN / MAX
    }
    return self;
}

集計タイプと生成される SQL の対応：

集計タイプ aggregateType SELECT 列の SQL
件数 "Count" COUNT("Issues"."IssueId") AS "Value"
合計 "Total" SUM("Issues"."WorkValue") AS "Value"
平均 "Average" AVG("Issues"."WorkValue") AS "Value"
最小 "Min" MIN("Issues"."WorkValue") AS "Value"
最大 "Max" MAX("Issues"."WorkValue") AS "Value"

クロス集計ビューの SQL

CrosstabDataRows メソッド

クロス集計のデータ取得は IssueUtilities.CrosstabDataRows() が担います。X 軸列の型（datetime か否か）で処理が分岐します。

X 軸が datetime 型でない場合（分類・数値など）

Models/Issues/IssueUtilities.cs
var column = Rds.IssuesColumn()
    .WithItemTitleCoalesced(context, ss, column: groupByX)  // X 軸グループ列
    .CrosstabColumns(context, ss, view,                    // 集計列（SUM・COUNT など）
        groupByY, columns, value, aggregateType);

var where = view.Where(context, ss);

// GROUP BY X 軸 + Y 軸
var groupBy = Rds.IssuesGroupBy()
    .WithItemTitleCoalesced(context, ss, column: groupByX)
    .WithItemTitleCoalesced(context, ss, column: groupByY);

dataRows = Repository.ExecuteTable(context, statements: Rds.SelectIssues(
    column:  column,
    join:    ss.Join(context, join: new IJoin[] { column, where, groupBy }),
    where:   where,
    param:   param,
    groupBy: groupBy))  // ← GROUP BY あり
        .AsEnumerable();

X 軸が datetime 型の場合（年・月・週・日単位の集計）

Models/Issues/IssueUtilities.cs
// 日付を年・月・週・日にグループ化する式を生成
var dateGroup = CrosstabUtilities.DateGroup(context, ss,
    column: groupByX, timePeriod: timePeriod);

var column = Rds.IssuesColumn()
    .Add(dateGroup, _as: groupByX.ColumnName)  // 日付グループ式を SELECT に追加
    .CrosstabColumns(context, ss, view,
        groupByY, columns, value, aggregateType);

// 日付範囲 WHERE（表示期間に応じた絞り込み）
var where = view.Where(context, ss,
    where: CrosstabUtilities.Where(context, ss,
        column: groupByX, timePeriod: timePeriod, month: month));

// GROUP BY 日付グループ式 + Y 軸
var groupBy = Rds.IssuesGroupBy()
    .Add(dateGroup)
    .WithItemTitleCoalesced(context, ss, column: groupByY);

dataRows = Repository.ExecuteTable(context, statements: Rds.SelectIssues(
    column:  column,
    join:    ss.Join(context, join: new IJoin[] { column, where, groupBy }),
    where:   where,
    param:   param,
    groupBy: groupBy))  // ← GROUP BY あり
        .AsEnumerable();

クロス集計の GROUP BY SQL

X 軸に「ステータス」、Y 軸に「担当者」、集計値に「件数（COUNT）」を設定した場合の SQL イメージ：

SELECT
    "Issues"."Status",          -- X 軸グループ
    "Issues"."Owner",           -- Y 軸グループ
    COUNT("Issues"."IssueId") AS "Count"
FROM "Issues"
INNER JOIN "Items" ON ...
WHERE
    "Issues"."TenantId" = 1
    AND "Issues"."SiteId" = 42
    AND ...（画面フィルタ条件）
GROUP BY
    "Issues"."Status",
    "Issues"."Owner"
-- ORDER BY なし

X 軸に日付型の列（例：StartTime）を設定し、時間軸を「月」にした場合：

SELECT
    -- 月単位グループ化式（DB 方言に依存）
    CONVERT(nvarchar, DATEPART(yyyy, "Issues"."StartTime")) + '/'
        + RIGHT('0' + CONVERT(nvarchar, DATEPART(mm, "Issues"."StartTime")), 2)
            AS "StartTime",
    "Issues"."Owner",
    COUNT("Issues"."IssueId") AS "Count"
FROM "Issues"
INNER JOIN ...
WHERE
    "Issues"."StartTime" between '2025-04-01' and '2026-03-31'  -- 12 か月分
    AND ...（画面フィルタ条件）
GROUP BY
    CONVERT(nvarchar, DATEPART(yyyy, "Issues"."StartTime")) + '/'
        + RIGHT('0' + CONVERT(nvarchar, DATEPART(mm, "Issues"."StartTime")), 2),
    "Issues"."Owner"

クロス集計の時間軸別 WHERE 範囲

X 軸が datetime 型の場合、CrosstabUtilities.Where() が自動的に表示範囲を絞り込みます。

時間軸 取得範囲
年（Yearly） 現在年の 12 年前〜翌年末
月（Monthly） 現在月の 11 か月前〜翌月末
週（Weekly） 最終週の 11 週前〜翌週末
日（Daily） 選択月の 1 日〜月末

これにより、クロス集計の画面に表示する列数分のデータだけを取得します。

CrosstabUtilities.Where() の実装

CrosstabUtilities.Where() は timePeriod に応じて日付範囲の SqlWhereCollection を直接生成します（@ パラメータではなくリテラル値を埋め込みます）。

Libraries/ViewModes/CrosstabUtilities.cs
public static SqlWhereCollection Where(
    Context context, SiteSettings ss, Column column, string timePeriod, DateTime month)
{
    switch (timePeriod)
    {
        case "Yearly":
            var year = new DateTime(month.Year, 1, 1);
            // 現在年の 12 年前〜翌年末（合計 13 年分）
            return new SqlWhereCollection(new SqlWhere(
                tableName: column.TableName(),
                columnBrackets: new string[]
                {
                    "\"{0}\" between '{1:yyyy/MM/dd HH:mm:ss.fff}' and '{2:...}'".Params(
                        column.Name,
                        year.AddYears(-11).ToUniversal(context),      // 12年前の1月1日
                        year.AddYears(1).AddMilliseconds(-3).ToUniversal(context))  // 翌年末
                }, _operator: null));

        case "Monthly":
            // 現在月の 11 か月前〜翌月末（合計 12 か月分）
            return new SqlWhereCollection(new SqlWhere(
                tableName: column.TableName(),
                columnBrackets: new string[]
                {
                    "\"{0}\" between '{1:...}' and '{2:...}'".Params(
                        column.Name,
                        month.AddMonths(-11).ToUniversal(context),
                        month.AddMonths(1).AddMilliseconds(-3).ToUniversal(context))
                }, _operator: null));

        case "Weekly":
            var end = WeeklyEndDate(month);  // 月末週の終わり（日曜）
            // 末尾週の 11 週前〜翌週末（合計 12 週分）
            return new SqlWhereCollection(new SqlWhere(...Params(
                        column.Name,
                        end.AddDays(-77).ToUniversal(context),
                        end.AddDays(7).AddMilliseconds(-3).ToUniversal(context))));

        case "Daily":
            // 選択月の 1 日〜月末
            return new SqlWhereCollection(new SqlWhere(...Params(
                        column.Name,
                        month.ToUniversal(context),
                        month.AddMonths(1).AddMilliseconds(-3).ToUniversal(context))));
    }
}

CrosstabUtilities.Where() は SQL パラメータ（@変数名）ではなく、日時をリテラル文字列として直接 SQL に埋め込みます。これはリテラルでも型安全なタイムスタンプ文字列を使うため、SQL インジェクションのリスクはありませんが、一覧画面の View.Where() とは異なるアプローチです。

CrosstabUtilities.DateGroup() による日付グループ化式

X 軸が datetime 型の場合、DateGroup() がデータベース方言に応じた日付グループ化 SQL 式を生成します。

Libraries/ViewModes/CrosstabUtilities.cs
public static string DateGroup(
    Context context, SiteSettings ss, Column column, string timePeriod)
{
    var columnBracket = "\"{0}\".\"{1}\"".Params(column.TableName(), column.Name);
    switch (timePeriod)
    {
        case "Yearly":  return context.Sqls.DateGroupYearly.Params(columnBracket);
        case "Monthly": return context.Sqls.DateGroupMonthly.Params(columnBracket);
        case "Weekly":
            var part = context.Sqls.DateGroupWeeklyPart.Params(columnBracket);
            return context.Sqls.DateGroupWeekly.Params(part);
        case "Daily":   return context.Sqls.DateGroupDaily.Params(columnBracket);
        default: return null;
    }
}

SQL Server の場合、DateGroupMonthly は次のような式になります：

-- 月単位グループ化（"Issues"."StartTime" を yyyy/MM 形式の文字列に変換）
CONVERT(nvarchar, DATEPART(yyyy, "Issues"."StartTime")) + '/'
    + RIGHT('0' + CONVERT(nvarchar, DATEPART(mm, "Issues"."StartTime")), 2)

この式が SELECT 句と GROUP BY 句の両方に追加されます（GROUP BY に集計関数は含まれない）。

また、クロス集計では X 軸・Y 軸の値が多すぎた場合のために InRangeX / InRangeY チェックも行われます。

Libraries/ViewModes/CrosstabUtilities.cs
// X 軸の種類数が CrosstabXLimit を超えたら警告
public static bool InRangeX(Context context, EnumerableRowCollection<DataRow> dataRows)
{
    return dataRows.Select(o => o.String("GroupByX")).Distinct().Count()
        <= Parameters.General.CrosstabXLimit;
}

// Y 軸の種類数が CrosstabYLimit を超えたら警告
public static bool InRangeY(Context context, EnumerableRowCollection<DataRow> dataRows)
{
    return dataRows.Select(o => o.String("GroupByY")).Distinct().Count()
        <= Parameters.General.CrosstabYLimit;
}

カンバンの InRange が SELECT COUNT SQL で件数をチェックするのに対し、クロス集計の InRangeX / InRangeY は 一度全件 SELECT してから C# の LINQ で種類数をカウントします。この違いにより、クロス集計は SELECT COUNT を発行しません。

集計タイプと SQL 関数の対応

集計タイプに応じて、SELECT 句の集計関数が変わります。

集計タイプ SQL 関数 説明
Count COUNT("Issues"."IssueId") レコード件数
Total SUM("Issues"."NumA") 合計値
Average AVG("Issues"."NumA") 平均値
Min MIN("Issues"."NumA") 最小値
Max MAX("Issues"."NumA") 最大値

カンバンとクロス集計の比較

（カンバンの詳細は第 8 回を参照してください）

項目 一覧（GridData） カンバン（Kamban） クロス集計
データ取得メソッド GetGridData() KambanData() CrosstabDataRows()
SELECT 列 GridColumns（多数） IssueId・Title・Status + X/Y/Value 列 X/Y グループ列 + 集計値列
GROUP BY なし なし（C# で集計） あり（SQL で集計）
ORDER BY UpdatedTime DESC（デフォルト） なし なし
集計の実装場所 なし C# の Summary() SQL の集計関数
ページネーション OFFSET … FETCH NEXT なし（全件） なし（全件）
件数チェック SELECT COUNT 後にページ分割 SELECT COUNT（InRange） InRangeX/Y（C# で種類数チェック）
日付軸サポート なし なし あり（年・月・週・日）
用途 レコード一覧表示 ステータス管理 多次元集計・傾向分析

カンバンは全レコードを取得して C# 側でグループ化・集計するのに対し、クロス集計は GROUP BY で DB 側に集計を委ねます。

まとめ

本記事では、クロス集計の SQL を解説しました。

  • クロス集計：GROUP BY を使って DB 側で集計する。X 軸が datetime 型の場合は日付グループ式と時間軸別の WHERE 範囲が追加される
  • クロス集計の InRangeX / InRangeY は全件 SELECT 後に C# で種類数をチェックする点でカンバンの InRange（SELECT COUNT）とは異なる

第 10 回では、画像ライブラリの Binaries INNER JOIN クエリを解説します。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?