游戏积分榜、直播平台礼物榜、电商热销榜

1. 游戏积分榜、直播平台礼物榜、电商热销榜#

Game Ranking List：通常需要实时更新玩家积分并快速查询前 N 名（如 Top 100）, 积分可能频繁变化，支持动态排序

Live Streaming Gift Leaderboard：主播或用户收到礼物后，礼物价值累加，排行榜需实时反映最新排名，查询 Top N 或某用户排名

E-commerce Bestsellers List：基于商品销量排序，可能涉及复杂排序规则（如销量+时间权重），更新频率可能稍低，但查询量大

共同特点：

实时或近实时更新
频繁查询 Top N 或某项排名
数据量可能较大（几十万到数亿条）
排序基于单一或复合分数

Priority Queues

Best when internal, performance-critical, and simple.

When implementing low-level logic.

Redis Sorted Sets

More frequently used in real-world, production-level systems

Leaderboard systems (e.g., scores in games)

Ranking systems (newsfeeds, social media)

Expiring content prioritization

Rate limiting

2. Bestsellers#

2.1. Database Design#

-- Products table
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    inventory_count INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Order Items table (for tracking individual products in orders)
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sales statistics table (to track sales for bestseller calculations)
CREATE TABLE product_sales_stats (
    stats_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    total_quantity_sold INT NOT NULL DEFAULT 0,
    total_revenue DECIMAL(15, 2) NOT NULL DEFAULT 0,
    last_sale_at TIMESTAMP NULL,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Separating orders from order items follows a common database normalization practice
- Reduce data redundancy
One-to-Many Relationship
- A single order typically contains multiple products. The separation represents this one-to-many relationship accurately
Performance for Order Processing
- Orders table can be queried quickly for order status checks without loading product details

Imagine an order with 10 different products. If we combined orders and order items into a single table:

order_id | user_id | product_id | quantity | price | status | created_at | ...
---------|---------|------------|----------|-------|--------|------------|-----
1        | 42      | 101        | 2        | 19.99 | pending| 2023-01-01 | ...
1        | 42      | 205        | 1        | 29.99 | pending| 2023-01-01 | ...
1        | 42      | 310        | 5        | 5.99  | pending| 2023-01-01 | ...

This approach has serious problems:

User information is duplicated for every product in the order
Status updates require updating multiple rows
Calculating order totals is more complex
Finding basic order information means sorting through product entries

Make sure your tables are properly indexed:

CREATE INDEX idx_order_items_product_id ON order_items(product_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

2.2. Query for Bestsellers#

-- Basic bestsellers query (by quantity)
SELECT 
    p.product_id,
    p.name,
    p.price,
    SUM(oi.quantity) as total_sold
FROM 
    products p
JOIN 
    order_items oi ON p.product_id = oi.product_id
JOIN 
    orders o ON oi.order_id = o.order_id
WHERE 
    o.status = 'completed'
    -- Optional: Add date range filter, e.g., for weekly bestsellers
    -- AND o.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) 
GROUP BY 
    p.product_id, p.name, p.price
ORDER BY 
    total_sold DESC
LIMIT 3;

Alternatively, if we maintain the product_sales_stats table through triggers or scheduled updates:

-- Faster bestsellers query using the stats table
SELECT 
    p.product_id,
    p.name,
    p.price,
    ps.total_quantity_sold
FROM 
    products p
JOIN 
    product_sales_stats ps ON p.product_id = ps.product_id
ORDER BY 
    ps.total_quantity_sold DESC
LIMIT 3;

Multiple joins are expensive - The original query joins three tables (products, order_items, and orders), which becomes increasingly costly as these tables grow.

The product_sales_stats table is actually the better approach for performance reasons. This query performs much better as it only requires a single join and the data is already aggregated.

Instead of updating the sales stats on every order, you could set up a scheduled job that runs periodically (e.g., every 5 minutes or hourly) to update the aggregated data:

-- Run in a scheduled job
UPDATE product_sales_stats ps
JOIN (
    SELECT 
        oi.product_id,
        SUM(oi.quantity) AS total_quantity,
        SUM(oi.quantity * oi.unit_price) AS total_revenue
    FROM 
        order_items oi
    JOIN 
        orders o ON oi.order_id = o.order_id
    WHERE 
        o.status = 'completed'
        AND o.updated_at > (SELECT MAX(last_update) FROM stats_update_log)
    GROUP BY 
        oi.product_id
) new_data ON ps.product_id = new_data.product_id
SET 
    ps.total_quantity_sold = ps.total_quantity_sold + new_data.total_quantity,
    ps.total_revenue = ps.total_revenue + new_data.total_revenue,
    ps.updated_at = NOW();

2.3. Redis sorted sets#

For even better performance, you could use Redis sorted sets to track bestsellers directly:

# When processing an order
def update_bestseller_ranking(product_id, quantity_sold):
    # Increment the score in a sorted set
    redis_client.zincrby('bestsellers:by_quantity', quantity_sold, product_id)
    
    # Store product details in a hash
    redis_client.hset(f'product:{product_id}', mapping={
        'name': product_name,
        'price': product_price
    })

# To get top 3 bestsellers
def get_top_bestsellers(count=3):
    # Get product IDs with highest scores
    top_product_ids = redis_client.zrevrange('bestsellers:by_quantity', 0, count-1, withscores=True)
    
    bestsellers = []
    for product_id, score in top_product_ids:
        product_data = redis_client.hgetall(f'product:{product_id}')
        bestsellers.append({
            'product_id': product_id,
            'name': product_data['name'],
            'price': product_data['price'],
            'total_quantity_sold': int(score)
        })
    
    return bestsellers