昨日の続きで、度数を文字列の長さで表して簡易なヒストグラム(横向き)にするストアドを作りました。度数分布は昨日のストアドで求めます。用いたPostgreSQLのバージョンは昨日と同様9.5.2。
create or replace function numarray_hst (
    vals numeric[],
    num_levels int,
    max_length int default null,  -- maximum length of horizontal bar
    mark char(1) default null)    -- character of bar
returns table (
    level int, range numrange, frequency int, histogram text)
language sql immutable as
$$
with your_data (v) as (
    -- select array_agg(random()) from generate_series(1, 10) -- ex 
    select $1
), your_settings (num_levels, max_length, mark) as (
    -- select 7, 10, text '*' -- ex
    select $2, coalesce($3, 10), coalesce($4, '*')
)
select level, range, frequency,
    repeat(mark, round(frequency :: numeric / max(frequency) over()
        * max_length) :: int)
from your_data, 
    your_settings,
    numarray_frq(v :: numeric[], num_levels)
order by level;
$$;


↓ 使い方の例1。対象のデータ(配列)と区間数だけ渡すと、デフォルトでアスタリスク10個を最大として横ヒストグラムにします。
-- usage example 1
\pset border 2
select rec.* from (
    -- put your data as array
    select array_agg(random()) as ary
    from generate_series(1, 20)
) as x, numarray_hst(ary :: numeric[], 10) as rec;
+-------+-----------------------------------------+-----------+------------+
| level |                  range                  | frequency | histogram  |
+-------+-----------------------------------------+-----------+------------+
|     1 | [0.00781922275200486,0.104482951154932) |         1 | ***        |
|     2 | [0.104482951154932,0.20114667955786)    |         2 | *****      |
|     3 | [0.20114667955786,0.297810407960787)    |         2 | *****      |
|     4 | [0.297810407960787,0.394474136363715)   |         1 | ***        |
|     5 | [0.394474136363715,0.491137864766642)   |         3 | ********   |
|     6 | [0.491137864766642,0.58780159316957)    |         4 | ********** |
|     7 | [0.58780159316957,0.684465321572498)    |         2 | *****      |
|     8 | [0.684465321572498,0.781129049975425)   |         0 |            |
|     9 | [0.781129049975425,0.877792778378353)   |         3 | ********   |
|    10 | [0.877792778378353,0.97445650678128]    |         2 | *****      |
+-------+-----------------------------------------+-----------+------------+
(10 rows)


今回に限らず、複数列を返すストアド一般に共通する注意点。↓ のような構文も使えるけど、列数の分だけストアド実行が繰り返されてしまいます。
-- not good 
select (numarray_hst(ary :: numeric[], 10)).* from (
    select array_agg(random()) as ary
    from generate_series(1, 20)
) as x;


↓ 問題を顕在化させるため、引数の所でrandom()を使った例。列数の分だけ新たな乱数群を作ってストアドに渡すことになり、度数とヒストグラムがくい違ってます。
-- not good
select (numarray_hst(array_agg(random()) :: numeric[], 10)).*
from generate_series(1, 20);
+-------+----------------------------------------+-----------+------------+
| level |                 range                  | frequency | histogram  |
+-------+----------------------------------------+-----------+------------+
|     1 | [0.0164752490818501,0.111931064259261) |         5 | *****      |
|     2 | [0.111931064259261,0.207386879436672)  |         1 |            |
|     3 | [0.207386879436672,0.302842694614082)  |         2 | ***        |
|     4 | [0.302842694614082,0.398298509791493)  |         0 | ********** |
|     5 | [0.398298509791493,0.493754324968904)  |         1 | ********   |
|     6 | [0.493754324968904,0.589210140146315)  |         1 | *****      |
|     7 | [0.589210140146315,0.684665955323726)  |         1 | *****      |
|     8 | [0.684665955323726,0.780121770501136)  |         4 | *****      |
|     9 | [0.780121770501136,0.875577585678547)  |         2 | ***        |
|    10 | [0.875577585678547,0.971033400855958]  |         3 | ********   |
+-------+----------------------------------------+-----------+------------+
(10 rows)


↓ 正しい使い方2。第3引数でヒストグラムの長さ(最大字数)を30に増やし、各区間の上下限を別々の列にして見やすく。
-- usage example 2
select lower(range), upper(range), frequency, histogram from (
    select array_agg(random() * 100) as ary
    from generate_series(1, 100)
) as x, numarray_hst(ary :: int[], 10, 30) as rec;
+-------+-------+-----------+--------------------------------+
| lower | upper | frequency |           histogram            |
+-------+-------+-----------+--------------------------------+
|     1 |  10.7 |        10 | *********************          |
|  10.7 |  20.4 |        14 | ****************************** |
|  20.4 |  30.1 |        11 | ************************       |
|  30.1 |  39.8 |         6 | *************                  |
|  39.8 |  49.5 |        10 | *********************          |
|  49.5 |  59.2 |         7 | ***************                |
|  59.2 |  68.9 |        12 | **************************     |
|  68.9 |  78.6 |         9 | *******************            |
|  78.6 |  88.3 |        11 | ************************       |
|  88.3 |    98 |        10 | *********************          |
+-------+-------+-----------+--------------------------------+
(10 rows)


↓ 使い方3。ヒストグラムに使う文字も変えてみました。このようにASCIIでなくとも使用可。データは、
昨日も使ったBox-Muller transformによる正規分布の乱数。
-- usage example 3
\pset border 1
select lower(range), upper(range), frequency, histogram from (
    select array_agg(
        sqrt(-2 * ln(random())) * sin(2 * pi() * random()) * 100
        -- normally distributed random numbers
        -- based on Box-Muller transform.
    ) as ary
    from generate_series(1, 100)
) as x, numarray_hst(ary :: int[], 10, 20, '㌂')
;
 lower | upper | frequency |                histogram
-------+-------+-----------+------------------------------------------
  -266 |  -211 |         2 | ㌂㌂
  -211 |  -156 |         5 | ㌂㌂㌂㌂
  -156 |  -101 |        11 | ㌂㌂㌂㌂㌂㌂㌂㌂㌂
  -101 |   -46 |        18 | ㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂
   -46 |     9 |        25 | ㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂
     9 |    64 |        16 | ㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂
    64 |   119 |        12 | ㌂㌂㌂㌂㌂㌂㌂㌂㌂㌂
   119 |   174 |         8 | ㌂㌂㌂㌂㌂㌂
   174 |   229 |         1 | ㌂
   229 |   284 |         2 | ㌂㌂
(10 rows)


集約関数と違い、呼び出す際にarray_aggで配列化する手間があるけど、引数追加などのカスタマイズが容易です。