2024-09-12 用户体系 上线版本数据初始化
user_base ==> user_system_grade_score
同步数据初始化
insert
into
`gugux-user-system`.user_system_grade_score (user_id,
phone,
nickname,
gugu_id,
total_score,
fun_score,
fun_score_prop,
soc_score,
soc_score_prop,
content_score,
content_score_prop,
trans_score ,
trans_score_prop,
create_time)
select
ub.id as user_id,
ub.phone,
ub.nickname,
ub.gugu_id,
0 total_score ,
0 fun_score ,
0 fun_score_prop ,
0 soc_score ,
0 soc_score_prop ,
0 content_score ,
0 content_score_prop ,
0 trans_score ,
0 trans_score_prop ,
now() as create_time
from
`gugux-user`.user_base ub;
---社交板块--- 功能权重：25%
1.俱乐部主理人认证 权重：10% 分值：1000 统计规则：完成一次俱乐部主理人认证，仅限第一次认证 统计 club_admin 表；
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select
admin.user_id,
1 * 1000 * 0.1 * 0.25 as soc_score
from
`gugux-activity`.club_admin admin
group by
admin.user_id) as t1 on
t1.user_id = usgs.user_id set
usgs.soc_score = t1.soc_score + usgs.soc_score
where
t1.user_id = usgs.user_id;
2.更换头像 权重：5% 分值：400 统计规则：完成头像更换，仅限第一次
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select
id as user_id,
1 * 400 * 0.05 * 0.25 as soc_score
from
`gugux-user`.user_base
where
icon != 'https://media.caigetuxun.com/imp/user_def.png') as t1 on
t1.user_id = usgs.user_id set
usgs.soc_score = t1.soc_score + usgs.soc_score
where
t1.user_id = usgs.user_id;
3.填写城市 权重：5% 分值：400 统计规则：城市填写，仅限第一次
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select
base.id as user_id,
1 * 400 * 0.05 * 0.25 as soc_score
from
`gugux-user`.user_base base
where
base.city is not null) as t1 on
t1.user_id = usgs.user_id set
usgs.soc_score = t1.soc_score + usgs.soc_score
where
t1.user_id = usgs.user_id;
4.性别选择 权重：5% 分值：400 统计规则：user_base表sex字段不为0 且不为null
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select
ub.id as user_id,
1 * 400 * 0.05 * 0.25 as soc_score
from
`gugux-user`.user_base ub
where
ub.sex <> 0
and ub.sex is not null) as t1 on
t1.user_id = usgs.user_id set
usgs.soc_score = t1.soc_score + usgs.soc_score
where
t1.user_id = usgs.user_id;
5.出生信息 权重：5% 分值：400 统计规则：user_base 表中 birth 字段不为空
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select
base.id as user_id,
1 * 400 * 0.05 * 0.25 as soc_score
from
`gugux-user`.user_base base
where
base.birth is not null) as t1 on
t1.user_id = usgs.user_id set
usgs.soc_score = t1.soc_score + usgs.soc_score
where
t1.user_id = usgs.user_id;
6.每成功添加一个好友 权重：15% 分值：30 统计规则：每成功添加一个好友，每天最多计10次 本次统计针对历史数据
>>> 不做统计
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select
uf.user_id,
count(uf.friend_id) * 30 * 0.15 * 0.25 as soc_score
from
`gugux-user`.user_friend uf
group by
uf.user_id) as t1 on
t1.user_id = usgs.user_id set
usgs.soc_score = t1.soc_score + usgs.soc_score
where
t1.user_id = usgs.user_id;
7.每加入一个群 权重：5% 分值：80 统计规则：每加入一个群，，每天最多计10次
>>> 不做统计
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select
cgu.user_id,
count(cgu.group_id) * 80 * 0.05 * 0.25 as soc_score
from
`gugux-im`.chat_group_user cgu
where
cgu.state = 1
group by
cgu.user_id ) as t1 on
t1.user_id = usgs.user_id set
usgs.soc_score = t1.soc_score + usgs.soc_score
where
t1.user_id = usgs.user_id;
8.每新增一个粉丝 权重：10% 分值：50 统计规则：每新增一个粉丝，每天最多计100次
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select
uf.user_id,
JSON_LENGTH(uf.fans) * 50 * 0.1 * 0.25 as soc_score
from
`gugux-user`.user_follow uf
where
uf.fans is not null) as t1 on
t1.user_id = usgs.user_id set
usgs.soc_score = t1.soc_score + usgs.soc_score
where
t1.user_id = usgs.user_id;
9.每新增一个关注 权重：10% 分值：50 统计规则：每新增一个粉丝，每天最多计10次
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select
uf.user_id,
JSON_LENGTH(uf.follows) * 50 * 0.1 * 0.25 as soc_score
from
`gugux-user`.user_follow uf
where
uf.follows is not null) as t1 on
t1.user_id = usgs.user_id set
usgs.soc_score = t1.soc_score + usgs.soc_score
where
t1.user_id = usgs.user_id;
---功能板块--- 功能权重：35%
1. 每绑定一辆车 权重：25% 分值：1000 统计规则：统计截止目前user_bind表中用户成功绑定的车辆数 “多辆车，按照一辆车处理”。
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select
ubc.user_id,
1 * 1000 * 0.25 * 0.35 as fun_score
from
`gugux-user`.user_bind ubc
where
ubc.state = '1'
group by
ubc.user_id) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
2.每成功免费开通主动道路救援 权重：10% 分值：800 统计规则：统计截止目前safety_guard_road表中用户开通的主动道路救援次数 过滤掉 ‘user_id=0’ 解绑车的情况
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select
sgr.user_id,
count(id) * 800 * 0.1 * 0.35 as fun_score
from
`gugux-location`.safety_guard_road sgr
where
sgr.user_id <> 0
group by
sgr.user_id) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
3.开通安全守护（必须要填写紧急联系人） 权重：10% 分值：400 统计规则：经过排查 线上无没填写紧急联系人就开通安全守护的情况 故直接统计safety_guard表数据
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select
guard.user_id,
1 * 400 * 0.1 * 0.35 as fun_score
from
`gugux-location`.safety_guard guard
where
guard.state = 1
group by
guard.user_id) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
--- 内容板块--- 功能权重：20%
1. 每发布一个内容
update
`gugux-user-system`.user_system_grade_score usgs
left join (
SELECT
base.id AS userId,
count(opus.id) * 150*0.5*0.15 as fun_score
FROM
`gugux-opus`.opus opus
INNER JOIN `gugux-user`.user_base base ON base.id = opus.created_by
WHERE
opus.state =3
group by base.id) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
2. 每浏览一个作品
update
`gugux-user-system`.user_system_grade_score usgs
left join (
SELECT
browse.mark_id AS userId,
count(browse.opus_id) * 2* 0.2 * 0.15 as fun_score
FROM
`gugux-opus`.opus_user_browse browse
GROUP BY
userId) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
3. 每点赞一个作品
update
`gugux-user-system`.user_system_grade_score usgs
left join (
SELECT
count( userLike.opus_id ) *6 * 0.2 *0.15 AS fun_score,
userLike.user_id
FROM
`gugux-opus`.opus_user_like userLike
GROUP BY
userLike.user_id) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
4. 评论一个作品
update
`gugux-user-system`.user_system_grade_score usgs
left join (
SELECT count(COMMENT.opus_id) * 8 * 0.2 * 0.15 as fun_score,
COMMENT.user_id
FROM
`gugux-opus`.COMMENT
GROUP BY
COMMENT.user_id) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
5. 每收藏一个作品
update
`gugux-user-system`.user_system_grade_score usgs
left join (
SELECT
collect.user_id,
count(collect.opus_id) * 6*0.2*0.15 as fun_score
FROM
`gugux-opus`.opus_user_collect collect
GROUP BY
collect.user_id) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
6. 被评论
update
`gugux-user-system`.user_system_grade_score usgs
left join (
SELECT
opus.created_by as user_id, IFNULL(t1.num,0) * 10 * 0.3*0.15 as fun_score
FROM
`gugux-opus`.opus
LEFT JOIN (
SELECT COMMENT
.opus_id,
count( opus.created_by ) AS num
FROM
`gugux-opus`.comment
COMMENT INNER JOIN `gugux-opus`.opus ON opus.id = COMMENT.opus_id
GROUP BY
COMMENT.opus_id) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
7. 被收藏
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select opus.created_by as user_id, IFNULL(t1.num,0) * 8 * 0.3*0.15 as fun_score from `gugux-opus`.opus left join (SELECT
count( opus.created_by ) num,
collect.opus_id
FROM
`gugux-opus`.opus_user_collect collect
INNER JOIN `gugux-opus`.opus ON opus.id = collect.opus_id
GROUP BY
collect.opus_id) as t1 on t1.opus_id = opus.id) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
8. 被点赞
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select opus.created_by as user_id, IFNULL(t1.num,0)*8*0.3*0.15 as fun_score from `gugux-opus`.opus
left join (
SELECT
userLike.opus_id,
count(opus.created_by) as num
FROM
`gugux-opus`.opus_user_like userLike
inner join `gugux-opus`.opus on opus.id = userLike.opus_id
group by userLike.opus_id) t1 on t1.opus_id = opus.id) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
--- 社交板块--- 功能权重：15%
1. 每加入一个群
update
`gugux-user-system`.user_system_grade_score usgs
left join (
SELECT
USER.user_id,
count(USER.group_id) * 80*0.05*0.25 as fun_score
FROM
`gugux-im`.chat_group_user USER
JOIN (
SELECT
user_id,
DATE(create_time) AS time,
COUNT(*) AS num
FROM
`gugux-im`.chat_group_user
GROUP BY
user_id, DATE(create_time)
) AS total ON USER.user_id = total.user_id AND DATE(USER.create_time) = total.time
GROUP BY
USER.user_id, DATE(USER.create_time)
HAVING
count(USER.group_id) <= 10) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
2. 每成功下单一笔
update
`gugux-user-system`.user_system_grade_score usgs
left join (
select user_id, count(id)*400*0.10*0.25 as fun_score from `gugux-mall`.mall_order where state =50 GROUP BY user_id) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
3. 每消费满200金额
update
`gugux-user-system`.user_system_grade_score usgs
left join (
SELECT
user_id,
count(id) *1000*0.20*0.25 AS fun_score
FROM
`gugux-mall`.mall_order
WHERE
state = 50 and actual_pay > 50
GROUP BY
user_id) as t1 on
t1.user_id = usgs.user_id set
usgs.fun_score = t1.fun_score + usgs.fun_score
where
t1.user_id = usgs.user_id;
select COMMENT.opus_id, COMMENT.user_id
from `gugux-opus`.COMMENT
GROUP BY COMMENT.opus_id,COMMENT.user_id
