tweeeetyのぶろぐ的めも

アウトプットが少なかったダメな自分をアウトプット<br>＼(＾o＾)／

PivotTable.jsを使ってjavascriptで集計を簡単に表示するテスト(jqueryでピボットテーブル)

javascript jquery

はじめに

アプリのデータの集計系画面ってもっと楽にできないかなーと思いますよね。

集計結果を画面に出したいにしてもその過程は

1.バッチで集計したものをDBに入れてそのままだす
2.半分集計したデータをDBに入れてサーバサイドでゴリゴリやって画面に出す
3.結構そのまま渡してsmartyなんかのテンプレートエンジンでゴリゴリやる
4.結構そのまま渡してjavascriptでゴリゴリやる

とかとかいろいろあるかと思います。

今回は4のパターンで
javascriptにお任せしてバッチもサーバサイドも手を抜いて簡単にやっちゃおう
っていう導入事例をメモしておきます。

今回使うのはPivotTable.jsというjqueryプラグインです。
詳細はこちらもどーぞ
→https://github.com/nicolaskruchten/pivottable

補足

こんなのも書いたので合わせてどうぞ
javascriptでjsonで受け取ったmysqlとかのDBデータからテーブルタグ(table)を生成するライブラリ＆そのjqueryプラグインを作ってみたテスト-tableMagic

まず最初に簡単なデータ例と表示例

こんなデータだったらっていう例を載せておきます
※idはitem_id、nameはitemの名前と思ってください。

DBの明細テーブルがこんな感じだったとして
date id kind price sales_num total_sales name
2014-01-16 1 food 150 1 150 ドーナツ
2014-01-16 2 drink 300 4 1200 コーヒー
2014-01-16 3 drink 120 2 240 コーラ
2014-01-16 5 dessert 260 3 780 アイス
2014-01-16 1 food 150 5 750 ドーナツ
2014-01-16 2 drink 300 1 300 コーヒー
2014-01-16 4 food 400 2 800 サンド的な
2014-01-16 2 drink 300 2 600 コーヒー
2014-01-16 4 food 400 5 2000 サンド的な
2014-01-16 5 dessert 260 2 520 アイス
2014-01-17 2 drink 300 4 1200 コーヒー
2014-01-17 2 drink 300 3 900 コーヒー
2014-01-17 1 food 150 2 300 ドーナツ
2014-01-17 2 drink 300 4 1200 コーヒー
2014-01-17 4 food 400 2 800 サンド的な
2014-01-17 2 drink 300 2 600 コーヒー
2014-01-17 3 drink 120 4 480 コーラ
2014-01-17 4 food 400 1 400 サンド的な
2014-01-17 4 food 400 2 800 サンド的な
2014-01-17 4 food 400 1 400 サンド的な
2014-01-17 6 dessert 800 3 2400 高いアイス
2014-01-17 5 dessert 260 2 520 アイス
表示結果(結果のindex.html)


DBの明細テーブルでいうと2014-01-16のidが1(ドーナツ)について、1行目のデータと5行目のデータがありますが
pivotに渡しただけでこんな感じで集計して表示してくれます。

サンプルの用意

今回のテストで使うソースの構成
C:.
│  index.html
│
├─css
│      pivot.css
│
└─js
        jquery-1.8.3.min.js
        jquery-ui-1.9.2.custom.min.js
        pivot.js
        sample_data.js
ダウンロード

まず、上記のファイルをダウンロードします。
index.htmlとsample_data.jsは自分で作るファイルです。

・pivot.css
・jquery-1.8.3.min.js
・jquery-ui-1.9.2.custom.min.js
・pivot.js
についてはこちらからダウンロードします。

使ってみる

ソースはこんな感じにしました

sample_data.js

こんかいはサンプルなのでベタ書きですが、ajaxなんかで取ってきたイメージです

