php实现日榜周榜月榜

实现日榜、周榜、月榜的基本思路

通过时间范围筛选数据并排序是核心逻辑。通常需要结合数据库查询与PHP日期函数处理。

数据库表结构设计

假设有一个记录用户行为的表（如文章点击、积分等）：

CREATE TABLE user_actions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    value INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

日榜实现方法

获取当天数据的统计排行：

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

周榜实现方法

获取本周数据的统计排行：

$monday = date('Y-m-d', strtotime('monday this week'));
$sunday = date('Y-m-d', strtotime('sunday this week'));
$sql = "SELECT user_id, SUM(value) as total 
        FROM user_actions 
        WHERE DATE(created_at) BETWEEN :monday AND :sunday 
        GROUP BY user_id 
        ORDER BY total DESC 
        LIMIT 10";
$stmt = $pdo->prepare($sql);
$stmt->execute([':monday' => $monday, ':sunday' => $sunday]);
$weeklyRanking = $stmt->fetchAll(PDO::FETCH_ASSOC);

月榜实现方法

获取当月数据的统计排行：

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

性能优化建议

对高频查询场景应建立缓存机制：

$cacheKey = 'daily_rank_' . date('Ymd');
if (!$ranking = $cache->get($cacheKey)) {
    // 执行数据库查询
    $cache->set($cacheKey, $ranking, 3600); // 缓存1小时
}

为提升查询效率，建议在数据库添加复合索引：

ALTER TABLE user_actions ADD INDEX idx_created_at_user_id (created_at, user_id);

时区处理方案

确保时区一致性，在PHP脚本开头设置：

date_default_timezone_set('Asia/Shanghai');

对于跨时区应用，可在查询时转换时区：

SELECT user_id, SUM(value) as total 
FROM user_actions 
WHERE DATE(CONVERT_TZ(created_at, '+00:00', '+08:00')) = :date
GROUP BY user_id