Статистика по вопросам, сгруппированная по меткам (Data.SE)

Дано:
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
ЗЫ Добавил также фильтр по дате для вопросов с заплюсованными ответами, но (лень план запроса разбирать подробно) индекса либо нет, либо он не используется - разницы не заметил.