RDBS/MYSQL

[프로그래머스]입양 시각 구하기(1)-MySQL

MoonSta 2023. 1. 7. 13:03

입양 시각 구하기(1)

출처 : https://school.programmers.co.kr/learn/courses/30/lessons/59412

프로그래머스

코드 중심의 개발자 채용. 스택 기반의 포지션 매칭. 프로그래머스의 개발자 맞춤형 프로필을 등록하고, 나와 기술 궁합이 잘 맞는 기업들을 매칭 받으세요.

programmers.co.kr

문제

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

NAME	TYPE	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_OUTCOME	VARCHAR(N)	FALSE

ANIMAL_OUTS 조회

SELECT ANIMAL_ID
     , ANIMAL_TYPE
     , DATETIME
     , NAME
     , SEX_UPON_OUTCOME
    FROM ANIMAL_OUTS;

ANIMAL_ID	ANIMAL_TYPE	DATETIME	NAME	SEX_UPON_OUTCOME
A349480	Dog	2013-12-22 11:30:00	Daisy	Spayed Female
A349733	Dog	2017-09-27 19:09:00	Allie	Spayed Female
A349990	Cat	2018-02-02 14:18:00	Spice	Spayed Female
A349996	Cat	2018-02-02 14:17:00	Sugar	Neutered Male
A350276	Cat	2018-01-28 17:51:00	Jewel	Spayed Female
A350375	Cat	2017-05-23 16:17:00	Meo	Neutered Male
A352555	Dog	2014-08-10 18:57:00	Harley	Spayed Female
A352713	Cat	2017-04-25 12:25:00	Gia	Spayed Female
A352872	Dog	2015-07-16 17:58:00	Peanutbutter	Neutered Male
A353259	Dog	2016-05-09 10:40:00	Bj	Neutered Male
A354540	Cat	2015-01-22 12:07:00	Tux	Neutered Male
A354597	Cat	2014-06-03 12:30:00	Ariel	Spayed Female
A354725	Dog	2015-08-29 15:17:00	Kia	Spayed Female
A354753	Dog	2017-04-22 11:27:00	Sammy	Neutered Male
A355519	Dog	2015-05-09 11:15:00	Faith	Spayed Female
A355688	Dog	2014-02-23 17:48:00	Shadow	Neutered Male
A355753	Dog	2015-09-19 18:08:00	Elijah	Neutered Male
A357021	Dog	2014-12-04 15:34:00	Queens	Spayed Female
A357444	Dog	2016-03-14 17:24:00	Puppy	Neutered Male
A357846	Dog	2016-03-17 18:32:00	Happy	Neutered Male
A358697	Dog	2015-02-12 18:50:00	Fuzzo	Neutered Male
A358879	Dog	2015-09-15 16:22:00	Simba	Neutered Male
A361391	Dog	2015-04-20 11:23:00	Baby Bear	Neutered Male
A362103	Dog	2014-11-19 15:35:00	Stitch	Neutered Male
A362137	Dog	2014-01-01 07:39:00	*Darcy	Spayed Female
A362383	Dog	2016-03-22 17:53:00	*Morado	Neutered Male
A362707	Dog	2017-01-10 10:44:00	Girly Girl	Spayed Female
A362967	Dog	2014-06-15 11:14:00	Honey	Spayed Female
A363653	Dog	2014-11-18 11:55:00	Goofy	Neutered Male
A364429	Dog	2015-09-28 18:13:00	Hugo	Neutered Male
A365172	Dog	2014-08-30 16:47:00	Diablo	Neutered Male
A365302	Dog	2017-01-08 17:29:00	Minnie	Spayed Female
A367012	Dog	2015-09-16 13:07:00	Miller	Neutered Male
A367438	Dog	2015-09-12 13:30:00	Cookie	Spayed Female
A367747	Dog	2014-11-04 08:34:00	Woody	Neutered Male
A368742	Dog	2018-02-03 16:06:00	Stormy	Spayed Female
A368930	Dog	2014-06-13 15:52:00		Spayed Female
A370439	Dog	2016-06-25 14:15:00	Sniket	Neutered Male
A370507	Cat	2015-08-15 09:24:00	Emily	Spayed Female
A370852	Dog	2013-11-08 13:19:00	Katie	Spayed Female
A371000	Cat	2015-10-02 12:19:00	Greg	Neutered Male
A371102	Dog	2015-08-03 12:19:00	Ceballo	Neutered Male
A371344	Dog	2015-05-11 15:43:00	Sailor	Neutered Male
A371534	Dog	2016-06-07 18:55:00	April	Spayed Female
A373219	Cat	2014-08-05 16:16:00	Ella	Spayed Female
A373687	Dog	2014-04-08 14:05:00	Rosie	Spayed Female
A375393	Dog	2015-06-13 14:22:00	Dash	Neutered Male
A376322	Dog	2014-02-18 16:53:00	Mama Dog	Spayed Female
A376459	Dog	2017-07-09 13:53:00	Dora	Spayed Female
A377750	Dog	2017-10-26 14:19:00	Lucy	Spayed Female
A378348	Dog	2014-04-23 07:43:00	Frijolito	Neutered Male
A378353	Dog	2014-08-02 12:31:00	Lyla	Intact Female
A378818	Dog	2014-07-07 16:34:00	Zoe	Spayed Female
A378946	Dog	2017-10-14 18:31:00	Mercedes	Spayed Female
A379998	Dog	2013-10-25 12:53:00	Disciple	Intact Male
A380009	Dog	2016-02-12 14:32:00	Pickle	Spayed Female
A380320	Dog	2014-02-05 14:26:00	Scooby	Neutered Male
A380420	Dog	2018-02-22 07:06:00	Laika	Spayed Female
A380506	Dog	2016-01-23 12:43:00	Ruby	Spayed Female
A381173	Dog	2014-08-06 17:12:00	Pepper	Spayed Female
A381217	Dog	2017-06-09 18:51:00	Cherokee	Neutered Male
A382192	Dog	2015-03-16 13:46:00	Maxwell 2	Neutered Male
A382251	Dog	2014-11-08 19:01:00	Princess	Spayed Female
A383036	Cat	2014-06-28 13:40:00	Oreo	Neutered Male
A383964	Dog	2017-02-05 17:56:00	Finney	Neutered Male
A384360	Cat	2014-07-16 18:34:00	Jj	Neutered Male
A384568	Cat	2014-12-28 13:47:00	Jedi	Neutered Male
A385442	Dog	2014-01-11 16:00:00	Clyde	Neutered Male
A386005	Dog	2015-09-26 11:15:00	Giovanni	Neutered Male
A386276	Cat	2015-12-28 16:24:00	Tiko	Neutered Male
A386688	Dog	2015-08-29 17:46:00	Punch	Neutered Male
A387083	Dog	2014-02-12 11:12:00	Goldie	Spayed Female
A387965	Dog	2014-06-27 11:13:00	Dakota	Spayed Female
A388360	Dog	2015-12-26 13:19:00	Spider	Neutered Male
A388691	Dog	2015-11-29 18:40:00	Blaze	Neutered Male
A390222	Dog	2013-12-15 17:10:00	Holly	Spayed Female
A391512	Dog	2016-04-06 18:14:00	Rome	Neutered Male
A391858	Dog	2017-03-19 18:12:00	Nellie	Spayed Female
A392027	Dog	2014-01-31 18:55:00	Penny	Spayed Female
A392075	Dog	2013-11-20 17:04:00	Skips	Neutered Male
A392615	Dog	2015-07-26 15:18:00	Chip	Neutered Male
A394547	Dog	2015-01-25 11:15:00	Snickerdoodl	Spayed Female
A395451	Dog	2016-01-03 15:54:00	Logan	Neutered Male
A396810	Dog	2016-12-23 13:19:00	Raven	Spayed Female
A397882	Dog	2017-07-12 16:41:00	Charlie	Neutered Male
A399421	Dog	2015-08-25 18:23:00	Lucy	Spayed Female
A399552	Dog	2013-10-18 13:46:00	Jack	Neutered Male
A400498	Dog	2016-10-15 13:57:00	Reggie	Neutered Male
A400680	Dog	2017-10-02 14:35:00	Lucy	Spayed Female
A403564	Dog	2013-11-18 18:51:00	Anna	Spayed Female
A405494	Dog	2014-05-20 11:44:00	Kaila	Spayed Female
A406756	Dog	2016-05-14 11:30:00	Sabrina	Spayed Female
A407156	Dog	2016-10-28 13:22:00	Jake	Neutered Male
A408035	Dog	2014-12-27 12:59:00	Lizzie	Spayed Female
A409637	Dog	2016-04-07 18:33:00	Stanley	Neutered Male
A410330	Dog	2016-09-13 13:46:00	Chewy	Spayed Female
A410668	Cat	2016-06-12 11:46:00	Raven	Spayed Female
A410684	Cat	2014-06-27 17:50:00	Mitty	Spayed Female
A412173	Dog	2015-08-08 12:22:00	Jimminee	Neutered Male
A412626	Dog	2016-03-28 13:15:00	*Sam	Neutered Male

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

