Выберите репутацию SO для каждого сообщения для данного пользователя

В настоящее время я работаю над SQL-запросом для SEDE, который выбирает все сообщения пользователя из переполнения стека и отображает, как много репутации каждый пост генерируется.

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

Поэтому я бы сгруппировал по Post id, чтобы отобразить Total score и показать, сколько репутации получено в целом.

Репутацию, которую производит каждый голос, можно увидеть здесь:

+-----------+----------+--------+
| Post type | Question | Answer |
+-----------+----------+--------+
| Upvote    |    5     |   10   |
+-----------+----------+--------+
| Downvote  |    2     |    2   |
+-----------+----------+--------+
| Accept    |    5     |   15   |
+-----------+----------+--------+

Схему базы данных, на которую я ориентируюсь, можно найти здесь. .

Мой запрос пока выглядит так:

select 
  p.Id as 'Post id', 
  pt.Name as 'Post type',
  p.Score as 'Total score',
  (
    case vt.Id
      when 1 then 'Accept'
      when 2 then 'Upvote'
      else 'Downvote'
      end
  ) as 'Reputation type'
from 
  Posts p
join 
  Votes v
on
  v.PostId = p.Id
join 
  VoteTypes vt
on 
  vt.Id = v.VoteTypeId
join
  PostTypes pt
on 
  pt.Id = p.PostTypeId
where
  p.OwnerUserId = ##UserId##
and 
  vt.Id in (1, 2, 3)
order by
  p.Score,
  vt.Id
asc

И вывод, который он производит, выглядит примерно так:

Я попытался сгруппировать по Vote type id:

group by  
  vt.id

так что я мог бы хотя бы узнать, сколько разных голосов набрал каждый пост, используя что-то вроде:

select
  ....
  count(vt.id)

но затем я получаю сообщение об ошибке, что столбец Posts.Id не может быть разрешен:

Текущий исполняемый, но неполный запрос можно найти здесь (вы должны ввести свой идентификатор пользователя для запуска)


sql dataexplorer sql-server-2017
person Lino    schedule 10.04.2019    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Репутация на публикацию немного сложнее.
В дополнение к голосованию "за"/"против"/ "принято" есть также:

  1. Баунти,
  2. Репсовые кепки,
  3. Пол репутации пользователя (пользователь не может иметь менее 1 репутации (обычно)).
  4. Вики сообщества.
  5. Спам или оскорбительные флаги.
  6. Голоса за вопросы учитываются по-разному, в зависимости от сайта.
  7. Многие сообщения вообще не имеют голосов, поэтому SQL должен это отражать. (Левое соединение, COALESCE и т. д.)
  8. Учитываете ли вы одобренное предложенное редактирование в сообщении (2 балла)?
  9. Наверное, что-то еще, что я забываю.


В зависимости от того, что вам действительно нужно, вы можете использовать этот метод API (а затем группировка результатов по post_id).

В любом случае, ваш запрос немного изменен:

SELECT      p.Id      AS [Post Link]
            , pt.Name AS 'Post type'
            , p.Score AS 'Total score'
            , COALESCE (vtStats.AcceptRep, 0)   AS [Accept Rep]
            , COALESCE (vtStats.numUpvotes, 0)  *  (
                CASE  p.PostTypeId
                    WHEN  1  THEN  5  -- Questions on most sites
                    WHEN  2  THEN 10  -- Answers
                    ELSE  1  -- Should not happen, but don't zero out
                END
            ) AS [Up Vt Rep]
            , COALESCE (vtStats.DwnVtRep, 0)    AS [Dwn Vt Rep]
FROM        Posts p
LEFT JOIN   (
    SELECT      v.PostId
                , SUM (CASE v.VoteTypeId    WHEN 1  THEN 15  ELSE 0  END)   AS AcceptRep
                , SUM (CASE v.VoteTypeId    WHEN 3  THEN -2  ELSE 0  END)   AS DwnVtRep
                , SUM (CASE v.VoteTypeId    WHEN 2  THEN  1  ELSE 0  END)   AS numUpvotes -- Needs special handling
    FROM        Votes v
    WHERE       v.VoteTypeId IN (1, 2, 3 )
    GROUP BY    v.PostId
)
AS vtStats  ON  vtStats.PostId = p.Id
INNER JOIN  Posttypes pt    ON pt.Id = p.PostTypeId
WHERE       p.OwnerUserId = ##UserId:Int##
ORDER  BY   p.Score DESC
            , [Accept Rep] DESC
            , pt.Name
person Brock Adams    schedule 10.04.2019
comment
Я отредактировал ваш запрос и добавил столбец net rep, вот запрос :) - person Lino; 16.04.2019
comment
Ладно, хорошо. Имейте в виду, что оба запроса являются всего лишь аппроксимациями первого порядка по причинам, перечисленным выше (за исключением #7, который обрабатывается). Кроме того, при повторном прочтении я забыл отметить, что самостоятельные ответы не дают никакой репутации «принятие-голосование». - person Brock Adams; 16.04.2019
comment
Да, возможно, в будущем я усовершенствую его, чтобы обрабатывать все случаи, но на данный момент это все, что мне нужно. - person Lino; 16.04.2019

arrow_upward
0
arrow_downward

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

 select 
 p.Id as 'Post id', 
 pt.Name as 'Post type',
 SUM
 (
    case vt.Id
    when 1 then 15
    when 2 then 10
    else -2
    end
    ) as 'Total Score'
    .....
   GROUP BY P.Id, Pt.Name
   ORDER BY....
person sabhari karthik    schedule 10.04.2019