promedio - sql contar registros agrupados

SUM complejo de varias tablas

Question

Aquí están mis tablas:

CREATE TABLE component
                            (id INTEGER PRIMARY KEY AUTOINCREMENT,
                            name TEXT UNIQUE);

CREATE TABLE file
                            (id INTEGER PRIMARY KEY AUTOINCREMENT,
                            component_id INTEGER,
                            name TEXT UNIQUE);

CREATE TABLE function
                            (id INTEGER PRIMARY KEY AUTOINCREMENT,
                            file_id INTEGER,
                            name TEXT,
                            FOREIGN KEY(file_id) REFERENCES file(id),
                            UNIQUE(file_id, name));

CREATE TABLE version
                            (id INTEGER PRIMARY KEY AUTOINCREMENT,
                            version TEXT UNIQUE);

CREATE TABLE data
                            (id INTEGER PRIMARY KEY AUTOINCREMENT,
                            file_id INTEGER,
                            version_id INTEGER,
                            function_id INTEGER,
                            errors INTEGER,
                            ...,
                            FOREIGN KEY(file_id) REFERENCES file(id),
                            FOREIGN KEY(version_id) REFERENCES version(id),
                            FOREIGN KEY(function_id) REFERENCES function(id),
                            UNIQUE(file_id, version_id, function_id));

Necesito dos consultas:

• Uno para SUMAR los datos.errores para todos los datos en un archivo. Para un ID de archivo dado, necesito la suma total de todos los errores.
• Uno para SUMAR los datos.errores para todas las funciones para todos los archivos dentro de un componente específico.
• TODOS los data.errors DEBEN pertenecer al version_id más reciente.

Ejemplo del requisito MAX de la versión anterior:

DATA
id  file_id     version_id  function_id     errors
1       1           3           1           40
2       1           3           2           231
3       1           2           3           19

Aquí lo necesito para devolver ids 1,2 y descartar 3 incluso si es la versión más reciente para una función específica. Coincide con la versión más reciente para las funciones que pertenecen a ese archivo. Imagine un escenario del mundo real donde una función se elimina de un archivo en una nueva versión.

El único requisito es que la consulta sea lo más rápida posible. Las restricciones no están cambiando demasiado en la base de datos (preferiblemente nada en absoluto). Si esto es posible en Django ORM, donde pretendo usarlo, sería genial, pero no es obligatorio.

Answer 1

La versión más reciente de un archivo se puede calcular así:

SELECT MAX(version_id)
FROM data
WHERE file_id = ?

Esto simplemente puede ser conectado a otra consulta para obtener la suma:

SELECT SUM(errors)
FROM data
WHERE file_id = ?
  AND version_id = (SELECT MAX(version_id)
                    FROM data
                    WHERE file_id = ?)

Para extender esto a un componente, se necesita otra subconsulta para buscar los archivos del componente:

SELECT SUM(errors)
FROM data
WHERE file_id IN (SELECT id
                  FROM file
                  WHERE component_id = ?)
  AND version_id = (SELECT MAX(version_id)
                    FROM data
                    WHERE file_id IN (SELECT id
                                      FROM file
                                      WHERE component_id = ?))