AQueryTool
- 무겁고 복잡하고 불편한 기존의 ERD 프로그램을 가볍고 깔끔하게, 사용하기 좋게 만든 ERD 프로그램이다.
- ERD를 작성한 후에 SQL문으로도 자동으로 작성해준다.
ERD
ERD에 대해서는 이전에 다룬 글이 있으니 생략하겠다.
ERD(Entity Relationship Diagram)
모바일 App의 DB 클론 설계 (쿠팡이츠)
본인은 모바일 App 중에서 쿠팡이츠의 DB를 클론 설계하였다. ERD는 처음 짜봐서 부족한 부분들이 많이 있다. 
설계를 하고 나서도 쿼리를 작성하면서 table을 새로 추가하기도 하고 column도 추가하며 수정해왔다. 아쉬운 부분이 있다면 ERD 설계 과정에서 모바일 App을 조금 더 자세히 둘러보고 짰으면 하는 부분이다.
- 식당의 메뉴들을 보여줄 때 식당 메뉴 안에서도 카테고리가 존재하는데 이를 생각하지 못했다.
- 메뉴, 식당에서 보이는 음식 이미지가 한개씩이라고 생각했는데 여러개를 등록해 놓은 업체들도 많았다. 이미지 테이블을 생성하여 설계하면 더 꼼꼼한 설계가 될 것 같다.
- 소비자 입장에서 설계한 ERD 이므로 배달원에 대한 table은 필요가 없었다.
- 자료형 중 TEXT는 크기를 많이 차지한다고 한다. 너무 긴 문자열이 아니라면 VARCHAR을 사용하는 것이 좋다.
화면별 Query 작성
#1 모든 카테고리 조회 화면
SELECT id
,name AS 카테고리
,imagUrl AS 사진
FROM category;
#2 특정 사용자의 즐겨찾기 목록 조회 화면
SELECT b.name AS 식당명
, b.imageUrl AS 식당사진
, b.delCost AS 배달비
, b.delTime AS 배달시간
, b.delTime + 10 AS 최대배달시간
, CASE WHEN starGrade IS NULL THEN 0 ELSE starGrade END AS 별점
, CASE WHEN starCount IS NULL THEN 0 ELSE starCount END AS 리뷰수
FROM favorites a
LEFT JOIN ( SELECT id
, name
, delCost
, delTime
, imageUrl
FROM restaurant
GROUP BY id ) AS b
ON a.restaurantId = b.id
LEFT JOIN ( SELECT id
,ROUND(SUM(score) / COUNT(restaurantId), 1) AS 'starGrade'
,COUNT(restaurantId) AS 'starCount'
,restaurantId
FROM review
GROUP BY restaurantId) AS c
ON a.restaurantId = c.restaurantId
WHERE a.userId=1;
#3 카테고리 선택 후 보이는 화면
SELECT c.name AS 카테고리명
,a.id, a.name AS 식당명
,a.imageUrl AS 사진
,a.delCost AS 배달비
,starGrade AS 별점
,starCount AS 리뷰수
FROM restaurant a
LEFT JOIN ( SELECT id
,ROUND ( SUM(score) / COUNT(restaurantId), 1) AS 'starGrade'
,COUNT(restaurantId) AS 'starCount'
,restaurantId
FROM review
GROUP BY restaurantId) AS b
ON a.id = b.restaurantId
LEFT JOIN ( SELECT id, name, restaurantId
FROM category
GROUP BY id, name, restaurantId) AS c
ON a.id = c.restaurantId
LEFT JOIN ( SELECT restaurantId, imageUrl
FROM menu
GROUP BY restaurantId) AS d
ON d.restaurantId = a.id
WHERE c.id = 1;
#4 과거 주문 내역
SELECT f.name AS 식당명
,CASE WHEN a.status = 0 THEN '배달 완료' ELSE '배달 취소' END AS 배달여부
,e.name AS 메뉴이름
,a.updatedAt AS 시간
,f.imageUrl AS 식당사진
,productCount AS 수량
,productCount * e.cost AS 금액
FROM orders a
LEFT JOIN ( SELECT id
FROM user
GROUP BY id) AS b
ON a.userId = b.id
LEFT JOIN ( SELECT userId
, id
FROM cart
GROUP BY userId) AS c
ON a.userId = c.userId
LEFT JOIN ( SELECT cartId
, menuId
, restaurantId
FROM cartedmenu ) AS d
ON a.restaurantId = d.restaurantId
LEFT JOIN ( SELECT id
, name
, COUNT(id) AS 'productCount'
, cost
FROM menu
GROUP BY id, name, cost) AS e
ON e.id = d.menuId
LEFT JOIN ( SELECT id
, name
, imageUrl
FROM restaurant
GROUP BY id, name, imageUrl) AS f
ON f.id = d.restaurantId
WHERE b.id = 1 AND f.name IS NOT NULL
ORDER BY a.updatedAt DESC;
#5 새로 들어왔어요! 신규 등록 업체 보이는 화면
SELECT a.id
, a.name AS 식당명
, CASE WHEN a.createdAt > '2021-07-14 15:06:00' THEN '신규' END AS 상태
, a.delCost AS 배달비
, a.imageUrl AS 식당사진
, a.delTime AS 배달시간
, a.delTime + 10 AS 최대배달시간
, a.createdAt AS 등록시간
FROM restaurant a
WHERE a.createdAt > '2021-07-14 15:06:00'
ORDER BY a.createdAt DESC;
#6 업체 메인 화면
SELECT a.id AS 식당id
, a.name AS 식당명
, starGrade AS 별점
, starCount AS 리뷰수
, delTime AS 배달시간
, a.delTime + 10 AS 최대배달시간
, delCost AS 배달비
, minCost AS 최소주문
, c.id AS 메뉴id
, c.name AS 메뉴명
, c.imageUrl AS 메뉴사진
, c.cost AS 가격
, c.contents AS 메뉴설명
FROM restaurant a
LEFT JOIN ( SELECT id
, restaurantId
, ROUND(SUM(score) / COUNT(restaurantId), 1) AS 'starGrade'
, COUNT(restaurantId) AS 'starCount'
FROM review
GROUP BY restaurantId ) AS b
ON a.id = b.restaurantId
LEFT JOIN ( SELECT id
, restaurantId
, name
, imageUrl
, contents
, cost
FROM menu ) AS c
ON a.id = c.restaurantId
WHERE a.id = 1
ORDER BY c.id ASC;
#7 치타 배달 업체 조회 화면 (배달시간 30분 이내)
SELECT a.id AS 식당id
, a.name AS 식당명
, a.imageUrl AS 식당사진
, CASE WHEN starGrade IS NULL THEN 0 ELSE starGrade END AS 별점
, CASE WHEN starCount IS NULL THEN 0 ELSE starCount END AS 리뷰수
, a.delTime AS 배달시간
, a.delTime + 10 AS 최대배달시간
, a.delCost AS 배달비
, CASE WHEN a.delTime <= '30' THEN '치타배달' END AS 배달유형
FROM restaurant a
LEFT JOIN ( SELECT id
, ROUND(SUM(score) / COUNT(restaurantId), 1) AS 'starGrade'
, COUNT(restaurantId) AS 'starCount'
, restaurantId
FROM review
GROUP BY restaurantId ) AS b
ON a.id = b.restaurantId
WHERE a.delTime <= '30';
#8 업체의 전체 리뷰 조회 화면
SELECT a.id AS '리뷰 id'
, f.name AS 식당
, menuId AS 메뉴id
, e.name AS 메뉴명
, b.name AS 고객명
, contents AS 리뷰내용
, score AS 별점
, a.createdAt AS '리뷰시간(날짜)'
, timestampdiff(day, a.createdAt, current_timestamp())AS '리뷰시간(일전)'
FROM review a
LEFT JOIN user b
ON a.userId = b.id
LEFT JOIN (SELECT o.cartId
, o.id
FROM orders o) AS c
ON a.orderId = c.id
LEFT JOIN (SELECT cartId
, menuId
FROM cartedmenu) AS d
ON c.cartId = d.cartId
LEFT JOIN (SELECT id
, name
FROM menu) AS e
ON d.menuId = e.id
LEFT JOIN ( SELECT id
, name
FROM restaurant ) AS f
ON a.restaurantId = f.id
WHERE restaurantId = 1;
#9 특정 사용자의 카트 조회
SELECT a.id AS 사용자id
, a.location AS 사용자위치
, d.name AS 식당명
, e.name AS 메뉴명
, COUNT(e.id) AS 수량
, e.cost AS 가격
, couponCount AS '사용가능쿠폰(장)'
, e.cost * COUNT(e.id) AS 주문금액
, d.delCost AS 배달비
, e.cost * COUNT(e.id) + d.delCost AS 총결제금액
FROM user a
LEFT JOIN ( SELECT id
, userId
FROM cart
GROUP BY userId) AS b
ON a.id = b.userId
LEFT JOIN ( SELECT id
, cartId
, menuId
, restaurantId
FROM cartedmenu
GROUP BY cartId ) AS c
ON b.id = c.cartId
LEFT JOIN ( SELECT id
, name
, delCost
FROM restaurant
GROUP BY id ) AS d
ON c.restaurantId = d.id
LEFT JOIN ( SELECT id
, name
, restaurantId
, cost
FROM menu
GROUP BY restaurantId ) AS e
ON c.menuId = e.id
LEFT JOIN ( SELECT id
, userId
, name
, COUNT(userId) AS 'couponCount'
FROM coupon
GROUP BY userId ) AS f
ON a.id = f.userId
LEFT JOIN ( SELECT id
, userId
, status
FROM cart
GROUP BY userId) AS g
ON c.cartId = g.id
WHERE a.id = 3;
#10 특정 사용자가 작성한 리뷰 조회 화면
SELECT a.id AS 리뷰id
, b.id AS 사용자id
, c.name AS 식당명
, f.name AS 주문메뉴
, a.score AS 별점
, a.contents AS 리뷰내용
, a.imageUrl AS 리뷰사진
, f.name AS 먹은음식
FROM review a
LEFT JOIN user b
ON a.userId = b.id
LEFT JOIN ( SELECT id
, name
FROM restaurant ) AS c
ON a.restaurantId = c.id
LEFT JOIN ( SELECT id
, userId
FROM cart
GROUP BY userId ) AS d
ON a.userId = d.id
LEFT JOIN ( SELECT id
, cartId
, menuId
FROM cartedmenu
GROUP BY cartId ) AS e
ON d.id = e.cartId
LEFT JOIN ( SELECT id
, name
, restaurantId
FROM menu
GROUP BY restaurantId ) AS f
ON c.id = f.restaurantId
WHERE a.userId = 1;
#11 최소 주문 금액이 얼마 이하인 업체 조회 화면
SELECT a.id AS 식당id
, a.name AS 식당명
, a.imageUrl AS 식당사진
, CASE WHEN starGrade IS NULL THEN 0 ELSE starGrade END AS 별점
, CASE WHEN starCount IS NULL THEN 0 ELSE starCount END AS 리뷰수
, a.delCost AS 배달비
, a.delTime AS 배달시간
, a.delTime AS 최대배달시간
, CASE WHEN a.delTime <= '30' THEN '치타배달' ELSE '일반배달' END AS 배달유형
FROM restaurant a
LEFT JOIN ( SELECT id
, restaurantId
, ROUND(SUM(score) / COUNT(restaurantId), 1) AS 'starGrade'
, COUNT(restaurantId) AS 'starCount'
FROM review
GROUP BY restaurantId ) AS b
ON a.id = b.restaurantId
WHERE a.minCost <= 12000;
#12 최소 주문 금액 얼마 이하인, 배달비 얼마 이하인 업체 별점 높은 순 조회 화면
SELECT a.id AS 식당id
, a.name AS 식당명
, a.imageUrl AS 식당사진
, CASE WHEN starGrade IS NULL THEN 0 ELSE starGrade END AS 별점
, CASE WHEN starCount IS NULL THEN 0 ELSE starCount END AS 리뷰수
, a.delCost AS 배달비
, a.delTime AS 배달시간
, a.delTime + 10 AS 최대배달시간
, CASE WHEN a.delTime <= '30' THEN '치타배달' ELSE '일반배달' END AS 배달유형
FROM restaurant a
LEFT JOIN ( SELECT id
, restaurantId
, ROUND(SUM(score) / COUNT(restaurantId), 1) AS 'starGrade'
, COUNT(restaurantId) AS 'starCount'
FROM review
GROUP BY restaurantId ) AS b
ON a.id = b.restaurantId
WHERE a.minCost <= 12000 AND a.delCost <=3000
ORDER BY starGrade DESC;
#13 특정 사용자의 영수증 조회
SELECT a.id AS 영수증id
, a.userId AS 사용자id
, e.name AS 식당명
, a.createdAt AS 주문일시
, d.name AS 메뉴명
, d.cost AS 가격
, e.delCost AS 배달비
, CASE WHEN f.benefits IS NULL THEN 0 ELSE f.benefits END AS 할인금액
, CASE WHEN f.benefits IS NULL THEN d.cost * menuCount + e.delCost ELSE d.cost * menuCount + e.delCost - f.benefits END AS 합계
, CASE WHEN a.status = 0 THEN '결제완료' END AS 상태
FROM orders a
LEFT JOIN ( SELECT id
, userId
, status
FROM cart ) AS b
ON a.cartId = b.id
LEFT JOIN ( SELECT cartId
, menuId
, restaurantId
FROM cartedmenu ) AS c
ON b.id = c.cartId
LEFT JOIN ( SELECT id
, name
, cost
, restaurantId
, COUNT(id) AS 'menuCount'
FROM menu
GROUP BY id ) AS d
ON c.menuId = d.id
LEFT JOIN ( SELECT id
, name
, delCost
FROM restaurant
GROUP BY id ) AS e
ON c.restaurantId = e.id
LEFT JOIN ( SELECT id
, benefits
, userId
FROM coupon ) AS f
ON a.couponId = f.id
WHERE a.userId = 3 AND a.status = 0;
#14 특정 사용자의 쿠폰 조회
SELECT a.id AS 사용자id
, b.id AS 쿠폰id
, b.name AS 쿠폰명
, DATE_FORMAT(DATE_ADD(b.createdAt, INTERVAL 14 DAY), "%M-%D") AS '기간 (까지)'
FROM user a
LEFT JOIN ( SELECT id
, name
, userId
, createdAt
, status
FROM coupon ) AS b
ON a.id = b.userId
WHERE a.id = 1 AND b.status = 0;
#15 별점 높은순, 치타 배달, 배달비 3000원이하, 최소주문 15000원이하인 식당 조회
SELECT a.id AS 식당id
, a.name AS 식당명
, a.imageUrl AS 식당사진
, a.delCost AS 배달비
, a.delTime AS 배달시간
, a.delTime + 10 AS 최대배달시간
, CASE WHEN delTime <= 30 THEN '치타배달' ELSE '일반배달' END AS 배달유형
, CASE WHEN starGrade IS NULL THEN 0 ELSE starGrade END AS 별점
, CASE WHEN starCount IS NULL THEN 0 ELSE starCount END AS 리뷰수
FROM restaurant a
LEFT JOIN ( SELECT restaurantId
, ROUND(SUM(score) / COUNT(restaurantId), 1) AS 'starGrade'
, COUNT (restaurantId) AS 'starCount'
FROM review
GROUP BY restaurantId ) AS b
ON a.id = b.restaurantId
WHERE a.delTime <= 30 AND a.delCost <= 3000 AND a.minCost <=15000
ORDER BY starGrade DESC;
#16 검색 순위 조회
SELECT MIN(a.searchId) AS 검색어id
, a.name AS 검색어
, ROW_NUMBER() OVER (ORDER BY searchCount DESC ) 검색순위
FROM Search a
LEFT JOIN ( SELECT searchId
, name
, COUNT(searchId) AS 'searchCount'
FROM Search ) AS b
ON a.searchId = b.searchId
GROUP BY a.searchId;
#17 검색어 검색 결과 조회
SELECT d.name AS 검색
, a.id AS 식당id
, a.name AS 식당명
, a.imageUrl AS 식당사진
, CASE WHEN starGrade IS NULL THEN 0 ELSE starGrade END AS 별점
, CASE WHEN starCount IS NULL THEN 0 ELSE starCount END AS 리뷰수
, a.location AS 위치
, a.delCost AS 배달비
, a.delTime AS 배달시간
, a.delTime + 10 AS 최대배달시간
, CASE WHEN a.status = 0 THEN '주문가능' ELSE '주문불가' END AS 상태
FROM restaurant a
LEFT JOIN ( SELECT restaurantId
, ROUND(SUM(score) / COUNT(restaurantId), 1) AS 'starGrade'
, COUNT(restaurantId) AS 'starCount'
FROM review
GROUP BY restaurantId ) AS b
ON a.id = b.restaurantId
LEFT JOIN ( SELECT id
, restaurantId
FROM category ) AS c
ON a.id = c.restaurantId
LEFT JOIN ( SELECT searchId
, name
, categoryId
FROM Search
GROUP BY categoryId) AS d
ON c.id = d.categoryId
WHERE d.categoryId = 13;
#18 특정 사용자의 검색 내역 조회
SELECT b.id AS 사용자id
, a.searchId AS 검색어id
, a.name AS 검색어
, a.categoryId AS 검색어의카테고리
, DATE_FORMAT ( a.createdAt, "%M-%D" ) AS 검색일시
FROM Search a
LEFT JOIN ( SELECT id
FROM user
GROUP BY id ) as b
ON a.userId = b.id
WHERE b.id = 1
ORDER BY createdAt DESC;
#19 주문 많은 순 식당 조회
SELECT CASE WHEN orderCount IS NULL THEN 0 ELSE orderCount END AS 주문횟수
, a.name AS 식당명
, a.imageUrl AS 식당사진
, CASE WHEN a.delTime <= 30 THEN'치타배달' ELSE '일반배달' END AS 배달유형
, CASE WHEN starGrade IS NULL THEN 0 ELSE starGrade END AS 별점
, CASE WHEN starCount IS NULL THEN 0 ELSE starCount END AS 리뷰수
, a.location AS 위치
, a.delCost AS 배달비
, a.delTime AS 배달시간
, a.delTime + 10 AS 최대배달시간
FROM restaurant a
LEFT JOIN ( SELECT id
, restaurantId
, ROUND ( SUM ( score ) / COUNT ( restaurantId ), 1) AS 'starGrade'
, COUNT(restaurantId) AS 'starCount'
FROM review
GROUP BY restaurantId ) AS b
ON a.id = b.restaurantId
LEFT JOIN ( SELECT restaurantId
, COUNT(restaurantId) AS 'orderCount'
FROM orders
GROUP BY restaurantId ) AS c
ON a.id = c.restaurantId
ORDER BY orderCount DESC;
#20 신규 식당 별점 높은순, 치타배달, 배달비 2000원 이하, 최소주문 15000원 이하
SELECT a.id AS 식당id
, a.name AS 식당명
, a.imageUrl AS 식당사진
, CASE WHEN a.delTime <=30 THEN '치타배달' ELSE '일반배달' END AS 배달유형
, CASE WHEN starGrade IS NULL THEN 0 ELSE starGrade END AS 별점
, CASE WHEN starCount IS NULL THEN 0 ELSE starCount END AS 리뷰수
, a.location AS 위치
, a.delTIme AS 배달시간
, a.delTime + 10 AS 최대배달시간
, a.delCost AS 배달비
FROM restaurant a
LEFT JOIN ( SELECT restaurantId
, ROUND (SUM(score) / COUNT (restaurantId), 1 ) AS 'starGrade'
, COUNT (restaurantId) AS 'starCount'
FROM review
GROUP BY restaurantId ) AS b
ON a.id = b.restaurantId
WHERE a.createdAt >= '2021-07-14 15:05:00' AND a.delTime<=30 AND a.delCost <=2000 AND a.minCost <=15000
ORDER BY starGrade DESC;
꾸준함을 꿈꾸는 SW 전공 학부생의 개발 일기