无限层级拉新，数据建立和数据分析 php

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);
            }
        }
    }