Selecione valores que atendam condições diferentes em linhas diferentes?

Esta é uma consulta muito básica que não consigo descobrir ….

Digamos que eu tenha uma tabela de duas colunas como esta:

userid | roleid --------|-------- 1 | 1 1 | 2 1 | 3 2 | 1 

Eu quero obter todos os userids distintos que têm roleids 1, 2 e 3. Usando o exemplo acima, o único resultado que eu quero retornar é userid 1. Como faço isso?

 SELECT userid FROM UserRole WHERE roleid IN (1, 2, 3) GROUP BY userid HAVING COUNT(DISTINCT roleid) = 3; 

Para quem lê isto: minha resposta é simples e direta, e recebi o status ‘aceito’, mas por favor leia a resposta dada por @cletus. Tem um desempenho muito melhor.


Apenas pensando em voz alta, outra maneira de escrever a auto-união descrita por @cletus é:

 SELECT t1.userid FROM userrole t1 JOIN userrole t2 ON t1.userid = t2.userid JOIN userrole t3 ON t2.userid = t3.userid WHERE (t1.roleid, t2.roleid, t3.roleid) = (1, 2, 3); 

Isto pode ser mais fácil de ler para você, e o MySQL suporta comparações de tuplas como essa. O MySQL também sabe como utilizar índices de cobertura de maneira inteligente para essa consulta. Basta executá-lo através de EXPLAIN e ver “Using index” nas notas de todas as três tabelas, o que significa que está lendo o índice e nem precisa tocar nas linhas de dados.

Eu executei essa consulta em mais de 2,1 milhões de linhas (o despejo de dados do Stack Overflow July para PostTags) usando o MySQL 5.1.48 no meu Macbook e ele retornou o resultado em 1,08 s. Em um servidor decente com memory suficiente alocada para innodb_buffer_pool_size , ele deve ser ainda mais rápido.

Ok, eu tenho downvoted sobre isso, então eu decidi testá-lo:

 CREATE TABLE userrole ( userid INT, roleid INT, PRIMARY KEY (userid, roleid) ); CREATE INDEX ON userrole (roleid); 

Rode isto:

 < ?php ini_set('max_execution_time', 120); // takes over a minute to insert 500k+ records $start = microtime(true); echo "
\n"; mysql_connect('localhost', 'scratch', 'scratch'); if (mysql_error()) { echo "Connect error: " . mysql_error() . "\n"; } mysql_select_db('scratch'); if (mysql_error()) { echo "Selct DB error: " . mysql_error() . "\n"; } $users = 200000; $count = 0; for ($i=1; $i< =$users; $i++) { $roles = rand(1, 4); $available = range(1, 5); for ($j=0; $j<$roles; $j++) { $extract = array_splice($available, rand(0, sizeof($available)-1), 1); $id = $extract[0]; query("INSERT INTO userrole (userid, roleid) VALUES ($i, $id)"); $count++; } } $stop = microtime(true); $duration = $stop - $start; $insert = $duration / $count; echo "$count users added.\n"; echo "Program ran for $duration seconds.\n"; echo "Insert time $insert seconds.\n"; echo "

\n"; function query($str) { mysql_query($str); if (mysql_error()) { echo "$str: " . mysql_error() . "\n"; } } ?>

Saída:

 499872 users added. Program ran for 56.5513510704 seconds. Insert time 0.000113131663847 seconds. 

Isso adiciona 500.000 combinações aleatórias de funções de usuário e há aproximadamente 25.000 que correspondem aos critérios escolhidos.

Primeira consulta:

 SELECT userid FROM userrole WHERE roleid IN (1, 2, 3) GROUP by userid HAVING COUNT(1) = 3 

Tempo de consulta: 0,312s

 SELECT t1.userid FROM userrole t1 JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2 JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3 AND t1.roleid = 1 

Tempo de consulta: 0,016 s

Está certo. A versão de junit que propus é vinte vezes mais rápida que a versão agregada.

Desculpe, mas eu faço isso para viver e trabalhar no mundo real e no mundo real nós testamos SQL e os resultados falam por si.

A razão para isso deve ser bem clara. A consulta agregada será dimensionada em custo com o tamanho da tabela. Cada linha é processada, agregada e filtrada (ou não) através da cláusula HAVING . A versão de associação (usando um índice) selecionará um subconjunto dos usuários com base em uma determinada function e, em seguida, verificará esse subconjunto na segunda function e, finalmente, esse subconjunto na terceira function. Cada seleção (em termos de álgebra relacional ) funciona em um subconjunto cada vez menor. A partir disso, você pode concluir:

O desempenho da versão de junit fica ainda melhor com uma menor incidência de correspondências.

Se houvesse apenas 500 usuários (da amostra de 500k acima) com as três funções declaradas, a versão de associação será significativamente mais rápida. A versão agregada não (e qualquer melhoria de desempenho é o resultado do transporte de 500 usuários em vez de 25k, que a versão de associação obviamente também recebe).

Eu também estava curioso para ver como um database real (ou seja, Oracle) lidaria com isso. Então eu basicamente repeti o mesmo exercício no Oracle XE (rodando na mesma máquina desktop do Windows XP que o MySQL do exemplo anterior) e os resultados são quase idênticos.

Joins parecem ser desaprovados mas, como demonstrei, as consultas agregadas podem ser uma ordem de magnitude mais lenta.

Atualização: Após alguns testes extensivos , a imagem é mais complicada e a resposta dependerá de seus dados, seu database e outros fatores. A moral da história é testar, testar e testar.

Assumindo userid, o roleid está contido em um índice exclusivo (o que significa que não pode haver dois registros em que userid = x e roleid = 1

 select count(*), userid from t where roleid in (1,2,3) group by userid having count(*) = 3 

A maneira clássica de fazer isso é tratá-lo como um problema de divisão relacional.

Em inglês: selecione os usuários para os quais nenhum dos valores de papel da situação desejados está faltando.

Suponho que você tenha uma tabela Usuários à qual a tabela UserRole se refere e presumirei que os valores do papel da function desejados estão em uma tabela:

 create table RoleGroup( roleid int not null, primary key(roleid) ) insert into RoleGroup values (1); insert into RoleGroup values (2); insert into RoleGroup values (3); 

Também assumirei que todas as colunas relevantes não são NULLable, portanto não há surpresas com IN ou NOT EXISTS. Aqui está uma consulta SQL que expressa o inglês acima:

 select userid from Users as U where not exists ( select * from RoleGroup as G where not exists ( select R.roleid from UserRole as R where R.roleid = G.roleid and R.userid = U.userid ) ); 

Outra maneira de escrever isso é

 select userid from Users as U where not exists ( select * from RoleGroup as G where G.roleid not in ( select R.roleid from UserRole as R where R.userid = U.userid ) ); 

Isso pode ou não acabar sendo eficiente, dependendo dos índices, plataforma, dados, etc. Procure na web por “divisão relacional” e você encontrará muito.

 select userid from userrole where userid = 1 intersect select userid from userrole where userid = 2 intersect select userid from userrole where userid = 3 

Isso não resolveria o problema? Quão boa é a solução nos bancos de dados relacionais típicos? Será que o otimizador de consultas automáticas otimizará isso?

Se você precisar de algum tipo de generalidade aqui (diferentes combinações de três funções ou diferentes combinações de n-function) … Eu sugiro que você use um sistema de mascaramento para suas funções e use os operadores bit-a-bit para realizar suas consultas …