タイトル通り、基本統計量の算出方法です。
SQL Serverには、中央値を算出するmedian関数はありません ...
(同じMicrosoftのExcelにはあるのに ...)
(SASのproc sqlではmedian関数が使えるのに ...)
とがっかりしましたが、どうやらpercentile_cont関数で対応できるようです。
ということで、サンプルデータを準備します。
PlaceboのAVALは{1,2,3,4}なのでmedian=2.5、XXX-001は{1,2,8,9}なのでmedian=5になる想定です。
if object_id(N'tempdb..#tmp1', N'U') is not null drop table #tmp1; create table #tmp1( USUBJID varchar(10) not null ,TRT01P varchar(10) not null ,AVAL int null ,constraint PK_tmp1 primary key(USUBJID) ); insert into #tmp1(USUBJID, TRT01P, AVAL) values ('ID101','Placebo',2) ,('ID102','Placebo',1) ,('ID103','Placebo',3) ,('ID104','Placebo',4) ,('ID105','XXX-001',9) ,('ID106','XXX-001',2) ,('ID107','XXX-001',8) ,('ID108','XXX-001',1) ;
このデータに対して基本統計量を算出します。
select TRT01P ,count(*) as 件数 ,max(AVAL) as 最大値 ,min(AVAL) as 最小値 ,avg(AVAL) as 平均値 ,max(第一四分位) as Q1 ,max(第二四分位) as Q2_中央値 ,max(第三四分位) as Q3 ,var(AVAL) as 標本分散 ,varp(AVAL) as 母分散 ,stdev(AVAL) as 標本標準偏差 ,stdevp(AVAL) as 母標準偏差 from ( select * ,percentile_cont(0.25) within group (order by AVAL) over (partition by TRT01P) as 第一四分位 ,percentile_cont(0.50) within group (order by AVAL) over (partition by TRT01P) as 第二四分位 ,percentile_cont(0.75) within group (order by AVAL) over (partition by TRT01P) as 第三四分位 from #tmp1 ) as tmp group by TRT01P
結果はこちら
TRT01P 件数 最大値 最小値 平均値 Q1 Q2_中央値 Q3 標本分散 母分散 標本標準偏差 母標準偏差 -------- ----- ------- ------- ------- ----- ---------- ----- --------- ------- ------------- ----------- Placebo 4 4 1 2 1.75 2.5 3.25 1.7 1.3 1.3 1.1 XXX-001 4 9 1 5 1.75 5 8.25 16.7 12.5 4.1 3.5 (2 行処理されました)
うまく算出できました。
中央値の部分を抜き出すと次の通りで、
TRT01P毎にAVALの50パーセントタイル値を算出する指定です。
percentile_cont(0.5) within group (order by AVAL) over (partition by TRT01P)
percentaile_cnt関数を0.25、0.75にすれば、第一四分位と第三四分位も算出できます。
なお、max関数などと一緒にgroup byで処理できなかったため、
先にサブクエリで中央値等を算出してから、max関数で1行に集約する方法をとっています。
ちなみに、もう一方のpercentile_disc関数は指定したパーセンタイル値に近い値のうち最小のものを返す関数のようです。
例えば、集合{1,2,3,4}の時は中央値=2.5がないため、近い値{2,3}のうち最小値の2が返ってきます。集合{1,2,90,100}の時も同じ要領で2が返ってきます(真の中央値とだいぶズレますね)。
集合{1,2,30,400,500}で中央値=30がある場合は、30が返ってきます。
本記事はここまでです。
参考
PERCENTILE_CONT (Transact-SQL) - SQL Server | Microsoft Docs
PERCENTILE_DISC (Transact-SQL) - SQL Server | Microsoft Docs