Извините, как многие из вас здесь новые? Помните, если я присоединяюсь к вам?

Я воспринял всплеск в новых рецензентах здесь, и я хотел посмотреть, поддерживают ли данные это наблюдение. Прошло некоторое время с тех пор, как я написал любой SQL, поэтому это было небольшое упражнение в использовании проводника данных Exchange.

Запрос находит первый вопрос и первый ответ от каждого пользователя, а затем группирует эти первые сообщения по неделям. Оказывается, я был прав, но рост первых вопросов опережает рост первых ответов.

Запрос кажется повторяющимся, и я не являюсь большим поклонником Full Outer Join. Есть ли лучший способ написать это?

 WITH FirstAnswers
AS (
  SELECT 
    Users.Id UserId
    ,dateadd(week, datediff(week, 0, Convert(Date,Min(Posts.CreationDate))), 0) WeekOf
  FROM Posts
  INNER JOIN Users
    ON Posts.OwnerUserId = Users.Id
  WHERE PostTypeId = 2 --answer
    AND Posts.CreationDate > '2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph.
  GROUP BY Users.Id 
), 

FirstQuestions
AS (
  SELECT 
    Users.Id UserId
    ,dateadd(week, datediff(week, 0, Convert(Date,Min(Posts.CreationDate))), 0) WeekOf
  FROM Posts
  INNER JOIN Users
    ON Posts.OwnerUserId = Users.Id
  WHERE PostTypeId = 1 --question
    AND Posts.CreationDate > '2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph.
  GROUP BY Users.Id 
) 

SELECT ISNULL(a.WeekOf,b.WeekOf) As WeekOf
  , a.AnswerCount, b.QuestionCount
FROM (
  SELECT WeekOf, Count(UserId) AnswerCount
  FROM FirstAnswers
  GROUP BY WeekOf
) a
FULL OUTER JOIN (
  SELECT WeekOf, Count(UserId) QuestionCount
  FROM FirstQuestions
  GROUP BY WeekOf
 )b
ON a.WeekOf = b.WeekOf
ORDER BY WeekOf

 First Qs & As Graph

35 голосов | спросил RubberDuck 24 J000000Friday15 2015, 03:36:22

3 ответа


22

Технически полное полное соединение недостаточно. Если в течение определенной недели не было ни одного пользователя, который отправил свой первый вопрос или ответ, сюжет на этой неделе должен быть равен 0. Было бы неверно строить прямую линию с предыдущей недели до следующей недели, пропуская тихий неделю. Итак, строго говоря, вам нужно сгенерировать серию из всех недель, к которой вы LEFT OUTER JOIN FirstQuestions и FirstQuestions CTE. Тем не менее, генерация серии дат в SQL Server - это хлопот по сравнению с некоторые более дружественные разработчикам базы данных . Я готов притвориться, что нет недель без новой активности пользователя (на самом деле явно неверно на неделю 2011-01-10).

Сначала перепишите

Вся необходимая информация содержится в таблице FirstAnswers. Нет необходимости присоединяться к таблице Posts.

Подзапросы в запросе показывают, что ваши CTE недостаточно развиты. В моем первом переписывании ниже я реорганизовал Users и FirstAnswers, чтобы выполнить роль ваших подзапросов.

FirstQuestions и a сделают более значимые псевдонимы таблиц, чем q и a.

Названия ваших функций неподтвержденно заглавные: b, dateadd, datediff, Convert, Min. Я считаю, что ISNULL вообще не требуется.

Convert(Date, …)

Лучшее решение

Как оказалось, существует гораздо более легкий способ избежать соединения вообще, используя условный WITH FirstPosts AS ( SELECT OwnerUserId As UserId , PostTypeId , DATEADD(week, DATEDIFF(week, 0, MIN(CreationDate)), 0) AS WeekOf FROM Posts WHERE CreationDate > '2011-01-01' -- There was very little activity prior to this date. Including it skews the graph. GROUP BY OwnerUserId, PostTypeId ), FirstAnswers AS ( SELECT WeekOf , COUNT(UserId) AS UserCount FROM FirstPosts WHERE PostTypeId = 2 -- answer GROUP BY WeekOf ), FirstQuestions AS ( SELECT WeekOf , COUNT(UserId) AS UserCount FROM FirstPosts WHERE PostTypeId = 1 -- question GROUP BY WeekOf ) SELECT ISNULL(a.WeekOf, q.WeekOf) AS WeekOf , ISNULL(a.UserCount, 0) AS AnswerCount , ISNULL(q.UserCount, 0) AS QuestionCount FROM FirstAnswers AS a FULL OUTER JOIN FirstQuestions AS q ON a.WeekOf = q.WeekOf ORDER BY 1; :

COUNT()
ответил 200_success 24 J000000Friday15 2015, 08:50:50
14
  

'2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph.

И затем

  

'2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph.

Если это не было Copy + Paste 'd, вы здесь много работали. И если бы это было ... ну тогда, вы дали себе больше работы, чем вам нужно, чтобы изменить «дату начала».

declare @startDate datetime
set @startDate = '2011-01-01' --There was very little activity prior to this date. Including it skews the graph.

