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})
		}
	}
	//*特殊1-90天活跃
	if activeRes, err := ua.ActiveUserLoad(ctx, runTime.AddDate(0, 0, -90), "", runTime.AddDate(0, 0, -1)); err != nil || len(activeRes) > 0 {
		returnData = append(returnData, &AnalysisRes{Name: fmt.Sprintf("除了昨天90天活跃用户"), Code: fmt.Sprintf("active_1_90"), Data: activeRes})
	}

	//标讯详情页访问
	if res, err := ua.ArticleVisit(ctx, runTime.AddDate(0, 0, -7)); err == nil && len(res) > 0 {
		returnData = append(returnData, res...)
	}
	if res, err := ua.SearchVisit(ctx, runTime.AddDate(0, 0, -7)); err == nil && len(res) > 0 {
		returnData = append(returnData, res...)
	}
	//标讯详情页访问
	if res, err := ua.ArticleVisit1Day(ctx, runTime.AddDate(0, 0, -1)); err == nil && len(res) > 0 {
		returnData = append(returnData, res...)
	}
	//标讯详情页访问
	if res, err := ua.ActiveDayNum(ctx, runTime, 7, 3); err == nil && len(res) > 0 {
		returnData = append(returnData, 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, entTime ...time.Time) (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 len(entTime) > 0 {
		execSql += " AND create_time < ?"
		values = append(values, entTime[0].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
}

func (ua *UserAnalysis) ActiveDayNum(ctx context.Context, runTime time.Time, day, actDayNum int) ([]*AnalysisRes, error) {
	rPc, err := g.DB().Query(ctx, `SELECT baseUserId,COUNT(DISTINCT toDate(create_time)) AS acDayNum
			FROM 
				pub_tags.user_log_byHour
			WHERE 
				create_time >= ?
			GROUP BY 
				baseUserId
			having   acDayNum >=?`, runTime.AddDate(0, 0, -day).Format(time.DateTime), actDayNum)
	if err != nil {
		g.Log().Errorf(ctx, "UserAnalysis.ActiveDayNum")
	}
	var m1 = map[BaseUserId]bool{}
	for _, m := range rPc.List() {
		var baseUserId = BaseUserId(gconv.Int64(m["baseUserId"]))
		if baseUserId > 0 {
			m1[baseUserId] = true
		}
	}
	return []*AnalysisRes{
		{fmt.Sprintf("近%d天活跃天数≥%d次", actDayNum, day), fmt.Sprintf("activeDay_gte%dday_%dday", actDayNum, day), m1, false},
	}, 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{}
		d4, d5     = 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
		}
		if count >= 15 {
			d4[baseUserId] = true
		}
		if count >= 20 {
			d5[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},
		{"7天内访问详情页次数>=15", "7day_article_gte_15", d4, false},
		{"7天内访问详情页次数>=20", "7day_article_gte_20", d5, false},
	}, nil
}

func (ua *UserAnalysis) SearchVisit(ctx context.Context, start time.Time) ([]*AnalysisRes, error) {
	var (
		d1 = map[BaseUserId]bool{}
	)

	rPc, err := g.DB().Query(ctx, `SELECT sum(search) as searchCount,baseUserId FROM pub_tags.user_log_byHour WHERE create_time > ? GROUP BY  baseUserId HAVING searchCount >=3 `, start.Format(time.DateTime))
	if err != nil {
		return nil, err
	}
	for _, m := range rPc.List() {
		var (
			baseUserId = BaseUserId(gconv.Int64(m["baseUserId"]))
		)
		if baseUserId <= 0 {
			continue
		}
		d1[baseUserId] = true
	}
	return []*AnalysisRes{
		{"近7天搜索次数≥3次", "search_gte3_7day", d1, false},
	}, nil
}
func (ua *UserAnalysis) ArticleVisit1Day(ctx context.Context, start time.Time) ([]*AnalysisRes, error) {
	var (
		d0 = 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 >=10 `, 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
		}
		d0[baseUserId] = true
	}
	return []*AnalysisRes{
		{"1天内访问详情页次数>=10", "1day_article_gte_10", d0, 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
}