LoginSignup
100
28

More than 5 years have passed since last update.

LivesenseAdvent Calendar 2016Day 2
@onunuin株式会社リブセンス

MySQLの標準出力で円グラフを描く

Last updated at Posted at 2016-11-28

みなさまこんにちは。新卒エンジニアのonunuです。
リブセンスアドベントカレンダーその1の2日目を担当いたします。よろしくお願いいたします。

ことの始まり。

上司「数字を出して終わりにしないでグラフにしたりするんだ。周りの人に理解してもらうには大事なことだよ」
ぼく「わかりました」

新卒エンジニアは試されていると思いました。

エンジニアならば黒い画面でグラフを作ってみせよ、

と言われた気がしたのです(言われてない)。
そしてできたのがこちらです。

+-------------------------------------------------------------------------------------+
| 東京・神奈川・大阪・愛知の人口の割合                                                |
+-------------------------------------------------------------------------------------+
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 東 東 東 東 東 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 東 東 東 東 東 東 東 東 東 　 　 　 　 　 　                      |
| 　 　 　 　 　 東 東 東 東 東 東 東 東 東 神 神 　 　 　 　 　                      |
| 　 　 　 　 東 東 東 東 東 東 東 東 東 神 神 神 神 　 　 　 　                      |
| 　 　 　 　 東 東 東 東 東 東 東 東 東 神 神 神 神 　 　 　 　                      |
| 　 　 　 東 東 東 東 東 東 東 東 東 神 神 神 神 神 神 　 　 　                      |
| 　 　 　 東 東 東 東 東 東 東 東 神 神 神 神 神 神 神 　 　 　                      |
| 　 　 　 愛 愛 愛 愛 愛 愛 愛 神 神 神 神 神 神 神 神 　 　 　                      |
| 　 　 　 愛 愛 愛 愛 愛 愛 愛 大 大 神 神 神 神 神 神 　 　 　                      |
| 　 　 　 愛 愛 愛 愛 愛 愛 愛 大 大 大 大 神 神 神 神 　 　 　                      |
| 　 　 　 　 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 神 　 　 　 　                      |
| 　 　 　 　 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 大 　 　 　 　                      |
| 　 　 　 　 　 愛 愛 愛 愛 大 大 大 大 大 大 大 　 　 　 　 　                      |
| 　 　 　 　 　 　 愛 愛 大 大 大 大 大 大 大 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 大 大 大 大 大 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
+-------------------------------------------------------------------------------------+
21 rows in set (0.01 sec)

以下作り方

今回の説明用に、都道府県名と人口を格納したテーブルを作りました。以下説明はこのテーブルを使って行っていきます。
テーブルの構造は以下のようになっています。

mysql> select * from prefecture_population limit 4;
+----+--------------+------------+
| id | name         | population |
+----+--------------+------------+
|  1 | 東京都       |   13515271 |
|  2 | 神奈川県     |    9126214 |
|  3 | 大阪府       |    8839469 |
|  4 | 愛知県       |    7483128 |
+----+--------------+------------+
4 rows in set (0.00 sec)

0. 準備

まず最初に、ユーザー変数の初期化とセッションにかかっている制限を解放します。

変数

あまり使用頻度は高くありませんがMySQLでも変数を使用可能です。
変数は@で始まる任意の文字列で設定できます。

variable.sql
# 変数の宣言
SET @var = 0;

# また、SQL文中での代入は := 演算子を使います
SELECT
    @var := @var + 1
FROM hoge
LIMIT 10
;

GROUP_CONCATの制限を解放する

今回使用するGROUP_CONCAT()関数は出力する文字の最大値が決まっていて、セッション毎に管理されています。
そのようなエラーが出てしまった場合は、一時的に制限の最大値をあげることでエラーを回避できます。

SET SESSION group_concat_max_len = 1000000;

1. (-1, -1) から (1,1)までの座標系を作る

