PostgreSQL中基于配送时效筛选产品并标记重要日期的SQL实现

Here's a solution that uses window functions to rank your records and a CASE statement to mark the Important_date as requested:

WITH filtered_ranked_data AS (
    SELECT
        -- Include all original table fields
        *,
        -- Assign a row number per product: prioritize smallest date_difference, then latest Purchasedate
        ROW_NUMBER() OVER (
            PARTITION BY Product
            ORDER BY date_difference ASC, Purchasedate DESC
        ) AS record_rank
    FROM your_table_name
    -- Apply initial filter criteria
    WHERE date_difference < 5
      AND customer_rating > 3
)
SELECT
    -- Select all original fields
    Product,
    Purchasedate,
    deliverydate,
    date_difference,
    customer_rating,
    -- Use CASE to mark Important_date only for the top-ranked record per product
    CASE WHEN record_rank = 1 THEN Purchasedate ELSE NULL END AS Important_date
FROM filtered_ranked_data;

How this works: #

1. CTE Filtering & Ranking:

   • First, we filter the records to only include those where date_difference is less than 5 days and customer_rating is above 3.
   • The ROW_NUMBER() window function assigns a unique rank to each record within its product group. We order first by date_difference ascending (to get the smallest value) and then by Purchasedate descending (to pick the latest purchase if there's a tie in the minimum date difference).

2. CASE Statement for Important_date:

   • In the final select, we use a CASE statement to set Important_date to the Purchasedate only for the record with a rank of 1 (the top-priority record for each product). All other records get NULL for this field.

Note: #

Don't forget to replace your_table_name with the actual name of your table in the database.

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