var sampleData = [
  {'date':'2014-01-16','id':'1','kind':'food','price':'150','sales_num':'1','total_sales':'150','name':'ドーナツ'},
  {'date':'2014-01-16','id':'2','kind':'drink','price':'300','sales_num':'4','total_sales':'1200','name':'コーヒー'},
  {'date':'2014-01-16','id':'3','kind':'drink','price':'120','sales_num':'2','total_sales':'240','name':'コーラ'},
  {'date':'2014-01-16','id':'5','kind':'dessert','price':'260','sales_num':'3','total_sales':'780','name':'アイス'},
  {'date':'2014-01-16','id':'1','kind':'food','price':'150','sales_num':'5','total_sales':'750','name':'ドーナツ'},
  {'date':'2014-01-16','id':'2','kind':'drink','price':'300','sales_num':'1','total_sales':'300','name':'コーヒー'},
  {'date':'2014-01-16','id':'4','kind':'food','price':'400','sales_num':'2','total_sales':'800','name':'サンド的な'},
  {'date':'2014-01-16','id':'2','kind':'drink','price':'300','sales_num':'2','total_sales':'600','name':'コーヒー'},
  {'date':'2014-01-16','id':'4','kind':'food','price':'400','sales_num':'5','total_sales':'2000','name':'サンド的な'},
  {'date':'2014-01-16','id':'5','kind':'dessert','price':'260','sales_num':'2','total_sales':'520','name':'アイス'},
  {'date':'2014-01-17','id':'2','kind':'drink','price':'300','sales_num':'4','total_sales':'1200','name':'コーヒー'},
  {'date':'2014-01-17','id':'2','kind':'drink','price':'300','sales_num':'3','total_sales':'900','name':'コーヒー'},
  {'date':'2014-01-17','id':'1','kind':'food','price':'150','sales_num':'2','total_sales':'300','name':'ドーナツ'},
  {'date':'2014-01-17','id':'2','kind':'drink','price':'300','sales_num':'4','total_sales':'1200','name':'コーヒー'},
  {'date':'2014-01-17','id':'4','kind':'food','price':'400','sales_num':'2','total_sales':'800','name':'サンド的な'},
  {'date':'2014-01-17','id':'2','kind':'drink','price':'300','sales_num':'2','total_sales':'600','name':'コーヒー'},
  {'date':'2014-01-17','id':'3','kind':'drink','price':'120','sales_num':'4','total_sales':'480','name':'コーラ'},
  {'date':'2014-01-17','id':'4','kind':'food','price':'400','sales_num':'1','total_sales':'400','name':'サンド的な'},
  {'date':'2014-01-17','id':'4','kind':'food','price':'400','sales_num':'2','total_sales':'800','name':'サンド的な'},
  {'date':'2014-01-17','id':'4','kind':'food','price':'400','sales_num':'1','total_sales':'400','name':'サンド的な'},
  {'date':'2014-01-17','id':'6','kind':'dessert','price':'800','sales_num':'3','total_sales':'2400','name':'高いアイス'},
  {'date':'2014-01-17','id':'5','kind':'dessert','price':'260','sales_num':'2','total_sales':'520','name':'アイス'},

];
index.html

たいしたソースでもないのでhtml内に直接scriptタグで書きました。

<!doctype html>
<html lang="ja">
<head>
  <meta charset="UTF-8">
  <title>pivotテスト</title>
  <link rel="stylesheet" type="text/css" href="css/pivot.css">
  <script src="js/jquery-1.8.3.min.js"></script>
  <script src="js/jquery-ui-1.9.2.custom.min.js"></script>
  <script src="js/pivot.js"></script>
  <script src="js/sample_data.js"></script>
</head>
<body>
<script type="text/javascript">
  $(function(){
    $("#output").pivotUI(sampleData,
      {
        rows: ["date"],
        cols: ["id", "name"],
        vals: ['total_sales'],
        aggregatorName: 'intSum',
        rendererName: 'table'
      },
      false
    );
  });
</script>
<div id="output"></div>
</body></html>
表示結果(結果のindex.html)


解説

GitHubのreadmeから読めば書いてありますが、補足です
pivot.jsですが基本的な使い方は下記の2通りあります

