2021年4月19日18:40:54
关于用户拉新活动,如果在活动开始之初,设计的时候没有做好用户关系处理的话,那就涉及统计的时候就是一个很麻烦的事情。
如果你刚好拉新员工的用户也是在这个表,需要筛选非员工的裂变用户数据就更麻烦了,因为这个是一个树状结构的,需要过滤掉员工数据,那就更蛋疼了,到现在我都没找到什么有效统计数据的办法,只能用简单的迭代查询数据库的办法来处理数据
然后通过一些非常规方式来查询数据库
调用服务
ini_set('memory_limit', '1024M');
$u = UUser::select(['id', 'phone', 'invite_people_id'])->orderBy('id', 'asc')->get()->toArray();
if (!empty($u)) {
foreach ($u as $k => $v) {
$p = new PullNewRelationshipService();
$p->runUserInvitRelationship($v);
}
}
class PullNewRelationshipService extends Base {
public $data;
public function __construct() {
$this->data = new SplStack();
}
/*
* 跑业务数据用户注册邀请关系
* 是否是用户ID是员工单独跑
*/
public function runUserInvitRelationship(array $data = [], array $ids = []) {
//插入
$uir = new UUserInvitRelationship();
if ($data['invite_people_id'] == 0) {
//如果invite_people_id 等于0说明是自己注册的直接写入
$uir->user_id = $data['id'];
$uir->level = 1;
$uir->save();
} else {
$this->recursionUserData($data['invite_people_id']);
// p($this->data);
//重置指针
$this->data->rewind();
$path = '';
$i = 1;
$run = [];
while ($this->data->valid()) {
$i++;
// echo $this->data->current(), "\n";
$path = $path . ',' . $this->data->current();
$run[] = $this->data->current();
$this->data->next();
}
// pp($upper_level_id);
$uir->user_id = $data['id'];
$uir->level = $i;
$uir->level_path = trim($path, ',');
$uir->upper_user_id = end($run);
$uir->root_user_id = reset($run);
$uir->save();
}
}
//递归u_user表数据,向上递归
public function recursionUserData(int $user_id = 0) {
if ($user_id == 0) {
return;
}
$u = UUser::where('id', $user_id)->first();
if ($u == null) {
} else {
$this->data->push($u->id);
$this->recursionUserData($u->invite_people_id);
}
}
数据库结构:
CREATE TABLE `u_user_invit_relationship` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`remark` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
`is_delete` tinyint(1) NOT NULL DEFAULT '10' COMMENT '10默认99删除',
`is_show` tinyint(1) NOT NULL DEFAULT '10' COMMENT '10默认显示99不显示',
`user_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',
`level` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '层级级别0是默认从1层',
`level_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '层级路径例如id,id,id',
`upper_user_id` bigint(20) DEFAULT '0' COMMENT '上层级别ID',
`root_user_id` bigint(20) DEFAULT '0' COMMENT '层级根用户ID',
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`),
KEY `level_path` (`level_path`)
) ENGINE=InnoDB AUTO_INCREMENT=404862 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户注册邀请关系表'
是从下到上的建立结构,查询sql
405,500,758,1753,这个是level_path数据结构
查询用户裂变数据SQL
SELECT count(*) FROM u_user_invit_relationship WHERE FIND_IN_SET(405,level_path)
性能还行比IN好
SELECT count(*) FROM u_user_invit_relationship WHERE 405 in(level_path);
如果你需要从上到下的过滤,有些东西,比如员工数据剔除,就只能从上到下剔除,想在上面 的建立的数据结构上去过滤,目前我没发现什么好办法
$mc = McStaffPullNewTask::select(['weixin_phone'])->get()->toArray();
$weixinPhones = array_column($mc, 'weixin_phone');
$ids = UUser::whereIn('phone', $weixinPhones)->get()->toArray();
$idArray = array_column($ids, 'id');
// $md = McStaffPullNewTask::orderBy('id', 'asc')->limit(10)->get();
$md = McStaffPullNewTask::orderBy('id', 'asc')->get();
foreach ($md as $k => $v) {
$t = new StaffRelationshipService();
$count = $t->runUserInvitRelationship($v->weixin_phone, $idArray);
$v->staff_pull_new = $count;
$v->save();
}
class StaffRelationshipService extends Base {
public $data;
public function __construct() {
$this->data = new SplStack();
}
//跑业务数据用户注册邀请关系
public function runUserInvitRelationship(int $phone = null, array $staffIdArray = []) {
$uUser = UUser::where('phone', $phone)->first();
// pp($uUser);
if ($uUser == null) {
// p('用户未找到');
return 0;
} else {
$key = array_search($uUser->id, $staffIdArray);
if ($key !== null) {
unset($staffIdArray[$key]);
}
$u = UUser::where('invite_people_id', $uUser->id)->whereNotIn('id', $staffIdArray)->get()->toArray();
// p(count($u));
$this->recursionUserData($u, $staffIdArray);
// p($this->data);
// //重置指针
$this->data->rewind();
$count = [];
while ($this->data->valid()) {
$count[] = $this->data->current();
$this->data->next();
}
// p(array_sum($count));
return array_sum($count);
}
}
//递归u_user表数据,向上递归
public function recursionUserData(array $ids = [], array $staffIdArray = []) {
if (!empty($ids)) {
$this->data->push(count($ids));
foreach ($ids as $k => $v) {
$key = array_search($v['id'], $staffIdArray);
if ($key !== null) {
unset($staffIdArray[$key]);
}
$ss = UUser::where('invite_people_id', $v['id'])->whereNotIn('id', $staffIdArray)->get();
$this->recursionUserData($ss->toArray(), $staffIdArray);
}
}
}