Почему бы не придерживаться формата ISO, YYYY-MM-DD? Не то, чтобы 2011-Jan-01 не читается, это просто ... немного удивительно, по крайней мере для меня.

Это говорит о том, что @startDate хорош, но даже приятнее будет параметр SEDE - и еще более заметным будет параметр SEDE, который позволяет указать количество недель , и чтобы сценарий вычислил @statDate; результаты запроса работают в течение недели в любом случае, а затем вы можете рассчитать дату начала, убедившись, что вы только фиксируете полные недели StackLand. таким образом, вы избегаете возвращения менее значимой цифры за первую неделю, если сегодня, например, суббота (например, 1 января 2011 года).


SELECT ISNULL(a.WeekOf,b.WeekOf) As WeekOf

Это единственный экземпляр ключевого слова AS, который я вижу, который используется для указания псевдонима столбца. Будьте последовательны, это! :)

ответил Mathieu Guindon 24 J000000Friday15 2015, 04:47:53
8

Вы можете сделать это более кратко с помощью первой группировки пользователем и типом сообщения, чтобы получить набор каждого первого вопроса и ответа. Затем установите группу, заданную типом сообщения и недели, в которой она произошла. Наконец, используйте оператор PIVOT, чтобы развернуть строки на два столбца, чтобы вы могли отображать их на диаграмме.

DECLARE @FirstDate DATE = '2011-01-01';  --There was very little activity prior to this date. Including it skews the graph.

SELECT WeekOf, Questions, Answers
FROM (
  SELECT WeekOf,
    CASE PostTypeId
      WHEN 1 THEN 'Questions'
      WHEN 2 THEN 'Answers'
    END AS PostType,
    COUNT(*) AS PostCount
  FROM (
    SELECT PostTypeId,
    DATEADD(week, DATEDIFF(week, 0, MIN(CreationDate)),0) AS WeekOf
    FROM Posts
    WHERE PostTypeId IN (1,2)
      AND CreationDate >= @FirstDate
    GROUP BY OwnerUserId, PostTypeId
  ) AS FirstPosts
  GROUP BY WeekOf, PostTypeId
) AS WeeklyCounts
PIVOT (
  MAX(PostCount)
  FOR PostType IN ([Questions],[Answers])
) AS p
ORDER BY WeekOf;

Нет необходимости вступать в таблицу Posts в таблицу Users, потому что оттуда нам не нужны столбцы. Группировка по столбцу OwnerUserId достаточна, она даже не должна быть в предложении SELECT.

Может быть полезна переменная, удерживающая начальную неделю, поэтому ее можно легко обновить в будущем.

Я заметил, что вы разделили все свои подзапросы в CTE. Если я не использую его в нескольких местах или не выполняю рекурсию, я не использую CTE. Лично мне легче читать подзапросы в строке, а не прокручивать вверх, чтобы посмотреть, что они делают.


Одна из проблем заключается в том, что если новые вопросы и ответы не будут сделаны за неделю, то эта неделя не появится в наборе результатов. Рекурсивный CTE может использоваться для создания набора всех дат за период.

Мои диапазоны дат обычно не такие большие, поэтому мне обычно не нужно менять параметр MAXRECURSION, но в этом случае в наборе больше 100 недель, а 100 - максимальный по умолчанию предел рекурсии. 300 будет достаточно.

Вторая переменная используется для хранения максимальной даты публикации для ограничения рекурсивного CTE. Если переменная не использовалась, запрос для определения максимальной даты создания сообщения будет выполняться каждый раз, когда выполняется CTE.

DECLARE @FirstWeek DATE = '2011-01-01';  --There was very little activity prior to this date. Including it skews the graph.

DECLARE @FinalWeek DATE = (
    SELECT MAX(CreationDate)
    FROM Posts
    WHERE PostTypeId IN (1,2)
);

WITH AllWeeks AS (
    SELECT DATEADD(week, DATEDIFF(week, 0, @FirstWeek), 0) AS WeekOf

    UNION ALL

    SELECT DATEADD(week, 1, WeekOf) AS WeekOf
    FROM AllWeeks
    WHERE WeekOf < @FinalWeek
)
SELECT AllWeeks.WeekOf, Questions, Answers
FROM AllWeeks
LEFT JOIN (
    SELECT WeekOf,
        CASE PostTypeId
            WHEN 1 THEN 'Questions'
            WHEN 2 THEN 'Answers'
        END AS PostType,
        COUNT(*) AS PostCount
    FROM (
        SELECT PostTypeId,
            DATEADD(week, DATEDIFF(week, 0, MIN(CreationDate)),0) AS WeekOf
        FROM Posts
        WHERE PostTypeId IN (1,2)
            AND CreationDate >= @FirstWeek
        GROUP BY OwnerUserId, PostTypeId
    ) AS FirstPosts
    GROUP BY WeekOf, PostTypeId
) AS WeeklyCounts
PIVOT (
    MAX(PostCount)
    FOR PostType IN ([Questions],[Answers])
) AS p ON p.WeekOf=AllWeeks.WeekOf
ORDER BY WeekOf
OPTION (MAXRECURSION 300);

Выполнение этого, конечно, замедлит ваш запрос.


Количество первых сообщений и новых пользователей в неделю

ответил Mike D. 24 J000000Friday15 2015, 19:34:15

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132