mysql 실무에 유용한 sql 로직 모음 #01

mysql 실무에 유용한 sql 로직 모음 #01

mysql 실무에 유용한 sql 로직 모음

안녕하세요.
mysql 또는 mariadb를 사용하시면서 실무에서 만나게 되는 다양한 sql 로직들에 대해서 정리를 해보도록 하겠습니다.
물론, 오라클이나 mssql등에서도 약간만 수정하시면 사용 하실 수 있는 로직들이니 한번씩 참고 하시면 되겠습니다.
많은 SQL 강의 수강생들이 문의 하시는 내용들 위주로 정리를 해보았습니다.
그럼 시작해 볼께요!

#우선 아래 데이터를 직접 mysql DB나 mariadb에 넣으시고 테스트 해보셔도 되고, 아니면 본인 DB에 있는 데이터에 맞게 컬럼등을 수정해서 사용 하시면 됩니다.

class_c_customer.csv

0.06MB

class_c_order.csv

0.94MB

class_date_sql.csv

0.32MB

class_table_ddl.sql

0.00MB

 

class_table_ddl.sql을 먼저 실행하고, 나머지 데이터를 insert 하시면 됩니다.

특정 컬럼의 유니크한 데이터 건수 카운트

우선 아래와 같은 테이블이 있습니다.

c_customer

여기에 STATE라는 컬럼이 있는데, 이는 고객의 주소로 등록된 주가 어디 인지를 나타내는 컬럼입니다.
당연히 중복이 있는 컬럼이겠죠? 여기에 어떤주들이 몇개 있는지 확인하는 SQL은 다음과 같이 두가지 방법으로 작성 할 수 있습니다.

select count(distinct STATE)
from c_customer;

여러분들이 count에 대해서는 잘 아실겁니다. 그 안에 distinct를 사용하여 STATE값에서 중복을 제거하고 count를 할 수 있습니다.

결과

 

 

 

다른 방법은 다음과 같습니다.

select count(*)
from (select STATE, count(*)
      from c_customer
      group by STATE) a;

group by와 inline view를 이용하는 방법 입니다. inline view에서 group by를 통하여 중복을 제거하고, 이를 다른 SQL로 감싸서 그 항목의 수를 count 하는 방법 입니다.

결과

#inline view의 참고 강의

2020.05.11 - [Database/sql 강의] - sql 독학 강의 # sub query 서브쿼리 16편 -sTricky

sql 독학 강의 # sub query 서브쿼리 16편 -sTricky

datetime 컬럼에서 날짜를 기준으로 group by 하기

다음과 같은 테이블 데이터가 있습니다.

c_order

위와 같이 데이터가 있고, 여기에서 날짜별로 몇건의 데이터가 있는지 세어보는 방법을 알아보겠습니다.
그 기준으로 우선 ORDERDATE 컬럼을 사용 하도록 하겠습니다.

select date_format(ORDERDATE, '%Y-%m-%d') as date, count(*)
from c_order
group by date_format(ORDERDATE, '%Y-%m-%d');

위와 같이 group by 절에 date_format 함수를 사용하여 년, 월, 일로 ORDERDATE 컬럼을 표현하면서 묶어줍니다. 그리고나서 count(*)를 하면 쉽게 일별 주문건수를 출력 할 수 있습니다.

결과

이러한 방법을 통해서 월별, 내지는 시간별로도 응용할 수 있습니다.

-- 월별
select date_format(ORDERDATE, '%Y-%m') as date, count(*)
from c_order
group by date_format(ORDERDATE, '%Y-%m');

-- 시간별
select date_format(ORDERDATE, '%Y-%m-%d %H') as date, count(*)
from c_order
group by date_format(ORDERDATE, '%Y-%m-%d %H');

#date_format 참고 강의

https://stricky.tistory.com/220

sql 독학 강의 # 단일행 함수 잘 사용 하기(날짜 함수) 6편 -sTricky

오늘 날짜의 데이터를 출력 하는 방법

자, 다음은 오늘 날짜의 데이터를 출력하는 방법 입니다.
날짜를 특정 날짜로 꼬집어 검색하는게 아니라, 실제로 실행하는 날짜에 맞는 데이터를 가지고 오는 방법에 대해서 알아보도록 하겠습니다.

우선, 다음과 같이 테이블 데이터가 있습니다.

date_sql

여기서, 오늘 날짜의 데이터만 조회하는 방법 두가지를 소개해 드리겠습니다.

select *
from date_sql
where order_date like concat(date_format(now(), '%Y-%m-%d'), '%');

