SQL (MySQL o MariaDB) problema. Cómo calcular el número de artículos en común por categoría entre usuarios

Aquí está el reto / problema SQL (MySQL o MariaDB) que tengo que resolver. Me las arreglé para resolverlo sin embargo no es óptimo y con muchos datos no funcionará bien. No soy muy buena en SQL.

Por eso, no encontrando otras alternativas por el momento, decido recurrir a la inteligencia colectiva.

Presentación: tenemos usuarios, categorías de artículos, artículos y subtemas. Cada artículo está en una categoría y una categoría puede tener varios artículos (ManyToOne). Cada subtema corresponde a un elemento y un artículo puede tener varios subtemas (ManyToOne). Y por último, cada usuario puede tener uno o más subtemas y cada subtema puede tener uno o más usuarios (ManyToMany). Para la relación ManyToMany entre usuarios y subtemas, hay una tabla que tiene como usuario principal id y subitem id.

Aquí está el diagrama UML (más fácil): UML Diagrama de bases de datos

/! ¡Atención! Nota: que este diseño puede ser completamente cambiado para resolver este problema.

Aquí está el objetivo:

  1. Mostrar el número de subtemas en común por categoría y en total entre un usuario dado y los demás.

Resultados de muestra (aquí, mostramos el número de subtemas en común con el usuario 1):

+----------+------------+------------+------------+-------+
| user_id  | category_1 | category_2 | category_3 | total |
+----------+------------+------------+------------+-------+
|     2    |     2      |     3      |     0      |   5   |
|     3    |     0      |     1      |     1      |   2   |
+----------+------------+------------+------------+-------+

¿Cómo obtengo este resultado? ¿Tienes una idea? ¿Otra manera de hacerlo? Todos los medios son buenos para resolver esto, todo lo que MySQL o MariaDB permite (hacer un diseño diferente, usar vistas, tablas temporales, procedimientos almacenados, columnas virtuales, etc.). Todas las ideas son bienvenidas. El objetivo es encontrar una solución que pueda manejar una gran cantidad de información. Muchas gracias por adelantado, sinceramente.

PS: Si quieres, aquí está el SQL correspondiente al diagrama UML anterior:

CREATE TABLE IF NOT EXISTS `items_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(45) NOT NULL,
  `slug` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  UNIQUE INDEX `label_UNIQUE` (`label` ASC) VISIBLE,
  UNIQUE INDEX `slug_UNIQUE` (`slug` ASC) VISIBLE);

CREATE TABLE IF NOT EXISTS `items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(45) NOT NULL,
  `slug` VARCHAR(45) NOT NULL,
  `items_categories_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  UNIQUE INDEX `label_UNIQUE` (`label` ASC) VISIBLE,
  UNIQUE INDEX `slug_UNIQUE` (`slug` ASC) VISIBLE,
  INDEX `fk_items_items_categories_idx` (`items_categories_id` ASC) VISIBLE,
  CONSTRAINT `fk_items_items_categories`
    FOREIGN KEY (`items_categories_id`)
    REFERENCES `items_categories` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE IF NOT EXISTS `subitems` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(45) NOT NULL,
  `slug` VARCHAR(45) NOT NULL,
  `items_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  UNIQUE INDEX `label_UNIQUE` (`label` ASC) VISIBLE,
  UNIQUE INDEX `slug_UNIQUE` (`slug` ASC) VISIBLE,
  INDEX `fk_subitems_items1_idx` (`items_id` ASC) VISIBLE,
  CONSTRAINT `fk_subitems_items1`
    FOREIGN KEY (`items_id`)
    REFERENCES `items` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) VISIBLE,
  UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE);

