Início / dba / Perguntas / 297492
Accepted
r2k2
Asked: 2021-08-03 11:24:25 +0800 CST

Contagem de linhas nulas que precedem cada valor não nulo

  • 772

Preciso responder a seguinte pergunta:

Qual é o número médio de envios de e-mail que leva antes que um cliente abra um e-mail? Eu sei que preciso contar o número de linhas entre cada Open_Date para obter o número de envios de email entre cada abertura. Percebo que preciso adicionar a função row_number () para isso, mas não consigo chegar à coluna desejada na minha segunda captura de tela.

Algo a ter em mente, precisarei obter um "CounttilOpens" médio por endereço de e-mail. No meu exemplo abaixo, tenho dois endereços de e-mail diferentes e suas respectivas datas de envio e abertura e preciso que a consulta funcione de forma independente para cada endereço de e-mail.

Meus dados estão estruturados da seguinte forma:

insira a descrição da imagem aqui

Preciso de uma consulta que me ajude a obter uma nova coluna como esta que reinicia para cada endereço de e-mail e para cada intervalo de datas abertas:

insira a descrição da imagem aqui

partitioning window-functions

1 respostas

  1. Best Answer

    Este é um tipo de problema de lacunas e ilhas. Existem muitas soluções diferentes.

    • Como COUNT(SomeValue)contará apenas valores não nulos, você pode usar uma contagem em janela para calcular um ID de agrupamento para cada ilha.

    • Subtraímos 1 para cada linha que não é nula, para mantê-la como parte do grupo anterior

    • Em seguida, usamos outra janela COUNTpara obter o resultado final, desta vez particionando também pelo ID do grupo.

    SELECT
  Email,
  Send_Date,
  Open_Date,
  CASE WHEN Open_Date IS NOT NULL THEN
      COUNT(*) OVER (PARTITION BY Email, GroupId ORDER BY Send_Date)
    END AS CountTilOpen
FROM (
    SELECT *,
        COUNT(Open_Date) OVER (PARTITION BY Email ORDER BY Send_Date)
          - CASE WHEN Open_Date IS NULL THEN 0 ELSE 1 END
          AS GroupId
    FROM YourTable t
) t;
    • 2

relate perguntas