Como faço com que consultas SQL complexas sejam mais fáceis de escrever? [fechadas]

40

Estou achando muito difícil escrever consultas SQL complexas envolvendo junções em muitas tabelas (pelo menos 3-4) e envolvendo várias condições aninhadas. As perguntas que estou sendo solicitada a escrever são facilmente descritas por algumas frases, mas podem exigir uma quantidade enganosa de código para serem concluídas. Estou me acostumando a usar frequentemente exibições temporárias para escrever essas consultas, o que parece ser uma muleta. Que dicas você pode fornecer para usar essas consultas complexas? Mais especificamente, como faço para quebrar essas consultas nas etapas que preciso usar para realmente escrever o código SQL?

Note que eu sou o SQL que me pedem para escrever é parte das tarefas de um curso de banco de dados, então eu não quero software que faça o trabalho para mim. Eu quero realmente entender o código que estou escrevendo.

Mais detalhes técnicos:

  • O banco de dados está hospedado em um servidor PostgreSQL em execução na máquina local.
  • O banco de dados é muito pequeno: não há mais de sete tabelas e a maior tabela possui menos de 50 linhas.
  • As consultas SQL estão sendo passadas inalteradas para o servidor, via LibreOffice Base.
por bwDraco 16.04.2012 / 04:41
fonte

6 respostas

47

Eu estou baseando a maior parte disso apenas tentando obter a resposta "certa", então você pode descobrir que há alguns problemas de desempenho. Não adianta acelerar uma consulta incorreta.

Entenda os relacionamentos da tabela - A maioria será de um para muitos. Conheça a tabela "muitos". Identifique os campos necessários para suas associações.

Pense nos cenários de junção LEFT - selecione todos os funcionários e seu salário no mês passado. E se eles não recebessem um salário no mês passado?

Conheça o conjunto de resultados: 1) Em uma planilha, insira manualmente pelo menos um registro correto para sua consulta. 2) Escreva a consulta de forma simples o suficiente para identificar quantos registros devem ser retornados. Use os dois para testar sua consulta para garantir que a entrada em uma nova tabela não altere o resultado.

Divida sua consulta em partes gerenciáveis - você não precisa escrever tudo de uma vez. Consultas complexas podem, às vezes, ser apenas uma coleção de consultas simples.

Cuidado com os níveis mistos de agregação : se você tiver que colocar valores mensais, trimestrais e acumulados no mesmo conjunto de resultados, precisará calculá-los separadamente nas consultas agrupadas em valores diferentes.

Saiba quando UNION Às vezes, é mais fácil dividir subgrupos em suas próprias declarações selecionadas. Se você tiver uma tabela misturada com gerentes e outros funcionários, e em cada coluna você tiver que fazer instruções Case com base na participação em um desses grupos, pode ser mais fácil escrever uma consulta e uma união do Manager para uma consulta Employee. Cada um conteria sua própria lógica. Ter que incluir itens de diferentes tabelas em linhas diferentes é um uso óbvio.

Fórmulas complexas / aninhadas - Tente recuar e não tenha medo de usar várias linhas. "CASO QUANDO O CASO QUANDO O CASO QUANDO" lhe deixará louco. Aproveite o tempo para pensar sobre isso. Salve os complexos cálculos por último. Obtenha os registros corretos selecionados primeiro. Então você ataca fórmulas complexas sabendo que você está trabalhando com os valores corretos. Ver os valores usados nas fórmulas ajudará você a identificar áreas nas quais você precisa considerar valores NULL e onde manipular a divisão por erro zero.

