クロス集計分析のバックアップ(No.9)

分類	特徴	変数の例
デモグラフィック変数	・人口統計分布に基づく変数。・基礎的情報として見ることが多い。	性別、年代、家族構成、職業、年収、学歴など
地理的変数	・地理的に分割される変数。・消費者向けのマーケティングで利用されることが多い。	国、行政区、気候地域、都市と農村など
心理的変数	・価値観やライフスタイル、好み等を表す変数。・意識調査の結果から変数をつくることが多い。	＊＊が好き、＊＊を常用しているなど
行動変数	・行動を表す変数。・IT化の進展によって集計が容易になった。	購買履歴、使用頻度、アクセスログなど

↑

縦横カテゴリ軸と設定 †

縦カテゴリと横カテゴリを設定し、
このカテゴリ間の関連を調査可能する。

縦横カテゴリ軸
- 縦（集計のグループ）
- 横（集計結果の射影）

仮説を立てて、軸を選択する。
- 横軸：今月は売上が下がったので、先月、今月の月。
- 縦軸
  - 夏休みが終わったので、客層が変わった？→客層（単身、ファミリー
  - 雨の日が多かったので、天候のせいではないか？→天候

↑

クロス・テーブル †

トランザクション・テーブル → クロス集計 → クロス・テーブル
トランザクション・テーブルをクロス集計するとクロス・テーブルになる。

↑

ルックアップ・テーブル †

SQLで言えばJOINに該当する表計算ソフトの機能。
≒トランザクション・テーブル（データ分析の元となるテーブル）

↑

ピボット・テーブル、ピボット・グラフ †

表計算ソフトや、分析ライブラリに実装されている機能。

トランザクション・テーブルやルックアップ・テーブル
からクロス・テーブル（とグラフ）を生成する。

さらに、ココから、以下のような操作が可能。

縦持ち・横持ちの相互変換
- ピボット
- アン・ピボット

データの分析

ドリルダウン / ドリルアップ / ドリルスルー
集計結果の概略化 ⇔ 詳細化

ダイス / スライス
集計軸の変更 / 集計対象への条件追加

↑

アンケート †

結果のまとめ

↑

マーケティング †

商品の売上高を顧客属性（年齢層や性別）毎に集計

↑

QC（クオリティコントロール †

管理図のような時系列分析で問題を早期に見つけ、
クロス集計どこで問題が起こっているのかを見つけ、
特性要因図（ロジックツリー）で考えて仮説を出し、

最後に調査で確認する。

↑

SQLの例 †

SQLを使用したクロス集計分析の例。

GROUP BYを使用する場合、SELECTには、
GROUP BYで指定した列と集計関数のみを指定できる。

↑

元データ †

↑

トランザクション・テーブル †

表名：Xテーブル
項目名
- 名称
- 属性A
- 属性B
- 属性C
- 分類A
- 分類B
- 分類C
- 数値

↑

クロス・テーブル †

クロス集計したビュー

SELECT
  名称,
  属性A,
  属性B,
  属性C,
  分類A,
  分類B,
  分類C,
  SUM(数値) as 合計
From Xテーブル
GROUP BY
  名称,
  属性A,
  属性B,
  属性C,
  分類A,
  分類B,
  分類C,

↑

ピボット / アン・ピボット †

↑

ピボット †

縦持ち → 横持ち

SELECT
  名称,
  max(CASE WHEN 属性A = 'XXXX' THEN 属性B END) AS XXXX,
  max(CASE WHEN 属性A = 'YYYY' THEN 属性C END) AS YYYY,
  分類A,
  分類B,
  分類C
FROM クロス・テーブル
GROUP BY
  名称,
  属性A,
  属性B,
  属性C,
  分類A,
  分類B,
  分類C

※ 1 : 1 の Key-Valueを縦持ちにする場合、
　THEN 属性X else null END の様にelse nullを追加する。

※ SQLだと、RDBMSによってはPIVOT句が用意されている。

↑

アン・ピボット †

横持ち → 縦持ち

SELECT 名称, 'XXXX' as 属性A, XXXX as 属性B, 分類A, 分類B, 分類C FROM ピボット・テーブル
UNION ALL
SELECT 名称, 'YYYY' as 属性A, YYYY as 属性C, 分類A, 分類B, 分類C FROM ピボット・テーブル
;