MySQlにはgenerate_series()関数はないので、ユーザー変数を用いてインクリメントを自作します。二つの連番をつなぎ合わせ、0.1刻みの座標を作ることができます。
また、この時、各座標におけるarctangentを求めておきます。
arctangentとは、tangentの逆演算です。つまりx,yの値から
角度を求めることができます。
MySQLではATAN()関数で求めることができます。
ATAN()関数で求められる値域は、[-π, π]であるため、次のステップの計算が楽になるよう、各値にπを加算して[0, 2π]に計算しなおしておきます。

geometory.sql
SET @n = -11;
SET @m = -11;

SELECT
  x.axis AS x_axis,
  y.axis AS y_axis,
  ATAN(y.axis, x.axis) + PI() AS atan
FROM (
  SELECT
    (@n := @n + 1) / 10 AS axis
  FROM prefecture_population
  LIMIT 21
  ) AS x, (
  SELECT
    (@m := @m + 1) / 10 AS axis
  FROM prefecture_population
  LIMIT 21
  ) AS y
ORDER BY x_axis, y_axis
;

こんな状態になります。

+---------+---------+---------------------+
| x_axis  | y_axis  | atan                |
+---------+---------+---------------------+
| -1.0000 | -1.0000 |  0.7853981633974483 |
| -1.0000 | -0.9000 |  0.7328151017865063 |
| -1.0000 | -0.8000 |  0.6747409422235524 |
| -1.0000 | -0.7000 |  0.6107259643892085 |
| -1.0000 | -0.6000 |   0.540419500270584 |
| -1.0000 | -0.5000 | 0.46364760900080615 |

...

2. 円グラフにしたいデータを取得してくる

各グループの値を取得してきます。
この時、各グループの累積の和を、最大2πとして弧度法に変換します。
円周率はPI()関数で出せます。

ammount_sum_radian.sql
SET @sum_ammount = 0;
SELECT
  name,
  population,
  (@sum_ammount := @sum_ammount + population)/(SELECT SUM(population) FROM (SELECT population FROM prefecture_population ORDER BY population DESC LIMIT 4) AS tmp) * 2 * PI() AS ammount_sum_radian
FROM
  prefecture_population
ORDER BY population DESC
LIMIT 4
;

この時点での実行結果はこんな感じ。

+--------------+------------+--------------------+
| name         | population | ammount_sum_radian |
+--------------+------------+--------------------+
| 東京都       |   13515271 |           2.179416 |
| 神奈川県     |    9126214 |           3.651071 |
| 大阪府       |    8839469 |           5.076487 |
| 愛知県       |    7483128 |           6.283185 |
+--------------+------------+--------------------+
4 rows in set (0.00 sec)

末尾の行のammount_sum_radianが2πにほぼ等しくなっています。

3. 1で作った座標に、2で作った角度を基にして値を割り当てる

1で作った座標に紐づくtangentの値が、2で作ったammount_sum_radianのどのグループに属するかによって値を割り当てていきます。
Kobito.AynWUi.png

上の画像を使って説明します。
手順1では、arctangentの演算処理によって、各座標での角度を算出しました。
手順2では、各グループの全体に占める比率を角度に変換することができました。
一例をとって考えると、画像中の座標(x,y)とその角θは、θg1 < θ < θg2 を満たしています。
このとき、座標(x,y)の所属するグループはg2であることがわかります。
同様のグループ分けを、各座標において行います。
まずは一時的に角度のみをMIN()関数で取り出し、その後改めてSELECTします。

SET @n = -11;
SET @m = -11;
SET @sum_ammount = 0;
SET @sum_ammount2 = 0;

SELECT
  key_map.x_axis,
  key_map.y_axis,
  group_values2.name
