DevTrend 프로젝트는 SEDE(Stack Exchange Data Explorer)라는 서비스에 HTTP 요청을 날려 원하는 결과를 받아오는 것이 프로젝트의 핵심이라고 말할 수 있을 것이다. 내가 원하는 정보는 크게 두 가지로 아래와 같았다.
-
특정 language 없이 period만 선택하면, period동안 모든 게시글에 태그된 태그들의 태그 수/랭킹 및 이와 함께 비교할 지난 3년간의 해당 태그들의 태그 수/랭킹
-
특정 language와 period을 선택하면, period동안 language와 함께 게시글에 태그된 태그들의 태그 수/랭킹 및 이와 함께 비교할 지난 3년간의 해당 태그들의 태그 수/랭킹
첫 번째 요구사항에 맞는 데이터를 가져올 mysql query는 아래와 같다.
select TOP 50
num.TagName as Tag,
rate.Rate as RecentCnt,
row_number() over (order by rate.Rate desc) as RecentRank,
num.Num as TotalCnt,
row_number() over (order by num.Num desc) as TotalRank
from
(select count(PostId) as Rate, TagName
from Tags, PostTags, Posts
where Tags.Id = PostTags.TagId
and Posts.Id = PostId
and CreationDate > DATEADD(month , -${period}, GETDATE())
and CreationDate < GETDATE()
group by TagName
) as rate
INNER JOIN
(select count(PostId) as Num, TagName
from
Tags, PostTags, Posts
where Tags.Id = PostTags.TagId
and Posts.Id = PostId
and CreationDate > DATEADD(year , -4, GETDATE())
and CreationDate < DATEADD(year , -1, GETDATE())
group by TagName
having count(PostId) > 800
) as num
ON rate.TagName = num.TagName
order by rate.Rate desc
;period 동안 태그된 태그들의 태그 수 및 랭킹을 먼저 뽑아내고, 이를 지난 3년간의 태그에 관한 데이터와 inner join 하는 방식으로 원하는 데이터를 가져온다.
아래는 두 번째 요구사항에 대한 sql query문이다.
select TOP 50
RecentCol.Tag,
RecentCnt,
row_number() over (order by recentCol.recentCnt desc) as RecentRank,
TotalCnt,
row_number() over (order by totalCol.totalCnt desc) as TotalRank
from
(
select b.TagName as Tag, count(a.PostId) as recentCnt
from
(select PostId
from Tags, PostTags
where Tags.Id = TagId
and TagName = '${language}'
) as a
INNER JOIN
(select PostId, Tags.TagName
from Tags, PostTags, Posts
where Tags.Id = TagId
and TagName not like '%${language}%'
and PostId = Posts.Id
and CreationDate > DATEADD(month , -${period}, GETDATE())
and CreationDate < GETDATE()
) as b
on a.PostId = b.PostId
group by b.TagName
) as recentCol
INNER JOIN
(
select d.TagName as Tag, count(c.PostId) as TotalCnt
from
(select PostId
from Tags, PostTags
where Tags.Id = TagId
and TagName = '${language}'
) as c
INNER JOIN
(select PostId, Tags.TagName
from Tags, PostTags, Posts
where Tags.Id = TagId
and PostId = Posts.Id
and TagName not like '%${language}%'
and CreationDate > DATEADD(year , -4, GETDATE())
and CreationDate < DATEADD(year , -1, GETDATE())
) as d
on c.PostId = d.PostId
group by d.TagName
having count(c.PostId) > 3000
) as TotalCol
on RecentCol.Tag = TotalCol.Tag
order by recentCol.recentCnt desc;첫 번째 것보다 훨씬 복잡해보이는데, 과정을 차근차근 설명해 보자면 다음과 같다.
1. tagName이 {language}인 tag가 들어가 있는 post들의 postId를 모두 받아옴.
2. 이 post들에 쓰인 tag들 중 tagName이 {language}가 아닌 태그들의 최근 period동안의 태그 수 및 랭킹을 받아옴.(using inner join)
3. 다시 한 번 tagName이 {language}인 tag가 들어가 있는 post들의 postId를 모두 받아옴.
4. 이 post들에 쓰인 tag들 중 tagName이 {language}가 아닌 태그들의 지난 3년동안의 태그 수 및 랭킹을 받아옴.(using inner join)
5. 2와 4에서 받아온 태그 수 및 랭킹을 tagName으로 inner join함.
위 쿼리는 총 3번의 inner join을 사용해 원하는 데이터를 받아온다. 글을 쓰면서 깨달은건데, 1번 과정과 3번 과정이 중복되므로 이 부분에서 좀 더 최적화를 할 수 있을 것 같다.
