标签归档:统计

Laravel 数据统计实例

以下程序大概对几百万数据进行汇总统计,依赖关系数据库而不是在程序中进行计算,所以需要先导入数据。

<?php
/* 统计订单重复购买率等
 * 
 * 用法:
 * 1 数据导入(定位文件:storage/app/orders/amazon/default/2015_01.txt)
 * php artisan statistic:order:purchase:rate --platform=amazon --account=default --year=2016 --month=01
 *  
 * 2 数据统计 (1月份)
 * php artisan statistic:order:purchase:rate --platform=amazon --account=default --year=2016 --month=01 --fire
 * 
 * 3 数据统计 (全年,--month=all表示全年)
 * php artisan statistic:order:purchase:rate --platform=amazon --account=default --year=2016 --month=all --fire
 * 
 */

namespace App\Console\Commands;

use Illuminate\Console\Command;

use DB,Storage,Validator;

class StatisticOrderPurchaseRate extends Command
{
    protected $signature = 'statistic:order:purchase:rate {--platform=} {--account=} {--year=} {--month=} {--fire}';
    protected $description = '';
    
    public function __construct()
    {       
        parent::__construct();
    }

    public function handle()
    {   
        $prefix = \DB::connection()->getTablePrefix();
        
        $platform = strtolower(trim($this->option('platform')));
        if(!in_array($platform, ['amazon', "ebay"])) {
            echo "平台必须是:amazon 和 ebay";
            return;
        }
        
        $account = strtolower(trim($this->option('account')));
        if(empty($account)) {
            $account = "default";
        }
        
        $year = strtolower(trim($this->option('year')));
        $month = strtolower(trim($this->option('month')));
        
        if(empty($year) || empty($month)) {
            echo "年 月必须指定,用来定位文件";
            return;
        }
        
        $fire = $this->option('fire');
        if(empty($fire)) {
            $file = "orders/".$platform."/".$account."/".$year."_".$month.".txt";
            
            if(!Storage::exists($file)){
                echo "数据文件无法定位:$file";
                return;
            }
            
            $filePath = storage_path("app/".$file);
            
            // 读取数据文件  一次性读完,然后批量插入数据库
            if($platform === "amazon") {
                $fp = fopen($filePath,"r");
                $data = array();
                while($line = fgetcsv ($fp, 0, "\t", ' ')) {
                    if(!isset($line[0])) { continue; }
                    
                    $order_id = $line[0];
                    if($order_id === 'amazon-order-id') {
                        continue;
                    }
                    $email = $line[10];
                    $total = round($line[17],2);
                    
                    if(isset($data[$order_id])) {
                        $data[$order_id]["total"] += $total;
                    } else {
                        $data[$order_id] = [
                            "platform" => $platform,
                            "account" => $account,
                            "year" => $year,
                            "month" => $month,
                            "order_id" => $order_id,
                            "email" => $email,
                            "total" => $total
                        ];
                    }
                }
                fclose($fp);
                
                // 数据分块批量插入数据库
                if(\DB::statement("DELETE FROM ".$prefix."order_summary WHERE platform='".$platform."' and account='".$account."' and year='".$year."' and month='".$month."'")) {
                    
                    // 分块插入
                    $chunks = array_chunk($data, 1000);
                    foreach($chunks as $chunk) {
                        \DB::table('order_summary')->insert($chunk);
                    }
                    
                }            
            } else {
                // 
            }
        
        } else {
            
            if($month === 'all') {
                // 客单价
                $total_order = DB::select("SELECT sum(total) as total_sale, count(order_id) as total_order, (sum(total)/count(order_id)) as avg_price,
                count(distinct email) as total_customer
                FROM ".$prefix."order_summary
                WHERE platform='".$platform."' and account='".$account."' and year='".$year."' and email != ''");
            } else {
                // 客单价
                $total_order = DB::select("SELECT sum(total) as total_sale, count(order_id) as total_order, (sum(total)/count(order_id)) as avg_price,
                count(distinct email) as total_customer
                FROM ".$prefix."order_summary
                WHERE platform='".$platform."' and account='".$account."' and year='".$year."' and month='".$month."' and email != ''"); 
            }
            
            $has = DB::table("order_lookup")->where("platform", $platform)->where("account", $account)
            ->where("year", $year)->where("month", $month)->first();
            
            if(isset($has->id)) {
                \DB::table("order_lookup")->where("platform", $platform)->where("account", $account)
                ->where("year", $year)->where("month", $month)->update([
                    "total_sale" => $total_order[0]->total_sale,
                    "total_order" => $total_order[0]->total_order,
                    "avg_price" => $total_order[0]->avg_price,
                    "total_customer" => $total_order[0]->total_customer
                ]);
            } else {
                \DB::table("order_lookup")->insert([
                    "platform" => $platform,
                    "account" => $account,
                    "year" => $year,
                    "month" => $month,
                    "total_sale" => $total_order[0]->total_sale,
                    "total_order" => $total_order[0]->total_order,
                    "avg_price" => $total_order[0]->avg_price,
                    "total_customer" => $total_order[0]->total_customer
                ]);
            }
            
            if($month === 'all') {
                // 获取重复购买
                $repeate = DB::table("order_summary")->where("platform", $platform)->where("account", $account)
                ->where("year", $year)->where("email", "!=", "")->groupBy("email")->havingRaw("count(order_id) > 1")->get(["email"]); 
            } else {
                // 获取重复购买
                $repeate = DB::table("order_summary")->where("platform", $platform)->where("account", $account)
                ->where("year", $year)->where("month", $month)->where("email", "!=", "")->groupBy("email")->havingRaw("count(order_id) > 1")->get(["email"]);
            }
            $repeate_total = count($repeate);
            
            if($total_order[0]->total_customer > 0) {
                \DB::table("order_lookup")->where("platform", $platform)->where("account", $account)
                ->where("year", $year)->where("month", $month)->update([
                    "total_customer_repeat" => $repeate_total,
                    "total_customer_uniq" => (int)($total_order[0]->total_customer - $repeate_total),
                    "total_customer_repeat_rate" => round($repeate_total/$total_order[0]->total_customer,5)
                ]);
            }
        }
    }
}
/*
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for order_summary
-- ----------------------------
DROP TABLE IF EXISTS `order_summary`;
CREATE TABLE `order_summary` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `platform` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `account` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `year` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `month` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `order_id` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `total` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_order` (`platform`,`account`,`year`,`month`,`order_id`),
  KEY `account` (`account`) USING BTREE,
  KEY `year` (`year`) USING BTREE,
  KEY `month` (`month`) USING BTREE,
  KEY `email` (`email`) USING BTREE,
  KEY `platform` (`platform`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1108415 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Table structure for order_lookup
-- ----------------------------
DROP TABLE IF EXISTS `order_lookup`;
CREATE TABLE `order_lookup` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `platform` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `account` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `year` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `month` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `total_sale` decimal(10,2) DEFAULT NULL,
  `total_order` int(11) DEFAULT NULL,
  `avg_price` decimal(10,2) DEFAULT NULL COMMENT '客单价(总销售/总订单)',
  `total_customer` int(11) DEFAULT NULL,
  `total_customer_uniq` int(11) DEFAULT NULL COMMENT '没有重复购买',
  `total_customer_repeat_rate` decimal(10,4) DEFAULT NULL,
  `total_customer_repeat` int(11) DEFAULT NULL COMMENT '有重复购买',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
*/