FROM (
  SELECT
    x_axis,
    y_axis,
    MIN(ammount_sum_radian) AS group_key
  FROM
    (
      SELECT
        name,
        population,
        (@sum_ammount := @sum_ammount + population)/(SELECT SUM(population) FROM (SELECT population FROM prefecture_population ORDER BY population DESC LIMIT 4) AS tmp) * 2 * PI() AS ammount_sum_radian
      FROM
        prefecture_population
      ORDER BY population DESC
      LIMIT 4
    ) AS group_values,
    (
      SELECT
        x.axis AS x_axis,
        y.axis AS y_axis,
        ATAN(y.axis, x.axis) + PI() AS atan
      FROM (
        SELECT
          (@n := @n + 1) / 10 AS axis
        FROM prefecture_population
        LIMIT 21
        ) AS x, (
        SELECT
          (@m := @m + 1) / 10 AS axis
        FROM prefecture_population
        LIMIT 21
        ) AS y
      ORDER BY x_axis, y_axis
    ) AS geometry
  WHERE geometry.atan <= group_values.ammount_sum_radian
  GROUP BY x_axis, y_axis
) AS key_map,
(
  SELECT
    name,
    population,
    (@sum_ammount2 := @sum_ammount2 + population)/(SELECT SUM(population) FROM (SELECT population FROM prefecture_population ORDER BY population DESC LIMIT 4) AS tmp) * 2 * PI() AS ammount_sum_radian
  FROM
    prefecture_population
  ORDER BY population DESC
  LIMIT 4
) AS group_values2
WHERE key_map.group_key = group_values2.ammount_sum_radian
ORDER BY x_axis, y_axis
;

この時点で、以下のような表になりました。

+---------+---------+--------------+
| x_axis  | y_axis  | name         |
+---------+---------+--------------+
| -1.0000 | -1.0000 | 東京都       |
| -1.0000 | -0.9000 | 東京都       |
| -1.0000 | -0.8000 | 東京都       |
| -1.0000 | -0.7000 | 東京都       |
| -1.0000 | -0.6000 | 東京都       |
| -1.0000 | -0.5000 | 東京都       |
| -1.0000 | -0.4000 | 東京都       |
| -1.0000 | -0.3000 | 東京都       |
| -1.0000 | -0.2000 | 東京都       |
| -1.0000 | -0.1000 | 東京都       |
| -1.0000 |  0.0000 | 愛知県       |
...

nameカラムを1文字に切り落として、座標上に並べると、すでにそれっぽくなっています。

+-------------------------------------------------------------------------------------+
| pie_chart                                                                           |
+-------------------------------------------------------------------------------------+
| 東 東 東 東 東 東 東 東 東 東 東 東 東 東 東 東 東 神 神 神 神                      |
| 東 東 東 東 東 東 東 東 東 東 東 東 東 東 東 東 東 神 神 神 神                      |
| 東 東 東 東 東 東 東 東 東 東 東 東 東 東 東 東 神 神 神 神 神                      |
| 東 東 東 東 東 東 東 東 東 東 東 東 東 東 東 神 神 神 神 神 神                      |
| 東 東 東 東 東 東 東 東 東 東 東 東 東 東 東 神 神 神 神 神 神                      |
| 東 東 東 東 東 東 東 東 東 東 東 東 東 東 神 神 神 神 神 神 神                      |
| 東 東 東 東 東 東 東 東 東 東 東 東 東 神 神 神 神 神 神 神 神                      |
| 東 東 東 東 東 東 東 東 東 東 東 東 東 神 神 神 神 神 神 神 神                      |
| 東 東 東 東 東 東 東 東 東 東 東 東 神 神 神 神 神 神 神 神 神                      |
| 東 東 東 東 東 東 東 東 東 東 東 神 神 神 神 神 神 神 神 神 神                      |
| 愛 愛 愛 愛 愛 愛 愛 愛 愛 愛 神 神 神 神 神 神 神 神 神 神 神                      |
| 愛 愛 愛 愛 愛 愛 愛 愛 愛 愛 大 大 神 神 神 神 神 神 神 神 神                      |
| 愛 愛 愛 愛 愛 愛 愛 愛 愛 愛 大 大 大 大 神 神 神 神 神 神 神                      |
| 愛 愛 愛 愛 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 神 神 神 神 神                      |
| 愛 愛 愛 愛 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 大 大 神 神 神                      |
| 愛 愛 愛 愛 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 大 大 大 神 神                      |
| 愛 愛 愛 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 大 大 大 大 大 大                      |
| 愛 愛 愛 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 大 大 大 大 大 大                      |
| 愛 愛 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 大 大 大 大 大 大 大                      |
| 愛 愛 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 大 大 大 大 大 大 大                      |
| 愛 愛 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 大 大 大 大 大 大 大                      |
+-------------------------------------------------------------------------------------+
21 rows in set (0.01 sec)