1.pivot(input [,options])
2.pivotUI(input [,options [,overwrite]])

1.pivotだとあまり対したことはできないので今回は2.pivotUIについて説明します。

pivotUIの引数とか
引数 引数名 中身とか 説明
第1引数 input jsonデータ
第2引数 options 行列やどのkeyで集計するかとかどーやって集計するかとかとか
rows 行に指定するjsonのkey
cols 列に指定するjsonのkey
vals どのjsonのkeyについて集計するか
aggregatorName valsで指定した方法をどうやって集計するか※1
rendererName テーブルの表示方法を指定※2
derivedAttributes 自前の処理結果のカラムを追加※4
第3引数 overwrite データのoverwrite※3

他にもoptionsに指定できる項目はありますが
詳しい説明はこちら→https://github.com/nicolaskruchten/pivottable/wiki/Parameters
※1〜4については↓で補足します

※1…aggregatorNameについて

集計方法についてです。
あらかじめ用意されたものが使えます。最初から用意されているのはこんな感じです。
使ってみたサンプルについては後で後述します

指定する文字列 説明
count レコードの数をカウント
countUnique valsで指定した値の一意なものをカウント
listUnique valsで指定した値の一意なものをカンマで区切ったリストで表示
sum valsで指定した値の合計
intSum valsで指定した値の合計の小数点無し
average valsで指定した値の平均
sumOverSum ある値xの和をある値yで割った値
ub80(lb80) よくわかりませんでした(汗)
sumAsFractionOfTotal Total(行列の)を100%としたときの合計構成比率
sumAsFractionOfRow Total(行の)を100%としたときの合計構成比率
sumAsFractionOfCol Total(列の)を100%としたときの合計構成比率
countAsFractionOfTotal Total(行列の)を100%としたときのカウント構成比率
countAsFractionOfRowl Total(行の)を100%としたときのカウント構成比率
countAsFractionOfRow Total(列の)を100%としたときのカウント構成比率

※自前の集計関数を定義することもできるようです
本家の説明はこちら→https://github.com/nicolaskruchten/pivottable/wiki/Aggregators

※2…rendererNameについて

テーブルの描画のタイプを選べます。
こちらもあらかじめ用意されているのはこんな感じです。
使ってみたサンプルについては後で後述します

指定する文字列 説明
Table ノーマルのテーブルで表示
Table Barchart バーチャート付きで表示
Heatmap 行列のヒートマップで表示
Row Heatmap 行のヒートマップで表示
Col Heatmap 列のヒートマップで表示

※こちらも自前で定義することもできるようです
本家の説明はこちら→https://github.com/nicolaskruchten/pivottable/wiki/Renderers

※3…overwriteについて

データを上書き(リフレッシュ)するかどうかのフラグです。

これはわかりにくかったんですが、
一度pivotUIを使ってテーブルを描画したとします。
その後、ブラウザの再読み込みなしにajaxなどで今表示している内容とは別の条件でデータを取得したとします
(最初とは内容が違うデータ)
pivotUIは一度読み込んだデータを内部でキャッシュしているのでこのフラグをtrueにして再度描画しないと内容が変わりません。

aggregatorNameサンプルいくつか

"aggregatorName":"count"で表示してみる

単純に行に指定したもの、列にしていしたものに該当するレコードをカウントします。


"aggregatorName":"sumAsFractionOfTotal"で表示してみる

行、列のtotalを100%としたときの構成比率を%で表示します。


"aggregatorName":"sumOverSum"で表示してみる

xの総和/yの総和なんですが、わかりにくいのでテストデータをちょっと変えてみます。
sumOverSumはvalsの指定にxとyが必要なためこんな値にしてみました

x … sales
y … mean(新しいカラム)

