Profundizando en un SQL complejo aquí. Quiero crear una vista/tabla virtual.
Tengo una tabla de objetos . Que se parece a esto (todos los valores son int)
| identificación | parent_id | clave_extranjera | fecha_inicio | fecha_fin |
CREATE TABLE objects ( id int AUTO_INCREMENT PRIMARY KEY, parent_id INT(11), foreign_key INT(11), start_date INT(11), end_date INT(11) ); INSERT INTO objects VALUES (1, 0, 1, 1638930577, 1638930578), (2, 1, 1, 1638930578, 1638930578), (3, 2, 1, 1638930578, 1638930578), (4, 0, 2, 1638930576, 1638930578), (5, 4, 2, 1638930578, 1638930578), (6, 5, 2, 1638930578, 1638930578), (7, 0, 1, 1638930574, 1638930578), (8, 7, 1, 1638930578, 1638930578), (9, 7, 1, 1638930578, 1638930579)Quiero crear una vista que se parezca a
| identificación | fecha_inicio | fecha_fin | número_de_objetos |
Nunca antes había usado vistas de SQL y sería ideal en mi situación en lugar de escribir código. es posible? Mi principal problema es que cuando hago esto en el código, necesito darle un object_id para comenzar, realmente no estoy seguro de cómo obtener un grupo de objetos con la misma identificación principal que no es 0.
Gracias
PD Usar la versión más reciente de MySQL/Maria, pero el dialecto probablemente no sea muy importante.
Violín SQL: http://sqlfiddle.com/#!9/429570/1
El resultado de retorno de la nueva vista, proporcionado desde el programa, debería verse como
| identificación | clave externa | fecha de inicio | fecha final | numero_de_objetos |
|---|---|---|---|---|
| 1 | 1 | 1638930577 | 1638930578 | 3 |
| 2 | 2 | 1638930576 | 1638930578 | 3 |
| 3 | 1 | 1638930574 | 1638930579 | 3 |
El resultado de retorno debe construirse algo como esto:
objectGroup . El elemento en la nueva vista consta deParece que sus datos tienen tres niveles de profundidad (pueden ser más profundos). Puede usar la recursividad para atravesar de padre a hijo a nietos. Desafortunadamente, esto requiere MySQL 8 o posterior:
with recursive rcte as ( /*** select all topmost level rows the id and foreign_key of the parent will be "copied" to all children and grandchildren the last id column is needed to link the next set of rows with this one ***/ select id as group_id, foreign_key, start_date, end_date, id from objects where parent_id = 0 union all /*** p is the set of rows from previous iteration (this is how recursive cte works) c is the set of rows that are direct children of p p.group_id and p.foreign_key are copied from parent (which in turn were copied from their parent and so on) ***/ select p.group_id, p.foreign_key, c.start_date, c.end_date, c.id from rcte as p join objects as c on c.parent_id = p.id ) /*** all "object groups" now have same group_id and foreign_key we just need to group by ***/ select group_id, foreign_key, min(start_date) as start_date, max(end_date) as end_date, count(*) as number_of_objects from rcte group by group_id, foreign_keyEs posible que desee considerar otros modelos para almacenar los datos. La lista de adyacencia (la que está usando) es más simple de mantener (insertar, actualizar, eliminar) pero difícil de consultar (por ejemplo, encontrar un subárbol de un nodo dado) sin recursividad. El modelo de conjunto anidado es una buena alternativa que es más simple de consultar pero difícil de mantener. El modelo de rutas materializadas es otro candidato que es simple de consultar pero difícil de mantener (más fácil de implementar con tipo de datos de matriz pero sin integridad referencial).
Si entendí correctamente, cada ObjectGroup tiene solo un padre, que es el registro donde parent_id = 0, por lo que básicamente un padre identifica un grupo, lo que significa que ya tiene un ObjectGroup.id. En cada grupo de objetos, el número de miembros es el recuento de todos los niños. con la misma clave_foránea más su padre (+1) Se deben tener en cuenta la fecha de inicio y la fecha de finalización del padre para identificar el mínimo y el máximo en el grupo de objetos
CREATE OR REPLACE VIEW v AS SELECT p.id, p.foreign_key, MIN(LEAST(p.start_date,c.start_date)) AS obj_start_date, MAX(GREATEST(p.end_date,c.end_date)) AS obj_end_date, COUNT(c.id) + 1 AS obj_count FROM objects AS p INNER JOIN myobjects AS c ON c.parent_id = p.id AND c.foreign_key = p.foreign_key WHERE p.parent_id = 0 GROUP BY p.id, p.foreign_key ; SELECT * FROM v;Haz tu vida más fácil. Agregue una columna group_id . Entonces la consulta es simplemente:
SELECT group_id, MIN(start_date), MAX(end_date), COUNT(*) FROM objects GROUP BY group_id;(Más un envoltorio para convertirlo en una VISTA).