Jak efektywnie liczyć wystąpienia wartości kolumny w SQL?

166

Mam tabelę studentów:

id | age
--------
0  | 25
1  | 25
2  | 23

Chcę zapytać o wszystkich uczniów i dodatkową kolumnę zliczającą, ilu uczniów jest w tym samym wieku:

id | age | count
----------------
0  | 25  | 2
1  | 25  | 2
2  | 23  | 1

Jaki jest najskuteczniejszy sposób na zrobienie tego? Obawiam się, że zapytanie podrzędne będzie powolne i zastanawiam się, czy istnieje lepszy sposób . Jest tu?

sql performance

— Assaf Lavie
źródło

256

To powinno działać:

SELECT age, count(age) 
  FROM Students 
 GROUP by age

Jeśli potrzebujesz również identyfikatora, możesz dołączyć powyższe jako zapytanie podrzędne, na przykład:

SELECT S.id, S.age, C.cnt
  FROM Students  S
       INNER JOIN (SELECT age, count(age) as cnt
                     FROM Students 
                    GROUP BY age) C ON S.age = C.age

— Mike Dinescu
źródło

2

dla drugiego zapytania zewnętrzny wybór powinien być na C.cnt, ponieważ nie ma S.cnt, w przeciwnym razie pojawi się błąd: Nieprawidłowa nazwa kolumny „cnt”

— KM.

1

daje mi błąd, gdy używam select case_id, count (pgm_code) z grupy pgm przez pgm_code; mówi, że nie jest to grupa według wyrażenia

— Rishabh Agarwal

26

Jeśli korzystasz z Oracle, wystarczy funkcja analityczna. To wygląda tak:

select id, age, count(*) over (partition by age) from students;

Jeśli nie korzystasz z Oracle, musisz ponownie dołączyć do liczenia:

select a.id, a.age, b.age_count
  from students a
  join (select age, count(*) as age_count
          from students
         group by age) b
    on a.age = b.age

— Jeremy Bourque
źródło

2

FYI, w SQL Server 2005 drugie zapytanie jest uruchamiane z prawie połową kosztów wykonania (przy użyciu SET SHOWPLAN_ALL ON ) jako pierwsze. Myślałem, że pierwsza byłaby lepsza, ale dołączenie starej szkoły pokonało to.

— KM.

1

„old school join beat it” po prostu dlatego, że CAŁKOWITA LICZBA WIERSZY do przetworzenia jest inna. W drugim zapytaniu jest osadzone grupowanie, które potencjalnie znacznie zmniejsza liczbę wierszy. Spróbuj dodać DISTINCT do pierwszego zapytania: „wybierz DISTINCT identyfikator, wiek, licz (*) na (podział według wieku) uczniów” - to powinno być porównywalne

— quetzalcoatl

19

Oto inne rozwiązanie. ten używa bardzo prostej składni. Pierwszy przykład przyjętego rozwiązania nie działał na starszych wersjach Microsoft SQL (tj. 2000)

SELECT age, count(*)
FROM Students 
GROUP by age
ORDER BY age

— Damian
źródło

1

Jeśli jednak pogrupujesz według wieku, dostaniesz tylko jedno zgłoszenie dla wieku 25 lat z liczbą 2 (kiedy faktycznie chcą 2 wpisy z liczbą 2 i oddzielnymi identyfikatorami dla podanego przykładu)?

— Ian

1

Ian, dzięki za informację zwrotną. Czy wykonałeś swoje roszczenie wobec bazy danych MS SQL 2000?

— Damian

7

Zrobiłbym coś takiego:

select
 A.id, A.age, B.count 
from 
 students A, 
 (select age, count(*) as count from students group by age) B
where A.age=B.age;

— quosoo
źródło

4

select s.id, s.age, c.count
from students s
inner join (
    select age, count(*) as count
    from students
    group by age
) c on s.age = c.age
order by id

— RedFilter
źródło

1

a jeśli dane w kolumnie „wiek” mają podobne rekordy (np. wiele osób ma 25 lat, wiele innych 32 itd.), powoduje to zamieszanie w wyrównywaniu liczby uczniów do każdego ucznia. aby tego uniknąć, dołączyłem do tabel na legitymacji studenckiej.

SELECT S.id, S.age, C.cnt
FROM Students S 
INNER JOIN (SELECT id, age, count(age) as cnt  FROM Students GROUP BY student,age) 
C ON S.age = C.age *AND S.id = C.id*

— afii_palang
źródło