Início / dba / Perguntas / 137834
Accepted
MSIS
Asked: 2016-05-07 19:15:43 +0800 CST

Subconsulta correlacionada para encontrar um total maior que a média do estado

  • 772

Tenho as tabelas Vendors (VendorName, VendorState,....) e Faturas (InvoiceID, InvoiceTotal,...). Desejo obter as faturas (como InvoiceId) maiores que o estado médio InvoiceTotal.

Eu sei que primeiro encontro o total médio por estado:

SELECT  VendorState, Avg(InvoiceTotal) AS AvgStateInvoice
from Invoices I join Vendors V on V.VendorID= I.VendorID 
group by VendorState

Portanto, agora tenho a lista de InvoiceTotal médio por estado. Agora preciso descobrir:

Como fazer uma consulta externa para selecionar aquelas notas fiscais maiores que a média do estado e é aqui que me perco, pois não lembro a sintaxe para fazer a comparação. Acho que seria algo do tipo:

SELECT InvoiceId from Invoices where InvoiceTotal > .....?

Alguma ideia, por favor?

sql-server sql-server-2012

2 respostas

  1. Best Answer

    Você pode fazer assim:

    select * 
from 
    dbo.Invoice I1 
    join dbo.Vendors V1 on V1.VendorID = I1.VendorID
where 
    I1.InvoiceTotal > (
        SELECT 
            Avg(I2.InvoiceTotal)
        from 
            dbo.Invoices I2 
            join dbo.Vendors V2 on V2.VendorID = I2.VendorID
        where 
            V1.VendorState = V2.VendorState 
    );

    Ou usando funções de janela, que podem ser mais rápidas, pois não precisam de uma junção extra:

    SELECT X.*
from (
    select
        *,
        Avg(I.InvoiceTotal) over (partition by V.VendorState) as AvgInv
    from 
        dbo.Invoices I
        join dbo.Vendors V on V.VendorID = I.VendorID
) X
where
    X.InvoiceTotal > X.AvgInv;

    A opção de função de janela pode não ser mais rápida. Embora salve uma junção na consulta, o plano de execução apresentará um spool de subexpressão (com duas junções extras). O spool da tabela é necessário para calcular e salvar a média da partição atual. O resultado em spool é repetido uma vez por partição.

    • 7

  2. Este funcionou para mim, acrescento porque incluo muitos detalhes abaixo, espero ajudar outras pessoas que têm problemas a entender melhor do que apenas ter uma solução (e não pedi uma explicação quando fiz a pergunta):

    SELECT VendorState, InvoiceDate FROM Invoices I JOIN Vendors V ON
V.VendorID=I.VendorID WHERE InvoiceDate IN (Select Min(InvoiceDate) FROM
Invoices JOIN Vendors ON Invoices.VendorID=Vendors.VendorID 
WHERE  V.VendorState=Vendors.VendorState)

    Funcionou apesar do fato de não conseguir ver a seção de formatação.

    A parte externa configura as entradas necessárias VendorState e InvoiceDate e as tabelas necessárias: Vendors, Invoices e a junção necessária para tê-los ambos, então a tabela interna/parênteses restringe os valores; a primeira parte da tabela interna fornece a primeira tabela necessária para a correlação em VendorState. Por fim, a expressão V.VendorState=Vendors.VendorState, também conhecida como correlação, é feita para que haja uma comparação de MinInvoiceState sobre todos os Vendor States, ou seja, sobre todas as entradas InvoiceDate no campo. VendorState. Observe que duas cópias da tabela Vendors (contendo uma lista de VendorState ) são necessárias para fazer a correlação.

    • 0

relate perguntas