0

Please login or register to vote for this query.

(click on this box to dismiss)

プログラマーと熱狂的なプログラマーのためのサイトに関する質問と回答

declare @user_id int = ##UserId:int##;
declare @group int = ##Group:int##;

WITH ClosedPostHistory as (
    SELECT
        *
    FROM
        PostHistory ph
    WHERE
        ph.PostHistoryTypeId = 10
),

CloseReasonPostHistory as (
    SELECT
        ph.PostId,
        last_value(crt.Id) over (ORDER BY ph.CreationDate DESC) as Id,
        last_value(crt.Name) over (ORDER BY ph.CreationDate DESC) as Name
    FROM
        ClosedPostHistory ph
    inner join
        CloseReasonTypes crt on cast(ph.Comment as tinyint) = crt.Id
    GROUP BY
        ph.PostId, ph.CreationDate, crt.Id, crt.Name
),

PostInfo as (
    SELECT
        pt.Name as PostType,
        p.Id,
        p.Score,
        case when p.ClosedDate = null then null else crph.Name end as CloseName,
        case when p.ClosedDate = null then null else crph.Id end as CloseId
    FROM
        Posts p
    inner join
        PostTypes pt on p.PostTypeId = pt.Id
    left outer join
        CloseReasonPostHistory crph on p.Id = crph.PostId
    WHERE
        (@user_id = -2 or p.OwnerUserId = @user_id)
    GROUP BY
        p.Id, p.Score, p.ClosedDate, pt.Name, crph.Id, crph.Name
),

PostInfoWithVotes as (
    SELECT
        p.PostType,
        case
            when p.CloseId is null then null
            when p.CloseName is null then null
            when p.CloseId = 101 then 'Duplicate'
            when p.CloseId = 1 then 'Duplicate'
            when @group != 0 then 'Off-topic'
            else p.CloseName
        end as AdditionalType,
        p.Id,
        p.Score,
        sum(case when p.Id = v.PostId and v.VoteTypeId = 2 then 1 else 0 end) as Upvotes,
        sum(case when p.Id = v.PostId and v.VoteTypeId = 3 then 1 else 0 end) as Downvotes
    FROM
        PostInfo p
    inner join
        Votes v on p.Id = v.PostId
    WHERE
        v.VoteTypeId = 3
        or v.VoteTypeId = 2
    GROUP BY
        p.Id, p.Score, p.PostType, p.CloseId, p.CloseName
)

SELECT
    p.PostType,
    p.AdditionalType,
    avg(cast(p.Score as float)) as Score,
    avg(cast(p.Upvotes as float)) as Upvotes,
    avg(cast(p.Downvotes as float)) as Downvotes
FROM
    PostInfoWithVotes p
GROUP BY
    p.PostType, p.AdditionalType

fork query

edited mar 4 19 Peilonrayz

created nov 27 12 Edgarator