[解決済み】SQL Serverで中央値を計算する関数

質問

によると MSDN Transact-SQL では、Median は集約関数として利用できません。しかし、私はこの機能を作成することが可能かどうかを調べたいのです ( 集計の作成 関数、ユーザー定義関数、または他の方法)。

集約クエリで中央値（数値データ型と仮定）を計算できるようにする、これを行う最良の方法（可能であれば）は何でしょうか。

どのように解決するのですか？

2019年UPDATE。 この回答を書いてから10年が経ち、より良い結果をもたらす可能性のある解決策が発見されました。また、その後リリースされたSQL Server（特にSQL 2012）では、中央値を計算するために使用できる新しいT-SQL機能が導入されました。SQL Server のリリースでは、クエリオプティマイザも改良され、様々な中央値計算ソリューションの完成度に影響を与える可能性があります。結局のところ、2009年の私の投稿はまだ問題ありませんが、最新のSQL Serverアプリケーションにはより良いソリューションがあるかもしれません。2012年に書かれたこの記事は、素晴らしいリソースです。 https://sqlperformance.com/2012/08/t-sql-queries/median

この記事では、少なくとも彼らがテストしたシンプルなスキーマでは、次のパターンが他のすべての選択肢よりもずっとずっと高速であることを発見しました。この解決策は、最も遅い ( PERCENTILE_CONT というものである。 このトリックは2つの別々のクエリーを必要とするので、すべてのケースで実用的でない可能性があることに注意してください。 また、SQL 2012以降が必要です。

DECLARE @c BIGINT = (SELECT COUNT(*) FROM dbo.EvenRows);

SELECT AVG(1.0 * val)
FROM (
    SELECT val FROM dbo.EvenRows
     ORDER BY val
     OFFSET (@c - 1) / 2 ROWS
     FETCH NEXT 1 + (1 - @c % 2) ROWS ONLY
) AS x;

もちろん、2012年のあるスキーマでの1回のテストで素晴らしい結果が得られたからといって、特にSQL Server 2014以降をお使いの場合は、走行距離が異なるかもしれません。中央値の計算においてperfが重要であれば、その記事で推奨されているオプションをいくつか試してperfテストし、自分のスキーマに最適なものを見つけることを強くお勧めします。

また、特に気をつけたいのが、（SQL Server 2012の新機能である）関数の使用です。 PERCENTILE_CONT の一つで推奨されている 他の回答 というのも、上記のリンク先の記事で、この組み込み関数が最速のソリューションよりも373倍も遅いことが判明したからです。 しかし、個人的には、他のソリューションとの比較でその性能を確認するまでは、大きなテーブルでこの関数を使用することはないと思います。

2009年の投稿はこちらです。

この方法には様々なものがあり、その性能も大きく異なります。ここでは、特に最適化されたソリューションの1つを紹介します。 中央値、ROW_NUMBER、そしてパフォーマンス . これは、実行中に発生する実際のI/Oに関して言えば、特に最適なソリューションです。他のソリューションよりもコストが高いように見えますが、実際にははるかに高速です。

そのページには、他の解決策やパフォーマンステストの詳細も記載されています。中央値列が同じ値を持つ行が複数ある場合の曖昧さ回避のために、ユニークな列を使用していることに注意してください。

SQL Serverのオプティマイザの変更や環境の特殊性によって、通常は高速なソリューションが遅くなる可能性があるからです。

SELECT
   CustomerId,
   AVG(TotalDue)
FROM
(
   SELECT
      CustomerId,
      TotalDue,
      -- SalesOrderId in the ORDER BY is a disambiguator to break ties
      ROW_NUMBER() OVER (
         PARTITION BY CustomerId
         ORDER BY TotalDue ASC, SalesOrderId ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY CustomerId
         ORDER BY TotalDue DESC, SalesOrderId DESC) AS RowDesc
   FROM Sales.SalesOrderHeader SOH
) x
WHERE
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY CustomerId
ORDER BY CustomerId;