ОБНОВЛЕНИЕ 2019: За 10 лет, прошедших с тех пор, как я написал этот ответ, было обнаружено больше решений, которые могут дать лучшие результаты. Кроме того, с тех пор выпуски SQL Server (особенно SQL 2012) представили новые функции T-SQL, которые можно использовать для вычисления медиан. В выпусках SQL Server также улучшен оптимизатор запросов, что может повлиять на производительность различных медианных решений. Net-net, мой исходный пост 2009 года все еще в порядке, но могут быть лучшие решения для современных приложений SQL Server. Взгляните на эту статью 2012 года, которая является отличным ресурсом: https://sqlperformance.com/2012/08/t-sql-queries/median.

В этой статье было обнаружено, что следующий шаблон намного, намного быстрее, чем все другие альтернативы, по крайней мере, на простой схеме, которую они тестировали. Это решение было в 373 раза быстрее (!!!), чем самое медленное (PERCENTILE_CONT) протестированное решение. Обратите внимание, что для этого трюка требуются два отдельных запроса, что может оказаться нецелесообразным во всех случаях. Также требуется 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 году дал отличные результаты, ваш опыт может отличаться, особенно если вы используете SQL Server 2014 или более поздней версии. Если производительность важна для расчета медианы, я настоятельно рекомендую попробовать и протестировать несколько вариантов, рекомендованных в этой статье, чтобы убедиться, что вы нашли лучший вариант для своей схемы.

Я также был бы особенно осторожен при использовании функции (новая в SQL Server 2012) _ 3_, который рекомендуется в одном из других ответов на этот вопрос, поскольку в статье, приведенной выше, обнаружено, что эта встроенная функция в 373 раза медленнее, чем самое быстрое решение. Возможно, это несоответствие улучшилось за 7 лет, прошедших с тех пор, но лично я не стал бы использовать эту функцию на большой таблице, пока не проверил ее производительность по сравнению с другими решениями.

ОРИГИНАЛЬНАЯ ЗАПИСЬ 2009 ГОДА НАХОДИТСЯ НИЖЕ:

Есть много способов сделать это с сильно различающейся производительностью. Вот одно особенно хорошо оптимизированное решение от Медианы, ROW_NUMBER и производительность. Это особенно оптимальное решение, когда речь идет о фактических операциях ввода-вывода, генерируемых во время выполнения - оно выглядит более затратным, чем другие решения, но на самом деле намного быстрее.

Эта страница также содержит обсуждение других решений и детали тестирования производительности. Обратите внимание на использование уникального столбца в качестве средства устранения неоднозначности в случае наличия нескольких строк с одинаковым значением среднего столбца.

Как и во всех сценариях производительности базы данных, всегда старайтесь протестировать решение с реальными данными на реальном оборудовании - вы никогда не знаете, когда изменение оптимизатора 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;

Если вы используете SQL 2005 или лучше, это хороший простой расчет медианы для одного столбца в таблице:

