MySQL技术问询：筛选2019年5月末最后状态为public的post_id

To solve this, we need to identify posts whose latest status change on or before May 31, 2019 is public. This ensures that any post switched to draft before the end of May (and not switched back) is excluded, while posts that stayed public (or were last set to public before May ends) are included.

Efficient Query Using Window Functions #

The most efficient way to handle this (especially for large datasets) is using a window function like ROW_NUMBER() to rank status changes by date for each post, then pick the latest one:

SELECT post_id
FROM (
    SELECT 
        post_id,
        status,
        -- Assign row number where 1 = latest status change per post
        ROW_NUMBER() OVER (
            PARTITION BY post_id 
            ORDER BY date DESC
        ) AS status_rank
    FROM your_table_name
    -- Only consider changes up to May 31, 2019
    WHERE date <= '2019-05-31'
) ranked_statuses
-- Filter to only the latest status, which must be public
WHERE status_rank = 1 AND status = 'public';

How This Works: #

1. Inner Subquery: For each post, we rank all its status changes (up to May 31) from newest to oldest. The latest change gets status_rank = 1.
2. Outer Query: We keep only the rows where the latest status is public, giving us exactly the posts we need.

Alternative Approach: Group By + Join #

If window functions aren't available (unlikely in modern SQL databases), you can use a subquery to find the latest date per post, then join back to get the status:

SELECT t.post_id
FROM your_table_name t
INNER JOIN (
    -- Get the latest status date for each post up to May 31
    SELECT post_id, MAX(date) AS latest_status_date
    FROM your_table_name
    WHERE date <= '2019-05-31'
    GROUP BY post_id
) latest_dates 
    ON t.post_id = latest_dates.post_id 
    AND t.date = latest_dates.latest_status_date
-- Keep only posts where the latest status is public
WHERE t.status = 'public';

Optimization Tip: #

To speed up either query, add an index on (post_id, date DESC, status). This allows the database to quickly find the latest status for each post without full table scans.

Testing this with your sample data:

• For post_id 1, the latest status up to May 31 is draft (from 2019-05-25), so it's excluded.
• For post_id 2, the latest status up to May 31 is public (from 2019-04-01), so it's included.

This matches your expected result perfectly!

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