期待値meanというカラムをレコードに追加してみます。
今回、期待値の値は適当で、必ず2個は売れて欲しいという期待を設定したということでmeanにはprice*2を設定しました。

  • meanを追加したjson
{'date':'2014-01-16','id':'1','kind':'food','price':'150','sales_num':'1','total_sales':'150','mean':'300','name':'ドーナツ'},
{'date':'2014-01-16','id':'2','kind':'drink','price':'300','sales_num':'4','total_sales':'1200','mean':'600','name':'コーヒー'},
{'date':'2014-01-16','id':'3','kind':'drink','price':'120','sales_num':'2','total_sales':'240','mean':'240','name':'コーラ'},
{'date':'2014-01-16','id':'5','kind':'dessert','price':'260','sales_num':'3','total_sales':'780','mean':'520','name':'アイス'},
{'date':'2014-01-16','id':'1','kind':'food','price':'150','sales_num':'5','total_sales':'750','mean':'300','name':'ドーナツ'},
{'date':'2014-01-16','id':'2','kind':'drink','price':'300','sales_num':'1','total_sales':'300','mean':'600','name':'コーヒー'},
{'date':'2014-01-16','id':'4','kind':'food','price':'400','sales_num':'2','total_sales':'800','mean':'800','name':'サンド的な'},
{'date':'2014-01-16','id':'2','kind':'drink','price':'300','sales_num':'2','total_sales':'600','mean':'600','name':'コーヒー'},
{'date':'2014-01-16','id':'4','kind':'food','price':'400','sales_num':'5','total_sales':'2000','mean':'800','name':'サンド的な'},
{'date':'2014-01-16','id':'5','kind':'dessert','price':'260','sales_num':'2','total_sales':'520','mean':'520','name':'アイス'},
{'date':'2014-01-17','id':'2','kind':'drink','price':'300','sales_num':'4','total_sales':'1200','mean':'600','name':'コーヒー'},
{'date':'2014-01-17','id':'2','kind':'drink','price':'300','sales_num':'3','total_sales':'900','mean':'600','name':'コーヒー'},
{'date':'2014-01-17','id':'1','kind':'food','price':'150','sales_num':'2','total_sales':'300','mean':'300','name':'ドーナツ'},
{'date':'2014-01-17','id':'2','kind':'drink','price':'300','sales_num':'4','total_sales':'1200','mean':'600','name':'コーヒー'},
{'date':'2014-01-17','id':'4','kind':'food','price':'400','sales_num':'2','total_sales':'800','mean':'800','name':'サンド的な'},
{'date':'2014-01-17','id':'2','kind':'drink','price':'300','sales_num':'2','total_sales':'600','mean':'600','name':'コーヒー'},
{'date':'2014-01-17','id':'3','kind':'drink','price':'120','sales_num':'4','total_sales':'480','mean':'240','name':'コーラ'},
{'date':'2014-01-17','id':'4','kind':'food','price':'400','sales_num':'1','total_sales':'400','mean':'800','name':'サンド的な'},
{'date':'2014-01-17','id':'4','kind':'food','price':'400','sales_num':'2','total_sales':'800','mean':'800','name':'サンド的な'},
{'date':'2014-01-17','id':'4','kind':'food','price':'400','sales_num':'1','total_sales':'400','mean':'800','name':'サンド的な'},
{'date':'2014-01-17','id':'6','kind':'dessert','price':'800','sales_num':'3','total_sales':'2400','mean':'1600','name':'高いアイス'},
{'date':'2014-01-17','id':'5','kind':'dessert','price':'260','sales_num':'2','total_sales':'520','mean':'520','name':'アイス'},
  • pivotUIの引数を変更

index.htmlのpivotUI部分

    $("#output").pivotUI(sampleData,
      {
        rows: ["date"],
        cols: ["id", "name"],
        vals: ['total_sales', 'mean'],
        aggregatorName: 'sumOverSum',
        rendererName: 'table'
      },
      false
    );

で、こんな感じ

2014-01-16のドーナツに絞ってみると
total_salesの総和 / meanの総和
=(150+750)/(300+300)
=1.5


rendererNameサンプルいくつか

"rendererName":"Table Barchart"で表示してみる

バーチャートでちょっと視覚的になります

"rendererName":Heatmap"で表示してみる

