MySQL 집계 함수: 합계, 개수 AVG & 맥스

집계 함수란 무엇인가요? MySQL?

An 집계 함수 단일 열의 여러 행을 읽어 하나의 값으로 통합합니다. 집계 함수는 다음과 같은 기능을 합니다.

• 여러 행에 대한 계산 수행
• 테이블의 단일 열
• 그리고 단일 값을 반환합니다.

ISO 표준은 다음과 같은 5가지 집계 함수를 정의합니다.

1. COUNT
2. SUM
3. AVG
4. MIN
5. MAX

다섯 가지 모두에 적용되는 규칙은 하나입니다. 집계 함수는 NULL 값을 무시합니다.COUNT(*)는 유일한 예외이며, 그 이유는 아래에서 살펴보겠습니다.

집계 함수를 사용하는 이유

조직의 각 계층은 필요한 정보가 다릅니다. 최고 경영진은 일반적으로 개별적인 세부 정보보다는 전체적인 수치에 관심이 있습니다.

집계 함수를 사용하면 데이터베이스에서 요약된 데이터를 쉽게 생성할 수 있습니다.

예를 들어, 마이플릭스 데이터베이스에서 경영진은 다음과 같은 보고서를 필요로 할 수 있습니다.

• 최소 대여 영화.
• 가장 많이 대여한 영화.
• 각 영화가 한 달에 대여되는 평균 횟수.

위의 보고서는 모두 집계 함수에서 나온 결과입니다. 각각을 자세히 살펴보겠습니다.

카운트 기능

COUNT 함수는 숫자형 및 비숫자형 데이터 유형 모두에서 지정된 필드의 총 값 개수를 반환합니다. 다른 모든 집계 함수와 마찬가지로 COUNT(column) 함수는 NULL 값을 제외합니다.

COUNT(*)는 테이블의 모든 행의 개수를 반환하는 특수한 형식입니다. 또한 특정 행의 개수도 계산합니다. NULL 그리고 값 대신 행 수를 세기 때문에 중복이 발생합니다.

영화 대여 테이블에는 다음 데이터가 포함되어 있습니다.

ID가 2인 영화가 대여된 횟수를 구하고 싶다고 가정해 봅시다.

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

이것을 실행하면 MySQL 워크 벤치 myflixdb에 대한 응답은 3입니다. 왜냐하면 세 행 모두 movie_id가 2이기 때문입니다.

고유 키워드

COUNT는 "몇 개인지"에 대한 답을 제시합니다. 그다음 질문은 대개 "몇 개인지"입니다. 다른 DISTINCT는 바로 그러한 목적을 위해 만들어졌습니다.

DISTINCT 키워드는 그룹별 결과에서 중복 항목을 제거합니다.ping 위의 그림에서 보여주는 것처럼 동일한 값들을 함께 배치합니다.

먼저 간단한 쿼리를 실행해 보겠습니다.

SELECT `movie_id` FROM `movierentals`;

이제 DISTINCT 키워드를 사용한 동일한 쿼리입니다.

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT는 중복 레코드를 제외합니다.

COUNT, COUNT(*), COUNT(DISTINCT): 어떤 것을 사용해야 할까요?

DISTINCT도 배치할 수 있습니다. 내부 집계 함수인데, 대부분의 초보자들이 여기서 어려움을 겪습니다. track개 행 중에서 실제로 계산되는 행의 수입니다. 아래 네 가지 양식은 모두 앞서 보여준 동일한 5행짜리 영화 대여 테이블을 대상으로 실행되지만, 모두 같은 결과를 반환하지는 않습니다. 이러한 차이는 두 가지 질문에서 비롯됩니다. 양식이 행을 계산하는지, 값을 계산하는지, 그리고 중복을 유지하는지 여부입니다.

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

💡 팁: 행 개수를 세려면 COUNT(*)를 사용하고, NULL 값이 "해당 없음"을 의미해야 하는 경우에는 COUNT(column)을 사용하며, 고유 값만 찾으려면 COUNT(DISTINCT column)을 사용합니다. DISTINCT의 반대는 ALL이며, 이는 기본값이므로 거의 사용되지 않습니다.

