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

PHP 统计模块数据聚合

wen PHP项目 142

本文目录导读：

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进行实时计数
  • 区间统计：按条件区间统计
  • 可视化数据：为图表提供格式化数据
  • 性能优化：缓存和索引优化建议

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

版权声明：NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播

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

下一篇PHP 发票管理模块

相关文章

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

友情链接攻略窝 · 礼包窝 · 配置窝 · 换算窝 · 算法窝 · 查文窝 · 手册窝 · 问答窝 · 全文搜 · 手册库
相关站点礼包窝 · 查文窝