Início / dba / Perguntas / 283286
Accepted
TheDemonLord
Asked: 2021-01-15 17:27:47 +0800 CST

Contar linhas que existem antes e não depois e depois, mas não antes

  • 772

Eu tenho um Banco de Dados SQL do Azure, uma Tabela que possui 3 Colunas Relevantes: Marca, ID e Mês/Ano e uma Tabela Temporária que simplesmente lista o Mês/Ano com um ID.

A tabela mostra uma lista de todos os IDs que estavam ativos naquele determinado mês.

Mês/Ano é uma coluna de carimbo de data, com a data como o primeiro do Mês. A coluna ID é apenas um ID.

O objetivo é ter uma saída que, para cada mês, mostre a contagem de todos os registros que estavam ativos neste mês, mas não ativos no mês seguinte (Churn) e IDs que não estavam ativos no mês anterior, mas estavam ativos neste mês (Novo) e ter estes agrupados por marca e mês assim:

Marca | Churn | Novo | Mês
1 | 10 | 12 | 01-12-2019
2 | 11 | 9 | 01-12-2019

Devo também observar que Table1 tem 19 milhões de linhas.

Tentei o código abaixo:

Declare @Id int
declare @my date

set @Id = 300
set @my = '2019-12-01'

select t1.[brand],
count(c.[ID]) as [churn],
count(n.[ID]) as [new],
t1.[month/year]
from Table1 T1

left join Table1 c on
c.[ID] = t1.[ID] and c.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id +1)))
left join Table1 n on
n.[ID] = T1.[ID] and n.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id -1)))

where t1.[Month/Year] = @my
group by t1.[Brand], t1.[Month/Year]

Que apesar de ter produzido uma saída, acho que não estava certo e demorou muito, provavelmente devido ao meu uso liberal de Sub-consultas em Joins.

Minha pergunta é dupla - alguém pode ajudar de uma maneira melhor para contar o número de linhas e juntá-las e existe uma maneira melhor de fazer isso sem tantas subconsultas?

sql-server join

2 respostas

  1. Best Answer

    Em geral, você deve incluir os scripts que geram seus esquemas de tabela e os preenche com dados. (Também para questões de desempenho, você deve incluir o plano de execução também.) Eu não poderia fazer muitos testes sem dados reais, mas acredito que esta consulta deve melhorar o desempenho que você está vendo:

    DECLARE @my DATE = '2019-12-01'
DECLARE @nextMonthYear DATE = DATEADD(MONTH, 1, @my)
DECLARE @prevMonthYear DATE = DATEADD(MONTH, -1, @my)

SELECT CurrentMonth.Brand, SUM(CASE WHEN NextMonth.ID IS NULL THEN 1 ELSE 0 END) AS Churn, SUM(CASE WHEN PreviousMonth.ID IS NULL THEN 1 ELSE 0 END) AS New, CurrentMonth.MonthYear
FROM Table1 AS CurrentMonth
LEFT JOIN Table1 AS NextMonth
    ON NextMonth.MonthYear = @nextMonthYear
    AND CurrentMonth.ID = NextMonth.ID  
LEFT JOIN Table1 AS PreviousMonth
    ON PreviousMonth.MonthYear = @prevMonthYear
    AND CurrentMonth.ID = PreviousMonth.ID  
WHERE CurrentMonth.MonthYear = @my
GROUP BY CurrentMonth.Brand, CurrentMonth.MonthYear

    Isso também é muito mais simples (tanto para o desenvolvedor ler quanto para o SQL Server analisar) do que um monte de subconsultas. Você provavelmente está certo de que a junção adicional desnecessária às subconsultas é uma fonte de seus problemas de desempenho (mas não posso verificar isso sem ver o plano de execução ).

    O que a consulta acima faz é usar a LEFT JOINpara Table1 em si mesmo duas vezes (uma vez para o PreviousMonthe uma vez para o NextMonth) IDpara obter todos os registros que correspondem e não correspondem ao CurrentMonth. Em seguida, usando a SUM()função de agregação com uma CASEinstrução dentro dela, podemos resumir apenas os NULLs(casos em que o IDdo CurrentMonthnão existia no PreviousMonthe NextMonth) para obter as contagens para o Newe Churn, agrupadas pelo Brande MonthYeardo CurrentMonth.

    • 1

  2. Se DAY(date)for sempre 1, então você pode usar algo como

    SELECT *, 
       CASE WHEN LAG([id]) OVER (ORDER BY [Month/Year] RANGE BETWEEN FOLLOWING 1 MONTH AND FOLLOWING 1 MONTH) IS NULL 
            THEN 'New' 
            WHEN LEAD([id]) OVER (ORDER BY [Month/Year] RANGE BETWEEN FOLLOWING 1 MONTH AND FOLLOWING 1 MONTH) IS NULL 
            THEN 'Churn'
            ELSE '' 
            END AS [state]
FROM [src table]

    Se você precisar apenas comuná-los, use a consulta semelhante à anterior no CTE e agregue-a na consulta principal.

    • 0

relate perguntas