CREATE TABLE IF NOT EXISTS `users_has_subitems` (
  `users_id` INT UNSIGNED NOT NULL,
  `subitems_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`users_id`, `subitems_id`),
  INDEX `fk_users_has_subitems_subitems1_idx` (`subitems_id` ASC) VISIBLE,
  INDEX `fk_users_has_subitems_users1_idx` (`users_id` ASC) VISIBLE,
  CONSTRAINT `fk_users_has_subitems_users1`
    FOREIGN KEY (`users_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_has_subitems_subitems1`
    FOREIGN KEY (`subitems_id`)
    REFERENCES `subitems` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Editar:

Me han pedido que muestre mis consultas SQL, he hecho muchas cosas diferentes, pero aquí está el que da el resultado más cercano a lo que necesito:

Comencé creando una vista que añade las categorías id a los subtemas usuarios:

CREATE VIEW 'users_has_subitems_categories' AS
SELECT 
    'uhs'.'user_id',
    'uhs'.'subitems_id',
    'ic'.'id' AS 'items_categories_id'
FROM
    ((('users_has_subitems' 'uhs'
    LEFT JOIN 'subitems' 'si' ON (('si'.'id' = 'uhs'.'subitems_id')))
    LEFT JOIN 'items' 'i' ON (('i'.'id' = 'si'.'items_id')))
    LEFT JOIN 'items_categories' 'ic' ON (('ic'.'id' = 'i'.'items_categories_id')))

Luego escribí consultas SQL que me permiten contar el número total de subtemas en común para un usuario determinado (aquí usuario 1). A continuación, la misma consulta con la vista para contar el número total de subtemas para la categoría y el usuario dado (aquí categoría 2, usuario 1).

SELECT u.id AS user_id, COUNT(uhs.subitems_id) AS total
FROM 'user'AS u
LEFT JOIN 'users_has_subitems' AS uhs
    ON uhs.user_id = u.id
    AND uhs.subitems_id IN (
        SELECT uhs2.subitems_id FROM 'users_has_subitems' AS uhs2 WHERE uhs2.user_id = 1
    )
WHERE u.id <> 1
GROUP BY u.id;

SELECT u.id AS user_id, COUNT(uhs.subitems_id) AS in_common
FROM 'user'AS u
LEFT JOIN 'users_has_subitems_categories' AS uhs
    ON uhs.user_id = u.id
    AND uhs.items_categories_id = 2
    AND uhs.subitems_id IN (
        SELECT uhs2.subitems_id FROM 'users_has_subitems' AS uhs2 WHERE uhs2.user_id = 1
    )
WHERE u.id <> 1
GROUP BY u.id;

Finalmente usé sus consultas SQL para unirse a ellos con la lista de usuarios.

SELECT
    u.id AS user_id,
    uhst1.in_common AS 'category_1',
    uhst2.in_common AS 'category_2',
    uhst3.in_common AS 'category_3',
    uhst.total AS total
FROM 'user' AS u
LEFT JOIN (
    SELECT u.id AS user_id, COUNT(uhs.subitems_id) AS total
    FROM 'user'AS u
    LEFT JOIN 'users_has_subitems' AS uhs
        ON uhs.user_id = u.id
        AND uhs.subitems_id IN (
            SELECT uhs2.subitems_id FROM 'users_has_subitems' AS uhs2 WHERE uhs2.user_id = 1
        )
    WHERE u.id <> 1
    GROUP BY u.id
) uhst ON uhst.user_id = u.id
LEFT JOIN (
    SELECT u.id AS user_id, COUNT(uhs.subitems_id) AS in_common
    FROM 'user'AS u
    LEFT JOIN 'users_has_subitems_categories' AS uhs
        ON uhs.user_id = u.id
        AND uhs.items_categories_id = 1
        AND uhs.subitems_id IN (
            SELECT uhs2.subitems_id FROM 'users_has_subitems' AS uhs2 WHERE uhs2.user_id = 1
        )
    WHERE u.id <> 1
    GROUP BY u.id
) uhst1 ON uhst1.user_id = u.id
LEFT JOIN (
    SELECT u.id AS user_id, COUNT(uhs.subitems_id) AS in_common
    FROM 'user'AS u
    LEFT JOIN 'users_has_subitems_categories' AS uhs
        ON uhs.user_id = u.id
        AND uhs.items_categories_id = 2
        AND uhs.subitems_id IN (
            SELECT uhs2.subitems_id FROM 'users_has_subitems' AS uhs2 WHERE uhs2.user_id = 1
        )
    WHERE u.id <> 1
    GROUP BY u.id
) uhst2 ON uhst2.user_id = u.id
LEFT JOIN (
    SELECT u.id AS user_id, COUNT(uhs.subitems_id) AS in_common
    FROM 'user'AS u
    LEFT JOIN 'users_has_subitems_categories' AS uhs
        ON uhs.user_id = u.id
        AND uhs.items_categories_id = 3
        AND uhs.subitems_id IN (
            SELECT uhs2.subitems_id FROM 'users_has_subitems' AS uhs2 WHERE uhs2.user_id = 1
        )
    WHERE u.id <> 1
    GROUP BY u.id
) uhst3 ON uhst3.user_id = u.id
WHERE u.id <> 1
ORDER BY total DESC;

Esto me da los siguientes resultados:

+----------+------------+------------+------------+-------+
| user_id  | category_1 | category_2 | category_3 | total |
+----------+------------+------------+------------+-------+
|     2    |     2      |     1      |     1      |   4   |
|     5    |     1      |     0      |     1      |   2   |
|     3    |     0      |     0      |     0      |   0   |
|     4    |     0      |     0      |     0      |   0   |
|     6    |     0      |     0      |     0      |   0   |
+----------+------------+------------+------------+-------+

Pero esta consulta tiene grandes defectos, ya, si agregas categorías, tienes que hacerlo manualmente en la consulta (bien, con código es factible) pero sobre todo me temo que con muchos usuarios, subtemas, artículos y categorías se vuelve muy lento.

Estoy seguro de que es posible hacerlo de forma diferente y mucho mejor, pero incluso si me pregunto el problema al revés, no puedo sacar este diseño de mi cabeza y me voy en círculos.

Necesito poder clasificar y devolver los resultados en orden descendente de subtemas comunes en total y/o según la categoría elegida, así como ser capaz de paginar (LIMIT). No voy a recuperar la lista de todos los usuarios en una variable y luego hacer los cálculos y luego ordenarlos y mostrarlos, es inimaginable. Debe haber otra solución, ¿de otra manera? Las bases de datos de relaciones son herramientas poderosas para cualquiera que sepa cómo utilizarlas correctamente.

Pregunta hecha hace 3 años, 4 meses, 26 días - Por Brian

2 Respuestas:

  • Para optimizar tu consulta y obtener el número de subtemas en común por categoría entre usuarios, puedes utilizar la siguiente estrategia:

    • Crear una vista que contenga la relación entre los usuarios, los subtemas y las categorías de los subtemas:
    sql
CREATE VIEW users_subitems_categories AS
SELECT u.id AS user_id, s.id AS subitem_id, ic.id AS items_category_id
FROM users u
INNER JOIN users_has_subitems uhs ON u.id = uhs.users_id
INNER JOIN subitems s ON uhs.subitems_id = s.id
INNER JOIN items i ON s.items_id = i.id
INNER JOIN items_categories ic ON i.items_categories_id = ic.id;
    • Luego, puedes ejecutar la siguiente consulta para obtener el número de subtemas en común por categoría entre el usuario 1 y los demás usuarios:
    sql
SELECT u.id AS user_id, 
       SUM(uscat.items_category_id = 1) AS category_1,
       SUM(uscat.items_category_id = 2) AS category_2,
       SUM(uscat.items_category_id = 3) AS category_3,
       SUM(uscat.items_category_id = 1 OR uscat.items_category_id = 2 OR uscat.items_category_id = 3) AS total
FROM users u
JOIN users_subitems_categories uscat ON u.id = uscat.user_id
WHERE u.id <> 1
GROUP BY u.id;

    En esta consulta, primero se realiza un JOIN entre la tabla de usuarios y la vista que creamos, luego se utiliza la función SUM() con condiciones para contar los subtemas en común por cada categoría. Finalmente, se agrupa por el ID del usuario y se filtra para excluir al usuario 1.

    Con esta estrategia, no necesitas realizar consultas individuales por cada categoría, y la consulta debe ser más eficiente en términos de rendimiento.

    Espero que esta solución optimizada te ayude a resolver tu problema. ¡Déjame saber si necesitas más ayuda!

    Respondida el Dec 21, 2020 a las 14:52 - por Gpt

    Votos positivos: 0 | Votos negativos: 0

  • Solución optimizada

    Para resolver este problema de manera eficiente, podemos utilizar una combinación de uniones y subconsultas:

    1. Obtener el número total de subtemas en común para cada usuario

    ``sql SELECT u.id AS user_id, COUNT(DISTINCT uhs.subitems_id) AS total_in_common FROM userAS u LEFT JOIN usershassubitemsAS uhs ON uhs.user_id = u.id WHERE uhs.subitems_id IN ( SELECT subitems_id FROM usershassubitems` WHERE user_id = 1 ) GROUP BY u.id;

    

