mysql - lentas - ¿Cómo puedo contar la cantidad de publicaciones?

count mysql

Question

Aquí está la estructura de mi mesa:

-- reputations
+----+-------------+---------+-------+------------+------------+
| id | post_id | user_id | score | reputation | date_time |
+----+-------------+---------+-------+------------+------------+ -- Suppose:
| 1 | 1 | 1 | 1 | 5 | 1500489844 | -- out of last week
| 2 | 4 | 3 | -1 | -2 | 1500499815 | -- out of last week
| 3 | 2 | 3 | 1 | 5 | 1500584821 |
| 4 | 3 | 1 | 1 | 5 | 1501389166 |
| 5 | 2 | 4 | 1 | 5 | 1501399142 |
| 6 | 2 | 1 | -1 | -2 | 1501399142 |
| 7 | 4 | 1 | 0 | 15 | 1501481186 |
| 8 | 5 | 1 | 1 | 5 | 1501481297 |
+----+-------------+---------+-------+------------+------------+
-- Note: the last row came from an accepted-answer, that''s why its score is 0
-- post_tag
+---------+--------+
| post_id | tag_id |
+---------+--------+
| 1 | 2 |
| 1 | 4 |
| 2 | 2 |
| 3 | 1 |
| 3 | 4 |
| 4 | 3 |
| 5 | 1 |
+---------+--------+
-- tags
+----+--------+
| id | name |
+----+--------+
| 1 | php |
| 2 | html |
| 3 | css |
| 4 | mysql |
+----+--------+

Y aquí está mi consulta:

SELECT
t.tag, sum(r.reputation) AS tag_reputation, sum(r.score) AS tag_score
FROM
users u
LEFT JOIN reputations r
ON r.user_id = u.id
AND r.date_time > 1500584821
JOIN post_tag pt ON pt.post_id = r.post_id
JOIN tags t ON t.id = pt.tag_id
WHERE u.id = 1 -- Specific user: Jack
GROUP BY
u.id, u.user_name, t.tag
ORDER BY
u.id, tag_reputation DESC;

Y aquí está el resultado actual:

tag | tag_reputation | tag_score
----: | :------------- | :---------
css | 15 | 0
php | 10 | 2
mysql | 5 | 1
html | -2 | -1

Como puede ver, el resultado es una lista de etiquetas con reputaciones y puntajes para un usuario específico ordenado por tag_reputation . Violín

Ahora también quiero contar la cantidad de publicaciones para cada etiqueta. Entonces este es el resultado esperado :

tag | tag_reputation | tag_score | post_num
----: | :------------- | :--------- | :-------
css | 15 | 0 | 1
php | 10 | 2 | 2
mysql | 5 | 1 | 1
html | -2 | -1 | 1

¿Cómo puedo hacer eso? Creo que tengo que trabajar en la columna post_id y en la cláusula GROUP BY . Pero no sé exactamente cómo.

Answer 1

¿Intentó agregar la identificación postal a la consulta?

count(r.post_id) as post_num en tu declaración de select y arrojó los resultados esperados .

Para eliminar duplicados, use distinct . ¿Quieres el conteo con respecto a la tabla de etiquetas? Tratar

1. COUNT(distinct pt.post_id) AS post_count ; o
2. COUNT(distinct r.post_id) AS post_count

Answer 2

Algunas cosas sobre los resultados esperados no se alinean exactamente; por ejemplo, los resultados esperados incluyen publicaciones anteriores a la fecha que excluyó en su cláusula where, y los resultados de la consulta original incluyen resultados que no provienen del ID de usuario = 1, así que Estoy un poco confundido en general, pero:

Creo que acaba de agregar:

SELECT
t.tag, sum(r.reputation) AS tag_reputation, sum(r.score) AS tag_score, count(r.id) as post_num

debería hacer que esto funcione?

De lo contrario, puede usar una selección en línea en su declaración de selección:

SELECT
t.tag, sum(r.reputation) AS tag_reputation, sum(r.score) AS tag_score,
max((select count(r2.id) from users u2
LEFT JOIN reputations r2 ON r2.user_id = u2.id AND r2.date_time > 1500584821
JOIN post_tag pt2 ON pt2.post_id = r2.post_id
JOIN tags t2 ON t2.id = pt2.tag_id
where t.tag = t2.tag)) AS post_num
FROM
users u
LEFT JOIN reputations r
ON r.user_id = u.id
AND r.date_time > 1500584821
JOIN post_tag pt ON pt.post_id = r.post_id
JOIN tags t ON t.id = pt.tag_id
WHERE u.id = 1 -- Specific user: Jack
GROUP BY
u.id, u.user_name, t.tag
ORDER BY
u.id, tag_reputation DESC;

Answer 3

Publico esto usando un teléfono móvil, así que no puedo probarlo con el violín que me diste. Modifico tu SQL para contar la cantidad de publicaciones usando COUNT .

SELECT
t.tag,
sum(r.reputation) AS tag_reputation,
sum(r.score) AS tag_score,
COUNT(DISTINCT pt.post_id) AS post_num
FROM
users u
LEFT JOIN reputations r
ON r.user_id = u.id
AND r.date_time > 1500584821
JOIN post_tag pt ON pt.post_id = r.post_id
JOIN tags t ON t.id = pt.tag_id
WHERE u.id = 1 -- Specific user: Jack
GROUP BY
u.id, u.user_name, t.tag
ORDER BY
u.id, tag_reputation DESC;

Editar: agrego COUNT con DISTINCT. Vea si lo soluciona.