Mysql pk 찾기 - Mysql pk chajgi

Question

DATA 전문가로 가는 길

테이블에 존재하는 순서 번호 값 중에서 빈 값을 찾는 쿼리를 작성하겠습니다.

순차적으로 번호가 1번부터 20번까지 존재한다고 했을 때 5번, 11번 ~ 14번, 17번 ~ 18번 이렇게 빈 값이 존재할 경우 찾아내는 방법과 빈 값에 대한 범위를 찾는 방법 그리고 마지막으로 어느 정도 비어있는지 확인 가능합니다.

1. 샘플 데이터

- 샘플 데이터를 생성하고, 임시 데이터를 삽입합니다.

DROP TABLE EST_SEQUENCE;

CREATE TABLE EST_SEQUENCE (ID INT NOT NULL PRIMARY KEY) ENGINE=INNODB;

INSERT INTO EST_SEQUENCE(ID)
    SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
    UNION ALL SELECT 15
    UNION ALL SELECT 16
    UNION ALL SELECT 19
    UNION ALL SELECT 20
;

2. 순차적 번호 중에서 빈 값 찾기

- 아래 쿼리 중에서 seq_1_to_10000을 이용해서 로우 데이터를 생성하고, 실제 검수해야 될 테이블을 LEFT OUTER JOIN을 해서 확인합니다.

- 실제 테이블 데이터 중에서 가장 큰 값을 비교해서 적용하면 성능적으로 개선됩니다.

- "r_total_time_ms": 0.1657, => 서브 쿼리 사용 할 때
- "r_total_time_ms": 1.3515, => 서브쿼리 사용 안 할 때

SELECT   A.SEQ
FROM     seq_1_to_10000 A
         LEFT OUTER JOIN EST_SEQUENCE B ON (B.ID = A.SEQ)
WHERE    B.ID IS NULL
AND      A.SEQ <= (SELECT  MAX(SQ1.ID)   
                   FROM    EST_SEQUENCE SQ1
                  )
;

[결과]

|SEQ                 |
|--------------------|
|5                   |
|11                  |
|12                  |
|13                  |
|14                  |
|17                  |
|18                  |

3. 순차적 번호 중에서 빈 값에 대한 사이 값 찾기

SELECT   CONCAT(X.EXPECTED, IF(X.GOT-1>X.EXPECTED, CONCAT(' ~ ',X.GOT-1), '')) AS MISSING
FROM     (
         SELECT
                  @ROWNUM:=@ROWNUM+1 AS EXPECTED,
                  IF(@ROWNUM=B.ID, 0, @ROWNUM:=B.ID) AS GOT
         FROM     (SELECT @ROWNUM:=0) AS A
                  INNER JOIN EST_SEQUENCE B
         ORDER BY B.ID 
         ) AS X
WHERE X.GOT!=0;

[결과]

|MISSING                                                                                      |
|---------------------------------------------------------------------------------------------|
|5                                                                                            |
|11 ~ 14                                                                                      |
|17 ~ 18                                                                                      |

4. 순차적 번호 중에서 빈 값에 대한 수 구하기

SELECT   X.CURRENT_ID AS ID
        ,X.MIN_ID AS NEXT_ID
        ,(X.MIN_ID - X.CURRENT_ID) -1 AS MISSING_INBETWEEN
FROM     (
         SELECT   A1.ID AS CURRENT_ID , MIN(A2.ID) AS MIN_ID 
         FROM     EST_SEQUENCE  AS A1
         LEFT OUTER JOIN EST_SEQUENCE AS A2 ON A2.ID > A1.ID
         WHERE    A1.ID <= 100
         GROUP BY A1.ID
         ) AS X
WHERE    X.MIN_ID > X.CURRENT_ID + 1
;

[결과]

|ID         |NEXT_ID    |MISSING_INBETWEEN|
|-----------|-----------|-----------------|
|4          |6          |1                |
|10         |15         |4                |
|16         |19         |2                |

DB 쿼리문을 작성 할 때, 특정 컬럼의 최대 값, 최소 값을 구해야할 경우가 있습니다.
최대 값, 최소 값을 구하기는 쉽지만, 최대, 최소 값을 가진 Row를 조회해야하는 경우 원하는 결과가 안나올 수 있습니다.
이때 사용할 수 있는 몇가지 방법을 알려드리겠습니다.
(더 좋은 방법이 있다면 댓글로 남겨주세요!)

테이블 생성 및 데이터 삽입 쿼리입니다.

CREATE TABLE `employee` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NULL DEFAULT NULL,
	`salary` INT(11) NULL DEFAULT NULL,
	`dept_id` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `fk_department_id` (`dept_id`),
	CONSTRAINT `fk_department_id` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`)
)

CREATE TABLE `department` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)

INSERT INTO `department` (`id`, `name`) VALUES (1, '사업부');
INSERT INTO `department` (`id`, `name`) VALUES (2, '개발부');
INSERT INTO `department` (`id`, `name`) VALUES (3, '영업부');

INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (6, 'hong', 5500, 1);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (3, 'lee', 7000, 1);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (5, 'jang', 4000, 1);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (8, 'lim', 3400, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (7, 'sin', 2800, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (1, 'kim', 3000, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (4, 'go', 3400, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (2, 'park', 2600, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (9, 'chang', 4400, 3);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (10, 'ho', 3600, 3);

1. employee 테이블에서 최고 연봉을 받는 Row 조회하기

1) order by 사용하기

select 
  b.name,
  a.name, 
  a.salary
from employee a
join department b on (b.id = a.dept_id)
ORDER BY a.salary desc
limit 1;

#결과
#사업부 lee 7000

연봉을 내림차순하고 한개만 조회하도록하여 연봉이 가장 높은 row를 조회하는 방법입니다.
최고 연봉을 가진 사람이 여러명이어도 한명만 조회됩니다. (정렬 조건에 따라서 출력되는 row가 달라집니다.)

2) where 절에 서브쿼리 사용하기

select 
  b.name,
  a.name, 
  a.salary
from employee a
join department b on (b.id = a.dept_id)
WHERE a.salary = (SELECT MAX(a.salary) from employee a)

#결과
#사업부 lee 7000

where절에 서브쿼리를 이용하는 방법입니다. 서브쿼리 구문을 통해 최고 연봉 값을 구하고 해당 값과 같은 연봉을 가진 row를 조회합니다. 최고 연봉가진 사람이 두명이라면 두개의 행이 출력됩니다.

2. 부서 별로 가장 높은 연봉을 받는 Row 조회하기

select 
  b.name,
  a.name, 
  a.salary
from employee a
join department b on (b.id = a.dept_id)
join (
	select max(salary) as 'max_salary'
	from employee
	group by dept_id
) c on (a.salary = c.max_salary);

#결과
#사업부 lee 7000
#개발부 go 3400
#개발부 lim 3400
#영업부 chang 4400