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

#sql #sql_server #оптимизация #datastackexchangecom

Дано:


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 я не умею, поэтому любые замечания по качеству
кода приветствуются.
    

Ответы

Ответ 1

На самом деле разница если и получится, то не очень большая. Проблему составляет
выборка "вопросы помноженные на метки", которые нужны все для сортировки и они занимают
около 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

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