如何计算得分为零或正数的帖子数？ [英] How can I count the number of posts that have either zero or positive vote score?
问题描述
我有这个表：
// qanda
+ ---- + ----- ----------------------------------- + --------- + ---- ------- + ------ +
| Id |身体|相关| user_id |免费|
+ ---- + ---------------------------------------- + --------- + ----------- + ------ +
| 1 |问题1的内容null | 2 | null |
| 2 |问题1的第一个答案的内容1 | 2 | null |
| 3 |问题2的内容null | 6 | 300 |
| 4 |问题1的第二个答案的内容1 | 4 | null |
| 5 |问题2的第一个答案的内容3 | 2 | null |
| 6 |问题3的内容NULL | 8 | null |
| 7 |问题3的第一个答案的内容6 | 4 | null |
| 8 |问题3的第二个答案的内容6 | 2 | null |
+ ---- + ---------------------------------------- + --------- + ----------- + ------ +
我有这个查询：
SELECT count（1）
FROM qanda问题
JOIN qanda answer ON question.Id = answer.related
WHERE answer.related IS NOT NULL
AND answer.user_id = 2
AND question.free IS NULL;
正如你所知，上面的查询返回了属于免费问题的答案数这种情况下，返回 2 ）。好了所有罚款。我也有这个表：
//票
+ ---- + ------- - + ------- +
| id | post_id |值|
+ ---- + --------- + ------- +
| 1 | 2 | 1 |
| 2 | 3 | -1 |
| 3 | 2 | 1 |
| 4 | 8 | -1 |
| 5 | 1 | 1 |
| 6 | 4 | 1 |
| 7 | 2 | -1 |
| 8 | 8 | 1 |
| 9 | 8 | -1 |
| 10 | 8 | -1 |
+ ---- + --------- + ------- +
我需要消除总分为负的答案。所以在这种情况下应该返回 1 。因为 qanda.id = 8 ，该答案有 -2 总投票分数，所以我不想算吧。
排除总票数小于零的帖子的最简单方法是这样的：
SELECT count（1）
从qanda问题
JOIN qanda answer ON question.Id = answer.related
WHERE answer.related IS NOT NULL
AND answer.user_id = 2
AND question.free IS NULL
AND question.id不在（
select post_id
从票
group by post_id
具有总和（值）<0）
这里的关键部分是具有sum（value）的 0
从注释...
要找到具有太多不良答案的用户，您应该可以返回他们做出多少好答案，并确定他们是否是坏用户。例如，具有5个答案的用户都是坏的，与具有1000个答案的用户非常不同，其中只有5个是坏的，即使他们都有5个错误的答案。
尝试以下操作：
select
sum（score< 0）bad，
count *）total，
sum（score <0）/ sum（.01）percent_bad
from（
SELECT coalesce（sum（value），0）score
from qanda question
JOIN qanda answer ON question.Id = answer.related
LEFT JOIN votes ON votes.post_id = answer.id
WHERE answer.related IS NOT NULL
AND answer.user_id = 2
AND question.free IS NULL
AND answer.timestamp> subdate（now（），365）
GROUP BY answer.id
）scores
关于一些SQL功夫的几个注释：
- 在MySQL中，true是1，false是0，所以通过求和一个条件，你可以计算它的真实的次数。与其他数据库所需的笨拙的
SUM（CASE ...）表达式相比，这更容易编写代码并更易于阅读 -
SUM（.01）（我只是想到BTW）是一个最简单的方法来获得一个百分比，因为它不仅简化了表达式，
免责声明：代码可能无法编译或工作，因为它是thumbed在我的手机上（但有一个合理的机会，它会工作）
I have this table:
// qanda
+----+----------------------------------------+---------+-----------+------+
| Id | body | related | user_id | free |
+----+----------------------------------------+---------+-----------+------+
| 1 | content of question1 | null | 2 | null |
| 2 | content of first answer for question1 | 1 | 2 | null |
| 3 | content of question2 | null | 6 | 300 |
| 4 | content of second answer for question1 | 1 | 4 | null |
| 5 | content of first answer for question2 | 3 | 2 | null |
| 6 | content of question3 | NULL | 8 | null |
| 7 | content of first answer for question3 | 6 | 4 | null |
| 8 | content of second answer for question3 | 6 | 2 | null |
+----+----------------------------------------+---------+-----------+------+
And I have this query:
SELECT count(1)
FROM qanda question
JOIN qanda answer ON question.Id = answer.related
WHERE answer.related IS NOT NULL
AND answer.user_id = 2
AND question.free IS NULL;
As you know, query above returns the number of answers that belongs to free questions (in this case, it returns 2). Ok all fine. I also have this table:
// votes
+----+---------+-------+
| id | post_id | value |
+----+---------+-------+
| 1 | 2 | 1 |
| 2 | 3 | -1 |
| 3 | 2 | 1 |
| 4 | 8 | -1 |
| 5 | 1 | 1 |
| 6 | 4 | 1 |
| 7 | 2 | -1 |
| 8 | 8 | 1 |
| 9 | 8 | -1 |
| 10 | 8 | -1 |
+----+---------+-------+
And I need to eliminate the answers that have negative total score. So it should return 1 in this case. Because where qanda.id = 8, that answer has -2 total vote score, So I don't want to count it. How can I do that?
The simplest way to exclude posts whose total votes are less than zero is like this:
SELECT count(1)
FROM qanda question
JOIN qanda answer ON question.Id = answer.related
WHERE answer.related IS NOT NULL
AND answer.user_id = 2
AND question.free IS NULL
AND question.id not in (
select post_id
from votes
group by post_id
having sum(value) < 0)
The key part here is the having sum(value) < 0 which select posts with net negative votes.
From comments...
To find users who have too many "bad" answers, you should probably return how many "good" answers they made and decide if overall they're a "bad" user. For example, a user that has 5 answers that are all bad is very different from a user with 1000 answers of which only 5 are bad, even though they both have 5 bad answers.
Try this:
select
sum(score < 0) bad,
count(*) total,
sum(score < 0) / sum(.01) percent_bad
from (
SELECT coalesce(sum(value), 0) score
FROM qanda question
JOIN qanda answer ON question.Id = answer.related
LEFT JOIN votes ON votes.post_id = answer.id
WHERE answer.related IS NOT NULL
AND answer.user_id = 2
AND question.free IS NULL
AND answer.timestamp > subdate(now(), 365)
GROUP BY answer.id
) scores
A couple of notes on some SQL Kung Fu in there:
- in MySQL, true is 1 and false is 0, so by summing a condition, you count how many times it's true. This is much simpler to code, and easier to read, than the clumsy
SUM(CASE ...)expressions needed by other DBs - diving a count by
SUM(.01)(which I only just thought of BTW) is the briefest way to get a percentage, as it not only simplifies the expression, but concerts the answer to float so you automatically avoid integer arithmetic rounding
Disclaimer: Code may not compile or work as it was thumbed in on my phone (but there's a reasonable chance it will work)
这篇关于如何计算得分为零或正数的帖子数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
