Databricks SQL(Redash)でヒストグラムを描画する

現時点で、Databricks SQL(Redash)にはヒストグラムを描画するモードがありません。そこで、SQLでヒストグラムを描画する方法を見ていきます。

# サンプルデータ

サンプルのデータとして[こちらの記事](こちらの記事でも使ってい)でも使っている、かつ、Databricksのサンプルデータセットに含まれるDiamondsデータを使って、`price`カラムのヒストグラムを描く方法を具体的に見ていきます。

```SQL
SELECT * FROM diamonds limit 10;

(結果)
+---+-----+---------+-----+-------+-----+-----+-----+----+----+----+
|_c0|carat|      cut|color|clarity|depth|table|price|   x|   y|   z|
+---+-----+---------+-----+-------+-----+-----+-----+----+----+----+
|  1| 0.23|    Ideal|    E|    SI2| 61.5| 55.0|  326|3.95|3.98|2.43|
|  2| 0.21|  Premium|    E|    SI1| 59.8| 61.0|  326|3.89|3.84|2.31|
|  3| 0.23|     Good|    E|    VS1| 56.9| 65.0|  327|4.05|4.07|2.31|
|  4| 0.29|  Premium|    I|    VS2| 62.4| 58.0|  334| 4.2|4.23|2.63|
|  5| 0.31|     Good|    J|    SI2| 63.3| 58.0|  335|4.34|4.35|2.75|
|  6| 0.24|Very Good|    J|   VVS2| 62.8| 57.0|  336|3.94|3.96|2.48|
|  7| 0.24|Very Good|    I|   VVS1| 62.3| 57.0|  336|3.95|3.98|2.47|
|  8| 0.26|Very Good|    H|    SI1| 61.9| 55.0|  337|4.07|4.11|2.53|
|  9| 0.22|     Fair|    E|    VS2| 65.1| 61.0|  337|3.87|3.78|2.49|
| 10| 0.23|Very Good|    H|    VS1| 59.4| 61.0|  338| 4.0|4.05|2.39|
+---+-----+---------+-----+-------+-----+-----+-----+----+----+----+
```

# 結論

以下のクエリでヒストグラムが描画できます。

他のテーブルでこのクエリを使用する場合に、クエリの中で変更する部分は以下の通りです。

* `diamonds`
* `price`

```SQL
WITH const AS (
  SELECT
    round(
      3.49 * stddev(price) / pow(count(1), 0.33),
      0
    ) AS bin_width
  FROM
    diamonds
)
SELECT
  floor(d.price / const.bin_width) * const.bin_width AS price_range,
  count(1) AS count
FROM
  const,
  diamonds d
GROUP BY
  price_range
ORDER BY
  price_range

(結果)
+-----------+-----+
|price_range|count|
+-----------+-----+
|        0.0|  126|
|      382.0| 8618|
|      764.0| 8226|
|     1146.0| 3040|
|     1528.0| 3489|
|     1910.0| 2548|
|     2292.0| 2494|
|     2674.0| 2042|
|     3056.0| 1656|
|     3438.0| 1581|
|     3820.0| 1707|
|     4202.0| 1871|
|     4584.0| 1683|
|     4966.0| 1438|
|     5348.0| 1153|
|     5730.0| 1000|
|     6112.0|  922|
|     6494.0|  855|
|     6876.0|  688|
|     7258.0|  666|
+-----------+-----+
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/62653/f5e18a8b-304c-5d82-3fe7-346f14dc9bfa.png)


以下、上記の結論のクエリを組み立てていく過程・詳細を見ていきます。



# Bin幅を決めて、値を丸める

ヒストグラムは、ある値の範囲内にいくつのデータ数があるのかを示すグラフです。この値の幅をBin幅と言います。そして適切なBin幅は、そのデータの値の大きさと範囲によって変わってきます。

ここでは元データからの`price`の値からとりあえずBin幅`=100`くらいでやってみましょう。
(このあと、データのサンプル数と対象の値の分布から最適なBin幅を計算する方法を取り込みます。)

まずは、`price`の値をBin幅`100`ごとに丸めます。

```SQL
-- Bin幅で丸める(`price`カラムも理解のため出しておくが実質不要)
SELECT price, floor( price / 100 ) * 100 AS price_range FROM diamonds

(結果)
+-----+-----------+
|price|price_range|
+-----+-----------+
|  326|        300|
|  326|        300|
|  327|        300|
|  334|        300|
|  335|        300|
|  336|        300|
....
```

# 丸めた値ごとに集計する

`GROUP BY price_range`でそれぞれのカウントを集計する。

```SQL
SELECT 
  floor( price / 100 ) * 100 AS price_range,
  count(1) AS count
FROM diamonds
GROUP BY price_range
ORDER BY price_range

(結果)
+-----------+-----+
|price_range|count|
+-----------+-----+
|        300|  247|
|        400| 1482|
|        500| 2380|
|        600| 2828|
|        700| 2846|
|        800| 2544|
|        900| 2172|
|       1000| 1857|
...
```

# 試しにプロットする

このBin幅で描画をしてみます。適当に決めたBin幅ですが、結果的に良いものになったようです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/62653/1e7a06ca-35d9-5bcc-a455-66df8b6db076.png)

ただし、この方法だと、対象データを変更する度に、データの値を眺めて、ヒストグラムのBin幅を決める必要があり、手間がかかります。実は、Bin幅の決め方はある程度定式化されていますので、それを取り込んでみましょう。


# Bin幅を自動で決める

[Bin幅の決め方はさまざまな方法](https://ja.wikipedia.org/wiki/%E3%83%92%E3%82%B9%E3%83%88%E3%82%B0%E3%83%A9%E3%83%A0)がありますが、その中でも有名なものに「Scottの選択」というものがあります。今回はこれを使ってみます。

```math
Bin_{width} = \frac{3.49 \sigma}{\sqrt[3]{N}}
```

ここで、$\sigma$ はヒストグラムの対象データの標準偏差、$N$はデータ数になります。これをSQLで書くと以下のようになります。

```SQL
SELECT 3.49 * stddev(price) / pow(count(1), 0.33) AS bin_width
FROM diamonds

(結果)
+-----------------+
|        bin_width|
+-----------------+
|382.1261206268962|
+-----------------+
```

これでも良いのですが、ヒストグラム結果を評価・理解しやすくするために、Bin幅は整数の方が良いでしょう。丸めます。

```SQL
SELECT round( 3.49 * stddev(price) / pow(count(1), 0.33), 0) AS bin_width
FROM diamonds

(結果)
+---------+
|bin_width|
+---------+
|    382.0|
+---------+
```

以上で、全ての値、クエリが整いました。

# 一つのSQLにまとめる

最後に、今までのクエリをまとめます。`WITH`句を使って、Bin幅を算出しておき、それを後段のクエリで使います。

(既出、記事冒頭の「結論のクエリ」に同じ)

```SQL
WITH const AS (
  SELECT
    round(
      3.49 * stddev(price) / pow(count(1), 0.33),
      0
    ) AS bin_width
  FROM
    diamonds
)
SELECT
  floor(d.price / const.bin_width) * const.bin_width AS price_range,
  count(1) AS count
FROM
  const,
  diamonds d
GROUP BY
  price_range
ORDER BY
  price_range
```