Supongamos que tengo etiquetas con varias tiendas asociadas a ellas de la siguiente manera:
label_id | store_id -------------------- label_1 | store_1 label_1 | store_2 label_1 | store_3 label_2 | store_2 label_2 | store_3 label_3 | store_1 label_3 | store_2¿Hay alguna buena manera en SQL (o jooq) para obtener todas las identificaciones de la tienda en la intersección de las etiquetas? ¿Significa simplemente devolver store_2 en el ejemplo anterior porque store_2 está asociado con label_1, label_2 y label_3? Me gustaría un método general para manejar el caso en el que tengo n etiquetas.
Este es un problema de división relacional, en el que desea que las tiendas tengan todas las etiquetas posibles. Aquí hay un enfoque usando la agregación:
select store_id from mytable group by store_id having count(*) = (select count(distinct label_id) from mytable) Tenga en cuenta que esto no supone tuplas duplicadas (store_id, label_id) . De lo contrario, debe cambiar la cláusula de having a:
having count(distinct label_id) = (select count(distinct label_id) from mytable)Dado que también está buscando una solución jOOQ, jOOQ admite un operador de división relacional sintético , que produce un enfoque más académico para la división relacional, utilizando solo operadores de álgebra relacional:
// Using jOOQ T t1 = T.as("t1"); T t2 = T.as("t2"); ctx.select() .from(t1.divideBy(t2).on(t1.LABEL_ID.eq(t2.LABEL_ID)).returning(t1.STORE_ID).as("t")) .fetch();Esto produce algo como la siguiente consulta:
select t.store_id from ( select distinct dividend.store_id from t dividend where not exists ( select 1 from t t2 where not exists ( select 1 from t t1 where dividend.store_id = t1.store_id and t1.label_id = t2.label_id ) ) ) tEn inglés simple:
Consígame todas las tiendas (dividendo), para las que no existe etiqueta (t2) para las que esa tienda (dividendo) no tiene entrada (t1)
O en otras palabras
Si hubiera una etiqueta (t2) que una tienda (dividendo) no tiene (t1), entonces esa tienda (dividendo) no tendría todas las etiquetas disponibles.
Esto no es necesariamente más legible o más rápido que las implementaciones de divisiones relacionales basadas en GROUP BY / HAVING COUNT(*) (como se ve en otras respuestas), de hecho, las soluciones basadas en GROUP BY / HAVING son probablemente preferibles aquí, especialmente porque solo una la mesa está involucrada. Una versión futura de jOOQ podría usar el enfoque GROUP BY / HAVING , en su lugar: #10450
Pero en jOOQ, podría ser bastante conveniente escribir de esta manera, y usted solicitó una solución jOOQ :)
Luego, convierta la consulta de @GMB en una función SQL que toma una matriz y devuelve una tabla de store_id.
create or replace function stores_with_all_labels( label_list text[] ) returns table (store_id text) language sql as $$ select store_id from label_store where label_id = any (label_list) group by store_id having count(*) = array_length(label_list,1); $$;Entonces todo lo que se necesita es una simple selección. Ver ejemplo completo aquí .