PostgreSQL结合DISTINCT ON实现分页获取不重复随机商品

Alright, let's tackle this problem step by step. The core issue here is that PostgreSQL's DISTINCT ON forces your ORDER BY clause to start with the column you're deduplicating on (in your case, products.id), which locks you into an order you don't want. Plus, using OFFSET with ORDER BY random() is risky because each query will generate a new random order, leading to duplicate or missing products as you paginate.

Here are two solid solutions to get the random/descending order you need, while still fetching each product's lowest price and avoiding duplicates across "load more" clicks:

Solution 1: Database-Side Fixed Sorting with CTEs #

This approach precomputes each product's lowest price, assigns a fixed sort order (random or descending), then paginates based on that order. This ensures consistent pagination without duplicates.

For Random Order:

We'll use a CTE to generate a random row number for each product, then fetch rows in chunks of 3:

WITH product_min_prices AS (
    -- First, get the lowest price for each product
    SELECT productId, MIN(price) AS min_price
    FROM product_price_size
    JOIN prices ON prices.id = product_price_size.priceId
    GROUP BY productId
),
ranked_products AS (
    -- Assign a random, fixed row number to each valid product
    SELECT 
        p.*, 
        pmp.min_price,
        ROW_NUMBER() OVER (ORDER BY random()) AS rn
    FROM products p
    JOIN product_min_prices pmp ON p.id = pmp.productId
    WHERE p.active = true AND (p.archived IS NULL OR p.archived = false)
)
-- Fetch the chunk of products based on your offset (0, 3, 6, etc.)
SELECT id, name, description, min_price -- Replace with your actual product columns
FROM ranked_products
WHERE rn BETWEEN :offset + 1 AND :offset + 3
ORDER BY rn;

• The ROW_NUMBER() with ORDER BY random() creates a one-time random order for all products. Subsequent "load more" calls just increment the :offset (e.g., 0 → 3 → 6) to get the next chunk without duplicates.
• This works great if your product set is stable (no frequent adds/deletes). If products change often, you might want to regenerate the ranked list on each session.

For Descending Order (e.g., by lowest price or product ID):

Swap out the ORDER BY in the ROW_NUMBER() clause to whatever you need. For example, to sort by lowest price descending:

WITH product_min_prices AS (
    SELECT productId, MIN(price) AS min_price
    FROM product_price_size
    JOIN prices ON prices.id = product_price_size.priceId
    GROUP BY productId
),
ranked_products AS (
    SELECT 
        p.*, 
        pmp.min_price,
        ROW_NUMBER() OVER (ORDER BY pmp.min_price DESC) AS rn
    FROM products p
    JOIN product_min_prices pmp ON p.id = pmp.productId
    WHERE p.active = true AND (p.archived IS NULL OR p.archived = false)
)
SELECT id, name, description, min_price
FROM ranked_products
WHERE rn BETWEEN :offset + 1 AND :offset + 3
ORDER BY rn;

Solution 2: App-Side Sorting (Simpler for Small Datasets) #

Since you only have 15 products total, this is a super straightforward option:

1. First, fetch all valid product IDs from the database.
2. Shuffle them (for random order) or sort them descending (for fixed order) in your application code.
3. Cache this sorted list (e.g., in the user's session or memory).
4. For each "load more" click, grab the next 3 IDs from the cached list and fetch their details + lowest price.

Example pseudocode (adjust to your language/framework):

# On initial page load
valid_product_ids = db.execute(
    "SELECT id FROM products WHERE active = true AND (archived IS NULL OR archived = false)"
).fetchall()

# Shuffle for random order (or sort descending for fixed order)
import random
random.shuffle(valid_product_ids)
# Cache this list (e.g., in session storage or app memory)
session['sorted_product_ids'] = valid_product_ids
current_offset = 0

# Load first 3 products
selected_ids = valid_product_ids[current_offset:current_offset+3]
products = db.execute(
    """
    SELECT p.*, MIN(pr.price) AS min_price
    FROM products p
    JOIN product_price_size pps ON p.id = pps.productId
    JOIN prices pr ON pr.id = pps.priceId
    WHERE p.id IN (%s)
    GROUP BY p.id
    """ % ','.join(['%s']*len(selected_ids)),
    selected_ids
).fetchall()

# On "load more" click
current_offset += 3
selected_ids = session['sorted_product_ids'][current_offset:current_offset+3]
# Repeat the product query with these IDs

This approach avoids any database-side sorting headaches and guarantees no duplicates because the order is fixed once the list is generated. It's perfect for small datasets like your 15 products.

Why Your Original Query Failed #

PostgreSQL requires that the first column in your ORDER BY matches the DISTINCT ON column to ensure deterministic deduplication. That's why you couldn't add random() or a descending sort without breaking the query. By separating the price calculation from the sorting/pagination, we get around this restriction.

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