SELECT
(
 (SELECT MAX(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf)
 +
 (SELECT MIN(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf)
) / 2 AS Median

В SQL Server 2012 вы должны использовать PERCENTILE_CONT:

SELECT SalesOrderID, OrderQty,
    PERCENTILE_CONT(0.5) 
        WITHIN GROUP (ORDER BY OrderQty)
        OVER (PARTITION BY SalesOrderID) AS MedianCont
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY SalesOrderID DESC

См. Также: http://blog.sqlauthority.com/2011/11/20/sql-server-introduction-to-percentile_cont-analytic-functions-introduced-in-sql-server-2012/

Мой первоначальный быстрый ответ был:

select  max(my_column) as [my_column], quartile
from    (select my_column, ntile(4) over (order by my_column) as [quartile]
         from   my_table) i
--where quartile = 2
group by quartile

Это даст вам медианный и межквартильный размах одним махом. Если вам действительно нужна только одна строка, которая является медианной, раскомментируйте предложение where.

Когда вы вставляете это в план объяснения, 60% работы приходится на сортировку данных, что неизбежно при вычислении такой статистики, зависящей от позиции.

Я изменил ответ, чтобы следовать отличному предложению Роберта Шевчика-Робайза в комментариях ниже:

;with PartitionedData as
  (select my_column, ntile(10) over (order by my_column) as [percentile]
   from   my_table),
MinimaAndMaxima as
  (select  min(my_column) as [low], max(my_column) as [high], percentile
   from    PartitionedData
   group by percentile)
select
  case
    when b.percentile = 10 then cast(b.high as decimal(18,2))
    else cast((a.low + b.high)  as decimal(18,2)) / 2
  end as [value], --b.high, a.low,
  b.percentile
from    MinimaAndMaxima a
  join  MinimaAndMaxima b on (a.percentile -1 = b.percentile) or (a.percentile = 10 and b.percentile = 10)
--where b.percentile = 5

Это должно вычислить правильные значения медианы и процентиля, когда у вас есть четное количество элементов данных. Опять же, раскомментируйте последнее предложение where, если вам нужна только медиана, а не все процентильное распределение.

Даже лучше:

SELECT @Median = AVG(1.0 * val)
FROM
(
    SELECT o.val, rn = ROW_NUMBER() OVER (ORDER BY o.val), c.c
    FROM dbo.EvenRows AS o
    CROSS JOIN (SELECT c = COUNT(*) FROM dbo.EvenRows) AS c
) AS x
WHERE rn IN ((c + 1)/2, (c + 2)/2);

От Самого мастера, Ицик Бен-Ган!

В MS SQL Server 2012 (и более поздних версиях) есть функция PERCENTILE_DISC, которая вычисляет определенный процентиль для отсортированных значений. PERCENTILE_DISC (0.5) вычислит медианное значение - https://msdn.microsoft.com/en-us/library/hh231327.aspx

Просто, быстро, точно

SELECT x.Amount 
FROM   (SELECT amount, 
               Count(1) OVER (partition BY 'A')        AS TotalRows, 
               Row_number() OVER (ORDER BY Amount ASC) AS AmountOrder 
        FROM   facttransaction ft) x 
WHERE  x.AmountOrder = Round(x.TotalRows / 2.0, 0)  

Вот как это сделать, если вы хотите использовать функцию Create Aggregate в SQL Server. Такой способ дает возможность писать чистые запросы. Обратите внимание: этот процесс можно легко адаптировать для расчета значения процентиля.

Создайте новый проект Visual Studio и установите для целевой платформы .NET 3.5 (это для SQL 2008, в SQL 2012 он может быть другим). Затем создайте файл класса и вставьте следующий код или эквивалент C #:

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.IO

<Serializable>
<SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, _
  IsInvariantToOrder:=True, MaxByteSize:=-1, IsNullIfEmpty:=True)>
Public Class Median
  Implements IBinarySerialize
  Private _items As List(Of Decimal)

  Public Sub Init()
    _items = New List(Of Decimal)()
  End Sub

  Public Sub Accumulate(value As SqlDecimal)
    If Not value.IsNull Then
      _items.Add(value.Value)
    End If
  End Sub

  Public Sub Merge(other As Median)
    If other._items IsNot Nothing Then
      _items.AddRange(other._items)
    End If
  End Sub

  Public Function Terminate() As SqlDecimal
    If _items.Count <> 0 Then
      Dim result As Decimal
      _items = _items.OrderBy(Function(i) i).ToList()
      If _items.Count Mod 2 = 0 Then
        result = ((_items((_items.Count / 2) - 1)) + (_items(_items.Count / 2))) / 2@
      Else
        result = _items((_items.Count - 1) / 2)
      End If

      Return New SqlDecimal(result)
    Else
      Return New SqlDecimal()
    End If
  End Function

  Public Sub Read(r As BinaryReader) Implements IBinarySerialize.Read
    'deserialize it from a string
    Dim list = r.ReadString()
    _items = New List(Of Decimal)

    For Each value In list.Split(","c)
      Dim number As Decimal
      If Decimal.TryParse(value, number) Then
        _items.Add(number)
      End If
    Next

  End Sub

  Public Sub Write(w As BinaryWriter) Implements IBinarySerialize.Write
    'serialize the list to a string
    Dim list = ""

    For Each item In _items
      If list <> "" Then
        list += ","
      End If      
      list += item.ToString()
    Next
    w.Write(list)
  End Sub
End Class

Затем скомпилируйте его и скопируйте файлы DLL и PDB на свой компьютер с SQL Server и выполните следующую команду в SQL Server:

CREATE ASSEMBLY CustomAggregate FROM '{path to your DLL}'
WITH PERMISSION_SET=SAFE;
GO

CREATE AGGREGATE Median(@value decimal(9, 3))
RETURNS decimal(9, 3) 
EXTERNAL NAME [CustomAggregate].[{namespace of your DLL}.Median];
GO

Затем вы можете написать запрос для вычисления медианы следующим образом: SELECT dbo.Median (Field) FROM Table

Я только что наткнулся на эту страницу, когда искал решение для медианы на основе набора. Посмотрев здесь на некоторые решения, я пришел к следующему. Надеюсь, это поможет / работает.

DECLARE @test TABLE(
    i int identity(1,1),
    id int,
    score float
)

INSERT INTO @test (id,score) VALUES (1,10)
INSERT INTO @test (id,score) VALUES (1,11)
INSERT INTO @test (id,score) VALUES (1,15)
INSERT INTO @test (id,score) VALUES (1,19)
INSERT INTO @test (id,score) VALUES (1,20)

INSERT INTO @test (id,score) VALUES (2,20)
INSERT INTO @test (id,score) VALUES (2,21)
INSERT INTO @test (id,score) VALUES (2,25)
INSERT INTO @test (id,score) VALUES (2,29)
INSERT INTO @test (id,score) VALUES (2,30)

INSERT INTO @test (id,score) VALUES (3,20)
INSERT INTO @test (id,score) VALUES (3,21)
INSERT INTO @test (id,score) VALUES (3,25)
INSERT INTO @test (id,score) VALUES (3,29)

DECLARE @counts TABLE(
    id int,
    cnt int
)

INSERT INTO @counts (
    id,
    cnt
)
SELECT
    id,
    COUNT(*)
FROM
    @test
GROUP BY
    id

SELECT
    drv.id,
    drv.start,
    AVG(t.score)
FROM
    (
        SELECT
            MIN(t.i)-1 AS start,
            t.id
        FROM
            @test t
        GROUP BY
            t.id
    ) drv
    INNER JOIN @test t ON drv.id = t.id
    INNER JOIN @counts c ON t.id = c.id
WHERE
    t.i = ((c.cnt+1)/2)+drv.start
    OR (
        t.i = (((c.cnt+1)%2) * ((c.cnt+2)/2))+drv.start
        AND ((c.cnt+1)%2) * ((c.cnt+2)/2) <> 0
    )
GROUP BY
    drv.id,
    drv.start

Следующий запрос возвращает медианное значение из списка значений в одном столбце. Его нельзя использовать как агрегатную функцию или вместе с ней, но вы все равно можете использовать его как подзапрос с предложением WHERE во внутреннем выборе.

SQL Server 2005+:

SELECT TOP 1 value from
(
    SELECT TOP 50 PERCENT value 
    FROM table_name 
    ORDER BY  value
)for_median
ORDER BY value DESC

Хотя решение Джастина Гранта кажется надежным, я обнаружил, что, когда у вас есть несколько повторяющихся значений в заданном ключе раздела, номера строк для повторяющихся значений ASC оказываются вне последовательности, поэтому они не выравниваются должным образом.

Вот отрывок из моего результата:

KEY VALUE ROWA ROWD  

13  2     22   182
13  1     6    183
13  1     7    184
13  1     8    185
13  1     9    186
13  1     10   187
13  1     11   188
13  1     12   189
13  0     1    190
13  0     2    191
13  0     3    192
13  0     4    193
13  0     5    194

Я использовал код Джастина как основу для этого решения. Хотя он не так эффективен, учитывая использование нескольких производных таблиц, он решает проблему с упорядочением строк, с которой я столкнулся. Любые улучшения будут приветствоваться, так как я не слишком разбираюсь в T-SQL.

SELECT PKEY, cast(AVG(VALUE)as decimal(5,2)) as MEDIANVALUE
FROM
(
  SELECT PKEY,VALUE,ROWA,ROWD,
  'FLAG' = (CASE WHEN ROWA IN (ROWD,ROWD-1,ROWD+1) THEN 1 ELSE 0 END)
  FROM
  (
    SELECT
    PKEY,
    cast(VALUE as decimal(5,2)) as VALUE,
    ROWA,
    ROW_NUMBER() OVER (PARTITION BY PKEY ORDER BY ROWA DESC) as ROWD 

    FROM
    (
      SELECT
      PKEY, 
      VALUE,
      ROW_NUMBER() OVER (PARTITION BY PKEY ORDER BY VALUE ASC,PKEY ASC ) as ROWA 
      FROM [MTEST]
    )T1
  )T2
)T3
WHERE FLAG = '1'
GROUP BY PKEY
ORDER BY PKEY

Приведенный выше пример Джастина очень хорош. Но эту необходимость в первичном ключе следует указать очень четко. Я видел этот код в дикой природе без ключа, и результаты плохие.

Жалоба, которую я получаю по поводу Percentile_Cont, заключается в том, что он не дает вам фактического значения из набора данных. Чтобы получить «медианное значение», которое является фактическим значением из набора данных, используйте Percentile_Disc.

SELECT SalesOrderID, OrderQty,
    PERCENTILE_DISC(0.5) 
        WITHIN GROUP (ORDER BY OrderQty)
        OVER (PARTITION BY SalesOrderID) AS MedianCont
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY SalesOrderID DESC

В UDF напишите:

 Select Top 1 medianSortColumn from Table T
  Where (Select Count(*) from Table
         Where MedianSortColumn <
           (Select Count(*) From Table) / 2)
  Order By medianSortColumn

Медианный результат

Это самый простой способ найти медиану атрибута.

Select round(S.salary,4) median from employee S 
where (select count(salary) from station 
where salary < S.salary ) = (select count(salary) from station
where salary > S.salary)

См. Другие решения для вычисления медианы в SQL здесь: "Простой способ вычисления медианы с помощью MySQL "(решения в большинстве своем не зависят от производителя).

Для непрерывной переменной / меры 'col1' из 'table1'

select col1  
from
    (select top 50 percent col1, 
    ROW_NUMBER() OVER(ORDER BY col1 ASC) AS Rowa,
    ROW_NUMBER() OVER(ORDER BY col1 DESC) AS Rowd
    from table1 ) tmp
where tmp.Rowa = tmp.Rowd

Часто нам может потребоваться вычисление медианы не только для всей таблицы, но и для агрегатов по некоторому идентификатору. Другими словами, рассчитайте медианное значение для каждого идентификатора в нашей таблице, где каждый идентификатор имеет много записей. (на основе решения, отредактированного @gdoron: хорошая производительность и работает во многих SQL)

SELECT our_id, AVG(1.0 * our_val) as Median
FROM
( SELECT our_id, our_val, 
  COUNT(*) OVER (PARTITION BY our_id) AS cnt,
  ROW_NUMBER() OVER (PARTITION BY our_id ORDER BY our_val) AS rnk
  FROM our_table
) AS x
WHERE rnk IN ((cnt + 1)/2, (cnt + 2)/2) GROUP BY our_id;

Надеюсь, это поможет.

Для крупномасштабных наборов данных вы можете попробовать этот GIST:

https://gist.github.com/chrisknoll/1b38761ce8c5016ec5b2

Он работает путем агрегирования различных значений, которые вы найдете в своем наборе (например, возраст, год рождения и т. Д.), И использует оконные функции SQL для определения любой позиции процентиля, указанной в запросе.

Использование одного оператора. Один из способов - использовать оконную функцию ROW_NUMBER (), COUNT () и фильтровать подзапрос. Вот средняя зарплата:

 SELECT AVG(e_salary) 
 FROM                                                             
    (SELECT 
      ROW_NUMBER() OVER(ORDER BY e_salary) as row_no, 
      e_salary,
      (COUNT(*) OVER()+1)*0.5 AS row_half
     FROM Employee) t
 WHERE row_no IN (FLOOR(row_half),CEILING(row_half))

Я видел аналогичные решения в сети с использованием FLOOR и CEILING, но пытался использовать один оператор. (отредактировано)

Я хотел найти решение самостоятельно, но мой мозг отключился и упал на пути. Я думаю, что это работает, но не просите меня объяснять это утром. :П

DECLARE @table AS TABLE
(
    Number int not null
);

insert into @table select 2;
insert into @table select 4;
insert into @table select 9;
insert into @table select 15;
insert into @table select 22;
insert into @table select 26;
insert into @table select 37;
insert into @table select 49;

DECLARE @Count AS INT
SELECT @Count = COUNT(*) FROM @table;

WITH MyResults(RowNo, Number) AS
(
    SELECT RowNo, Number FROM
        (SELECT ROW_NUMBER() OVER (ORDER BY Number) AS RowNo, Number FROM @table) AS Foo
)
SELECT AVG(Number) FROM MyResults WHERE RowNo = (@Count+1)/2 OR RowNo = ((@Count+1)%2) * ((@Count+2)/2)

Это работает с SQL 2000:

DECLARE @testTable TABLE 
( 
    VALUE   INT
)
--INSERT INTO @testTable -- Even Test
--SELECT 3 UNION ALL
--SELECT 5 UNION ALL
--SELECT 7 UNION ALL
--SELECT 12 UNION ALL
--SELECT 13 UNION ALL
--SELECT 14 UNION ALL
--SELECT 21 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 29 UNION ALL
--SELECT 40 UNION ALL
--SELECT 56

--
--INSERT INTO @testTable -- Odd Test
--SELECT 3 UNION ALL
--SELECT 5 UNION ALL
--SELECT 7 UNION ALL
--SELECT 12 UNION ALL
--SELECT 13 UNION ALL
--SELECT 14 UNION ALL
--SELECT 21 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 29 UNION ALL
--SELECT 39 UNION ALL
--SELECT 40 UNION ALL
--SELECT 56


DECLARE @RowAsc TABLE
(
    ID      INT IDENTITY,
    Amount  INT
)

INSERT INTO @RowAsc
SELECT  VALUE 
FROM    @testTable 
ORDER BY VALUE ASC

SELECT  AVG(amount)
FROM @RowAsc ra
WHERE ra.id IN
(
    SELECT  ID 
    FROM    @RowAsc
    WHERE   ra.id -
    (
        SELECT  MAX(id) / 2.0 
        FROM    @RowAsc
    ) BETWEEN 0 AND 1

)

Для таких новичков, как я, которые изучают самые основы, я лично считаю, что этому примеру легче следовать, поскольку легче понять, что именно происходит и откуда берутся медианные значения ...

select
 ( max(a.[Value1]) + min(a.[Value1]) ) / 2 as [Median Value1]
,( max(a.[Value2]) + min(a.[Value2]) ) / 2 as [Median Value2]

from (select
    datediff(dd,startdate,enddate) as [Value1]
    ,xxxxxxxxxxxxxx as [Value2]
     from dbo.table1
     )a

Хотя в абсолютном трепете перед некоторыми из приведенных выше кодов !!!

Это самый простой ответ, который я мог придумать. Хорошо работал с моими данными. Если вы хотите исключить определенные значения, просто добавьте предложение where к внутреннему выбору.

SELECT TOP 1 
    ValueField AS MedianValue
FROM
    (SELECT TOP(SELECT COUNT(1)/2 FROM tTABLE)
        ValueField
    FROM 
        tTABLE
    ORDER BY 
        ValueField) A
ORDER BY
    ValueField DESC

Следующее решение работает при этих предположениях:

• Нет повторяющихся значений
• Нет NULL

Код:

IF OBJECT_ID('dbo.R', 'U') IS NOT NULL
  DROP TABLE dbo.R

CREATE TABLE R (
    A FLOAT NOT NULL);

INSERT INTO R VALUES (1);
INSERT INTO R VALUES (2);
INSERT INTO R VALUES (3);
INSERT INTO R VALUES (4);
INSERT INTO R VALUES (5);
INSERT INTO R VALUES (6);

-- Returns Median(R)
select SUM(A) / CAST(COUNT(A) AS FLOAT)
from R R1 
where ((select count(A) from R R2 where R1.A > R2.A) = 
      (select count(A) from R R2 where R1.A < R2.A)) OR
      ((select count(A) from R R2 where R1.A > R2.A) + 1 = 
      (select count(A) from R R2 where R1.A < R2.A)) OR
      ((select count(A) from R R2 where R1.A > R2.A) = 
      (select count(A) from R R2 where R1.A < R2.A) + 1) ; 

Я пробую использовать несколько альтернатив, но из-за того, что в моих записях данных есть повторяющиеся значения, версии ROW_NUMBER мне не подходят. Итак, вот запрос, который я использовал (версия с NTILE):

SELECT distinct
   CustomerId,
   (
       MAX(CASE WHEN Percent50_Asc=1 THEN TotalDue END) OVER (PARTITION BY CustomerId)  +
       MIN(CASE WHEN Percent50_desc=1 THEN TotalDue END) OVER (PARTITION BY CustomerId) 
   )/2 MEDIAN
FROM
(
   SELECT
      CustomerId,
      TotalDue,
     NTILE(2) OVER (
         PARTITION BY CustomerId
         ORDER BY TotalDue ASC) AS Percent50_Asc,
     NTILE(2) OVER (
         PARTITION BY CustomerId
         ORDER BY TotalDue DESC) AS Percent50_desc
   FROM Sales.SalesOrderHeader SOH
) x
ORDER BY CustomerId;

Основываясь на приведенном выше ответе Джеффа Этвуда, здесь используется GROUP BY и коррелированный подзапрос для получения медианы для каждой группы.

SELECT TestID, 
(
 (SELECT MAX(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts WHERE TestID = Posts_parent.TestID ORDER BY Score) AS BottomHalf)
 +
 (SELECT MIN(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts WHERE TestID = Posts_parent.TestID ORDER BY Score DESC) AS TopHalf)
) / 2 AS MedianScore,
AVG(Score) AS AvgScore, MIN(Score) AS MinScore, MAX(Score) AS MaxScore
FROM Posts_parent
GROUP BY Posts_parent.TestID

На ваш вопрос Джефф Этвуд уже дал простое и эффективное решение. Но, если вы ищете какой-то альтернативный подход для вычисления медианы, вам поможет приведенный ниже код SQL.

create table employees(salary int);

insert into employees values(8); insert into employees values(23); insert into employees values(45); insert into employees values(123); insert into employees values(93); insert into employees values(2342); insert into employees values(2238);

select * from employees;

declare @odd_even int; declare @cnt int; declare @middle_no int;


set @cnt=(select count(*) from employees); set @middle_no=(@cnt/2)+1; select @odd_even=case when (@cnt%2=0) THEN -1 ELse 0 END ;


 select AVG(tbl.salary) from  (select  salary,ROW_NUMBER() over (order by salary) as rno from employees group by salary) tbl  where tbl.rno=@middle_no or tbl.rno=@middle_no+@odd_even;

Если вы хотите рассчитать медианное значение в MySQL, эта ссылка на github будет быть полезным.

Это наиболее оптимальное решение для поиска медиан, которое я могу придумать. Имена в примере основаны на примере Джастина. Убедитесь, что существует индекс для таблицы Sales.SalesOrderHeader со столбцами индекса CustomerId и TotalDue в указанном порядке.

SELECT
 sohCount.CustomerId,
 AVG(sohMid.TotalDue) as TotalDueMedian
FROM 
(SELECT 
  soh.CustomerId,
  COUNT(*) as NumberOfRows
FROM 
  Sales.SalesOrderHeader soh 
GROUP BY soh.CustomerId) As sohCount
CROSS APPLY 
    (Select 
       soh.TotalDue
    FROM 
    Sales.SalesOrderHeader soh 
    WHERE soh.CustomerId = sohCount.CustomerId 
    ORDER BY soh.TotalDue
    OFFSET sohCount.NumberOfRows / 2 - ((sohCount.NumberOfRows + 1) % 2) ROWS 
    FETCH NEXT 1 + ((sohCount.NumberOfRows + 1) % 2) ROWS ONLY
    ) As sohMid
GROUP BY sohCount.CustomerId

ОБНОВЛЕНИЕ

Я был немного не уверен в том, какой метод имеет лучшую производительность, поэтому я провел сравнение между моим методом Джастина Грантса и Джеффа Этвудса, выполнив запрос на основе всех трех методов в одном пакете, и пакетная стоимость каждого запроса составила:

Без индекса:

• Моя 30%
• Джастин Грантс 13%
• Джефф Этвудс 58%

И с индексом

• Мои 3%.
• Джастин Грантс 10%
• Джефф Этвудс 87%

Я попытался увидеть, насколько хорошо масштабируются запросы, если у вас есть индекс, создавая больше данных из примерно 14 000 строк с коэффициентом от 2 до 512, что означает в итоге около 7,2 миллиона строк. Примечание. Я удостоверился, что поле CustomeId уникально для каждого раза, когда я делал одну копию, поэтому пропорция строк по сравнению с уникальным экземпляром CustomerId оставалась постоянной. В то время как я делал это, я запускал выполнение, в котором я впоследствии перестраивал индекс, и я заметил, что результаты стабилизировались примерно с коэффициентом 128 с данными, которые у меня были до этих значений:

• Мои 3%.
• Джастин Грантс 5%
• Джефф Этвудс 92%

Мне было интересно, как на производительность могло повлиять масштабирование количества строк, но сохранение уникального идентификатора CustomerId постоянным, поэтому я установил новый тест, в котором я сделал именно это. Теперь, вместо стабилизации, соотношение стоимости пакета продолжало расходиться, также вместо примерно 20 строк на CustomerId на среднее значение, которое у меня в итоге было около 10000 строк на такой уникальный идентификатор. Цифры, где:

• Моя 4%
• Джастинс 60%
• Джеффс 35%

Я убедился, что правильно реализовал каждый метод, сравнив результаты. Я пришел к выводу, что метод, который я использовал, обычно работает быстрее, пока существует index. Также заметил, что этот метод рекомендуется для этой конкретной проблемы в этой статье https://www.microsoftpressstore.com/articles/article.aspx?p=2314819&seqNum=5

Еще один способ еще больше повысить производительность последующих вызовов этого запроса - сохранить информацию о счетчике во вспомогательной таблице. Вы даже можете поддерживать его, имея триггер, который обновляет и хранит информацию о количестве строк SalesOrderHeader, зависящих от CustomerId, конечно, вы также можете просто сохранить медианное значение.

Ниже мое решение:

with tempa as

 (

    select value,row_number() over (order by value) as Rn,/* Assigning a 
                                                           row_number */
           count(value) over () as Cnt /*Taking total count of the values */
    from numbers
    where value is not null /* Excluding the null values */
 ),

tempb as

  (

    /* Since we don't know whether the number of rows is odd or even, we shall 
     consider both the scenarios */

    select round(cnt/2) as Ref from tempa where mod(cnt,2)=1
    union all
    select round(cnt/2) a Ref from tempa where mod(cnt,2)=0
     union all
    select round(cnt/2) + 1 as Ref from tempa where mod(cnt,2)=0
   )
  select avg(value) as Median_Value

  from tempa where rn in

    ( select Ref from tempb);

Используя агрегат COUNT, вы можете сначала подсчитать количество строк и сохранить их в переменной с именем @cnt. Затем вы можете вычислить параметры для фильтра OFFSET-FETCH, чтобы указать, в зависимости от порядка количества, сколько строк пропустить (значение смещения) и сколько строк отфильтровать (значение выборки).

Число строк, которые нужно пропустить, равно (@cnt - 1) / 2. Понятно, что для нечетного числа этот расчет верен, потому что вы сначала вычитаете 1 для единственного среднего значения, а затем делите на 2.

Это также правильно работает для четного счета, потому что в выражении используется целочисленное деление; Таким образом, при вычитании 1 из четного числа остается нечетное значение.

При делении этого нечетного значения на 2 дробная часть результата (0,5) усекается. Количество строк для выборки: 2 - (@cnt% 2). Идея состоит в том, что, когда счетчик нечетный, результат операции по модулю равен 1, и вам нужно получить 1 строку. Когда счетчик четный, результат операции по модулю равен 0, и вам нужно получить 2 строки. Вычитая результат операции по модулю 1 или 0 из 2, вы получаете желаемые 1 или 2 соответственно. Наконец, чтобы вычислить медианное количество, возьмите одно или два результата и примените среднее значение после преобразования входного целочисленного значения в числовое следующим образом:

DECLARE @cnt AS INT = (SELECT COUNT(*) FROM [Sales].[production].[stocks]);
SELECT AVG(1.0 * quantity) AS median
FROM ( SELECT quantity
FROM [Sales].[production].[stocks]
ORDER BY quantity
OFFSET (@cnt - 1) / 2 ROWS FETCH NEXT 2 - @cnt % 2 ROWS ONLY ) AS D;

Получить медианное значение зарплаты из таблицы сотрудников

with cte as (select salary, ROW_NUMBER() over (order by salary asc) as num from employees)

select avg(salary) from cte where num in ((select (count(*)+1)/2 from employees), (select (count(*)+2)/2 from employees));

Попробуйте следующую логику, чтобы узнать медиану:

Рассмотрим таблицу со следующими числами: 1,1,2,3,4,5

МЕДИАНА составляет 2,5

с tempa as (выберите num, count (num) over () as Cnt, row_number () over (order by num) as Rnum from temp), tempb as (выберите round (cnt / 2) as ref_value из tempa, где mod (cnt , 2) ‹> 0 union all select round (cnt / 2) from tempa, где mod (cnt, 2) = 0 union all select round (cnt / 2 + 1) from tempa, где mod (cnt, 2) = 0) select avg (число) из tempa, где rnum in (выберите * из tempb);