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, 7, []int{5}, []int{7, 10, 15, 20}); err == nil && len(res) > 0 {
		returnData = append(returnData, res...)
	}
	if res, err := ua.ArticleVisit(ctx, runTime, 3, []int{}, []int{5}); 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, runTime time.Time, dayNum int, gt, gte []int) ([]*AnalysisRes, error) {
	var (
		staticGtMap  = map[int]map[BaseUserId]bool{}
		staticGteMap = map[int]map[BaseUserId]bool{}

		result = []*AnalysisRes{}
	)
	for _, num := range gt {
		staticGtMap[num] = map[BaseUserId]bool{}
	}
	for _, num := range gte {
		staticGteMap[num] = 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 `, runTime.AddDate(0, 0, -dayNum).Format(time.DateTime))
	if err != nil {
		return nil, err
	}
	for _, m := range rPc.List() {
		var (
			baseUserId = BaseUserId(gconv.Int64(m["baseUserId"]))
			count      = gconv.Int(m["articleCount"])
		)
		if baseUserId <= 0 {
			continue
		}
		for num, v := range staticGtMap {
			if count > num {
				v[baseUserId] = true
			}
		}

		for num, v := range staticGteMap {
			if count >= num {
				v[baseUserId] = true
			}
		}
	}
	for i, i2 := range staticGtMap {
		result = append(result, &AnalysisRes{
			Name:        fmt.Sprintf("%d天内访问详情页次数>%d", dayNum, i),
			Code:        fmt.Sprintf("%dday_article_gt_%d", dayNum, i),
			Data:        i2,
			SaveOldData: false,
		})
	}
	for i, i2 := range staticGteMap {
		result = append(result, &AnalysisRes{
			Name:        fmt.Sprintf("%d天内访问详情页次数>=%d", dayNum, i),
			Code:        fmt.Sprintf("%dday_article_gte_%d", dayNum, i),
			Data:        i2,
			SaveOldData: false,
		})
	}
	return result, 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
}