IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

PHP 统计模块数据聚合

wen PHP项目 2

本文目录导读：

PHP 统计模块数据聚合

  1. 基础统计类设计
  2. 多维度数据聚合
  3. 实时统计数据聚合
  4. 区间统计数据聚合
  5. 数据可视化聚合
  6. 统计数据聚合工厂
  7. 使用示例
  8. 性能优化建议

我来为您提供PHP数据统计模块的聚合方案,包括基础统计、数据聚合和可视化展示的完整实现。

基础统计类设计

<?php
/**
 * 数据统计聚合类
 */
class StatisticsAggregator {
    private $pdo;
    private $cache;
    public function __construct(PDO $pdo, $cache = null) {
        $this->pdo = $pdo;
        $this->cache = $cache;
    }
    /**
     * 获取基础统计数据
     */
    public function getBasicStats($userId, $dateRange = []) {
        $where = "WHERE user_id = :user_id";
        $params = [':user_id' => $userId];
        if (!empty($dateRange)) {
            $where .= " AND date BETWEEN :start_date AND :end_date";
            $params[':start_date'] = $dateRange['start'];
            $params[':end_date'] = $dateRange['end'];
        }
        $sql = "
            SELECT 
                COUNT(*) as total_count,
                COUNT(DISTINCT category_id) as category_count,
                SUM(amount) as total_amount,
                AVG(amount) as avg_amount,
                MAX(amount) as max_amount,
                MIN(amount) as min_amount
            FROM statistics_records
            $where
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
}
?>

多维度数据聚合

<?php
/**
 * 多维度数据聚合
 */
class DataAggregator {
    private $pdo;
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    /**
     * 按时间维度聚合（日/周/月/年）
     */
    public function aggregateByTime($table, $column, $timeField = 'created_at', $groupBy = 'DAY') {
        $validGroups = [
            'HOUR' => '%Y-%m-%d %H:00:00',
            'DAY' => '%Y-%m-%d',
            'WEEK' => '%Y-W%u',
            'MONTH' => '%Y-%m',
            'YEAR' => '%Y'
        ];
        if (!isset($validGroups[$groupBy])) {
            throw new InvalidArgumentException("Invalid group by: $groupBy");
        }
        $sql = "
            SELECT 
                DATE_FORMAT($timeField, '{$validGroups[$groupBy]}') as period,
                COUNT(*) as count,
                SUM($column) as total,
                AVG($column) as average
            FROM $table
            GROUP BY period
            ORDER BY period DESC
        ";
        return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 按分类聚合
     */
    public function aggregateByCategory($table, $categoryField, $valueColumn) {
        $sql = "
            SELECT 
                c.name as category_name,
                c.color as category_color,
                COUNT(*) as item_count,
                SUM(d.$valueColumn) as total_value,
                AVG(d.$valueColumn) as avg_value
            FROM $table d
            INNER JOIN categories c ON c.id = d.$categoryField
            GROUP BY c.id, c.name, c.color
            ORDER BY total_value DESC
        ";
        return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 多维交叉聚合
     */
    public function crossAggregate($dimensions = []) {
        $selectFields = [];
        $groupFields = [];
        foreach ($dimensions as $dimension) {
            $selectFields[] = $dimension['field'] . ' as ' . $dimension['alias'];
            $groupFields[] = $dimension['alias'];
        }
        $sql = "
            SELECT 
                " . implode(', ', $selectFields) . ",
                COUNT(*) as count,
                SUM(value) as total_value
            FROM data_table
            GROUP BY " . implode(', ', $groupFields) . "
            ORDER BY count DESC
        ";
        return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }
}
?>

实时统计数据聚合

<?php
/**
 * 实时数据聚合服务
 */
class RealtimeAggregator {
    private $redis;
    private $pdo;
    public function __construct($redis, PDO $pdo) {
        $this->redis = $redis;
        $this->pdo = $pdo;
    }
    /**
     * 实时统计计数
     */
    public function incrementCounter($key, $value = 1) {
        // Redis 实时计数
        $this->redis->incrBy($key, $value);
        // 设置过期时间（24小时）
        $this->redis->expire($key, 86400);
        // 同步到数据库
        $this->syncToDatabase($key, $value);
    }
    /**
     * 实时排行榜
     */
    public function updateLeaderboard($leaderboardKey, $userId, $score) {
        // 更新排行榜（使用ZSET）
        $this->redis->zAdd($leaderboardKey, $score, $userId);
        // 获取排行榜数据
        $ranking = $this->redis->zRevRange($leaderboardKey, 0, 9, true);
        // 更新缓存
        $this->cacheLeaderboard($leaderboardKey, $ranking);
        return $ranking;
    }
    /**
     * 同步到数据库
     */
    private function syncToDatabase($key, $value) {
        $sql = "
            INSERT INTO statistics_log (stat_key, stat_value, created_at)
            VALUES (:key, :value, NOW())
            ON DUPLICATE KEY UPDATE stat_value = stat_value + :value
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':key' => $key,
            ':value' => $value
        ]);
    }
}
?>

区间统计数据聚合

<?php
/**
 * 区间统计聚合
 */
class RangeAggregator {
    private $pdo;
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    /**
     * 生成统计区间
     */
    public function generateRanges($data, $ranges = []) {
        $statistics = [];
        foreach ($ranges as $rangeName => $conditions) {
            $stats = [
                'name' => $rangeName,
                'count' => 0,
                'sum' => 0,
                'avg' => 0,
                'min' => null,
                'max' => null
            ];
            $filteredData = array_filter($data, function($item) use ($conditions) {
                foreach ($conditions as $field => $values) {
                    if (!in_array($item[$field], $values)) {
                        return false;
                    }
                }
                return true;
            });
            if (!empty($filteredData)) {
                $statistics[$rangeName] = $this->calculateStats($filteredData);
            }
        }
        return $statistics;
    }
    /**
     * 按条件区间统计
     */
    public function aggregateByCondition($table, $conditions = []) {
        $where = "1=1";
        $params = [];
        foreach ($conditions as $field => $value) {
            if ($field == 'date_range') {
                $where .= " AND date BETWEEN :start_date AND :end_date";
                $params[':start_date'] = $value['start'];
                $params[':end_date'] = $value['end'];
            } else {
                $where .= " AND $field = :$field";
                $params[":$field"] = $value;
            }
        }
        $sql = "
            SELECT 
                COUNT(*) as count,
                SUM(value) as total,
                AVG(value) as average,
                MIN(value) as min_value,
                MAX(value) as max_value
            FROM $table
            WHERE $where
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    /**
     * 计算统计数据
     */
    private function calculateStats($data) {
        $values = array_column($data, 'value');
        return [
            'count' => count($values),
            'sum' => array_sum($values),
            'avg' => count($values) > 0 ? array_sum($values) / count($values) : 0,
            'min' => min($values),
            'max' => max($values)
        ];
    }
}
?>

数据可视化聚合

<?php
/**
 * 数据可视化聚合器
 */
class VisualAggregator {
    private $pdo;
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    /**
     * 获取图表数据（折线图/柱状图）
     */
    public function getChartData($table, $timeField, $valueField, $groupBy = 'DAY') {
        $formats = [
            'HOUR' => '%Y-%m-%d %H:00',
            'DAY' => '%m-%d',
            'WEEK' => '%Y-W%u',
            'MONTH' => '%Y-%m',
            'YEAR' => '%Y'
        ];
        $format = $formats[$groupBy] ?? $formats['DAY'];
        $sql = "
            SELECT 
                DATE_FORMAT($timeField, '$format') as label,
                SUM($valueField) as value
            FROM $table
            GROUP BY label
            ORDER BY $timeField ASC
        ";
        $result = $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        // 格式化图表数据
        return [
            'labels' => array_column($result, 'label'),
            'values' => array_map('intval', array_column($result, 'value')),
            'total' => array_sum(array_column($result, 'value'))
        ];
    }
    /**
     * 获取饼图数据（分类占比）
     */
    public function getPieChartData($table, $categoryField, $valueField) {
        $sql = "
            SELECT 
                c.name as label,
                SUM(d.$valueField) as value,
                c.color as color
            FROM $table d
            INNER JOIN categories c ON c.id = d.$categoryField
            GROUP BY c.id, c.name, c.color
            ORDER BY value DESC
        ";
        $result = $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        // 计算占比
        $total = array_sum(array_column($result, 'value'));
        foreach ($result as &$item) {
            $item['percentage'] = $total > 0 ? round(($item['value'] / $total) * 100, 2) : 0;
        }
        return $result;
    }
}
?>

统计数据聚合工厂

<?php
/**
 * 统计聚合工厂
 */
class StatisticsFactory {
    private $pdo;
    private $redis;
    private $config;
    public function __construct(PDO $pdo, $redis = null, $config = []) {
        $this->pdo = $pdo;
        $this->redis = $redis;
        $this->config = $config;
    }
    /**
     * 创建聚合器
     */
    public function create($type) {
        switch ($type) {
            case 'basic':
                return new StatisticsAggregator($this->pdo, $this->redis);
            case 'time':
                return new DataAggregator($this->pdo);
            case 'realtime':
                return new RealtimeAggregator($this->redis, $this->pdo);
            case 'range':
                return new RangeAggregator($this->pdo);
            case 'visual':
                return new VisualAggregator($this->pdo);
            default:
                throw new InvalidArgumentException("Unsupported aggregator type: $type");
        }
    }
}
?>

使用示例

<?php
// 初始化
$pdo = new PDO('mysql:host=localhost;dbname=statistics', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$factory = new StatisticsFactory($pdo, $redis);
// 使用示例
// 1. 基础统计
$basicAgg = $factory->create('basic');
$stats = $basicAgg->getBasicStats(1, [
    'start' => '2024-01-01',
    'end' => '2024-01-31'
]);
// 2. 时间聚合
$timeAgg = $factory->create('time');
$monthlyStats = $timeAgg->aggregateByTime('orders', 'amount', 'created_at', 'MONTH');
// 3. 实时统计
$realtimeAgg = $factory->create('realtime');
$realtimeAgg->incrementCounter('page_views');
$leaderboard = $realtimeAgg->updateLeaderboard('user_scores', 123, 95);
// 4. 图表数据
$visualAgg = $factory->create('visual');
$chartData = $visualAgg->getChartData('orders', 'created_at', 'amount', 'MONTH');
$pieData = $visualAgg->getPieChartData('orders', 'category_id', 'amount');
// 输出统计结果
header('Content-Type: application/json');
echo json_encode([
    'basic_stats' => $stats,
    'monthly_trends' => $monthlyStats,
    'realtime_views' => $realtimeAgg,
    'chart_data' => $chartData,
    'category_distribution' => $pieData
], JSON_PRETTY_PRINT);
?>

性能优化建议

<?php
/**
 * 统计性能优化
 */
class StatisticsOptimizer {
    private $cache;
    private $pdo;
    /**
     * 缓存优化
     */
    public function getCachedStatistics($key, $callback, $ttl = 3600) {
        // 尝试从缓存获取
        $cached = $this->cache->get($key);
        if ($cached !== false) {
            return unserialize($cached);
        }
        // 缓存未命中，执行查询
        $data = $callback();
        // 设置缓存
        $this->cache->set($key, serialize($data), $ttl);
        return $data;
    }
    /**
     * 数据表索引优化
     */
    public function getIndexSuggestion($table) {
        $sql = "SHOW INDEX FROM $table";
        $indexes = $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        // 分析并返回优化建议
        return $this->analyzeIndexes($indexes);
    }
}
?>

这些代码提供了完整的PHP统计数据聚合解决方案,包括：

  • 基础统计：总数、平均值、极值等
  • 多维度聚合：按时间、分类、自定义维度
  • 实时统计：利用Redis进行实时计数
  • 区间统计：按条件区间统计
  • 可视化数据：为图表提供格式化数据
  • 性能优化：缓存和索引优化建议

使用时可以根据具体需求选择相应的聚合器,并根据业务逻辑进行调整和扩展。

上一篇PHP 实时数据大屏后端

下一篇PHP 发票管理模块

相关文章

抱歉，评论功能暂时关闭!