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

More than 1 year has passed since last update.

みなさまこんにちは。新卒エンジニアの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()関数などを用いて簡略化、最適化(のつもり)を行いました。

あと全角文字対応とか。

クエリは全て自分で書いたものです。

とても勉強になりました。