4. 円グラフにする

円グラフにするためには、座標の原点からの距離を算出して一定距離以上の距離の場合にスペースとかに入れ替えればOKです。
原点からの距離は、x,yの値から三平方の定理を用いて簡単に計算可能です。
今回は、原点からの距離が0.75よりも大きい時に全角スペースを挟むことにします。
最終的なクエリは以下のようになりました。

comp.sql
SET @n = -11;
SET @m = -11;
SET @sum_ammount = 0;
SET @sum_ammount2 = 0;

SELECT
  GROUP_CONCAT(
    CONCAT(population_tmp.value)
    ORDER BY x_axis
    SEPARATOR ' '
  ) AS pie_chart
FROM (
  SELECT
    key_map.x_axis,
    key_map.y_axis,
    CASE
      WHEN SQRT(POW(key_map.x_axis, 2) + POW(key_map.y_axis, 2)) > 0.75
        THEN '　'
      ELSE SUBSTRING(group_values2.name, 1, 1)
    END AS value
  FROM (
    SELECT
      x_axis,
      y_axis,
      MIN(ammount_sum_radian) AS group_key
    FROM
      (
        SELECT
          name,
          population,
          (@sum_ammount := @sum_ammount + population)/(SELECT SUM(population) FROM (SELECT population FROM prefecture_population ORDER BY population DESC LIMIT 4) AS tmp) * 2 * PI() AS ammount_sum_radian
        FROM
          prefecture_population
        ORDER BY population DESC
        LIMIT 4
      ) AS group_values,
      (
        SELECT
          x.axis AS x_axis,
          y.axis AS y_axis,
          ATAN(y.axis, x.axis) + PI() AS atan
        FROM (
          SELECT
            (@n := @n + 1) / 10 AS axis
          FROM prefecture_population
          LIMIT 21
          ) AS x, (
          SELECT
            (@m := @m + 1) / 10 AS axis
          FROM prefecture_population
          LIMIT 21
          ) AS y
        ORDER BY x_axis, y_axis
      ) AS geometry
    WHERE geometry.atan <= group_values.ammount_sum_radian
    GROUP BY x_axis, y_axis
  ) AS key_map,
  (
    SELECT
      name,
      population,
      (@sum_ammount2 := @sum_ammount2 + population)/(SELECT SUM(population) FROM (SELECT population FROM prefecture_population ORDER BY population DESC LIMIT 4) AS tmp) * 2 * PI() AS ammount_sum_radian
    FROM
      prefecture_population
    ORDER BY population DESC
    LIMIT 4
  ) AS group_values2
  WHERE key_map.group_key = group_values2.ammount_sum_radian
  ORDER BY x_axis, y_axis
) AS population_tmp,
(SELECT @sel_size := 10) AS sel_size
GROUP BY y_axis
;

そして実行結果は・・・・

