.NET 開発基盤部会 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。

目次

概要

最も基本的なデータ分析方法でビジネス インテリジェンス(BI)でも可能。

  • 集まったデータを年齢、性別、地域、職業などのさまざまな属性別に集計
    • 2、3種類程度の複数の属性についての相関関係を分析
    • 属性ごとに大まかなトレンドを把握
  • 以下で利用される。
    • アンケートの集計
    • 仕入れの計画
    • 販売予測
    • 世論調査

用語

クロス・テーブル

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

ルックアップ・テーブル

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

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

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

  • さらに、ココから、以下のような操作が可能。
  • データの分析

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

※ 次元(軸)を減らし、3次元 → 2次元データへ。

ドリルスルー

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

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

参考

  • 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 階層を下げた詳細データの表示について(ドリルダウン)

縦横変換

多次元データベースのオンライン分析処理(OLAP)

  • 上記のピボットと同じクロス集計を行うことが出来る。
  • ファクト・テーブルからクロス・テーブル(とグラフ)を生成
  • ファクト・テーブルは、スタース・キーマの構成要素。
  • スター・スキーマは、
    • 1つ以上のファクト・テーブルと、
    • 外部キーを通じて関連付けられた1つ以上のディメンション・テーブル

で構成される。

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


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2020-02-25 (火) 09:30:26 (42d)