[필기노트] 2021-07-19 SQL UNION, CONCAT_GROUP, CASE

14. tag와 img의 기본키가 곱연산 되는 문제를 해결하자.
SELECT u.userId, u.nickname, u.profileImg, p.postId, p.reBlog, p.title, i.img, p.content, t.tag,
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
(SELECT COALESCE(MIN('Y'), 'N') 
        FROM Follows
        WHERE EXISTS (SELECT 1 FROM  Follows WHERE followUserId = 1 and followerUserId=p.userId)) AS follow,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) AS reaction,
p.createdAt
FROM Posts AS p
JOIN Users AS u
on p.userId = u.userId
LEFT JOIN Images AS i
on p.postId = i.postId
LEFT JOIN Tags AS t
on p.postId = t.postId;
 - 곱연산 문제가 발생하는 4중 JOIN


//TODO JOIN부분을 서브쿼리로 짜면 어떻게 될까?
 - 기본 KEY 중복문제로 인해 다른방법을 찾아봄

15. JOIN 출력이 아닌 CONCAT으로 서브쿼리 ROW를 합쳐서 출력시키자.

SELECT postId , GROUP_CONCAT(img ORDER BY img ASC SEPARATOR ', ')
from Images 
where postId = 1
GROUP BY postId ;
- Row별로 Post를 출력해준다.




SELECT u.userId, u.nickname, u.profileImg, p.postId, p.reBlog, p.title,
(SELECT GROUP_CONCAT(img ORDER BY img ASC SEPARATOR ', ')
    FROM Images
    WHERE postId = p.postId
    GROUP BY postId) AS img,
p.content,
(SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ')
    FROM Tags
    WHERE postId = p.postId
    GROUP BY postId) AS tag,
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
(SELECT COALESCE(MIN('Y'), 'N')
    FROM Follows
    WHERE EXISTS (SELECT 1 FROM  Follows WHERE followUserId = 1 AND followerUserId=p.userId)) AS follow,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) +
    (SELECT COUNT(*) FROM Posts WHERE reBlog=p.postId) AS reactionCount,
p.createdAt
FROM Posts AS p
INNER JOIN Users AS u
USING(userId)
WHERE p.title LIKE '%black number%'
ORDER BY p.createdAt DESC
LIMIT 0, 30
;
 - 서브쿼리가 너무 많기는하지만 정상적으로 출력된다.
 - 쿼리 조회속도를 최적화 시킬 방법을 찾아야 한다.




-- 최상단 게시글 출력 SQL
UNION ALL을 붙이고 WHERE로 구분해 출력해보자
 - 불필요한 Query문이 늘어나서 비효율 적이다.


ORDER BY함수를 이용해 정리

SELECT u.userId, u.nickname, u.profileImg, p.postId, p.reBlog, p.title,
(SELECT GROUP_CONCAT(img ORDER BY img ASC SEPARATOR ', ')
    FROM Images
    WHERE postId = p.postId
    GROUP BY postId) AS img,
p.content,
(SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ')
    FROM Tags
    WHERE postId = p.postId
    GROUP BY postId) AS tag,
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
(SELECT COALESCE(MIN('Y'), 'N')
    FROM Follows
    WHERE EXISTS (SELECT 1 FROM  Follows WHERE followUserId = 1 AND followerUserId=p.userId)) AS follow,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) AS reactionCount,
p.createdAt
FROM Posts AS p
INNER JOIN Users AS u
USING(userId)
ORDER BY 
    CASE WHEN postId = 36 THEN 1
    ELSE  0 END DESC,
    createdAt DESC
LIMIT 0, 30
;
 - 단점 : createdAt순서대로 정렬은 하지만 동일한 createdAt이 있을경우 postId 의 순서가 맞지않는다.


SELECT u.userId, u.nickname, u.profileImg, p.postId, p.reBlog, p.title,
(SELECT GROUP_CONCAT(img ORDER BY img ASC SEPARATOR ', ')
    FROM Images
    WHERE postId = p.postId
    GROUP BY postId) AS img,
p.content,
(SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ')
    FROM Tags
    WHERE postId = p.postId
    GROUP BY postId) AS tag,
CASE WHEN p.postId IN (SELECT postId FROM Favorites WHERE userId=1) THEN "Y" ELSE "N" END AS favorite,
(SELECT COALESCE(MIN('Y'), 'N')
    FROM Follows
    WHERE EXISTS (SELECT 1 FROM  Follows WHERE followUserId = 1 AND followerUserId=p.userId)) AS follow,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) +
    (SELECT COUNT(*) FROM Posts WHERE reBlog=p.postId) AS reactionCount,
p.createdAt
FROM Posts AS p
INNER JOIN Users AS u
USING(userId)
ORDER BY 
    CASE WHEN postId = 4 THEN 1
    ELSE  0 END DESC,
    postId DESC,
    createdAt DESC
