如何用LAG()和PARTITION BY筛选10天内关联的橙子采购记录

Alright, let's tackle this problem step by step. You need to trace back orange purchase records starting from the entry on 2018-03-20 (id=6), and include all entries where there's another orange purchase within a 10-day window (forming a connected chain). The final result should include ids 3,4,6 and exclude id=1.

Your current query uses LAG() to get the immediately preceding orange record, but that only checks the single prior entry—not any entries within a 10-day range, nor does it handle the chain of records linking back to id=6.

Solution: Recursive CTE for Chain Tracing #

The most straightforward way to solve this is using a recursive Common Table Expression (CTE). This lets us start with your target record and recursively pull in all connected orange purchases within the 10-day window:

WITH RECURSIVE orange_chain AS (
    -- Anchor member: Start with the specified record (id=6, 2018-03-20)
    SELECT id, fruit_bought, quantity, date
    FROM fruits
    WHERE id = 6
    
    UNION ALL
    
    -- Recursive member: Find all orange records within 10 days before the current chain records
    SELECT f.id, f.fruit_bought, f.quantity, f.date
    FROM fruits f
    INNER JOIN orange_chain oc 
        ON f.fruit_bought = 'orange'
        -- Check if the record falls within 10 days prior to the chain entry
        AND f.date >= DATE_SUB(oc.date, INTERVAL 10 DAY)
        -- Ensure it's an earlier record (not the same or later)
        AND f.date < oc.date
        -- Avoid duplicate entries in the chain
        AND f.id NOT IN (SELECT id FROM orange_chain)
)
-- Return all records in the connected chain, sorted by id
SELECT * FROM orange_chain ORDER BY id;

How This Works #

1. Anchor Member: We start with the record you specified (id=6) as the base of our chain.
2. Recursive Member: We join the existing chain with the fruits table to find orange records that are within 10 days before any entry already in the chain. The NOT IN check prevents infinite loops and duplicate entries.
3. Iteration Process:
   • First iteration: Returns id=6.
   • Second iteration: Finds id=4 (2018-03-15) since it's within 10 days of id=6's date.
   • Third iteration: Finds id=3 (2018-03-07) since it's within 10 days of id=4's date.
   • Fourth iteration: No new records qualify (id=1's date is too far outside id=3's 10-day window), so recursion stops.

The final output will be exactly the records you need: ids 3, 4, and 6.

内容的提问来源于stack exchange，提问作者gb_spectrum