昨日の続きで、度数を文字列の長さで表して簡易なヒストグラム(横向き)にするストアドを作りました。度数分布は昨日のストアドで求めます。用いた
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; $$;
↓ 使い方の例
-- 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;
↓ 問題を顕在化させるため、引数の所で
-- 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)
↓ 正しい使い方
-- 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)
↓ 使い方
-- 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)
集約関数と違い、呼び出す際に