LIMIT 0, 30
;
 - 1. 지정한 postId를 최하단으로 보냄
 - 2. postId를 기준으로 내림차순으로 정렬
 - 3. createdAt을 기준으로 가장 최근의 게시글을 최상단에 보이게 하기위해 내림차순으로 정렬







-- 게시글의 반응 / 좋아요 및 리블로그의 데이터를 가져 온다.
 - Favorites, Posts 테이블에서 가져오는 데이터를 UNION으로 정의하자.

1. 해당하는 postId의 좋아요를 가져 온다.
SELECT * FROM Favorites WHERE postId = 4;

2. 해당하는 postId의 리블로그를 가져 온다.
SELECT * FROM Posts WHERE reBlog = 4;

3. Users와 JOIN한다.
SELECT u.userId, u.nickname, 1 AS type, u.profileImg
FROM Posts AS p
INNER JOIN Users AS u
ON p.userId = u.userId 
WHERE reBlog = 4;

4. Favorites 와 JOIN 한다.
SELECT u.userId, u.nickname, 3 AS type, u.profileImg
FROM Favorites AS f
INNER JOIN Users AS u
ON f.userId = u.userId
WHERE f.postId = 4;


5. UNION 한다.
SELECT u.userId, u.nickname, 1 AS type, u.profileImg
FROM Posts AS p
INNER JOIN Users AS u
ON p.userId = u.userId 
WHERE reBlog = 4
UNION ALL
SELECT u.userId, u.nickname, 3 AS type, u.profileImg
FROM Favorites AS f
INNER JOIN Users AS u
ON f.userId = u.userId
WHERE f.postId = 4
;



6. LIMIT를 설정하고 createdAt을 기준으로 ORDER BY를 설정한다.

SELECT u.userId, u.nickname, 1 AS type, u.profileImg, p.createdAt
FROM Posts AS p
INNER JOIN Users AS u
ON p.userId = u.userId 
WHERE reBlog = 4
UNION ALL

SELECT u.userId, u.nickname, 3 AS type, u.profileImg, f.createdAt
FROM Favorites AS f
INNER JOIN Users AS u
ON f.userId = u.userId
WHERE f.postId = 4
ORDER BY createdAt DESC
LIMIT 0,10;





-- 좋아요한 게시글을 가져오기 ☆

SELECT SUM( (SELECT COUNT(*) FROM Favorites WHERE postId = 4) +
        (SELECT COUNT(*) FROM Posts WHERE reBlog = 4) )  AS  reactionCount;
 - Favorites에서 지정한 posId와 Posts에서 reBlog 되고있는 갯수를 반환한다.

SELECT (SELECT COUNT(*) FROM Favorites WHERE postId = 4) +
        (SELECT COUNT(*) FROM Posts WHERE reBlog = 4) AS reactionCount;
 - SUM을 삭제해도 정상적으로 구동된다.



SELECT DISTINCT u.userId, u.nickname, u.profileImg, p.postId, p.reBlog, p.title, 
(SELECT GROUP_CONCAT(img ORDER BY img ASC SEPARATOR ', ')
    FROM Images
    WHERE postId = p.postId
    GROUP BY postId) AS img,
p.content,
(SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ')
    FROM Tags
    WHERE postId = p.postId
    GROUP BY postId) AS tag,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) +
    (SELECT COUNT(*) FROM Posts WHERE reBlog=p.postId) AS reactionCount,
f.createdAt
FROM Posts AS p
INNER JOIN Users AS u
ON p.userId = u.userId 
INNER JOIN Favorites AS f
ON f.userId = 2
WHERE f.postId = p.postId
ORDER BY createdAt DESC,
    postId DESC
LIMIT 0,20
;
 - DISTINCT를 이용해 중복된 데이터들을 삭제
 - ON f.userId = 2 : 현재 검색하고 있는 주체가 누구인지 정의한다.
 - WHERE f.postId = p.postId에서 내가 좋아요를 누른 게시글만 출력되도록 설정한다.
 - 좋아요를 최근 등록한 순서대로 내림차순으로 출력한다. 
  - 동일한 시간에 추가했다면 postId기준으로 내림차순한다.



-- 내가 작성한 게시글을 가져온다.

SELECT userId, postId, reBlog, title, 
(SELECT GROUP_CONCAT(img ORDER BY img ASC SEPARATOR ', ')
    FROM Images
    WHERE postId = p.postId
    GROUP BY postId) AS img,
content,
(SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ')
    FROM Tags
    WHERE postId = p.postId
    GROUP BY postId) AS tag,
(SELECT COUNT(*) FROM Favorites WHERE postId=p.postId) +
    (SELECT COUNT(*) FROM Posts WHERE reBlog=p.postId) AS reactionCount
FROM Posts AS p
where userId=1
ORDER BY createdAt DESC, 
    postId DESC
LIMIT 0,20
;
 - Posts에서 userId가 1로 작성된 게시글들을 가져옵니다.