行列で高い値から色づけされるので視覚的になります

※4のderivedAttributesについて

derivedAttributesは「自前の処理結果のカラムを追加」と書きましたが
説明を書くのが面倒になってきたのと見たほうが早いと思うのでソース書きます。

こんなデータをjsonで受け取ったとします
・jsonデータに売れたアイテムの単価と個数しか入ってない
・アイテムの名前も入ってない(アイテムのマスタは別に受け取っている)

こんなときに使えるパラメータです。ではさっそく

jsonサンプル
var sampleData = [
  {'date':'2014-01-16','id':'1','kind':'food','price':'150','sales_num':'1'},
  {'date':'2014-01-16','id':'2','kind':'drink','price':'300','sales_num':'4'},
  {'date':'2014-01-16','id':'3','kind':'drink','price':'120','sales_num':'2'},
  {'date':'2014-01-16','id':'5','kind':'dessert','price':'260','sales_num':'3'},
  {'date':'2014-01-16','id':'1','kind':'food','price':'150','sales_num':'5'},
  {'date':'2014-01-16','id':'2','kind':'drink','price':'300','sales_num':'1'},
  {'date':'2014-01-16','id':'4','kind':'food','price':'400','sales_num':'2'},
  {'date':'2014-01-16','id':'2','kind':'drink','price':'300','sales_num':'2'},
  {'date':'2014-01-16','id':'4','kind':'food','price':'400','sales_num':'5'},
  {'date':'2014-01-16','id':'5','kind':'dessert','price':'260','sales_num':'2'},
  {'date':'2014-01-17','id':'2','kind':'drink','price':'300','sales_num':'4'},
  {'date':'2014-01-17','id':'2','kind':'drink','price':'300','sales_num':'3'},
  {'date':'2014-01-17','id':'1','kind':'food','price':'150','sales_num':'2'},
  {'date':'2014-01-17','id':'2','kind':'drink','price':'300','sales_num':'4'},
  {'date':'2014-01-17','id':'4','kind':'food','price':'400','sales_num':'2'},
  {'date':'2014-01-17','id':'2','kind':'drink','price':'300','sales_num':'2'},
  {'date':'2014-01-17','id':'3','kind':'drink','price':'120','sales_num':'4'},
  {'date':'2014-01-17','id':'4','kind':'food','price':'400','sales_num':'1'},
  {'date':'2014-01-17','id':'4','kind':'food','price':'400','sales_num':'2'},
  {'date':'2014-01-17','id':'4','kind':'food','price':'400','sales_num':'1'},
  {'date':'2014-01-17','id':'6','kind':'dessert','price':'800','sales_num':'3'},
  {'date':'2014-01-17','id':'5','kind':'dessert','price':'260','sales_num':'2'},
];
var sampleItem = {
  1 : 'ドーナツ',
  2 : 'コーヒー',
  3 : 'コーラ',
  4 : 'サンド的な',
  5 : 'アイス',
  6 : '高いアイス'
}
index.htmlのスクリプト部分
  $(function(){
    $("#output").pivotUI(sampleData,
      {
        rows: ["date"],
        cols: ["id", "my_item_name"],
        vals: ['my_total_sales'],
        aggregatorName: 'intSum',
        rendererName: 'table',
        derivedAttributes: {
          my_total_sales: function(record){
            return record.price * record.sales_num;
          },
          my_item_name: function(record){
            return sampleItem[record.id];
          }
        }
      }
      ,true
    );
  });
結果


単価と個数から売り上げを算出し、アイテムのidとアイテムのマスタから名前を追加しています！

まとめ

ってことで、ざざっと書いて雑な感じになってしまいましたが、
集計処理を任せちゃっても良いので楽ですね！
いろいろできそう＾□＾

補足

上述しましたが簡単な表が出したい場合はこちらも。
javascriptでjsonで受け取ったmysqlとかのDBデータからテーブルタグ(table)を生成するライブラリ＆そのjqueryプラグインを作ってみたテスト-tableMagic