Есть ли способ вычислить взвешенную сумму с весами, основанными на выражении WHERE?

Есть ли способ вычислить взвешенную сумму с весами, основанными на выражении WHERE?

01.11.2013 10:01:43 Просмотров 48 Источник

Я хотел знать свою репутацию переполнения стека, исключая всю репутацию, полученную или потерянную от вопросов и ответов на вопросы регулярных выражений (—для меня это похоже на "грязные деньги"!)

Обозреватель данных Stack Exchange позволит мне вычислить это. Но я когда-либо программировал только с использованием проприетарных баз данных и классов-оболочек C++, поэтому я не был слишком уверен в своих навыках SQL. Тем не менее, я дал ему шанс, и в конечном итоге пришел с вопросом, который предоставил мой ответ:

-- (approximate) reputation gained/lost on a specified tag
-- only counts post upvotes and downvotes

DECLARE @UserId        int = ##UserId##
DECLARE @QuestionsUp   int = 0;
DECLARE @QuestionsDown int = 0;
DECLARE @AnswersUp     int = 0;
DECLARE @AnswersDown   int = 0;
DECLARE @Tag           nvarchar(25) = 'regex';

SELECT
    @QuestionsUp = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.id
    INNER JOIN Posts ON Posts.ParentId = PostTags.PostId
    INNER JOIN Votes ON Votes.PostId = Posts.Id and VoteTypeId = 2
WHERE 
    Posts.OwnerUserId = @UserId and
    Posts.PostTypeId = 1 and
    Tags.TagName = @Tag

SELECT
    @QuestionsDown = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.id
    INNER JOIN Posts ON Posts.ParentId = PostTags.PostId
    INNER JOIN Votes ON Votes.PostId = Posts.Id and VoteTypeId = 3
WHERE 
    Posts.OwnerUserId = @UserId and
    Posts.PostTypeId = 1 and
    Tags.TagName = @Tag

SELECT
    @AnswersUp = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.id
    INNER JOIN Posts ON Posts.ParentId = PostTags.PostId
    INNER JOIN Votes ON Votes.PostId = Posts.Id and VoteTypeId = 2
WHERE 
    Posts.OwnerUserId = @UserId and
    Posts.PostTypeId = 2 and
    Tags.TagName = @Tag

SELECT
    @AnswersDown = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.id
    INNER JOIN Posts ON Posts.ParentId = PostTags.PostId
    INNER JOIN Votes ON Votes.PostId = Posts.Id and VoteTypeId = 3
WHERE 
    Posts.OwnerUserId = @UserId and
    Posts.PostTypeId = 2 and
    Tags.TagName = @Tag

SELECT @QuestionsUp * 5 +
       @AnswersUp * 10 +
       (@QuestionsDown + @AnswersDown) * -2

Но это не самое лучшее, что можно сделать. Четыре отдельных запроса только для того, чтобы взвесить повышенные вопросы на 5, повышенные ответы на 10 и пониженные вопросы и ответы на -2? Есть ли способ сжать этот запрос для выполнения в одном запуске?

(Пожалуйста, не стесняйтесь комментировать, если у вас есть какие-либо дополнительные советы относительно синтаксиса, форматирования, хороших практик и т. д. Кроме того, пожалуйста, прокомментируйте, есть ли другие способы получить или потерять репутацию, специфичную для тегов, которые я не учитывал.)

У вопроса есть решение - Посмотреть?

Ответы - Есть ли способ вычислить взвешенную сумму с весами, основанными на выражении WHERE? / Is there a way to calculate a weighted sum with weights based on a WHERE expression?

Adriaan Stander

01.11.2013 10:09:20

Вы всегда можете попробовать что-то вроде

SELECT
    SUM(
        CASE
            WHEN VoteTypeId = 2 AND Posts.PostTypeId = 1
                THEN 1
            ELSE 0
        END
    )   QuestionsUp,
    SUM(
        CASE
            WHEN VoteTypeId = 3 AND Posts.PostTypeId = 1
                THEN 1
            ELSE 0
        END
    )   QuestionsDown,
    SUM(
        CASE
            WHEN VoteTypeId = 2 AND Posts.PostTypeId = 2
                THEN 1
            ELSE 0
        END
    )   AnswersUp,
    SUM(
        CASE
            WHEN VoteTypeId = 3 AND Posts.PostTypeId = 2
                THEN 1
            ELSE 0
        END
    )   AnswersDown
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.id
    INNER JOIN Posts ON Posts.ParentId = PostTags.PostId
    INNER JOIN Votes ON Votes.PostId = Posts.Id 
WHERE 
    Posts.OwnerUserId = @UserId and
    Tags.TagName = @Tag

РЕДАКТИРОВАТЬ:

Вы можете использовать CTE, а затем использовать столбцы в своих расчетах.

Что-то вроде

;WITH Vals AS (
        SELECT
            SUM(
                CASE
                    WHEN VoteTypeId = 2 AND Posts.PostTypeId = 1
                        THEN 1
                    ELSE 0
                END
            )   QuestionsUp,
            SUM(
                CASE
                    WHEN VoteTypeId = 3 AND Posts.PostTypeId = 1
                        THEN 1
                    ELSE 0
                END
            )   QuestionsDown,
            SUM(
                CASE
                    WHEN VoteTypeId = 2 AND Posts.PostTypeId = 2
                        THEN 1
                    ELSE 0
                END
            )   AnswersUp,
            SUM(
                CASE
                    WHEN VoteTypeId = 3 AND Posts.PostTypeId = 2
                        THEN 1
                    ELSE 0
                END
            )   AnswersDown
        FROM Tags
            INNER JOIN PostTags ON PostTags.TagId = Tags.id
            INNER JOIN Posts ON Posts.ParentId = PostTags.PostId
            INNER JOIN Votes ON Votes.PostId = Posts.Id 
        WHERE 
            Posts.OwnerUserId = @UserId and
            Tags.TagName = @Tag
        )
SELECT  QuestionsUp * 5 +
       AnswersUp * 10 +
       (QuestionsDown + AnswersDown) * -2
FROM    Vals
https://stackoverflow.com/questions/19721600/is-there-a-way-to-calculate-a-weighted-sum-with-weights-based-on-a-where-express/19721695#comment29299480_19721695
О, здорово! Есть ли способ покончить с переменными QuestionsUp, QuestionsDown, AnswersUp, AnswersDown в целом и выполнить весь расчет в пределах SELECT? (Мне нужна только сумма взвешенных баллов в конце.) Я попытался заменить THEN 1s соответствующими оценками (5, -2, 10, -2) и сделать все цели одной переменной-но это вернуло мне только 4 столбца (все с именем одной переменной) без каких-либо результатов.
Закрыть X