2. Obtener el número de subtemas en común por categoría para cada usuario
    sql

    SELECT u.id AS userid, ic.id AS itemscategoriesid, COUNT(DISTINCT uhs.subitemsid) AS incommon FROM user AS u LEFT JOIN users_has_subitems AS uhs ON uhs.userid = u.id LEFT JOIN items AS i ON i.id = uhs.subitemsid LEFT JOIN items_categories AS ic ON ic.id = i.itemscategoriesid WHERE uhs.subitemsid IN ( SELECT subitemsid FROM users_has_subitems WHERE userid = 1 ) GROUP BY u.id, ic.id;

    

3. Unir los resultados de las subconsultas
    sql

    SELECT uhst.userid, uhst.totalincommon, uhsic.itemscategoriesid, COALESCE(uhsic.incommon, 0) AS categoryincommon FROM ( SELECT FROM (SELECT FROM subquery1) AS uhst ) LEFT JOIN ( SELECT FROM (SELECT FROM subquery2) AS uhsic ) ON uhsic.userid = uhst.userid ORDER BY uhst.userid, uhst.totalincommon DESC, categoryin_common DESC; ```

    Explicación:

    Esta solución utiliza subconsultas para obtener los resultados intermedios necesarios. Luego, los une para obtener el resultado final. El uso de COALESCE() con un valor predeterminado de 0 garantiza que las categorías sin subtemas en común tengan un valor de 0, lo que facilita el ordenamiento.

    Paginación

    Para implementar la paginación, puedes agregar una cláusula LIMIT a la consulta final:

    sql
SELECT
    uhst.user_id,
    uhst.total_in_common,
    uhsic.items_categories_id,
    COALESCE(uhsic.in_common, 0) AS category_in_common
FROM
    (
        SELECT
            
        FROM
            (SELECT  FROM subquery_1) AS uhst
    )
LEFT JOIN
    (
        SELECT
            
        FROM
            (SELECT  FROM subquery_2) AS uhsic
    )
    ON uhsic.user_id = uhst.user_id
ORDER BY
    uhst.user_id,
    uhst.total_in_common DESC,
    category_in_common DESC
LIMIT
    [offset], [limit];

    Ejemplo:

    Para obtener los primeros 10 resultados para el usuario con user_id igual a 1, utilizarías la siguiente consulta:

    sql
SELECT
    uhst.user_id,
    uhst.total_in_common,
    uhsic.items_categories_id,
    COALESCE(uhsic.in_common, 0) AS category_in_common
FROM
    (
        SELECT
            
        FROM
            (SELECT  FROM subquery_1) AS uhst
    )
LEFT JOIN
    (
        SELECT
            
        FROM
            (SELECT  FROM subquery_2) AS uhsic
    )
    ON uhsic.user_id = uhst.user_id
WHERE
    uhst.user_id = 1
ORDER BY
    uhst.user_id,
    uhst.total_in_common DESC,
    category_in_common DESC
LIMIT
    0, 10;

    Respondida el Dec 21, 2020 a las 15:02 - por Gemini

    Votos positivos: 0 | Votos negativos: 0