Início / dba / Perguntas / 332024
Accepted
Jaigus
Asked: 2023-10-11 06:10:09 +0800 CST

Como posso obter um conjunto de resultados tal que a data de cada linha seja x número de dias após a anterior?

  • 772

Digamos que eu tenha uma tabela de compras parecida com esta:

Purchases
---------
Item_ID
Purchase_Date
Customer_ID

Como eu conseguiria obter o conjunto de compras, a partir da primeira compra, para cada cliente que esteja pelo menos X dias após a última compra? Por exemplo, se tivermos os dados abaixo e x=10:

Item_ID    PurchaseDate    Customer_ID    
123        07/29/23        1000
123        08/04/23        1000
123        08/16/23        1000
563        07/03/23        7785
563        07/05/23        7785
788        08/17/23        2489

A consulta deve retornar:

Item_ID    PurchaseDate    Customer_ID    
123        07/29/23        1000
123        08/11/23        1000
563        07/03/23        7785
788        08/17/23        2489

Então, para o cliente 1000, a primeira compra conta, porque é uma nova compra. A segunda compra ocorre há menos de 10 dias da última compra, por isso é descartada. A terceira compra deste cliente conta porque é superior ou igual a 10 dias após a última compra. Para o cliente 7785, apenas a primeira conta porque a segunda compra ocorre menos de 10 dias após a última compra e, finalmente, para o cliente 2489, ele tem apenas uma nova compra, então conta apenas como a nova compra. )

Eu estava pensando em usar a função lag

SELECT
    t.Item_ID,
    t.PurchaseDate,
    t.Customer_ID
FROM (
    SELECT
        p.Item_ID,
        p.PurchaseDate,
        p.Customer_ID,        
        LAG OVER(PARTITION BY Customer_ID ORDER BY PurchaseDate) next_purchase
        ROW_NUMBER() OVER(PARTITION BY Customer_ID ORDER BY PurchaseDate) rownum


        
    FROM
        PurchaseTable p
) t
WHERE
    DATEDIFF(DAY, t.PurchaseDate, t.next) >= 10) or rownum=1

EDIT: Desculpas, a descrição estava incorreta. Mudei a pergunta e o exemplo para os detalhes corretos.

sql-server

1 respostas

  1. Best Answer

    Peguei seu código e o aperfeiçoei um pouco - de acordo com sua descrição, ele fornece os resultados solicitados:

       CREATE TABLE Purchases (
Item_ID int, 
purchase_date datetime, 
customer_id int
);

INSERT INTO Purchases
VALUES 
(123,'2023-07-29',1000),
(123,'2023-08-04',1000),
(123,'2023-08-16',1000),
(563,'2023-07-03',7785),
(563,'2023-07-05',7785),
(788,'2023-08-17',2489)

DECLARE @threshold int = -10;   
SELECT
    t.Item_ID,
    t.purchase_date,
    t.Customer_ID
FROM (
    SELECT
        p.Item_ID,
        p.purchase_date,
        p.Customer_ID,        
        LAG(purchase_date) OVER(PARTITION BY Customer_ID ORDER BY purchase_date) next_purchase,
        ROW_NUMBER() OVER(PARTITION BY Customer_ID ORDER BY purchase_date) rownum,
        DATEDIFF(DAY,purchase_date,LAG(purchase_date) OVER(PARTITION BY Customer_ID ORDER BY purchase_date) ) AS purchases_difference
      
    FROM
        Purchases p
) t
WHERE
    (purchases_difference IS NULL OR purchases_difference < @threshold) /* or rownum=1 */

    A maior diferença no seu código é que o LAG fornece um valor negativo (ele verifica a linha anterior e subtrai a data posterior de uma data anterior - portanto, valor negativo. Portanto, declarei @threshold como -10 (dias) e mostrando tudo o que está diferença -10 dias ou mais (ou seja, -11, -12, ...). Também comentei deliberadamente o rownum = 1, porque isso é redundante para shopping_difference IS NULL (será nulo para cada primeiro (ou apenas) linha no grupo - portanto, qualifica a linha a ser mostrada).

    • 0

relate perguntas