埋点统计

2024年04月19日

柏拉文

越努力，越幸运

一、统计每个用户触发每个事件的频率

---

select 
	operator,
	event_id,
	count(event_id) as count
from `base_point`
group by 
    operator,
    event_id;

二、统计每个事件触发频率最高的用户

---

select 
	joinTable.event_id,
	joinTable.operator,
	maxTable.max 
from 
	(
		select 
			event_id,
			max(countTable.count) as max
	 	from 
			(
				select 
					event_id,
					operator,
					count(event_id) as count 
				from base_point 
				group by 
					 event_id,
					 operator
			) as countTable 
	 	group by 
			 event_id
	) as maxTable
inner join 
		(
			select 
				event_id,
				operator,
				count(event_id) as count 
		 	from base_point 
		 	group by 
				 event_id,
				 operator
		) as joinTable 
		  on joinTable.event_id = maxTable.event_id
where 
	joinTable.count = maxTable.max;