php_script/onlive/sc_user_cond...

<?php

require_once 'DB_PDO.class.php';
require_once 'TBK.class.php';
require_once 'conf.class.php';

define("DETAILDEBUG", 0);//打印流程详细信息，如有需要，设为1
define("LIMIT_NUM", 0);//限制导入条数,0不限制,方便测试,上线设为0

/**
 * 快手网红数据筛选条件中间表
 */
#设置时区
date_default_timezone_set('PRC');
set_time_limit(0);
ini_set('memory_limit','1024M');

	# 记录脚本开始时间
	$scriptStart = date("Y-m-d H:i:s");
	$sql = "SELECT user_id,photo from sc_kwai_users where user_id>0 and fan>=10000 and fan<50000 and photo>0 order by id desc ";  
	$_PDO=DB_PDO::getInstance( conf::$DB_CONF );
	$stmt = $_PDO->prepare($sql);  
	$stmt->execute();  	 
	$res = $stmt->fetchAll(PDO::FETCH_ASSOC); 

echo "\n条数：".count($res);
$arr = array();
$str = '';
$nu = 0;
$up = 0;
foreach($res as $k=>$v){
	$sql = "select avg(view_count) as avg_view_count, avg(like_count) as avg_like_count, avg(comment_count) as avg_comment_count from sc_kwai_video where user_id= {$v['user_id']}"	;
	$_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);
    $stmt->execute();
    $ret = $stmt->fetch(PDO::FETCH_ASSOC);
	$v['photo'] = (int)$v['photo'];

	if(!empty($ret)){
		$view_ave = (int)$ret['avg_view_count'];
		$like_ave = (int)$ret['avg_like_count'];
		$comment_ave = (int)$ret['avg_comment_count'];
		$view_count = $ret['avg_view_count'] * $v['photo'];
        $like_count = $ret['avg_like_count'] * $v['photo'];
        $comment_count = $ret['avg_comment_count'] * $v['photo'];
	}else{
		$view_ave = 0;
        $like_ave = 0;
        $comment_ave = 0;
        $view_count = 0;
        $like_count = 0;
        $comment_count = 0;

	}

	$sql = "select distinct scc.author_id,su.age,su.birthdayTs,su.cityName,scc.user_sex from sc_kwai_video_comment scc left join sc_kwai_users su on su.user_id=scc.author_id where scc.user_id = {$v['user_id']}";
	$_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);
    $stmt->execute();
    $fanlist = $stmt->fetchAll(PDO::FETCH_ASSOC);
	$age_t = array();
	$city_t = array();
	$sex_t = array();
	$age_arr = array();
	foreach($fanlist as $fan){
		if(empty($fan['age']) && !empty($fan['birthdayTs'])){
			$fan['age'] = getTrueAge($fan['birthdayTs']);
		}
		if($fan['age']>0) $age_arr[] = (int)$fan['age'];
		$age_key = getage($fan['age']);
		if( $age_key>0) { if(isset($age_t[$age_key])){ $age_t[$age_key]++;}else{ $age_t[$age_key] = 1;} }

		$city_name_arr = explode(' ', $fan['cityName']);
		$city_key= isset($city_name_arr[0]) ? $city_name_arr[0] : null;
		if(!empty($city_key)){ if(isset($city_t[$city_key])){ $city_t[$city_key]++;}else{ $city_t[$city_key] = 1;} }

		$sex_key = $fan['user_sex'];
		if(in_array($sex_key, ['F','M'])){ if(isset($sex_t[$sex_key])){ $sex_t[$sex_key]++;}else{ $sex_t[$sex_key] = 1;} }
		
	}

	arsort($age_t);
	arsort($city_t);
	arsort($sex_t);
    reset($age_t);
    reset($city_t);
    reset($sex_t);
	$fan_sex = key($sex_t);
	$fan_age = (int)key($age_t);
	$fan_city = key($city_t);
	#平均年龄
	if(!empty($age_arr)){ $fan_age_ave = round( array_sum($age_arr) / count($age_arr) ); }else{ $fan_age_ave =0; }

	#推广品类/产品价格范围
	$root_category_id = '';
	$category_ids = '';
	$price_type = 0;
	$sql = "select root_category_id, category_id, price from sc_kwai_onlive_commodity where kwai_user_id={$v['user_id']} order by sell_count desc limit 15";
	$stmt = $_PDO->prepare($sql);
    $stmt->execute();
    $goods_info = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $goods_arr = array();
    $goods_info_new = array();
    if(!empty($goods_info)){
    	foreach($goods_info as $good){
    		$category_id = $good['category_id'];
    		if(isset($goods_arr[$category_id])){
    			$goods_arr[$category_id] ++;
    		}else{
    			$goods_arr[$category_id] = 1;
    		}

    		$goods_info_new[$category_id] = $good;
    	}
    	arsort($goods_arr);
    	reset($goods_arr);
    	$cate_key = key($goods_arr);
    	$root_category_id = $goods_info_new[$cate_key]['root_category_id'];
    	$category_ids = $goods_info_new[$cate_key]['category_id'];
    	$price = $goods_info_new[$cate_key]['price'];
    	$price_type = price_type($price);
    }

	#是否已存在
	$sql = "select id from sc_users_condition where user_id='".$v['user_id']."' limit 1";
	$stmt = $_PDO->prepare($sql);
    $stmt->execute();
    $if_exists = $stmt->fetch(PDO::FETCH_ASSOC);
	if(!empty($if_exists)){
		#已存在更新操作
		$sql = "update sc_users_condition set view_count={$view_count},like_count={$like_count},comment_count={$comment_count},view_ave={$view_ave},like_ave={$like_ave},comment_ave={$comment_ave},fan_cityName='{$fan_city}',fan_sex='{$fan_sex}',fan_age_limits={$fan_age},photo={$v['photo']},fan_age_ave={$fan_age_ave}, root_category_id='{$root_category_id}',category_ids='{$category_ids}',price_type='{$price_type}' where user_id = '".$v['user_id']."'";
		$stmt = $_PDO->prepare($sql);
    	$stmt->execute();	
		$upres = $stmt->rowCount();
		if($upres) $up++;
	}else{
		$sql = "insert into sc_users_condition (user_id, view_count, like_count, comment_count, view_ave, like_ave, comment_ave, fan_cityName, fan_sex, fan_age_limits, photo, fan_age_ave, root_category_id, category_ids, price_type) values({$v['user_id']}, {$view_count}, {$like_count}, {$comment_count}, {$view_ave}, {$like_ave}, {$comment_ave}, '{$fan_city}','{$fan_sex}',{$fan_age},{$v['photo']},{$fan_age_ave},'{$root_category_id}','{$category_ids}',{$price_type} ) ";
		$stmt = $_PDO->prepare($sql);
    	$stmt->execute();
		$res_in = $_PDO->lastinsertid();	

    	if($res_in) $nu++; 
	}
}
echo "\n成功更新:".$up;
echo "\n成功新增:".$nu;

