workTasks/userSign/userAnalysis/analysisLogsAnalysis.go

package userAnalysis

import (
	"context"
	"fmt"
	"github.com/gogf/gf/v2/frame/g"
	"github.com/gogf/gf/v2/util/gconv"
	"time"
)

func (ua *UserAnalysis) GetAnalysisLogRes(ctx context.Context, runTime time.Time) ([]*AnalysisRes, error) {
	var (
		sourceArr    = []string{"pc", "wx", "app"}
		unActiveTime = []int{1, 30, 90}
		returnData   = []*AnalysisRes{}
	)
	//活跃用户分析
	for _, day := range unActiveTime {
		if day == 30 { //30天活跃用户各端
			for _, source := range sourceArr {
				if res, err := ua.ActiveUserLoad(ctx, runTime.AddDate(0, 0, -day), source); err == nil && len(res) > 0 {
					returnData = append(returnData, &AnalysisRes{Name: fmt.Sprintf("30天%s活跃用户", source), Code: fmt.Sprintf("active_%s_%d", source, 30), Data: res})
				}
			}
		}
		if res, err := ua.ActiveUserLoad(ctx, runTime.AddDate(0, 0, -day), ""); err == nil && len(res) > 0 {
			returnData = append(returnData, &AnalysisRes{Name: fmt.Sprintf("%d天活跃用户", day), Code: fmt.Sprintf("active_%d", day), Data: res})
		}
	}
	//标讯详情页访问
	if res, err := ua.ArticleVisit(ctx, runTime.AddDate(0, 0, -7)); err == nil && len(res) > 0 {
		returnData = append(returnData, res...)
		//returnData = append(returnData, &AnalysisRes{Name: fmt.Sprintf("%d天内访问详情页次数%s", param.Day, param.Compare), Code: fmt.Sprintf("%dday_article_gt_%d", param.Day, param.Compare), Data: res})
	}

	// 已访问过
	if res, err := ua.Visited(ctx, runTime.AddDate(0, 0, -7)); err == nil && len(res) > 0 {
		returnData = append(returnData, &AnalysisRes{Name: "访问过剑鱼产品", Code: "visited", Data: res, SaveOldData: true})
	}
	return returnData, nil
}

// ActiveUserLoad 活跃用户
func (ua *UserAnalysis) ActiveUserLoad(ctx context.Context, start time.Time, source string) (map[BaseUserId]bool, error) {
	var (
		execSql = "SELECT DISTINCT(baseUserId) as baseUserId FROM user_log_byHour WHERE create_time > ? "
		values  = []interface{}{start.Format(time.DateTime)}
	)

	if source == "wx" || source == "pc" || source == "app" {
		execSql += " AND s_source =? "
		values = append(values, source)
	}
	rPc, err := g.DB().Query(ctx, execSql, values...)
	if err != nil {
		return nil, err
	}
	activeUser := map[BaseUserId]bool{}
	for _, m := range rPc.List() {
		// 插入pc30天活跃用户
		activeUser[BaseUserId(gconv.Int64(m["baseUserId"]))] = true
	}
	return activeUser, nil
}

// UnActiveUserLoad 未活跃用户
func (ua *UserAnalysis) UnActiveUserLoad(ctx context.Context, start time.Time) (map[BaseUserId]bool, error) {
	rPc, err := g.DB().Query(ctx, `SELECT DISTINCT(dmp.baseUserId) as baseUserId1 FROM dwd_mgo_position dmp
	left join (	SELECT DISTINCT(baseUserId) as baseUserId
	FROM user_log_byHour ulbh
	WHERE create_time > ? ) ulog  on  dmp.baseUserId =ulog.baseUserId where ulog.baseUserId=0`, start.Format(time.DateTime))
	if err != nil {
		return nil, err
	}
	unActiveUser := map[BaseUserId]bool{}
	for _, m := range rPc.List() {
		// 插入pc30天活跃用户
		unActiveUser[BaseUserId(gconv.Int64(m["baseUserId1"]))] = true
	}
	return unActiveUser, nil
}

// ArticleVisit 指定时间内访问详情页数量大于num次的用户
func (ua *UserAnalysis) ArticleVisit(ctx context.Context, start time.Time) ([]*AnalysisRes, error) {
	var (
		d1, d2, d3 = map[BaseUserId]bool{}, map[BaseUserId]bool{}, map[BaseUserId]bool{}
	)

	rPc, err := g.DB().Query(ctx, `SELECT sum(article) as articleCount,baseUserId FROM pub_tags.user_log_byHour WHERE create_time > ? GROUP BY  baseUserId HAVING articleCount >5 `, start.Format(time.DateTime))
	if err != nil {
		return nil, err
	}
	for _, m := range rPc.List() {
		var (
			baseUserId = BaseUserId(gconv.Int64(m["baseUserId"]))
			count      = gconv.Int64(m["articleCount"])
		)
		if baseUserId <= 0 {
			continue
		}
		if count > 5 {
			d1[baseUserId] = true
		}
		if count >= 7 {
			d2[baseUserId] = true
		}
		if count >= 10 {
			d3[baseUserId] = true
		}
	}
	return []*AnalysisRes{
		{"7天内访问详情页次数>5", "7day_article_gt_5", d1, false},
		{"7天内访问详情页次数>=7", "7day_article_gte_7", d2, false},
		{"7天内访问详情页次数>=10", "7day_article_gte_10", d3, false},
	}, nil
}

// Visited 指定日期内访问过剑鱼
func (ua *UserAnalysis) Visited(ctx context.Context, start time.Time) (map[BaseUserId]bool, error) {
	rPc, err := g.DB().Query(ctx, `SELECT sum(article)+sum(search)+sum(portrait)+sum(other) as allCount,baseUserId FROM pub_tags.user_log_byHour WHERE create_time >? GROUP BY  baseUserId`, start.Format(time.DateTime))
	if err != nil {
		return nil, err
	}
	visitedUser := map[BaseUserId]bool{}
	for _, m := range rPc.List() {
		// 插入pc30天活跃用户
		visitedUser[BaseUserId(gconv.Int64(m["baseUserId"]))] = true
	}
	return visitedUser, nil
}