Fonction pour calculer la médiane dans le serveur SQL

si vous utilisez SQL 2005 ou mieux il s'agit d'un beau calcul de médiane simple ish pour une seule colonne dans un tableau:

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

dans SQL Server 2012 vous devez utiliser 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

Voir aussi: http://blog.sqlauthority.com/2011/11/20/sql-server-introduction-to-percentile_cont-analytic-functions-introduced-in-sql-server-2012 /

ma réponse rapide originale était:

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

cela vous donnera la médiane et l'intervalle interquartile en un seul mouvement. Si vous voulez vraiment une seule rangée qui est la médiane puis découpler la clause où.

quand vous insérez cela dans un plan d'explication, 60% du travail est le tri des données qui est inévitable lors du calcul des statistiques dépendantes de position comme ceci.

j'ai modifié la réponse pour suivre le excellente suggestion de Robert Ševčík-Robajz dans les commentaires ci-dessous:

;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

cela devrait calculer les valeurs médianes et percentiles correctes lorsque vous avez un nombre pair d'éléments de données. Encore une fois, découplez la clause finale où si vous voulez seulement la médiane et non l'ensemble de la distribution percentile.

encore mieux:

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);

du maître lui-même, Itzik Ben-Gan !

MS SQL Server 2012 (et versions ultérieures) a la fonction PERCENTILE_DISC qui calcule un percentile spécifique pour les valeurs triées. PERCENTILE_DISC (0.5) calcule la médiane - https://msdn.microsoft.com/en-us/library/hh231327.aspx

je viens de croiser cette page en cherchant une solution basée sur un jeu pour median. Après avoir examiné quelques-unes des solutions ici, j'ai trouvé ce qui suit. L'espoir est aide/œuvres.

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

Simple, rapide, précis

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)  

si vous voulez utiliser la fonction Create Aggregate dans SQL Server, voici comment faire. Le faire de cette façon a l'avantage d'être capable d'écrire des requêtes propres. Notez que ce processus pourrait être adapté pour calculer assez facilement une valeur de Percentile.

crée un nouveau projet de studio visuel et fixe le cadre cible à .net 3.5 (C'est pour SQL 2008, il peut être différent en SQL 2012). Puis créer un fichier de classe et mettre dans le code suivant, ou l'équivalent 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

compilez-le et copiez le fichier DLL et PDB sur votre machine SQL Server et exécutez la commande suivante dans 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

Vous pouvez ensuite écrire une requête pour calculer la médiane comme ceci: SELECT dbo.Médiane (champ) du tableau

La requête suivante renvoie les médiane à partir d'une liste de valeurs dans une colonne. Il ne peut pas être utilisé ou avec une fonction d'agrégation, mais vous pouvez toujours l'utiliser comme une sous-requête avec une clause where dans la requête select.

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

L'exemple de Justin ci-dessus est très bon. Mais ce besoin primordial doit être énoncé très clairement. J'ai vu que le code dans la nature sans la clé, et les résultats sont mauvais.

la plainte que je reçois au sujet du Percentile_Cont est qu'il ne vous donnera pas une valeur réelle de l'ensemble de données. Pour obtenir une "médiane" qui est une valeur réelle de l'ensemble de données, utilisez 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

bien que la solution de Justin grant semble solide, j'ai constaté que lorsque vous avez un certain nombre de valeurs dupliquées dans une clé de partition donnée, les numéros de ligne pour les valeurs dupliquées de L'ASC finissent hors séquence de sorte qu'ils ne s'alignent pas correctement.

voici un fragment de mon résultat:

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

j'ai utilisé le code de Justin comme base pour cette solution. Bien que pas aussi efficace étant donné l'utilisation de plusieurs tables dérivées, il ne résout la rangée la commande de problème que j'ai rencontré. Toute amélioration serait la bienvenue car je ne suis pas que l'expérience en 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

dans un UDF, écrire:

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

Voir autres solutions pour le calcul de la médiane en SQL ici: " méthode Simple pour calculer la médiane avec MySQL " (les solutions sont généralement indépendantes du vendeur).

pour une variable/mesure continue "col1" du "tableau 1 "

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

je voulais trouver une solution par moi-même, mais mon cerveau a trébuché et est tombé sur la voie. J' penser il fonctionne, mais ne me demandez pas de l'expliquer dans le matin. : P

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)

--Create Temp Table to Store Results in
DECLARE @results AS TABLE 
(
    [Month] datetime not null
 ,[Median] int not null
);

--This variable will determine the date
DECLARE @IntDate as int 
set @IntDate = -13


WHILE (@IntDate < 0) 
BEGIN

--Create Temp Table
DECLARE @table AS TABLE 
(
    [Rank] int not null
 ,[Days Open] int not null
);