MIN 기능

MIN 함수 지정된 테이블 필드에서 가장 작은 값을 반환합니다..

우리가 보유한 영화 라이브러리 중 가장 오래된 영화의 개봉 연도를 알고 싶다고 가정해 봅시다. MySQL's MIN 함수는 그것을 알려줍니다.

SELECT MIN(`year_released`) FROM `movies`;

결과 :

MAX 기능

이름에서 알 수 있듯이 MAX 함수는 MIN 함수의 반대입니다. 그것 지정된 테이블 필드에서 가장 큰 값을 반환합니다..

데이터베이스에 있는 최신 영화의 개봉 연도를 알고 싶다고 가정해 보겠습니다. 다음 예제는 해당 연도를 반환합니다.

SELECT MAX(`year_released`) FROM `movies`;

결과 :

SUM 함수

MIN과 MAX는 열에서 기존 값을 선택합니다. SUM과 AVG 전체 열을 이용하여 새로운 숫자를 계산합니다.

지금까지 이루어진 총 지불액을 알고 싶다고 가정해 봅시다. MySQL SUM 기능 지정된 열의 모든 값의 합계를 반환합니다.. SUM은 숫자 필드에서만 작동합니다.예산 및 NULL 값은 결과에서 제외됩니다..

다음 표는 지불 내역을 보여줍니다.

아래 쿼리는 모든 결제 내역을 가져와 합산하여 하나의 결과(2500 + 2000 + 6000 = 10500)를 보여줍니다.

SELECT SUM(`amount_paid`) FROM `payments`;

결과 :

AVG 기능

The MySQL AVG 기능 지정된 열에 있는 값의 평균을 반환합니다.. SUM 함수와 마찬가지로 숫자 데이터 유형에서만 작동합니다..

평균 지불액을 구하고 싶다고 가정해 보겠습니다. 이 경우 총액 10500을 NULL이 아닌 세 개의 지불 행으로 나누는 다음 쿼리를 사용할 수 있습니다.

SELECT AVG(`amount_paid`) FROM `payments`;

결과 :

⚠️ 경고: AVG 테이블의 행 수가 아니라 NULL이 아닌 행 수로 나눕니다. NULL 값은 0으로 계산되는 대신 건너뛰어지므로 평균값이 은근히 높아집니다. AVG(IFNULL(`amount_paid`, 0))는 결측값이 0을 의미하는 경우입니다.

실제 예시: 집계 함수와 GROUP BY 절의 결합

위의 각 함수는 표 전체에 대해 하나의 수치를 반환합니다. 추가하면 GROUP BY 절은 하나의 수치를 반환합니다. 그룹당 오히려, 바로 그런 방식으로 실제 보고서가 작성됩니다.

다음 예시는 구성원을 이름별로 그룹화한 다음, 각 구성원별 총 결제 횟수, 평균 결제 금액 및 총 결제 금액을 계산합니다.

SELECT m.`full_names`,
       COUNT(p.`payment_id`) AS `paymentscount`,
       AVG(p.`amount_paid`) AS `averagepaymentamount`,
       SUM(p.`amount_paid`) AS `totalpayments`
FROM members m, payments p
WHERE m.`membership_number` = p.`membership_number`
GROUP BY m.`full_names`;

위의 예제를 다음에서 실행 MySQL 워크벤치는 다음과 같은 결과를 보여줍니다.

이 쿼리는 WHERE 절에서 두 테이블을 조인합니다. 이는 예전 방식인 쉼표를 이용한 조인 방식입니다. 최신 코드는 동일한 논리를 명시적인 조인으로 표현합니다. 내부 참여 … 켜짐또한 SELECT 목록에 있는 집계되지 않은 모든 열은 GROUP BY 절에 포함되어야 합니다. MySQL 5.7 버전 이상에서는 ONLY_FULL_GROUP_BY에 대한 쿼리를 거부합니다. 자세한 내용은 다음을 참조하십시오. 공무원 MySQL 집계 함수 참조.

자주 묻는 질문