タイトル通り、基本統計量の算出方法です。
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',7)
,('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
,round(var(AVAL),1) as 標本分散
,round(varp(AVAL),1) as 母分散
,round(stdev(AVAL),1) as 標本標準偏差
,round(stdevp(AVAL),1) 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()で１行に集約する方法をとっています。
ちなみに、もう一方のpercentile_disc()は指定したパーセンタイル値に近い最小値を返す関数のようです。
例えば、集合{1,2,3,4}の時は中央値=2.5がないため、近い値{2,3}のうち最小値の2が返ってきます。集合{1,2,90,100}の時も同じ要領で2が返ってきます（真の中央値とだいぶズレますね）。
集合{1,2,30,400,500}で中央値=30がある場合は、30が返ってきます。
本記事はここまでです。
documentationはこちら。