※ SQLだと、RDBMSによってはUNPIVOT句が用意されている。

↑

分析例 †

↑

基礎となる分析結果 †

クロス・テーブルを分類A, 属性Aで集計。

SELECT
  分類A,
  属性A,
  SUM(合計)
From クロス・テーブル
GROUP BY
  分類A,
  属性A

※ ピボット / アン・ピボットしたテーブルを対象としても良い。

↑

ドリルダウン †

基礎分析結果の、分類Aを固定し、分類Bで集計

SELECT
  分類B,
  属性A,
  SUM(合計)
From クロス・テーブル
Where 分類A = 'XXXX'
GROUP BY 分類B, 属性A

↑

ドリルアップ †

ドリルダウンしたものを元の基礎分析結果に戻す。

↑

ダイス †

基礎分析結果の、分類ではなく、属性を変える（属性A → 属性B）。

SELECT
  分類A,
  属性B,
  SUM(合計)
From クロス・テーブル
GROUP BY
  分類A,
  属性B

※ 分類A → 分類Bは詳細化。属性A → 属性Bは回転（軸の変更）。

↑

スライス †

基礎分析結果の、非表示の属性項目を固定する。

SELECT
  分類A,
  属性A,
  SUM(合計)
From クロス・テーブル
Where 属性B = 'XXXX'
GROUP BY 分類A, 属性A

※ 次元（軸）を減らし、３次元 → ２次元データへ。

↑

ドリルスルー †

例えば、ピボット・テーブルに対して、元データで内訳をみる。

SELECT *
FROM Xテーブル
WHERE
  名称 = '集計条件に指定した列の値'
  AND 属性A = 'XXXX or YYYY : 縦持ちにした値の列名'

↑

参考 †

SQLと「ドリルダウン（及びドリルアップ）」，
「ダイス」，「スライス」の関係 - ウィリアムのいたずらの開発？日記
https://blog.goo.ne.jp/xmldtp/e/c5be25e2caf4e3cf3fa02dcdeed7c1e5

3-5　ドリルダウンやドリルスルーによる分析
https://manual.wingarc-support.com/ja/manual/drsum/dtl51excel/Content/109412452.html
- 3-5-1　階層を下げて詳細データを表示する（ドリルダウン）
- 3-5-2　階層を上げて要約データを表示する（ドリルアップ）
- 3-5-3　集計データから内訳を表示する（ドリルスルー）
- 3-5-4　階層を下げた詳細データの表示について（ドリルダウン）

↑

縦横変換 †

SQL で縦横変換まとめ（pivot と unpivot） - Qiita
https://qiita.com/k24d/items/79bc4828c918dfeeac34

[SQL]データの縦持ち、横持ちを入れ替える｜ Developers.IO
https://dev.classmethod.jp/etc/sql-data-horizontal-vertical/

↑

多次元データベースのオンライン分析処理（OLAP） †

上記のピボットと同じクロス集計を行うことが出来る。

ファクト・テーブルからクロス・テーブル（とグラフ）を生成

ファクト・テーブルは、スタース・キーマの構成要素。

スター・スキーマは、
- 1つ以上のファクト・テーブルと、
- 外部キーを通じて関連付けられた1つ以上のディメンション・テーブル

で構成される。

↑

クロス集計分析 のバックアップ(No.9)

目次 †

概要 †

詳細 †

分析プロジェクト的に †

どう計画するか？ †

どう読めばいいか？ †

EXCELでどう作るか？ †

分析に関する用語 †

属性、変数、カテゴリ †

縦横カテゴリ軸と設定 †

クロス・テーブル †

ルックアップ・テーブル †

ピボット・テーブル、ピボット・グラフ †

可視化の方法 †

100%積上棒グラフ †

複数系列の棒グラフ †

ビジネス上での事例 †

アンケート †

マーケティング †

QC（クオリティコントロール †

SQLの例 †

元データ †

トランザクション・テーブル †

クロス・テーブル †

ピボット / アン・ピボット †

ピボット †

アン・ピボット †

分析例 †

基礎となる分析結果 †

ドリルダウン †

ドリルアップ †

ダイス †

スライス †

ドリルスルー †

参考 †

縦横変換 †

多次元データベースのオンライン分析処理（OLAP） †

Excel ピボット・テーブル、ピボット・グラフ †

クロス集計分析のバックアップ(No.9)