입출력 예

SQL문을 실행하면 다음과 같이 나와야 합니다.

HOUR	COUNT
9	1
10	2
11	13
12	10
13	14
14	9
15	7
16	10
17	12
18	16
19	2

풀이 보기

Key

📌HOUR 함수를 사용하여 시간 값 DATETIME의 시간값만 반환

📌시간에 대한 GROUP BY

2022.12.25 - [MYSQL] - [MYSQL]GROUP BY(그룹화

[MYSQL]GROUP BY(그룹화)

⭐GROUP BY란? SQL에 대해 학습하면서 꼭 알아야 할 구문 중 하나인 GROUP BY에 대해 알아보도록 하겠습니다. 실제 데이터를 조회하면서 데이터를 집계해야 할 상황이 생깁니다. 이러한 상황에서 GROUP

mooonstar.tistory.com

📌Query

SELECT HOUR(DATETIME) AS HOUR
     , count(HOUR(DATETIME)) AS COUNT
    FROM ANIMAL_OUTS
    WHERE  HOUR(DATETIME) >=9 and HOUR(DATETIME) < 20
   group by HOUR(DATETIME)
  ORDER BY HOUR(DATETIME);

더 효율적인 코드가 있다면 피드백 부탁드립니다.
감사합니다!

저작자표시 비영리 변경금지

'RDBS > MYSQL' 카테고리의 다른 글

[프로그래머스]입양 시각 구하기(2)-MySQL (9)	2023.01.09
[MYSQL]INSERT - 여러 건 삽입 (12)	2023.01.09
[MYSQL]USING과 ON의 차이 (6)	2023.01.05
[MYSQL]윈도우함수(Window Function) (5)	2023.01.03
[MYSQL]INDEX란? (2)	2023.01.02

현재글[프로그래머스]입양 시각 구하기(1)-MySQL

[프로그래머스]입양 시각 구하기(1)-MySQL

입양 시각 구하기(1)

문제

ANIMAL_OUTS 조회

입출력 예

풀이 보기

Key

'RDBS > MYSQL' 카테고리의 다른 글

'RDBS/MYSQL'의 다른글

티스토리툴바

[프로그래머스]입양 시각 구하기(1)-MySQL

입양 시각 구하기(1)

문제

ANIMAL_OUTS 조회

입출력 예

풀이 보기

Key

'RDBS > MYSQL' 카테고리의 다른 글

'RDBS/MYSQL'의 다른글

관련글

티스토리툴바