--Insert records into Temp Table
insert into @table 

SELECT 
    rank() OVER (ORDER BY DATEADD(mm, DATEDIFF(mm, 0, DATEADD(ss, SVR.close_date, '1970')), 0), DATEDIFF(day,DATEADD(ss, SVR.open_date, '1970'),DATEADD(ss, SVR.close_date, '1970')),[SVR].[ref_num]) as [Rank]
 ,DATEDIFF(day,DATEADD(ss, SVR.open_date, '1970'),DATEADD(ss, SVR.close_date, '1970')) as [Days Open]
FROM
 mdbrpt.dbo.View_Request SVR
 LEFT OUTER JOIN dbo.dtv_apps_systems vapp 
 on SVR.category = vapp.persid
 LEFT OUTER JOIN dbo.prob_ctg pctg 
 on SVR.category = pctg.persid
 Left Outer Join [mdbrpt].[dbo].[rootcause] as [Root Cause] 
 on [SVR].[rootcause]=[Root Cause].[id]
 Left Outer Join [mdbrpt].[dbo].[cr_stat] as [Status]
 on [SVR].[status]=[Status].[code]
 LEFT OUTER JOIN [mdbrpt].[dbo].[net_res] as [net] 
 on [net].[id]=SVR.[affected_rc]
WHERE
 SVR.Type IN ('P') 
 AND
 SVR.close_date IS NOT NULL 
 AND
 [Status].[SYM] = 'Closed'
 AND
 SVR.parent is null
 AND
 [Root Cause].[sym] in ( 'RC - Application','RC - Hardware', 'RC - Operational', 'RC - Unknown')
 AND
 (
  [vapp].[appl_name] in ('3PI','Billing Rpts/Files','Collabrent','Reports','STMS','STMS 2','Telco','Comergent','OOM','C3-BAU','C3-DD','DIRECTV','DIRECTV Sales','DIRECTV Self Care','Dealer Website','EI Servlet','Enterprise Integration','ET','ICAN','ODS','SB-SCM','SeeBeyond','Digital Dashboard','IVR','OMS','Order Services','Retail Services','OSCAR','SAP','CTI','RIO','RIO Call Center','RIO Field Services','FSS-RIO3','TAOS','TCS')
 OR
  pctg.sym in ('Systems.Release Health Dashboard.Problem','DTV QA Test.Enterprise Release.Deferred Defect Log')
 AND  
  [Net].[nr_desc] in ('3PI','Billing Rpts/Files','Collabrent','Reports','STMS','STMS 2','Telco','Comergent','OOM','C3-BAU','C3-DD','DIRECTV','DIRECTV Sales','DIRECTV Self Care','Dealer Website','EI Servlet','Enterprise Integration','ET','ICAN','ODS','SB-SCM','SeeBeyond','Digital Dashboard','IVR','OMS','Order Services','Retail Services','OSCAR','SAP','CTI','RIO','RIO Call Center','RIO Field Services','FSS-RIO3','TAOS','TCS')
 )
 AND
 DATEADD(mm, DATEDIFF(mm, 0, DATEADD(ss, SVR.close_date, '1970')), 0) = DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,@IntDate,getdate())), 0)
ORDER BY [Days Open]



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

WITH MyResults(RowNo, [Days Open]) AS
(
    SELECT RowNo, [Days Open] FROM
        (SELECT ROW_NUMBER() OVER (ORDER BY [Days Open]) AS RowNo, [Days Open] FROM @table) AS Foo
)


insert into @results
SELECT 
 DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,@IntDate,getdate())), 0) as [Month]
 ,AVG([Days Open])as [Median] FROM MyResults WHERE RowNo = (@Count+1)/2 OR RowNo = ((@Count+1)%2) * ((@Count+2)/2) 


set @IntDate = @IntDate+1
DELETE FROM @table
END

select *
from @results
order by [Month]

cela fonctionne avec 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

)

pour les débutants comme moi qui apprennent les bases, je trouve personnellement cet exemple plus facile à suivre, car il est plus facile de comprendre exactement ce qui se passe et d'où viennent les valeurs médianes...

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

Dans la crainte absolue de certains des codes ci-dessus!!!

C'est une réponse aussi simple que possible. A bien fonctionné avec mes données. Si vous souhaitez exclure certaines valeurs, il suffit d'ajouter une clause where à la sélection interne.

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

la solution suivante fonctionne selon ces hypothèses:

• Pas de doublons
• Pas De Valeurs Null

Code:

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) ; 

