Odliczanie SQL różni się od partycji

Mam tabelę z dwiema kolumnami, chcę policzyć różne wartości na Col_B powyżej (warunkowane przez) Col_A.

Mój stół

Col_A | Col_B 
A     | 1
A     | 1
A     | 2
A     | 2
A     | 2
A     | 3
b     | 4
b     | 4
b     | 5

Spodziewany wynik

Col_A   | Col_B | Result
A       | 1     | 3
A       | 1     | 3
A       | 2     | 3
A       | 2     | 3
A       | 2     | 3
A       | 3     | 3
b       | 4     | 2
b       | 4     | 2
b       | 5     | 2

Wypróbowałem następujący kod

select *, 
count (distinct col_B) over (partition by col_A) as 'Result'
from MyTable

count (odrębny col_B) nie działa. Jak mogę przepisać funkcję count, aby policzyć różne wartości?

Oto jak bym to zrobił:

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY (   SELECT COUNT(DISTINCT mt2.Col_B) AS dc
                FROM   #MyTable AS mt2
                WHERE  mt2.Col_A = mt.Col_A
                -- GROUP BY mt2.Col_A 
            ) AS ca;

GROUP BYKlauzula jest zbędny, biorąc pod uwagę dane dostarczone w odpowiedzi na pytanie, ale może dać lepsze plan wykonania. Zobacz dalsze pytania i odpowiedzi KRZYŻ APLIKUJ tworzy sprzężenie zewnętrzne .

Rozważ głosowanie na żądanie rozszerzenia klauzuli OVER - klauzula DISTINCT dla funkcji agregujących w witrynie z opiniami, jeśli chcesz tę funkcję dodać do SQL Server.

Możesz go emulować za pomocą dense_rank, a następnie wybrać maksymalną rangę dla każdej partycji:

select col_a, col_b, max(rnk) over (partition by col_a)
from (
    select col_a, col_b
        , dense_rank() over (partition by col_A order by col_b) as rnk 
    from #mytable
) as t    

Trzeba by wykluczyć wszelkie wartości null z col_baby uzyskać takie same wyniki jak COUNT(DISTINCT).

Jest to w pewnym sensie rozszerzenie rozwiązania Lennarta , ale jest tak brzydkie, że nie śmiem sugerować go jako edycji. Celem jest uzyskanie wyników bez tabeli pochodnej. Może nigdy nie będzie takiej potrzeby, a w połączeniu z brzydotą zapytania całe przedsięwzięcie może wydawać się zmarnowanym wysiłkiem. Nadal chciałem to zrobić jako ćwiczenie i chciałbym teraz podzielić się moim wynikiem:

SELECT
  Col_A,
  Col_B,
  DistinctCount = DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B ASC )
                + DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B DESC)
                - 1
                - CASE COUNT(Col_B) OVER (PARTITION BY Col_A)
                  WHEN COUNT(  *  ) OVER (PARTITION BY Col_A)
                  THEN 0
                  ELSE 1
                  END
FROM
  dbo.MyTable
;

Podstawowa część obliczeń jest następująca (i przede wszystkim chciałbym zauważyć, że pomysł nie jest mój, dowiedziałem się o tej sztuczce gdzie indziej):

  DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B ASC )
+ DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B DESC)
- 1

Tego wyrażenia można używać bez żadnych zmian, jeśli Col_Bgwarantowane wartości nigdy nie będą miały wartości null. Jeśli jednak kolumna może mieć wartości null, należy to uwzględnić, i właśnie do tego CASEsłuży wyrażenie. Porównuje liczbę wierszy na partycję z liczbą Col_Bwartości na partycję. Jeśli liczby się różnią, oznacza to, że niektóre wiersze mają wartość null Col_B, dlatego wstępne obliczenia ( DENSE_RANK() ... + DENSE_RANK() - 1) należy zmniejszyć o 1.

Zauważ, że ponieważ - 1jest to część podstawowej formuły, postanowiłem tak to zostawić. Można go jednak włączyć do CASEwyrażenia w bezskutecznej próbie sprawienia, by całe rozwiązanie wyglądało mniej brzydko:

SELECT
  Col_A,
  Col_B,
  DistinctCount = DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B ASC )
                + DENSE_RANK() OVER (PARTITION BY Col_A ORDER BY Col_B DESC)
                - CASE COUNT(Col_B) OVER (PARTITION BY Col_A)
                  WHEN COUNT(  *  ) OVER (PARTITION BY Col_A)
                  THEN 1
                  ELSE 2
                  END
FROM
  dbo.MyTable
;

To demo na żywo w db <> fiddle.uk może być użyte do przetestowania obu wariantów rozwiązania.

create table #MyTable (
Col_A varchar(5),
Col_B int
)

insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)

insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)


;with t1 as (

select t.Col_A,
       count(*) cnt
 from (
    select Col_A,
           Col_B,
           count(*) as ct
      from #MyTable
     group by Col_A,
              Col_B
  ) t
  group by t.Col_A
 )

select a.*,
       t1.cnt
  from #myTable a
  join t1
    on a.Col_A = t1.Col_a

Alternatywnie, jeśli jesteś uczulony na skorelowane podzapytania (odpowiedź Erika Darlinga) i CTE (odpowiedź kevinnwhat) jak ja.

Pamiętaj, że gdy do miksu zostaną wprowadzone wartości zerowe, żaden z nich może nie działać tak, jak byś tego chciał. (ale dość łatwo jest je zmodyfikować do smaku)

Prosty przypadek:

--ignore the existence of nulls
SELECT [mt].*, [Distinct_B].[Distinct_B]
FROM #MyTable AS [mt]

INNER JOIN(
    SELECT [Col_A], COUNT(DISTINCT [Col_B]) AS [Distinct_B]
    FROM #MyTable
    GROUP BY [Col_A]
) AS [Distinct_B] ON
    [mt].[Col_A] = [Distinct_B].[Col_A]
;

To samo co powyżej, ale z komentarzami na temat tego, co zmienić w przypadku obsługi wartości zerowej:

--customizable null handling
SELECT [mt].*, [Distinct_B].[Distinct_B]
FROM #MyTable AS [mt]

INNER JOIN(
    SELECT 

    [Col_A],

    (
        COUNT(DISTINCT [Col_B])
        /*
        --uncomment if you also want to count Col_B NULL
        --as a distinct value
        +
        MAX(
            CASE
                WHEN [Col_B] IS NULL
                THEN 1
                ELSE 0
            END
        )
        */
    )
    AS [Distinct_B]

    FROM #MyTable
    GROUP BY [Col_A]
) AS [Distinct_B] ON
    [mt].[Col_A] = [Distinct_B].[Col_A]
/*
--uncomment if you also want to include Col_A when it's NULL
OR
([mt].[Col_A] IS NULL AND [Distinct_B].[Col_A] IS NULL)
*/