Aprenda a combinar dados com uma junção cruzada

então como você obtém combinações de Título de trabalho e sexo quando a contagem é zero?

Podemos usar uma junção cruzada. A idéia é primeiro fazer uma junção cruzada em valores distintos de gênero e título. Estes resultados podem então ser unidos de volta à tabela de funcionários para obter a conta.

então o que esta consulta realmente faz?

para torná-lo mais simples de escrever e ler, eu usei CTE (Common Table Expressions) para criar esta consulta. Se você não está familiarizado com o CTE, pense neles como pontos de vista por agora., Nota: você pode ler mais sobre Eti no meu artigo Introdução A expressões de tabela comuns.

criamos dois CTE de distintos valores de emprego e gênero a partir da tabela de Funcionários. Cruzando estas tabelas podemos exibir todas as combinações possíveis de títulos de trabalho e gênero.,

Aqui é a consulta que podemos usar para criar combinações distintas:

WITH cteJobTitle (JobTitle)AS (SELECT DISTINCT JobTitle FROM HumanResources.Employee),cteGender (Gender)AS (SELECT DISTINCT Gender FROM HumanResources.Employee)SELECT J.JobTitle, G.GenderFROM cteJobTitle AS J CROSS JOIN cteGender AS GORDER BY J.JobTitle

Depois de estudar a consulta acima você vai ver o CROSS JOIN é apenas criando combinações partir de duas distintas valorizado listas, codificados por cores azul e vermelho, cujos resultados são:

Agora, tudo o que precisamos fazer é pegar os resultados e combiná-los com os dados resumidos obtemos por agrupar os dados. Mais uma vez um CTE é usado para coletar os dados resumidos. Uma junção externa é então usada para combinar isso com todas as combinações de JobTitle e gênero.,

A razão pela qual isso funciona é que estamos usando uma junção externa para devolver todos os resultados de uma tabela, independentemente de se eles correspondem a outra. Isto é importante porque queremos incluir todos os resultados da cross join, que são todas as combinações de gênero e título, independentemente de haver ou não dados resumidos.

para resumir, aqui estão os passos que estamos a tomar para criar este resultado:

1. obtenha uma lista distinta de funções. Isto acontece num CTE.
2. obtenha uma lista distinta de gêneros. Isto acontece num CTE.,
3. Criar todas as combinações possíveis de títulos de trabalho e gêneros usando CROSS JOIN.calcule uma contagem sumária dos trabalhadores por função e sexo.
4. corresponde à contagem de resumo calculada com uma lista distinta.

Aqui está a consulta final que realiza estes passos:

WITH cteJobTitle (JobTitle)AS (SELECT DISTINCT JobTitle FROM HumanResources.Employee), cteGender (Gender)AS (SELECT DISTINCT Gender FROM HumanResources.Employee), cteCounts (JobTitle, Gender, NumberEmployees)AS (SELECT JobTitle, Gender, COUNT(1) AS NumberEmployees FROM HumanResources.Employee GROUP BY JobTitle, Gender)SELECT J.JobTitle, G.Gender, COALESCE (C.NumberEmployees, 0) as NumberEmployeesFROM cteJobTitle AS J CROSS JOIN cteGender AS G LEFT OUTER JOIN cteCounts AS C ON C.JobTitle = J.JobTitle AND C.Gender = G.GenderORDER BY J.JobTitle, G.Gender;

para torná-lo mais fácil de ler, os dois CTE e cruz se unem para criar todas as combinações de JobTitle e sexo são de cor Azul e vermelho. O CTE para resumir os dados é de cor verde.

também notamos que usamos a função SQL COALESCE para substituir NULL com um valor zero.,

Aqui está o resultado da consulta:

INNER JOIN como CROSS JOIN

Como você começa a conhecer o SQL você perceber que há, normalmente, mais de uma maneira de escrever uma consulta. Por exemplo, há uma maneira de usar a cláusula onde ter uma junção cruzada se comportar como uma junção interior.vamos tomar estas duas tabelas:

suponha que desejamos consultar todos os empregados e mostrar a sua data de nascimento e apelidos. Para fazer isso, temos que relacionar a mesa de funcionários com a pessoa.,

Como já vimos, podemos escrever uma associação cruzada para combinar linhas como assim

SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person P

Mas esta consulta não é muito útil neste caso, como ele retorna 5,791,880 linhas!

para limitar as combinações de linhas de modo que os registros da pessoa são devidamente correspondidos às linhas de funcionários, podemos usar uma cláusula onde. Aqui é o final da consulta:

SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person PWHERE P.BusinessEntityID = E.BusinessEntityID

Aqui estão as primeiras 14 linhas de 290:

Esta consulta retorna os mesmos resultados que um escrito com uma ASSOCIAÇÃO INTERNA., A consulta usando uma junção interna é:

SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID

Qual consulta é melhor? Se você olhar para os planos de consulta, você verá que eles são muito semelhantes.

Aqui está o plano para a associação entre…

Aqui é o plano para o inner join…

Como você vê que eles são idênticos. A razão por que eles são assim, é que SQL é uma linguagem declarativa, o que significa que dizemos ao DB o resultado que queremos, não necessariamente como fazê-lo. Quando fornecemos o DBMS com a nossa consulta, o Optimizador montou o melhor plano., Na maioria dos casos será o mesmo para declarações equivalentes.