游戏类应用排行榜（含总榜/月榜/周榜/日榜）排名趋势功能的数据库实现问询

Hey David, let's tackle that ranking trend feature you're stuck on—here's a practical, performance-friendly approach that aligns with your existing schema:

Core Idea for Ranking Trends #

To show whether a user's rank went up or down, you need to compare their current rank in a given period (day/week/month/total) to their rank in the previous identical period. Calculating this on-the-fly for large datasets will kill performance, so we'll lean on precomputed history to make queries fast.

Step 1: Add a Rank History Table #

First, create a table to store precomputed ranks for each period type. This avoids re-scanning massive user_scores or user tables every time you need a trend.

CREATE TABLE rank_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_uuid VARCHAR(255) NOT NULL,
    period_type ENUM('day', 'week', 'month', 'total') NOT NULL,
    period_start DATETIME NOT NULL, -- e.g., Monday 00:00 for weekly, 1st of month for monthly
    total_points INT NOT NULL, -- Sum of score_change (for period tables) or user.points (for total)
    rank INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_period (user_uuid, period_type, period_start), -- Critical for fast trend queries
    INDEX idx_period_rank (period_type, period_start, rank)
);

Step 2: Precompute Ranks with Scheduled Jobs #

Use a cron job, MySQL Event Scheduler, or your backend's task runner (like Quartz for Java, APScheduler for Python) to calculate and store ranks on a regular basis:

Example: Weekly Rank Calculation

-- Insert weekly Top100 ranks into history
INSERT INTO rank_history (user_uuid, period_type, period_start, total_points, rank)
SELECT
    user_uuid,
    'week' AS period_type,
    DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AS period_start, -- Start of current week (Monday)
    SUM(score_change) AS total_points,
    ROW_NUMBER() OVER (ORDER BY SUM(score_change) DESC) AS rank
FROM user_scores
WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)
  AND timestamp < DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 7 DAY)
GROUP BY user_uuid
ORDER BY total_points DESC
LIMIT 100;

Example: Total Rank Calculation

For your total leaderboard (based on user.points), run a similar job:

INSERT INTO rank_history (user_uuid, period_type, period_start, total_points, rank)
SELECT
    uuid AS user_uuid,
    'total' AS period_type,
    CURDATE() AS period_start, -- Store daily snapshots of total ranks
    points AS total_points,
    ROW_NUMBER() OVER (ORDER BY points DESC) AS rank
FROM user
ORDER BY points DESC
LIMIT 100;

Step 3: Fetch Rank Trends for Users #

When rendering a leaderboard, for each user, compare their current period rank to the previous period's rank:

Example: Get a User's Weekly Trend

-- Get current week's rank
SELECT rank FROM rank_history
WHERE user_uuid = 'target_user_uuid'
  AND period_type = 'week'
  AND period_start = DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY);

-- Get previous week's rank
SELECT rank FROM rank_history
WHERE user_uuid = 'target_user_uuid'
  AND period_type = 'week'
  AND period_start = DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 7 DAY);

Then apply your UI logic:

• If previous rank > current rank → Green upward arrow ↑ (rank improved)
• If previous rank < current rank → Red downward arrow ↓ (rank dropped)
• If no previous rank exists → Show a "New Entry" marker (e.g., ★)

Step 4: Optimizations #

• Clean up old data: Schedule a job to delete rank history older than 3-6 months (adjust based on your needs) to keep the table small.
• Cache frequent queries: For high-traffic leaderboards, cache the Top100 list and user trends in Redis to reduce database load.
• Real-time alternative (if needed): If you can't wait for scheduled jobs, use Redis Sorted Sets to maintain live ranks. For example, update a daily sorted set every time a user gets a score change, then compare the current rank to a snapshot of yesterday's set for trends. This uses more memory but offers real-time results.

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