function getsign($name){
	$sql = 'select id,parent_id from sc_web_signs where name = "'.$name.'"';
	$_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $stmt = $_PDO->prepare($sql);
    $stmt->execute();
    $res = $stmt->fetch(PDO::FETCH_ASSOC);

	if(empty($res)){
		return 20;
	}elseif($res['parent_id'] == 0){
		return $res['id'];
	}else{
		return $res['parent_id'];
	}

}

function getage($age){
	if($age>=6 && $age<=17){
		return 1;
	}elseif($age>17 && $age<=24){
		return 2;
	}elseif($age>24 && $age<=30){
        return 3;
    }elseif($age>30 && $age<=35){
        return 4;
    }elseif($age>35 && $age<=40){
        return 5;
    }elseif($age>40 ){
        return 6;
    }

	return 0;
}

function getTrueAge($age){
        if($age === false){
            return false;
        }
        list($y1,$m1,$d1) = explode("-",date("Y-m-d",$age));
        $now = strtotime("now");
        list($y2,$m2,$d2) = explode("-",date("Y-m-d",$now));
        $age = $y2 - $y1;
        if((int)($m2.$d2) < (int)($m1.$d1)) $age -= 1;
        return $age;
    }

//1：10元一下；2：10-50；3：50-100；4：100-500；5：500-1000；6：1000-5000；7：5000+
function price_type($price){
	$type = 0;
	if($price>=5000){
		$type = 7;
	}elseif($price>=1000){
		$type = 6;
	}elseif($price>=500){
		$type = 5;
	}elseif($price>=100){
		$type = 4;
	}elseif($price>=50){
		$type = 3;
	}elseif($price>=10){
		$type = 2;
	}elseif($price>=0){
		$type = 1;
	}

	return $type;
}   

echo "\n结果：".trim($str, ',');