热门服务号计算

获取服务号圈包

kyp_prod_df = spark.sql("""
select item_id from tableKYP where dt='20221212' and item_id!='NULL' and pool_code='services'
""")

日期与当日相隔天数函数

import numpy as np
import pyspark.sql.functions as f
import pyspark.sql.types as T
import datetime
@f.udf(T.IntegerType())
def diff_days(updated_date):
	d1 = datetime.datetime.now()
	d2 = datetime.datetime.strptime(updated_date[:8],'%Y%m%d')
	return (d1-d2).days

衰减函数，用于资讯权重计算，发布的时间越久远，权重降低

@f.udf(T.DoubleType())
def delay(n):
	return float(np.power(0.9,(n/2)))

服务号发布的资讯得分计算

ser_rel_df = spark.sql(f"""select tt.service_id,tt.id,substr(tt.release_date,1,8) release_date from (
select t.id,regexp_replace(t.release_date,'-','') release_date from tableA t inner join (
select service_id,info_id from tableB where dt='20221212' and nvl(service_id,'') <> '' and info_id is not null
) t1 
on t.id=t1.info_id where t.dt='20221212') tt
""")

#计算服务号发布的资讯得分，发布时间越久，得分越低
ser_cnt_df = ser_rel_df.withColumn('release_days',diff_days('release_date')).withColumn('release_deay',delay(col('release_days')))

#计算服务号发布的资讯总得分
ser_cnt_df = ser_cnt_df.groupby('service_id').agg(f.sum('release_decay').alias('release_news_cnt'),f.max('release_date').alias('release_date'))
df1 = ser_cnt_df.selectExpr('service_id as item_id','release_news_cnt','release_date')

服务号关注的用户数得分

df2 = spark.sql(f"""
select cust_no ,service_id
from table group by cust_no,service_id
""").groupby('service_id').agg(f.count('*').alias('attent_users'))
df2 = df2.selectExpr('service_id as item_id','attent_users')

服务号总得分

#通过服务号发布的资讯数和关注人数，计算服务号总得分
res_df = df1.join(df2,on='item_id',how='left').fillna(0.0,'attent_users').withColumn('score',col('attent_users')*f.log10(col('release_news_cnt')+10))
#服务号按得分排序
from pyspark.sql.window import Window
res_df = res_df.join(serv_df,on='item_id',how='inner'). \
withColumn('rk',f.row_number().over(Window.partitionBy(f.lit('1')). \
orderBy(f.desc("score")))).filter('rk<=30').withColumn('weight',1/col('rk'))