프롬프트 AI&OpenAPI&공공데이터를 활용한 웹앱개발자 양성 과정 22일차

1. 과제 해결 및 고찰 1

1. 과제
   가장 많이 주문한 사람의 아이디와 이름, 주문개수는?

2. 문제점
   COUNT(1)자체가 그룹 함수 이기때문에, 이걸 다시 MAX의 매개변수로 넣을 수 없었다.
   MAX와 COUNT로 검색해보니 서브쿼리로 해결 할 수 있다는 힌트가 있었기에 다음과 같이 구현해봤다.

3. 구현

SELECT userId, userName, COUNT(1) FROM t_shopping GROUP BY userName 
HAVING COUNT(1) = (SELECT MAX(test.buycount) FROM (SELECT userId, userName, COUNT(1) AS buycount FROM t_shopping GROUP BY userName) AS test);

4. 강사님의 답

SELECT userId, userName, COUNT(1)
FROM  t_shopping 
GROUP BY userName 
ORDER BY COUNT(1) DESC
LIMIT 1 

5. 비교 및 고찰
   ①강사님의 코드 쪽이 성능이슈가 발생할 확률이 좋가 효율적이다.
   ②다만 가장 많이 주문한 사람이 두명 이상인 경우라면 내가 짠 코드가 더 적절한 답이 될 것이다.
   ③아래와 같이 코드를 개선하면 성능 이슈도 대폭 나아지면서 ②번 이슈에 대한 해결책도 될 것이다.

2. 과제 해결 및 고찰 2

1. 과제
   스커트를 산 사람은?

2. 내 구현

SELECT DISTINCT userName FROM t_shopping WHERE pname='스커트';

3. 생각이 못미친 부분
   실제 과제의 데이터에는 동명이인이 없었지만, 동명이인을 생각해서 이름과 id를 같이 DISTINCT 해서 출력해 주는 편이 더 좋았다.

3. 과제 해결 및 고찰 3

1. 과제
   HAVING 절을 서브쿼리로 대체하기
   아래는 HAVING절을 사용해 구현했던 코드이다.
   힌트는 UNION과 서브쿼리라고 하셨는데, 뭐랑 뭐를 UNION해야되는지 좀처럼 감을 잡지 못했다.

SELECT dept.name AS '부서명', GROUP_CONCAT(employee.name SEPARATOR ", " ) AS '사원리스트', CONCAT(TRUNCATE(AVG(annual_income)/10000, 0), '만원') AS '평균연봉', CONCAT(TRUNCATE(MAX(annual_income)/10000, 0), '만원') AS '최고연봉',
CONCAT(TRUNCATE(MIN(annual_income)/10000, 0), '만원') AS '최소연봉', COUNT(1) AS '사원수' 
FROM employee INNER JOIN dept
ON dept.id = employee.deptId
GROUP BY dept.id
HAVING AVG(annual_income) >= 50000000;

2. 그룹함수에 대한 고찰
   그룹함수들은 결국 조건에 맞는 데이터들을 취합 하기 때문에, 따로 GROUP BY 조건이 없다면, 모든 레코드를 취합한다.
   반대로 말하자면 GROUP BY로 취합한 데이터란, GROUP BY의 쿼리 조건이 되는 데이터들을 하나하나 WHERE 절로 가져와서 여러차례 실행한 SELECT문의 집합으로도 볼 수 있을 것이다.

3. 구현
   2번의 고찰로 GROUP BY 의 조건이된 dept.id의 범위 값을 하나하나 WHERE 절로 옮겨서 다음과 같이 구현하였다.

SELECT employ_group.부서명, employ_group.사원리스트, CONCAT(TRUNCATE(employ_group.평균연봉/10000, 0), '만원') AS '평균연봉', CONCAT(TRUNCATE(employ_group.최고연봉/10000, 0), '만원') AS '최고연봉',
CONCAT(TRUNCATE(employ_group.최소연봉/10000, 0), '만원') AS '최소연봉', COUNT(1) AS '사원수'   FROM(
(SELECT dept.name AS '부서명', GROUP_CONCAT(employee.name SEPARATOR ", " ) AS '사원리스트', AVG(annual_income) AS '평균연봉', MAX(annual_income) AS '최고연봉',
MIN(annual_income) AS '최소연봉', COUNT(1) AS '사원수' 
FROM employee INNER JOIN dept
ON dept.id = employee.deptId
WHERE dept.id=1)
UNION ALL
(SELECT dept.name AS '부서명', GROUP_CONCAT(employee.name SEPARATOR ", " ) AS '사원리스트', AVG(annual_income) AS '평균연봉', MAX(annual_income) AS '최고연봉',
MIN(annual_income) AS '최소연봉', COUNT(1) AS '사원수' 
FROM employee INNER JOIN dept
ON dept.id = employee.deptId
WHERE dept.id=2)
) AS employ_group
WHERE employ_group.평균연봉 >= 50000000;

4. 도움이 되는 사이트

https://www.kaggle.com/
이터 과학 및 기계 학습 프로젝트를 수행하는 데이터 과학자와 엔지니어들을 위한 온라인 플랫폼입니다. Kaggle은 데이터셋을 공유하고, 경진대회를 개최하며, 사용자들이 데이터 분석 및 예측 모델을 개발하고 평가할 수 있는 환경을 제공한다.

5. 몰랐던 sql 함수들

함수	기능
GROUP_CONCAT	특정 컬럼의 값을 결합하여 문자열로 반환
TRUNCATE	숫자를 특정 소수 자릿수로 줄이는 함수
CURDATE	현재 날짜를 반환하는 함수
ADDDATE	날짜에 일수를 더하는 함수
SUBDATE	날짜에서 일수를 빼는 함수
DATEDIFF	두 날짜 간의 차이를 계산하는 함수