select *
from date_sql
where date_format(order_date, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d');

위의 쿼리는 오늘날짜 출력 함수인 now()를 order_date에서 like 검색을 통해서 출력해 냅니다.
그리고 두번째 sql은 order_date를 date_format으로 년,월,일을 출력한 다음 오늘 날짜 now()를 년,월,일로 출력하여 = 검색을 합니다.

결국 검색 결과는 다음과 같습니다.

결과

여기서 특정 시간 데이터만 출력하려면 다음과 같이 할 수 있습니다.

select *
from date_sql
where date_format(order_date, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d')
and date_format(order_date, '%H') = '14';

위와 같이 and 조건으로 오늘 데이터중에서 14시 데이터만 출력하도록 해봤습니다.
결과는 다음과 같습니다.

결과

 

 

 

특정 상품의 첫번째 주문 데이터 불러오기

이번에는 특정 상품의 데이터를 기준으로 첫번째 주문 데이터를 불러오는 방법에 대해서 알아보겠습니다.
다음과 같이 c_order 테이블을 준비 합니다.

c_order

여기서 사진에 표시된것과 같이 PRODUCTID에 상품번호가 있습니다. 해당 상품 ID별로 몇건이 있는지 먼저 확인 해보겠습니다.

select PRODUCTID, count(*) as 주문건수
from c_order
group by PRODUCTID
order by count(*) desc;

위와 같이 group by 를 이용해서 각 PRODUCTID별로 몇건의 주문이 있었는지 확인 했습니다.
그리고 여기에서 각 PRODUCTID별로 첫번째로 들어온 주문의 주문정보를 불러와 보겠습니다.

여기서 주의 하실것이 단순하게, 첫번째 주문일자를 구하는것 이라면 group by를 통해서 min(ORDERDATE)로 뽑아 올수 있지만, 다른 컬럼들의 정보까지 다 가지고 오려면 group by 를 통해서 하기는 좀 복잡해 집니다. 또한 같은 시간에 주문한 경우 하나만 불러온다는 확실한 보장이 없기때문에 아래에서 소개해 드리는 로직을 사용하시는게 가장 확실 합니다.

크게 두 단계로 로직은 이루어 집니다. 첫번째는 각 상품별로 주문순서를 따주는것 입니다. 가장 먼저 주문한것이 1, 그다음이 2.. 3... 이런식으로요.

select o.*, row_number() over (partition by PRODUCTID order by ORDERDATE) as 주문순서
from c_order o;

위의 sql을 실행했을때 다음과 같은 결과가 나오게 됩니다.

결과

<row_number() over (partition by PRODUCTID order by ORDERDATE) as 주문순서> 라고 제가 select 절에 쓴건데, row_number 함수는 위와 같이 쓰시면 됩니다. 우선 over()에다가 PRODUCTID별로 partition by 를 하고, 나눈다는 의미가 되는거죠? 그리고 order by ORDERDATE, 즉, 주문일자별로 순서를 매기겠다는 의미가 됩니다. 그럼 그림에서와 같이 각 PRODUCTID별로 1번부터 번호가 매겨지기 시작합니다. 주문일자 순서대로요. 그럼 가장 먼저 주문한것에 1번이 매겨지게 되겠죠? 그러고나서 다음 단계로 sql을 계속 작성 해주시면 됩니다.

 

 

 

select *
from (select o.*, row_number() over (partition by PRODUCTID order by ORDERDATE) as 주문순서
      from c_order o) a
where 주문순서 = 1;

자, 여기서 가장 먼저 주문이 일어난 주문순서가 1인 데이터만 불러오면 우리가 원하던 첫번째 주문에 대한 데이터를 불러 올 수 있게 된 것 입니다. 간단하죠?

이렇게 몇가지 간단한 로직들을 소개해 드렸습니다.
다음 이시간에도 더 유용한 로직들을 소개해 드릴께요!
좋은 하루 되시길 바랍니다!!
감사합니다.

#기타 참고 자료

2021.09.30 - [Database/프로그래머스SQL] - 프로그래머스 SQL 답 GROUP BY 문제 풀이 #04

프로그래머스 SQL 답 GROUP BY 문제 풀이 #04

2021.10.21 - [python 기초강의] - python으로 excel 데이터 탭 별로 mysql DB로 이관 하기 (data migration)

python으로 excel 데이터 탭 별로 mysql DB로 이관 하기 (data migration)

2021.06.03 - [Database/mariaDB administrator] - [mysql 독학] 독학으로 예제따라 익히는 mysql/mariadb SQL 입문서 출간

[mysql 독학] 독학으로 예제따라 익히는 mysql/mariadb SQL 입문서 출간

by.sTricky