【SQL Server】基本統計量の算出(特に中央値!percentile_cont関数、percentile_disc関数)

タイトル通り、基本統計量の算出方法です。
SQL Serverには、中央値を算出するmedian関数はありません ...

(同じMicrosoftExcelにはあるのに ...)
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

 

本ブログは個人メモです。 本ブログの内容によって生じた損害等の一切の責任を負いかねますのでご了承ください。