+-------------------------------------------------------------------------------------+
| pie_chart                                                                           |
+-------------------------------------------------------------------------------------+
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 東 東 東 東 東 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 東 東 東 東 東 東 東 東 東 　 　 　 　 　 　                      |
| 　 　 　 　 　 東 東 東 東 東 東 東 東 東 神 神 　 　 　 　 　                      |
| 　 　 　 　 東 東 東 東 東 東 東 東 東 神 神 神 神 　 　 　 　                      |
| 　 　 　 　 東 東 東 東 東 東 東 東 東 神 神 神 神 　 　 　 　                      |
| 　 　 　 東 東 東 東 東 東 東 東 東 神 神 神 神 神 神 　 　 　                      |
| 　 　 　 東 東 東 東 東 東 東 東 神 神 神 神 神 神 神 　 　 　                      |
| 　 　 　 愛 愛 愛 愛 愛 愛 愛 神 神 神 神 神 神 神 神 　 　 　                      |
| 　 　 　 愛 愛 愛 愛 愛 愛 愛 大 大 神 神 神 神 神 神 　 　 　                      |
| 　 　 　 愛 愛 愛 愛 愛 愛 愛 大 大 大 大 神 神 神 神 　 　 　                      |
| 　 　 　 　 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 神 　 　 　 　                      |
| 　 　 　 　 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 大 　 　 　 　                      |
| 　 　 　 　 　 愛 愛 愛 愛 大 大 大 大 大 大 大 　 　 　 　 　                      |
| 　 　 　 　 　 　 愛 愛 大 大 大 大 大 大 大 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 大 大 大 大 大 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
+-------------------------------------------------------------------------------------+
21 rows in set (0.01 sec)

超キレイ！！！！！
ふつくしい・・・・

これで黒い画面を使いこなすいっぱしのエンジニアになれたと思います。

まとめ

  • MySQLはwith句がないので大変
  • generate_seriesもないから大変

SQLだけで円グラフを作るのはとても大変なので、適切にExcelとか使ったほうが良いです。

おまけ

ちなみに先ほどの原点からの距離を算出する過程で、以下のような分岐を挟むことでドーナツ型の円グラフにすることも可能です。

CASE
  WHEN SQRT(POW(key_map.x_axis, 2) + POW(key_map.y_axis, 2)) > 0.75
    THEN '　'
  WHEN SQRT(POW(key_map.x_axis, 2) + POW(key_map.y_axis, 2)) < 0.25
    THEN '　'
  ELSE SUBSTRING(group_values2.name, 1, 1)
END AS value

実行結果はこちら。

+-------------------------------------------------------------------------------------+
| pie_chart                                                                           |
+-------------------------------------------------------------------------------------+
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 東 東 東 東 東 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 東 東 東 東 東 東 東 東 東 　 　 　 　 　 　                      |
| 　 　 　 　 　 東 東 東 東 東 東 東 東 東 神 神 　 　 　 　 　                      |
| 　 　 　 　 東 東 東 東 東 東 東 東 東 神 神 神 神 　 　 　 　                      |
| 　 　 　 　 東 東 東 東 東 東 東 東 東 神 神 神 神 　 　 　 　                      |
| 　 　 　 東 東 東 東 東 東 　 　 　 神 神 神 神 神 神 　 　 　                      |
| 　 　 　 東 東 東 東 東 　 　 　 　 　 神 神 神 神 神 　 　 　                      |
| 　 　 　 愛 愛 愛 愛 愛 　 　 　 　 　 神 神 神 神 神 　 　 　                      |
| 　 　 　 愛 愛 愛 愛 愛 　 　 　 　 　 神 神 神 神 神 　 　 　                      |
| 　 　 　 愛 愛 愛 愛 愛 愛 　 　 　 大 大 神 神 神 神 　 　 　                      |
| 　 　 　 　 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 神 　 　 　 　                      |
| 　 　 　 　 愛 愛 愛 愛 愛 大 大 大 大 大 大 大 大 　 　 　 　                      |
| 　 　 　 　 　 愛 愛 愛 愛 大 大 大 大 大 大 大 　 　 　 　 　                      |
| 　 　 　 　 　 　 愛 愛 大 大 大 大 大 大 大 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 大 大 大 大 大 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
| 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　                      |
+-------------------------------------------------------------------------------------+
21 rows in set (0.01 sec)

参考

http://code.openark.org/blog/mysql/sql-pie-chart
こちらの記事を参考に、ATAN()関数などを用いて簡略化、最適化(のつもり)を行いました。
あと全角文字対応とか。
クエリは全て自分で書いたものです。
とても勉強になりました。

100
28
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
100
28