当前位置：首页 > PHP

php实现日榜周榜月榜

2026-04-03 14:08:43PHP

PHP实现日榜、周榜、月榜的方法

实现日榜、周榜和月榜的核心逻辑是通过时间筛选数据，并按指定规则排序。以下是具体实现方案：

数据库表设计

假设有一个记录用户得分的表scores，结构如下：

CREATE TABLE scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    score INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

日榜实现

获取当天得分的排行榜：

$today = date('Y-m-d');
$sql = "SELECT user_id, SUM(score) as total_score 
        FROM scores 
        WHERE DATE(created_at) = :today 
        GROUP BY user_id 
        ORDER BY total_score DESC 
        LIMIT 10";
$stmt = $pdo->prepare($sql);
$stmt->execute([':today' => $today]);
$dailyRanking = $stmt->fetchAll(PDO::FETCH_ASSOC);

周榜实现

获取本周得分的排行榜：

$weekStart = date('Y-m-d', strtotime('this week Monday'));
$weekEnd = date('Y-m-d', strtotime('this week Sunday'));
$sql = "SELECT user_id, SUM(score) as total_score 
        FROM scores 
        WHERE DATE(created_at) BETWEEN :weekStart AND :weekEnd 
        GROUP BY user_id 
        ORDER BY total_score DESC 
        LIMIT 10";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':weekStart' => $weekStart,
    ':weekEnd' => $weekEnd
]);
$weeklyRanking = $stmt->fetchAll(PDO::FETCH_ASSOC);

月榜实现

获取本月得分的排行榜：

$monthStart = date('Y-m-01');
$monthEnd = date('Y-m-t');
$sql = "SELECT user_id, SUM(score) as total_score 
        FROM scores 
        WHERE DATE(created_at) BETWEEN :monthStart AND :monthEnd 
        GROUP BY user_id 
        ORDER BY total_score DESC 
        LIMIT 10";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':monthStart' => $monthStart,
    ':monthEnd' => $monthEnd
]);
$monthlyRanking = $stmt->fetchAll(PDO::FETCH_ASSOC);

性能优化方案

对于大型应用，考虑以下优化措施：

为created_at字段创建索引
使用缓存（Redis）存储排行榜结果
定时任务预计算排行榜数据

Redis缓存实现示例

$redis = new Redis();
$redis->connect('127.0.0.1', 6379);

// 获取日榜缓存
if (!$dailyRanking = $redis->get('daily_ranking')) {
    // 缓存不存在时从数据库获取
    $dailyRanking = getDailyRankingFromDB();
    $redis->setex('daily_ranking', 3600, json_encode($dailyRanking));
} else {
    $dailyRanking = json_decode($dailyRanking, true);
}

定时任务更新

使用Cron定时更新排行榜：

php实现日榜周榜月榜

0 0 * * * /usr/bin/php /path/to/update_daily_ranking.php
1 0 * * 1 /usr/bin/php /path/to/update_weekly_ranking.php
2 0 1 * * /usr/bin/php /path/to/update_monthly_ranking.php