请问，你们中有多少人是新来的？介意我加入你吗？

 Full Outer Join 

#1 楼

从技术上讲，完整的外部联接是不够的。如果在某个星期内没有一个用户发布了第一个问题或答案，则该周的图表必须为0。如果画一条从前一周到下一周的直线，则可能会产生误导，而忽略了周。因此，严格来说，您需要生成所有每周的序列，LEFT OUTER JOIN，FirstQuestions和FirstAnswers CTE对应于这些周。但是，与某些对开发人员更友好的数据库相比，在SQL Server中生成日期序列很麻烦。我愿意假装没有几周没有新的用户活动（实际上在2011-01-10的一周中确实是错误的）。您需要的所有信息都包含在Posts表中。不需要加入Users表。查询中的子选择表明您的CTE不发达。在下面的第一次重写中，我对FirstAnswers和FirstQuestions进行了重组，以执行子选择的作用。 >您的函数名称大小写不一致：a，q，a，b和dateadd。我相信根本不需要datediff。通过使用条件Convert完全避免连接：

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;

#2 楼

'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.


如果未复制+粘贴，您在这里做了很多工作。而且，如果这样...那么，您为完成“开始日期”付出了比应做的更多的工作。

转换为ISO格式YYYY-MM-DD？并不是说2011-Jan-01不可读，至少对我来说有点令人惊讶。

那说，@startDate很好，但更好的是SEDE参数-而且还更好仍然是SEDE参数，可让您指定周数，并让脚本计算@statDate；查询结果无论如何都需要数周的时间，然后您可以通过确保只捕获整周的StackLand周数来计算开始日期。这样，您可以避免返回不太有意义的第一周数据，例如，如果今天是星期六（例如2011年1月1日是）。

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

这是我看到的AS关键字的唯一实例，用于指定列别名。要始终如一，沟它！ :)

#3 楼

您可以通过按用户和帖子类型进行首先分组来获得每个第一个问题和答案的集合，从而更加简洁地执行此操作。然后，根据帖子的类型和发生的星期进行分组。最后，使用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);

这样做会减慢您对以下内容的查询课程。


每周首次发布的帖子和新用户的数量