[MySQL] Group By 최신값, 최근값 데이터 가져오기 예제

 

예제 테이블

예제에 사용할 테이블은 mysql 샘플 데이터베이스의 sakila 데이터베이스의 rental 테이블입니다.

샘플 데이터베이스 다운로드 방법 바로가기

[MySQL] 샘플 데이터베이스 다운로드 방법

 

예제로 사용할 테이블의 DDL 구문입니다.

# rental 테이블
CREATE TABLE `rental` (
  `rental_id` int NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint unsigned NOT NULL,
  `customer_id` smallint unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# inventory 테이블
CREATE TABLE `inventory` (
  `inventory_id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint unsigned NOT NULL,
  `store_id` tinyint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  KEY `idx_fk_film_id` (`film_id`),
  KEY `idx_store_id_film_id` (`store_id`,`film_id`),
  CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

 

가장 최근 데이터 1건 가져오기

그룹 함수를 사용하여 rental 테이블에서 가장 최근에 rental 된 데이터 1건을 조회하는 쿼리입니다.

# 그룹 함수를 사용하여 가장 최근 데이터 1건 조회
select *
from sakila.rental
where rental_date = (select max(rental_date) from sakila.rental)
limit 1;

 

rental_date가 가장 큰 데이터와 일치하는 데이터를 조회하는 쿼리입니다.

날짜가 중복되는 경우, 조회 개수를 제한하기 위해 limit을 사용하였습니다.

 

 

order by와 limit을 사용해도 조회가 가능합니다.

# order by와 limit을 사용하여 가장 최근 데이터 1건 조회
select *
from sakila.rental
order by rental_date desc
limit 1;

 

order by는 중복된 데이터가 있는 경우, 어떻게 정렬할 지 처리하려면 order by 컬럼을 여러개 사용하는 것이 좋습니다.

(예를 들어, rental_date에 중복 데이터가 있는 경우 마지막 업데이트 시간의 최신순으로 정렬하려면 order by rental_date desc, last_update desc로 사용)

select *
from sakila.rental
order by rental_date desc, last_update desc
limit 1;

 

Group By 그룹별 최신값 가져오기

Group by로 그룹화된 데이터의 최신값을 조회하는 쿼리입니다.

예제 쿼리는 inventory_id 별 가장 최근에 렌탈된 날짜를 가져오는 쿼리입니다.

# inventory_id 별 rental_date 최신 날짜 조회
select inventory_id, max(rental_date)
from sakila.rental
group by inventory_id;

 

 

Group By 없으면 0 표시

group by 결과 집계된 데이터가 없으면 0으로 표시하는 예제입니다.

예제는 한번도 렌탈된 적이 없는 inventory는 렌탈 횟수를 0으로 표시하는 쿼리입니다.

# 한번도 렌탈된 적이 없는 inventory는 렌탈 횟수 0으로 표시
select inventory.inventory_id, ifnull(rental_count, 0) as rental_count
from sakila.inventory as inventory
left join (
select inventory_id, count(1) as rental_count
from sakila.rental
group by inventory_id) as rental_inventory on rental_inventory.inventory_id = inventory.inventory_id;

 

left join으로 inventory_id 별 카운트를 집계한 쿼리 결과를 조인한 후 카운트를 조회합니다.

ifnull을 사용한 이유는 조인 테이블과 매칭되는 컬럼이 없는 경우 null로 표시되는데, null로 표시되는 데이터를 0으로 표시하기 위해서 입니다.

 

 

 

읽으면 좋은 글

[MySQL] GROUP BY HAVING 사용법 및 예제

[MySQL] GROUP BY HAVING 사용법 및 예제