Computer >> 컴퓨터 >  >> 프로그래밍 >> MySQL

MySQL에서 WHERE 절과 GROUP BY, HAVING COUNT(*) > 1 조건 함께 사용하는 방법

WHERE 절과 HAVING COUNT(*) > 1 조건을 활용한 MySQL GROUP BY

WHERE 절과 함께 GROUP BY를 사용하는 방법을 이해하기 위해 먼저 예제용 테이블을 생성해 보겠습니다. 테이블 생성 쿼리는 다음과 같습니다.

mysql> create table GroupByWithWhereClause
    -> (
    -> Id int NOT NULL AUTO_INCREMENT,
    -> IsDeleted tinyint(1),
    -> MoneyStatus varchar(20),
    -> UserId int,
    -> PRIMARY KEY(Id)
    -> );
Query OK, 0 rows affected (0.57 sec)

생성된 테이블은 삭제 여부(IsDeleted), 금전 처리 상태(MoneyStatus), 사용자 ID(UserId) 세 개의 컬럼으로 구성되어 있습니다. 이제 INSERT 명령을 사용하여 샘플 데이터를 삽입합니다.

mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'Undone',101);
Query OK, 1 row affected (0.17 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',101);
Query OK, 1 row affected (0.19 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',101);
Query OK, 1 row affected (0.14 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',102);
Query OK, 1 row affected (0.18 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'Undone',102);
Query OK, 1 row affected (0.20 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'done',102);
Query OK, 1 row affected (0.59 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'Undone',103);
Query OK, 1 row affected (0.15 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',103);
Query OK, 1 row affected (0.20 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',103);
Query OK, 1 row affected (0.18 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',103);
Query OK, 1 row affected (0.10 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',104);
Query OK, 1 row affected (0.14 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'Undone',104);
Query OK, 1 row affected (0.12 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'Undone',105);
Query OK, 1 row affected (0.15 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'done',105);
Query OK, 1 row affected (0.26 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'done',105);
Query OK, 1 row affected (0.12 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',105);
Query OK, 1 row affected (0.24 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'Undone',106);
Query OK, 1 row affected (0.23 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',106);
Query OK, 1 row affected (0.16 sec)
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',106);
Query OK, 1 row affected (0.14 sec)

SELECT 문을 사용하여 테이블에 저장된 모든 레코드를 확인해 보겠습니다.

mysql> select *from GroupByWithWhereClause;

실행 결과는 다음과 같습니다.

+----+-----------+-------------+--------+
| Id | IsDeleted | MoneyStatus | UserId |
+----+-----------+-------------+--------+
|  1 |         0 | Undone      |    101 |
|  2 |         0 | done        |    101 |
|  3 |         0 | done        |    101 |
|  4 |         0 | done        |    102 |
|  5 |         1 | Undone      |    102 |
|  6 |         1 | done        |    102 |
|  7 |         0 | Undone      |    103 |
|  8 |         0 | done        |    103 |
|  9 |         0 | done        |    103 |
| 10 |         0 | done        |    103 |
| 11 |         0 | done        |    104 |
| 12 |         0 | Undone      |    104 |
| 13 |         1 | Undone      |    105 |
| 14 |         1 | done        |    105 |
| 15 |         1 | done        |    105 |
| 16 |         0 | done        |    105 |
| 17 |         0 | Undone      |    106 |
| 18 |         0 | done        |    106 |
| 19 |         0 | done        |    106 |
+----+-----------+-------------+--------+
19 rows in set (0.00 sec)

총 19개의 레코드가 저장되어 있으며, UserId는 101부터 106까지의 값을 가집니다. IsDeleted 값이 0인 레코드는 삭제되지 않은 유효한 데이터를 의미합니다.

다음은 WHERE 절과 GROUP BY, 그리고 HAVING 절을 함께 사용하는 핵심 쿼리입니다.

mysql> SELECT * FROM GroupByWithWhereClause
    -> WHERE IsDeleted= 0 AND MoneyStatus= 'done'
    -> GROUP BY SUBSTR(UserId,1,3)
    -> HAVING COUNT(*) > 1
    -> ORDER BY Id DESC;

실행 결과는 다음과 같습니다.

+----+-----------+-------------+--------+
| Id | IsDeleted | MoneyStatus | UserId |
+----+-----------+-------------+--------+
| 18 |         0 | done        |    106 |
|  8 |         0 | done        |    103 |
|  2 |         0 | done        |    101 |
+----+-----------+-------------+--------+
3 rows in set (0.00 sec)

쿼리 동작 원리 살펴보기

위 쿼리가 어떤 순서로 동작하는지 단계별로 정리하면 다음과 같습니다.

  • WHERE 절: IsDeleted가 0이고 MoneyStatus가 'done'인 레코드만 먼저 필터링합니다. 즉, 삭제되지 않았으면서 상태가 'done'인 데이터만 집계 대상이 됩니다.
  • GROUP BY: SUBSTR(UserId,1,3) 함수로 UserId의 앞 3자리를 기준으로 그룹화합니다. 이 예제에서는 UserId가 3자리 숫자이므로 사실상 각 UserId별로 그룹이 형성됩니다.
  • HAVING COUNT(*) > 1: 그룹화된 결과 중 레코드 수가 1보다 많은, 즉 2개 이상인 그룹만 최종 결과에 포함시킵니다.
  • ORDER BY Id DESC: Id를 기준으로 내림차순 정렬하여 출력합니다.

그 결과 UserId 101, 103, 106은 'done' 상태이면서 삭제되지 않은 레코드가 각각 2개 이상 존재하므로 최종 결과에 포함되었습니다. 반면 UserId 102와 105는 해당 조건을 만족하는 레코드가 1개뿐이거나 없어서 결과에서 제외되었습니다.

이처럼 WHERE 절은 그룹화 전에 행을 필터링하고, HAVING 절은 그룹화 후에 집계 결과를 필터링한다는 점을 기억하면 두 절의 차이를 명확하게 이해할 수 있습니다.