DECLARE @Obs int
DECLARE @RowAsc table
(
ID      INT IDENTITY,
Observation  FLOAT
)
INSERT INTO @RowAsc
SELECT Observations FROM MyTable
ORDER BY 1 
SELECT @Obs=COUNT(*)/2 FROM @RowAsc
SELECT Observation AS Median FROM @RowAsc WHERE ID=@Obs

j'essaye avec plusieurs alternatives, mais en raison de mes enregistrements de données a des valeurs répétées, les versions ROW_NUMBER semblent ne pas être un choix pour moi. Voici donc la requête que j'ai utilisée (une version avec 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;

en S'appuyant sur la réponse de Jeff Atwood ci-dessus, c'est avec GROUP BY et un sous-ensemble corrélé d'obtenir la médiane pour chaque groupe.

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

souvent, nous pouvons avoir besoin de calculer la médiane non seulement pour l'ensemble du tableau, mais pour les agrégats par rapport à une certaine ID. En d'autres termes, calculez la médiane pour chaque ID dans notre tableau, où chaque ID a de nombreux enregistrements. (basé sur la solution éditée par @gdoron: bonne performance et fonctionne dans beaucoup de 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;

J'espère que ça aidera.

pour votre question, Jeff Atwood avait déjà donné la solution simple et efficace. Mais, si vous êtes à la recherche d'une approche alternative pour calculer la médiane, en dessous du code SQL vous aidera.

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;

si vous cherchez à calculer la médiane dans MySQL, ce lien github sera utile.

C'est la solution la plus optimale pour trouver des médianes à laquelle je peux penser. Les noms dans l'exemple sont basés sur L'exemple de Justin. Assurez-vous d'un index pour la table Commerciale.Salesderheader existe avec des colonnes D'index CustomerId et TotalDue dans cet ordre.

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

mise à JOUR

j'étais un peu incertain sur quelle méthode a la meilleure performance, donc j'ai fait une comparaison entre ma méthode Justin Grants et Jeff Atwoods par l'exécution de requête basée sur les trois méthodes dans un lot et le coût de lot de chaque requête étaient:

sans index:

• Mine 30%
• Justin Subventions De 13%
• Jeff Atwoods 58%

et avec index

• Mine 3%.
• Justin Subventions De 10%
• Jeff Atwoods 87%

j'ai essayé de voir comment l'échelle des requêtes Si vous avez index en créant plus de données à partir d'environ 14 000 lignes par un facteur de 2 jusqu'à 512 ce qui signifie à la fin environ 7,2 millions de lignes. Note: J'ai veillé à ce que le champ CustomeId soit unique à chaque fois que j'ai fait une seule copie, de sorte que la proportion de lignes comparées à l'instance unique de CustomerId a été maintenue constante. Pendant que je faisais cela, j'ai exécuté des exécutions où j'ai reconstruit index par la suite, et j'ai remarqué que les résultats se sont stabilisés autour d'un facteur de 128 avec les données j'ai eu à ces valeurs:

• Mine 3%.
• Justin Subventions De 5%
• Jeff Atwoods 92%

je me suis demandé comment la performance aurait pu être affectée par l'échelle du nombre de lignes mais en gardant unique CustomerId constante, donc j'ai mis en place un nouveau test où je l'ai fait. Maintenant, au lieu de se stabiliser, le rapport de coût de fournée a continué à diverger, aussi au lieu d'environ 20 lignes par CustomerId par moyenne j'avais à la fin environ 10000 lignes par Id unique. Les nombres où:

• Mine 4%
• Justins 60%
• Jeffs 35%

je me suis assuré de mettre en œuvre chaque méthode correcte en comparant les résultats. Ma conclusion est que la méthode que j'ai utilisée est généralement plus rapide aussi longtemps que l'index existe. Également remarqué que cette méthode est ce qui est recommandé pour ce problème particulier dans cet article https://www.microsoftpressstore.com/articles/article.aspx?p=2314819&seqNum=5

un moyen d'améliorer encore plus la performance des appels ultérieurs à cette requête est de persister l'information de comptage dans une table auxiliaire. Vous pouvez même le maintenir en ayant un déclencheur que la mise à jour et détient des informations concernant le nombre de lignes Salesderheader dépendant de CustomerId, bien sûr, vous pouvez alors simple magasin de la médiane.

pour les ensembles de données à grande échelle, vous pouvez essayer ce GIST:

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

cela fonctionne en agrégeant les valeurs distinctes que vous trouverez dans votre ensemble (comme les âges, ou l'année de naissance, etc.), et utilise les fonctions de fenêtre SQL pour localiser n'importe quelle position de percentile que vous spécifiez dans la requête.

Résultat Médian

C'est la méthode la plus simple pour trouver la médiane d'un attribut.

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)

AVG (column)

simmplest réponse possible