Дано:
Posts — сообщения двух типов: вопросы и ответы, связаны через ParentId, тип указан в PostTypeId (1 — вопрос, 2 — ответ).
Вопрос закрыт, если ClosedDate is not null
Вопрос отвечен, если AcceptedAnswerId is not null или есть ответ со Score > 0
Вопросы содержат счётчики AnswerCount, ViewCount, CommentCount — количество ответов, просмотров, комментариев.
Tags — текстовые метки, связанные многие-ко-многим с вопросами через PostTags
Требуется:
Собрать статистику по вопросам, сгруппированную по меткам: сколько всего вопросов, сколько закрыто, сколько имеет хоть какой-то ответ, сколько отвечено. А также собрать статистику: сколько в среднем ответов, просмотров, комментариев. Статистика должна быть за некоторый временной период (например, за последние 8 недель, кроме последних 3 дней).
Решение:
declare @IncludeRecentWeeks int = 8
declare @ExcludeRecentDays int = 3
declare @LastDate datetime =
(select max(CreationDate) from Posts)
declare @StartDate datetime =
dateadd(week, -@IncludeRecentWeeks, @LastDate)
declare @EndDate datetime =
dateadd(day, -@ExcludeRecentDays, @LastDate)
;with
Questions as (
select
q.*,
pt.TagId
from
Posts as q
inner join
PostTags as pt on pt.PostId = q.Id
where
q.PostTypeId = 1 -- question
and q.CreationDate > @StartDate
and q.CreationDate < @EndDate
),
TagStats as (
select
t.TagName as [Tag],
(
select count(q.Id) from Questions as q where q.TagId = t.Id
) as [QuestionCount],
(
select count(q.Id) from Questions as q where q.TagId = t.Id
and q.ClosedDate is not null
) as [ClosedQuestionCount],
(
select count(q.Id) from Questions as q where q.TagId = t.Id
and q.AnswerCount > 0
) as [AnsweredQuestionCount],
(
select count(*)
from (
select q.Id
from Questions as q
inner join Posts as a on a.ParentId = q.Id
where
q.TagId = t.Id
and (a.Score > 0 or q.AcceptedAnswerId = a.Id)
group by q.Id
) as _
) as [UpvotedAnsweredQuestionCount],
(
select sum(AnswerCount) from Questions as q where q.TagId = t.Id
) as [TotalAnswerCount],
(
select sum(ViewCount) from Questions as q where q.TagId = t.Id
) as [TotalViewCount],
(
select sum(CommentCount) from Questions as q where q.TagId = t.Id
) as [TotalCommentCount]
from
Tags as t
)
select top 50
Tag,
QuestionCount as [Qs],
format(1.0 * ClosedQuestionCount / QuestionCount, 'p') as [Qs Closed %],
format(1.0 * AnsweredQuestionCount / (QuestionCount - ClosedQuestionCount), 'p') as [Qs with As %],
format(1.0 * UpvotedAnsweredQuestionCount / (QuestionCount - ClosedQuestionCount), 'p') as [Qs with + As %],
format(1.0 * TotalAnswerCount / QuestionCount, 'f') as [Avg As],
format(1.0 * TotalViewCount / QuestionCount, 'f') as [Avg views],
format(1.0 * TotalCommentCount / QuestionCount, 'f') as [Avg Cs]
from
TagStats
order by
QuestionCount desc
Запрос на Data.StackExchange.com, где можно посмотреть результаты выполнения.
Вопрос:
В получившемся вопросе присутствует большое количество подзапросов, что вряд ли положительно сказывается на производительности. Можно ли оптимизировать запрос и избавиться от такого большого числа подзапросов?
Предупреждение:
Этот запрос — продукт технологий программирования SODD (Stack Overflow Driven Development) и CPDD (Copy-Paste Driven Development). SQL я не умею, поэтому любые замечания по качеству кода приветствуются.
Ответ
На самом деле разница если и получится, то не очень большая. Проблему составляет выборка "вопросы помноженные на метки", которые нужны все для сортировки и они занимают около 100% времени выполнения. Остальные операции занимают сущие копейки. Тем не менее, данные можно получить одним запросом (ну почти), код просто будет короче и местами быстрее:
declare @IncludeRecentWeeks int = 8
declare @ExcludeRecentDays int = 3
declare @LastDate datetime =
(select max(CreationDate) from Posts)
declare @StartDate datetime =
dateadd(week, -@IncludeRecentWeeks, @LastDate)
declare @EndDate datetime =
dateadd(day, -@ExcludeRecentDays, @LastDate)
select top 50 tagname Tag,
cnt Qs,
format(1.0*closed/cnt,'p') [Qs Closed %],
format(1.0*answered/(cnt-closed),'p') [Qs with As %],
format(1.0*accepted/(cnt-closed),'p') [Qs with + As %],
format(avganswers, 'f') [Avg As],
format(avgviews, 'f') [Avg views],
format(avgcomments,'f') [Avg Cs]
from (
select t.tagname,
count(pt.postid) cnt,
sum(iif(q.ClosedDate is not null,1,0)) closed,
sum(iif(q.AnswerCount>0,1,0)) answered,
sum(iif(q.AcceptedAnswerId is not null or a.upvotedanswers>0,1,0)) accepted,
sum(q.AnswerCount) answers,
avg(q.AnswerCount+0.0) avganswers,
sum(q.ViewCount) views,
avg(q.ViewCount+0.0) avgviews,
sum(q.CommentCount) comments,
avg(q.CommentCount+0.0) avgcomments
from postTags pt
join Tags t on pt.tagid = t.id
join Posts q on pt.postId = q.id
join (select qq.id postId, sum(isnull(aa.id,0)) upvotedanswers
from Posts qq
left join Posts aa on qq.id = aa.parentid and aa.score>0
where qq.CreationDate between @StartDate and @EndDate
group by qq.id
) a
on q.id = a.postid
where q.CreationDate between @StartDate and @EndDate
group by t.tagname
) aggtags
order by aggtags.cnt desc
http://data.stackexchange.com/ru/query/313286/tags-stats-mod
ЗЫ Добавил также фильтр по дате для вопросов с заплюсованными ответами, но (лень план запроса разбирать подробно) индекса либо нет, либо он не используется - разницы не заметил.
