복붙노트

[SQL] SQL Server의 계산 중간에 기능

SQL

SQL Server의 계산 중간에 기능

MSDN에 따르면, 중간은 거래-SQL의 집계 함수로 사용할 수 없습니다. 그러나, 나는 그 ((가) 집계 함수, 사용자 정의 함수, 또는 다른 방법을 만들기 사용)이 기능을 만들 수를 알고 싶습니다.

어떻게이 작업을 수행하는 가장 좋은 방법은 (가능한 경우) 것 - 집계 쿼리 (숫자 데이터 유형을 가정) 중간 값의 계산을 허용?

해결법

  1. ==============================

    1.2019 업데이트 : 나는이 대답을 쓴 이후 10 년, 더 솔루션은 더 나은 결과를 얻을 수를 발견했다. 또한, 그 이후 SQL Server 릴리스 (특히 SQL 2012) 도입 한 새로운 T-SQL은이 계산의 중간 값에 사용할 수 있습니다. SQL Server 릴리스는 다양한 중간 솔루션의 반환 한 영향을 미칠 수의 쿼리 최적화를 향상되었습니다. 그물 그물, 내 원래 2009 포스트는 아직 확인하지만 현대 SQL 서버 애플 리케이션을위한에 더 나은 솔루션이있을 수 있습니다. https://sqlperformance.com/2012/08/t-sql-queries/median : 훌륭한 자원이다 2012이 문서에서보세요

    2019 업데이트 : 나는이 대답을 쓴 이후 10 년, 더 솔루션은 더 나은 결과를 얻을 수를 발견했다. 또한, 그 이후 SQL Server 릴리스 (특히 SQL 2012) 도입 한 새로운 T-SQL은이 계산의 중간 값에 사용할 수 있습니다. SQL Server 릴리스는 다양한 중간 솔루션의 반환 한 영향을 미칠 수의 쿼리 최적화를 향상되었습니다. 그물 그물, 내 원래 2009 포스트는 아직 확인하지만 현대 SQL 서버 애플 리케이션을위한에 더 나은 솔루션이있을 수 있습니다. https://sqlperformance.com/2012/08/t-sql-queries/median : 훌륭한 자원이다 2012이 문서에서보세요

    이 문서는 테스트 간단한 스키마 적어도 다음과 같은 패턴이 훨씬 더 빨리 다른 모든 대안보다 많은 것으로 판명. 이 솔루션은 빠른 (!) 테스트 느린 (PERCENTILE_CONT) 솔루션보다 373x했다. 이 트릭은 모든 경우에 실용적이지 않을 수있는 두 가지 별도의 쿼리가 필요합니다. 또한 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 서버 2014 이상에있어 특히, 다를 수 있습니다. 반환 한 당신의 중간 계산을 위해 중요하다면, 난 강력하게 시도하고 당신이 당신의 스키마에 가장 적합한 하나를 발견했는지 확인하기 위해이 문서에서 권장하는 옵션 몇 가지를를 규칙적-테스트하는 것이 좋습니다 것입니다.

    위의 링크 된 글이 내장 된 기능 373x 느린 가장 빠른 솔루션보다 발견하기 때문에 나는 또한,이 질문에 대한 다른 답변 중 하나를 추천 년대 (새 SQL 서버 2012) 함수 PERCENTILE_CONT를 사용하여 특히 조심해야 것입니다. 그것은이 격차는 이후 7 년 만에 개선되었습니다,하지만 난 다른 솔루션 대비 성능을 검증 할 때까지 개인적으로 큰 테이블에이 기능을 사용하지 않을 가능성이 있습니다.

    ORIGINAL 2009 POST는 다음과 같습니다 :

    극적으로 변화하는 성능이 작업을 수행 할 수있는 여러가지 방법이 있습니다. 여기서 중앙값, ROW_NUMBERs 및 성능 하나 특히 최적화 솔루션이다. 그것은 실제 I에 / O를 실행 중 발생하는 때 이것은 특히 최적의 솔루션입니다 - 다른 솔루션보다 더 비용이 많이 드는 보이지만, 그것은 훨씬 더 빨리 사실이다.

    해당 페이지는 다른 솔루션과 성능 테스트의 세부 사항에 대한 논의가 포함되어 있습니다. 중간 열의 값이 동일한 복수의 열이있는 경우에 disambiguator 같은 고유 열의 사용을 참고.

    모든 데이터베이스 성능 시나리오와 마찬가지로, 항상 실제 하드웨어에서 실제 데이터 솔루션을 테스트하려고 - 당신은 당신의 환경에서 SQL 서버의 최적화에 대한 변경 또는 특색은 일반적으로 - 빠른 솔루션을 느리게 만들 때 절대 모릅니다.

    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;
  2. ==============================

    2.당신은 SQL 2005을 사용하거나 더 나은 경우이 테이블의 단일 컬럼을위한 좋은, 단순 틱 중간 계산은 다음과 같습니다

    당신은 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
  3. ==============================

    3.SQL 서버 2012에서는 PERCENTILE_CONT를 사용한다 :

    SQL 서버 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/

  4. ==============================

    4.내 원래 신속한 답변했다 :

    내 원래 신속한 답변했다 :

    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 %는이 같은 위치에 따라 통계를 계산할 때 피할 수있는 데이터를 정렬한다.

    나는 아래의 코멘트에 로버트 Ševčík-Robajz에서 우수한 제안을 따르도록 답을 수정했습니다 :

    ;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

    당신이 데이터 항목의 짝수를 할 때 올바른 중간 및 값 백분위 수를 계산해야한다. 다시 말하지만, 주석 만 중간이 아닌 유통 백분위 전체를 원하는 절 경우 경우 마지막.

  5. ==============================

    5.더 나은 :

    더 나은 :

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

    마스터 자신에서, Itzik 벤 웨이 코 뮤니시!

  6. ==============================

    6.MS SQL 서버 2012 (이상) 정렬 된 값을 백분 특정 PERCENTILE_DISC 계산하는 기능을 갖는다. https://msdn.microsoft.com/en-us/library/hh231327.aspx - PERCENTILE_DISC (0.5)의 평균을 계산합니다

    MS SQL 서버 2012 (이상) 정렬 된 값을 백분 특정 PERCENTILE_DISC 계산하는 기능을 갖는다. https://msdn.microsoft.com/en-us/library/hh231327.aspx - PERCENTILE_DISC (0.5)의 평균을 계산합니다

  7. ==============================

    7.단순, 신속, 정확

    단순, 신속, 정확

    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)
  8. ==============================

    8.당신은이 SQL 서버에서 집계 함수를 만듭니다 사용하려는 경우이 방법으로 할 수 있습니다. 이런 식으로 이렇게하면 깨끗한 쿼리를 작성 할 수 있다는 이점이있다. 이 주이 과정은 매우 쉽게 백분위 값을 계산하도록 할 수있다.

    당신은이 SQL 서버에서 집계 함수를 만듭니다 사용하려는 경우이 방법으로 할 수 있습니다. 이런 식으로 이렇게하면 깨끗한 쿼리를 작성 할 수 있다는 이점이있다. 이 주이 과정은 매우 쉽게 백분위 값을 계산하도록 할 수있다.

    새 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

    그런 다음 컴파일하고 SQL Server 시스템에 DLL과 PDB 파일을 복사 및 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 (필드)

  9. ==============================

    9.중간에 설정을 기반으로 솔루션을 찾고있는 동안 난 그냥이 페이지를 건너 왔어요. 여기에 솔루션의 일부보고 후, 나는 다음과 함께했다. 희망은 / 일을하는 데 도움이됩니다.

    중간에 설정을 기반으로 솔루션을 찾고있는 동안 난 그냥이 페이지를 건너 왔어요. 여기에 솔루션의 일부보고 후, 나는 다음과 함께했다. 희망은 / 일을하는 데 도움이됩니다.

    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
  10. ==============================

    10.다음 쿼리는 하나의 열에서 값 목록에서 중간 값을 반환합니다. 그것은 또는 집계 함수와 함께 사용할 수 없습니다,하지만 당신은 여전히 ​​내부 SELECT의 WHERE 절을 하위 쿼리로 사용할 수 있습니다.

    다음 쿼리는 하나의 열에서 값 목록에서 중간 값을 반환합니다. 그것은 또는 집계 함수와 함께 사용할 수 없습니다,하지만 당신은 여전히 ​​내부 SELECT의 WHERE 절을 하위 쿼리로 사용할 수 있습니다.

    SQL 서버 2005 + :

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

    11.저스틴 보조금의 솔루션은 고체 난 당신이 주어진 파티션 키 내에서 중복 값의 수있을 때 제대로 정렬하지 않도록 ASC 중복 값의 행 번호는 순서가 끝날 것으로 보이지만.

    저스틴 보조금의 솔루션은 고체 난 당신이 주어진 파티션 키 내에서 중복 값의 수있을 때 제대로 정렬하지 않도록 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
  12. ==============================

    12.위의 저스틴의 예는 매우 좋다. 그러나 기본 키의 필요성은 매우 명확하게 명시해야합니다. 나는 키없이 야생에서 그 코드를 본 결과는 나쁘다.

    위의 저스틴의 예는 매우 좋다. 그러나 기본 키의 필요성은 매우 명확하게 명시해야합니다. 나는 키없이 야생에서 그 코드를 본 결과는 나쁘다.

    나는 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
  13. ==============================

    13.UDF를 쓰기에서 :

    UDF를 쓰기에서 :

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

    14.여기에 SQL에서 중간 계산을위한 다른 솔루션을 참조하십시오 : "MySQL의와 계산의 중간에 간단한 방법은"(솔루션은 대부분 벤더 독립적이다).

    여기에 SQL에서 중간 계산을위한 다른 솔루션을 참조하십시오 : "MySQL의와 계산의 중간에 간단한 방법은"(솔루션은 대부분 벤더 독립적이다).

  15. ==============================

    15."표"의 연속 가변 / 계수 "COL1 '의

    "표"의 연속 가변 / 계수 "COL1 '의

    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
  16. ==============================

    16.중간 찾기

    중간 찾기

    이 속성의 중간을 찾을 수있는 가장 간단한 방법입니다.

    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)
  17. ==============================

    17.나는 나 자신에 의해 해결책을 해결 싶었지만, 내 머리가 트립 길에 떨어졌다. 나는 그것이 작동 생각하지만, 아침에 그것을 설명 부탁하지 않습니다. :피

    나는 나 자신에 의해 해결책을 해결 싶었지만, 내 머리가 트립 길에 떨어졌다. 나는 그것이 작동 생각하지만, 아침에 그것을 설명 부탁하지 않습니다. :피

    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)
  18. ==============================

    18.

    --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]
  19. ==============================

    19.이것은 SQL 2000에서 작동합니다

    이것은 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

)
  20. ==============================

    20.무슨 일이 일어나고 어디 중간 값에서 오는 정확히 이해하기 쉽게로 매우 기초를 배우고 자신과 같은 초보자를 위해, 나는 개인적으로, 따라하기이 예를 쉽게 찾을 수 ...

    무슨 일이 일어나고 어디 중간 값에서 오는 정확히 이해하기 쉽게로 매우 기초를 배우고 자신과 같은 초보자를 위해, 나는 개인적으로, 따라하기이 예를 쉽게 찾을 수 ...

    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

    하지만 위의 코드 중 일부의 절대적인 경외!

  21. ==============================

    21.이것은 내가 가지고 올 수 있기 때문에 대답 간단하다. 내 데이터와 잘 일했다. 특정 값을 제외 할 경우 단지 내 선택에 where 절을 추가합니다.

    이것은 내가 가지고 올 수 있기 때문에 대답 간단하다. 내 데이터와 잘 일했다. 특정 값을 제외 할 경우 단지 내 선택에 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
  22. ==============================

    22.다음 솔루션은 이러한 가정 하에서 작동합니다 :

    다음 솔루션은 이러한 가정 하에서 작동합니다 :

    암호:

    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) ;
  23. ==============================

    23.

    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
  24. ==============================

    24.나는 몇 가지 대안과 노력하지만 내 데이터 레코드 값을 반복 한 탓으로 ROW_NUMBER 버전은 나를 위해 선택하지 않은 것 같다. 그래서 여기에 쿼리 나는 (NTILE있는 버전)를 사용 :

    나는 몇 가지 대안과 노력하지만 내 데이터 레코드 값을 반복 한 탓으로 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;
  25. ==============================

    25.이 GROUP BY와 상관 하위 쿼리 함께 위의 제프 앳 우드의 대답에 구축하는 것은 각 그룹의 평균을 얻을 수 있습니다.

    이 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
  26. ==============================

    26.자주, 우리는 전체 테이블에 있지만, 일부 ID에 대한 집계를 위해 단지 중간 값을 계산해야 할 수도 있습니다. 즉, 각각의 ID가 많은 레코드가 우리 테이블의 각 ID에 대한 계산의 중간. (: 좋은 성능과 많은 SQL에서 작동 @gdoron에 의해 편집 솔루션을 기반으로)

    자주, 우리는 전체 테이블에 있지만, 일부 ID에 대한 집계를 위해 단지 중간 값을 계산해야 할 수도 있습니다. 즉, 각각의 ID가 많은 레코드가 우리 테이블의 각 ID에 대한 계산의 중간. (: 좋은 성능과 많은 SQL에서 작동 @gdoron에 의해 편집 솔루션을 기반으로)

    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;

    희망이 도움이.

  27. ==============================

    27.귀하의 질문에 대해 제프 앳 우드는 이미 간단하고 효과적인 솔루션을 부여했다. 그러나, 당신은 SQL 코드가 당신을 도울 것입니다 아래에 평균을 계산하는 몇 가지 다른 방법을 찾고 있다면.

    귀하의 질문에 대해 제프 앳 우드는 이미 간단하고 효과적인 솔루션을 부여했다. 그러나, 당신은 SQL 코드가 당신을 도울 것입니다 아래에 평균을 계산하는 몇 가지 다른 방법을 찾고 있다면.

    테이블 직원 (급여 int)를 만들; 종업원 값 (8)에 삽입; 직원에 값 (23)를 삽입; 직원에 값 (45)를 삽입; 종업원 값 (123)에 삽입; 직원에 값 (93)를 삽입; 직원 값 (2342)에 삽입; 직원 값 (2238)에 삽입; 직원에서 *를 선택; @odd_even INT를 선언; @cnt INT를 선언; @middle_no INT를 선언; @ CNT = (직원에서 SELECT COUNT (*))를 설정; @middle_no = 설정 (@ CNT / 2) +1] THEN @ odd_even = 때 케이스 (@ 2 % CNT = 0) -1 0 ELSE END를 선택;  TBL (급여함으로써 종업원 군으로부터 선택 RNO 같은 급여 급여하여 ROW_NUMBER () 이상 (순서))에서 AVG (tbl.salary)을 선택하거나 여기서 tbl.rno=@middle_no tbl.rno = @ middle_no + @ odd_even;

    당신이 MySQL의에서 계산의 중간에 찾고 있다면,이 github의 링크는 도움이 될 것입니다.

  28. ==============================

    28.이것은 내가 생각할 수있는 중간 값을 찾기위한 최적의 솔루션입니다. 예제의 이름은 저스틴의 예를 기반으로합니다. 테이블에 대한 확인 인덱스를 확인 Sales.SalesOrderHeader는 순서대로 인덱스 컬럼 고객 ID 및 TotalDue로 존재한다.

    이것은 내가 생각할 수있는 중간 값을 찾기위한 최적의 솔루션입니다. 예제의 이름은 저스틴의 예를 기반으로합니다. 테이블에 대한 확인 인덱스를 확인 Sales.SalesOrderHeader는 순서대로 인덱스 컬럼 고객 ID 및 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

    최신 정보

    나는 하나 개의 배치에서 세 가지 방법과 각 쿼리의 배치 비용을 기반으로 쿼리를 실행하여 내 방법 저스틴 보조금과 제프 Atwoods 사이의 비교를했다, 그래서 나는 방법은 최상의 성능을 가지고있는 대한 비트 확실했다 있었다 :

    인덱스 없음 :

    그리고 색인

    나는 7,2 수백만 행 주위 결국 2 512까지하는 수단의 요인에 의해 약 14 000 행에서 더 많은 데이터를 생성하여 색인이있는 경우 쿼리를 확장하는 방법을 잘보기 위해 노력했다. 고객 ID의 고유 한 인스턴스가 일정하게 유지에 행의 비율이 비교 그래서, 나는 하나의 사본을했다마다 고유해야합니다 CustomeId 필드를했다합니다. 내가 나중에 인덱스를 재 구축이 I의 만났 실행을 수행하고있는 동안 나는이 값에 있던 데이터를 128 배 주위에서 안정 결과를 발견 :

    나는 성능, 고유의 고객 ID를 일정하게 행의 수를 확장하지만 유지하여 난 그냥 이런 짓을 내가 설정 때문에 새로운 테스트를 영향을받을 수 있는지 궁금. 대신 안정화의 자, 배치 비용 비율은 분기 유지, 대신의 I는 고유 한 ID 당 10000 행 주위에 결국 한 평균 당 고객 ID 당 약 20 행. 숫자 여기서

    나는 결과를 비교하여 올바른 각각의 방법을 구현 확인했다. 내 결론은 사용 방법 내가 한 인덱스가 존재하는 일반적으로 빠른입니다. 또한이 방법은이 문서에서이 특정 문제에 https://www.microsoftpressstore.com/articles/article.aspx?p=2314819&seqNum=5에 대한 권장 것입니다 것으로 나타났습니다

    더욱 더욱이 쿼리에 대한 후속 호출의 성능을 개선 할 수있는 방법은 보조 테이블에서 카운트 정보를 유지하는 것입니다. 당신은 물론 당신이 간단한 저장 중간뿐만 아니라 다음 수, 고객 ID에 의존하는 SalesOrderHeader 행의 수에 대한 트리거 해당 업데이트를함으로써 그것을 유지하고 정보를 보유 할 수있다.

  29. ==============================

    29.대규모 데이터 세트의 경우,이 GIST를 시도 할 수 있습니다 :

    대규모 데이터 세트의 경우,이 GIST를 시도 할 수 있습니다 :

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

    그것은 (등 나이, 또는 출생 연도와 같은) 당신이 당신의 세트에서 찾을 것 고유 한 값을 통합하여 작동하고, 쿼리에서 지정한 백분위 위치를 찾을 수 SQL 창 함수를 사용합니다.

  30. ==============================

    30.tempa로와

    tempa로와

    (

    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로

    (

    /* 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

    ) 중간 값)의 평균 (값을 선택

    에서 RN tempa에서

    ( select Ref from tempb);

    31. from https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL에서 100 년 달력 테이블을 만드는 방법 [마감]  (0) 2020.03.10
[SQL] MySQL의 쿼리의 FROM 절에서 오류가 발생 WHERE에 열 별칭을 사용하여  (0) 2020.03.10
[SQL] SQL Server의 숫자, 부동 소수점과 소수의 차이  (0) 2020.03.10
[SQL] 왼쪽 외부는 내 왼쪽 테이블에서 모든 행을 반환하지 않습니다하세요?  (0) 2020.03.10
[SQL] 당신은 MySQL의에서 WHERE 절에 별칭을 사용할 수 있습니까?  (0) 2020.03.10
,

티스토리툴바