【SQL Server】基本統計量の算出（特に中央値！percentile_cont関数、percentile_disc関数）

タイトル通り、基本統計量の算出方法です。
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関数で１行に集約する方法をとっています。

ちなみに、もう一方の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