Teste com frequência à medida que adiciona novas tabelas para garantir que você ainda esteja recebendo o conjunto de resultados desejado e sabendo qual junção ou cláusula é a culpada.

    
por 16.04.2012 / 16:58
fonte
27
  1. Recuo seria a primeira coisa a fazer, se você não estiver fazendo isso. Não só é útil até com consultas simples, mas é crucial quando se trata de junções e consultas um pouco mais complexas do que um select top 1 [ColumnName] from [TableName] .

  2. Depois de recuado corretamente, nada proíbe adicionar comentários dentro da própria consulta, quando apropriado. Não exagere: se o código for explícito o suficiente, a adição de comentários prejudicará a clareza do código. Mas eles ainda são bem-vindos para as partes menos explícitas da consulta.

    Observe que consultas mais longas (incluindo consultas com comentários) significariam uso de largura de banda maior entre seu servidor de aplicativos e seu servidor de banco de dados. Observe também que, a menos que você esteja trabalhando em um produto da escala do Google com uma enorme quantidade de solicitações por segundo, exigindo um desempenho e uso de recursos excepcionais, o tamanho adicionado pelos comentários pode não alterar nada em termos de desempenho.

  3. Aplicar o mesmo estilo a tabelas, colunas etc. também ajuda muito na legibilidade. Quando um banco de dados herdado tem as tabelas PRODUCT , users , USERS_ObsoleteDONT_USE , PR_SHIPMENTS e HRhbYd_UU , alguém está fazendo algo muito errado.

  4. Também é importante impor o mesmo estilo às consultas . Por exemplo, se você estiver escrevendo consultas para o Microsoft SQL Server e decidir usar [TableName] em vez de TableName , mantenha-o. Se você for para uma nova linha depois de um select , não faça isso em apenas metade das suas consultas, mas todas elas.

  5. Não use * , a menos que haja motivos strongs para isso (como em if exists(select * from [TableName] where ...) no Microsoft SQL Server). Não apenas * tem um impacto negativo no desempenho em alguns bancos de dados (se não na maioria), mas também não é útil para o desenvolvedor que usa sua consulta. Da mesma forma, um desenvolvedor deve acessar os valores por nome, nunca por índice.

  6. Por fim, para as seleções, não há nada de errado em fornecer uma visualização . Para qualquer outra coisa, procedimentos armazenados também podem ser usados dependendo do projeto e das pessoas com quem você está trabalhando².

¹ Algumas pessoas odeiam procedimentos armazenados. Outros não gostam deles por várias razões (perfeitamente válidas, pelo menos para eles).

² Seus colegas, os outros alunos, seu professor, etc.

    
por 16.04.2012 / 05:37
fonte
9

Um pouco de tiro no escuro aqui, mas se você está escrevendo muitas visualizações temporárias talvez você ainda não tenha percebido que a maioria dos lugares você poderia colocar uma tabela em uma instrução SQL, essa tabela pode ser substituída por uma consulta .

Portanto, em vez de unir a tabela A à exibição temporária B, você pode unir a tabela A à consulta que estava usando como exibição temporária B. Por exemplo:

    SELECT A.Col1, A.Col2, B.Col1,B.Col2
      FROM (SELECT RealTableZ.Col1, RealTableY.Col2, RealTableY.ID as ID
              FROM RealTableZ 
   LEFT OUTER JOIN RealTableY
                ON RealTableZ.ForeignKeyY=RealTableY.ID
             WHERE RealTableY.Col11>14
            ) As B
        INNER JOIN A
                ON A.ForeignKeyY=B.ID

Este exemplo é bastante inútil, mas deve explicar a sintaxe.

Para visualizações que não são "especiais" (indexadas, particionadas), isso deve resultar no mesmo plano de consulta, como se você tivesse usado uma visualização.

Para facilitar a gravação, você pode verificar cada peça para garantir que está obtendo o que espera antes de escrever a consulta inteira.

Minhas desculpas se este já é um chapéu antigo para você.

    
por 16.04.2012 / 19:36
fonte
7

Em vez de visualizações temporárias, use a cláusula WITH . Isso facilita muito a divisão de grandes consultas em partes menores mais legíveis.

    
por 16.04.2012 / 08:40
fonte
3
  1. Torne-se mais familiarizado com a teoria dos conjuntos, se você ainda não estiver. O SQL é baseado na teoria dos conjuntos e a compreensão mais sobre conjuntos ajudará você a se familiarizar mais com o funcionamento do SQL.
  2. Pratique mais SQl, se você estiver apenas aprendendo SQL, vai levar tempo para entender como fazer tudo, algumas coisas levam tempo até que você realmente as entenda, Joins é um ótimo exemplo, quanto mais você as usa, melhor você terá nisso.
  3. Verifique se as tabelas que você está consultando estão adequadamente projetadas
  4. Não tenha medo de usar exibições em consultas selecionadas, especialmente se você tiver um conjunto em comum que precise ser refinado de várias maneiras diferentes
por 16.04.2012 / 16:25
fonte
1

Como qualquer outra coisa, você quer dividir o problema em partes gerenciáveis.

Isso realmente é como você resolve problemas complexos, a propósito.

Então: Você quer verificar a subconsulta para ver se ela realmente retorna o que você deseja antes de executar uma consulta externa nela. Você quer tentar uma junção mínima de cada mesa em que está participando, para que possa ver que está realmente pensando nela corretamente. Coisas assim. Na esperança de digitar tudo e sair exatamente o que você quer em um whack é apenas irrealista.

Uma instrução SQL, uma vez que atinge um certo nível de complexidade, é basicamente um pequeno programa em si. Faz uma grande diferença entender realmente como os dados são combinados, selecionados, filtrados e gerados.

    
por 16.04.2012 / 17:09
fonte

Tags