mysql - Extraño comportamiento duplicado de GROUP_CONCAT de dos LEFT JOINs de GROUP_BYs

group-by left-join

Question

Here está la estructura de todas mis tablas y la consulta (concéntrese en la última consulta, adjunta a continuación) . Como puede ver en el violín, aquí está la salida actual:

+---------+-----------+-------+------------+--------------+
| user_id | user_name | score | reputation | top_two_tags |
+---------+-----------+-------+------------+--------------+
| 1 | Jack | 0 | 18 | css,mysql |
| 4 | James | 1 | 5 | html |
| 2 | Peter | 0 | 0 | null |
| 3 | Ali | 0 | 0 | null |
+---------+-----------+-------+------------+--------------+

Es correcto y todo bien.

Ahora tengo una existencia más llamada "categoría". Cada publicación puede tener solo una categoría. Y también quiero obtener las dos categorías principales para cada usuario. Y here está mi nueva consulta. Como ve en el resultado, sucedieron algunos duplicados:

+---------+-----------+-------+------------+--------------+------------------------+
| user_id | user_name | score | reputation | top_two_tags | top_two_categories |
+---------+-----------+-------+------------+--------------+------------------------+
| 1 | Jack | 0 | 18 | css,css | technology,technology |
| 4 | James | 1 | 5 | html | political |
| 2 | Peter | 0 | 0 | null | null |
| 3 | Ali | 0 | 0 | null | null |
+---------+-----------+-------+------------+--------------+------------------------+

¿Ver? css,css , technology, technology . ¿Por qué estos son duplicados? Acabo de agregar uno más a la LEFT JOIN para las categories , exactamente como las tags . Pero no funciona como se esperaba e incluso afecta las etiquetas.

De todos modos, este es el resultado esperado:

+---------+-----------+-------+------------+--------------+------------------------+
| user_id | user_name | score | reputation | top_two_tags | category |
+---------+-----------+-------+------------+--------------+------------------------+
| 1 | Jack | 0 | 18 | css,mysql | technology,social |
| 4 | James | 1 | 5 | html | political |
| 2 | Peter | 0 | 0 | null | null |
| 3 | Ali | 0 | 0 | null | null |
+---------+-----------+-------+------------+--------------+------------------------+

¿Alguien sabe cómo puedo lograr eso?

CREATE TABLE users(id integer PRIMARY KEY, user_name varchar(5));
CREATE TABLE tags(id integer NOT NULL PRIMARY KEY, tag varchar(5));
CREATE TABLE reputations(
id integer PRIMARY KEY,
post_id integer /* REFERENCES posts(id) */,
user_id integer REFERENCES users(id),
score integer,
reputation integer,
date_time integer);
CREATE TABLE post_tag(
post_id integer /* REFERENCES posts(id) */,
tag_id integer REFERENCES tags(id),
PRIMARY KEY (post_id, tag_id));
CREATE TABLE categories(id INTEGER NOT NULL PRIMARY KEY, category varchar(10) NOT NULL);
CREATE TABLE post_category(
post_id INTEGER NOT NULL /* REFERENCES posts(id) */,
category_id INTEGER NOT NULL REFERENCES categories(id),
PRIMARY KEY(post_id, category_id)) ;
SELECT
q1.user_id, q1.user_name, q1.score, q1.reputation,
substring_index(group_concat(q2.tag ORDER BY q2.tag_reputation DESC SEPARATOR '',''), '','', 2) AS top_two_tags,
substring_index(group_concat(q3.category ORDER BY q3.category_reputation DESC SEPARATOR '',''), '','', 2) AS category
FROM
(SELECT
u.id AS user_Id,
u.user_name,
coalesce(sum(r.score), 0) as score,
coalesce(sum(r.reputation), 0) as reputation
FROM
users u
LEFT JOIN reputations r
ON r.user_id = u.id
AND r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
GROUP BY
u.id, u.user_name
) AS q1
LEFT JOIN
(
SELECT
r.user_id AS user_id, t.tag, sum(r.reputation) AS tag_reputation
FROM
reputations r
JOIN post_tag pt ON pt.post_id = r.post_id
JOIN tags t ON t.id = pt.tag_id
WHERE
r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
GROUP BY
user_id, t.tag
) AS q2
ON q2.user_id = q1.user_id
LEFT JOIN
(
SELECT
r.user_id AS user_id, c.category, sum(r.reputation) AS category_reputation
FROM
reputations r
JOIN post_category ct ON ct.post_id = r.post_id
JOIN categories c ON c.id = ct.category_id
WHERE
r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
GROUP BY
user_id, c.category
) AS q3
ON q3.user_id = q1.user_id
GROUP BY
q1.user_id, q1.user_name, q1.score, q1.reputation
ORDER BY
q1.reputation DESC, q1.score DESC ;

Answer 1

Su segunda consulta es de la forma:

q1 -- PK user_id
LEFT JOIN (...
GROUP BY user_id, t.tag
) AS q2
ON q2.user_id = q1.user_id
LEFT JOIN (...
GROUP BY user_id, c.category
) AS q3
ON q3.user_id = q1.user_id
GROUP BY -- group_concats

Los GROUP BY internos dan como resultado (user_id, t.tag) y (user_id, c.category) como claves / UNIQUEs. Aparte de eso, no abordaré esos GROUP BY.

TL; DR Cuando une (q1 JOIN q2) a q3 no está en una clave / ÚNICA de una de ellas, por lo que por cada user_id obtiene una fila para cada combinación posible de etiqueta y categoría. Entonces, el GROUP BY final ingresa duplicados por (user_id, tag) y per (user_id, category) e inapropiadamente GROUP_CONCAT duplica etiquetas y categorías por user_id. Correcto sería (q1 JOIN q2 GROUP BY) JOIN (q1 JOIN q3 GROUP BY) en el que todas las combinaciones están en la clave común / UNIQUE (user_id) y no hay agregación espuria. Aunque a veces puedes deshacer esa agregación espuria.

Un enfoque simétrico INNER JOIN correcto: IZQUIERDA UNIR q1 & q2--1: muchos, luego GROUP BY & GROUP_CONCAT (que es lo que hizo su primera consulta); luego por separado de manera similar IZQUIERDA UNIR q1 & q3--1: muchos - luego GROUP BY & GROUP_CONCAT; luego INTERIOR ÚNASE a los dos resultados EN user_id - 1: 1.

Un enfoque de subconsulta escalar simétrica correcta: SELECCIONE GROUP_CONCATs de q1 como subconsultas escalares cada una con un GROUP BY.

Un enfoque acumulativo correcto de IZQUIERDA IZQUIERDA: IZQUIERDA ÚNICA q1 y q2--1: muchos, luego GROUP BY & GROUP_CONCAT; luego IZQUIERDA UNIRSE a que & q3--1: muchos - luego GROUP BY & GROUP_CONCAT.

Un enfoque correcto como su segunda consulta: Primero, SE DEJÓ UNIRSE q1 y q2--1: muchos. Entonces DEJASTE UNIRTE a eso y q3 - muchos: 1: muchos. Proporciona una fila para cada combinación posible de una etiqueta y una categoría que aparecen con un user_id. Luego, después de GROUP BY you GROUP_CONCAT - sobre pares duplicados (id_usuario, etiqueta) y pares duplicados (id_usuario, categoría). Es por eso que tiene elementos de lista duplicados. Pero agregar DISTINCT a GROUP_CONCAT da un resultado correcto. (Según el comentario de wchiquito .)

Lo que prefiere es, como de costumbre, una compensación de ingeniería para estar informado por los planes de consulta y los tiempos, según los datos / uso / estadísticas reales. entrada y estadísticas para la cantidad esperada de duplicación), el tiempo de las consultas reales, etc. Un problema es si las filas adicionales de los muchos: 1: enfoque de unir muchos compensan el ahorro de un GROUP BY.

-- cumulative LEFT JOIN approach
SELECT
q1.user_id, q1.user_name, q1.score, q1.reputation,
top_two_tags,
substring_index(group_concat(q3.category ORDER BY q3.category_reputation DESC SEPARATOR '',''), '','', 2) AS category
FROM
-- your 1st query (less ORDER BY) AS q1
(SELECT
q1.user_id, q1.user_name, q1.score, q1.reputation,
substring_index(group_concat(q2.tag ORDER BY q2.tag_reputation DESC SEPARATOR '',''), '','', 2) AS top_two_tags
FROM
(SELECT
u.id AS user_Id,
u.user_name,
coalesce(sum(r.score), 0) as score,
coalesce(sum(r.reputation), 0) as reputation
FROM
users u
LEFT JOIN reputations r
ON r.user_id = u.id
AND r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
GROUP BY
u.id, u.user_name
) AS q1
LEFT JOIN
(
SELECT
r.user_id AS user_id, t.tag, sum(r.reputation) AS tag_reputation
FROM
reputations r
JOIN post_tag pt ON pt.post_id = r.post_id
JOIN tags t ON t.id = pt.tag_id
WHERE
r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
GROUP BY
user_id, t.tag
) AS q2
ON q2.user_id = q1.user_id
GROUP BY
q1.user_id, q1.user_name, q1.score, q1.reputation
) AS q1
-- finish like your 2nd query
LEFT JOIN
(
SELECT
r.user_id AS user_id, c.category, sum(r.reputation) AS category_reputation
FROM
reputations r
JOIN post_category ct ON ct.post_id = r.post_id
JOIN categories c ON c.id = ct.category_id
WHERE
r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
GROUP BY
user_id, c.category
) AS q3
ON q3.user_id = q1.user_id
GROUP BY
q1.user_id, q1.user_name, q1.score, q1.reputation
ORDER BY
q1.reputation DESC, q1.score DESC ;