工作中常用数据统计分析手段

常用sql语句

mysql查询语句

作用1：查询普通的数据
order by 的作用是根据某个字段进行升序排序，如果在后面加上desc则为降序排序

select * from user order by id;

mysql分组查询

作用2：根据某个字段进行分组查询
通常group by 一般搭配着count,sum()等统计函数一起使用

select count(*) from eb_user group by gender;

mysql更新插入

作用3：更新插入数据
更新user表中id为1的用户设置name字段的值为libai，可同时设置多个字段的值

update user set name='libai' where id = 1;
update user set name='libai',gender=1 where id = 1;

mysql时间戳的操作

作用4：统计时间戳数据
根据用户的注册时间统计目标每月的新增用户量
from_unixtime可以将时间戳转化为时间

select FROM_UNIXTIME(add_time,'%Y-%m'),count(*) from eb_user GROUP BY From_unixtime(add_time,'%Y-%m');

mysql时间的操作

作用5：统计时间字符串数据
date_format能操作时间字符串，如：2021-10-28 14:07:16
常用时间格式化字符串：‘%Y-%m-%d %H:%i:%s’

select DATE_FORMAT(time,"%Y_%m"),sum(money) from cash where status = 1 GROUP BY DATE_FORMAT(time,"%Y年%m月") 

导出某张表的注释信息

作用6：导出某张表的注释信息
在工作中常常需要分析用户表并生成excel文件，表头需要手动复制粘贴很麻烦，使用该语句可以直接导出中文注释等相关信息

select column_name,column_type,data_type,character_maximum_length,is_nullable,column_default,column_comment from information_schema.columns where table_schema=数据库名 and table_name = 表名;

导出所有表名与该表名注释信息

作用7：导出所有表名与该表名注释信息

select table_name,table_comment from information_schema.tables;

python脚本常用算法思路

在统计传销数据常用到：统计该组织下级最大层级，下级人数。计算层级和下级人数的时候是可以一起计算的。
在此总结了一些算法思路。
1.计算层级，下级人数
工作中常见目标的数据库提供的等级信息有：个人用户id，上级id
如果使用遍历循环这样的方式计算，时间复杂度很大，而一个传销组织用户量在我现在的工作岗位经常是8W~20W用户量的，这样脚本遍历计算需要一整天时间，如果出错还需要重新运行。
所以在这里进行了优化并总结：

（1）.将目标数据的用户数据进行全部查询返回到程序中并保存在变量里面，如需查询某个用户的上级或者查询以某个用户为上级的所有用户，就可以直接在程序变量里面直接找。那么我们如果保存在列表里面，寻找某个用户的上级的时候就需要遍历列表，这个计算量其实还不如通过sql语句查询。

（2）.保存数据的变量用字典，因为在字典中找数据传入相对应的键值即可立刻找到，这就是字典的优势。这里保存用的字典结构为：self.top_datas = {“上级id”:[下级id1,下级id2]}，这样就可以输入某一个id即可立刻查询出该id的所有下级id，至于怎么保存成self.top_datas格式可以参考以下代码

self.top_datas = {}
for user in users:	# 遍历所有用户信息
	uid = user[0]	# 用户id
	father_id = user[1]	# 该用户的上级id
	try:
		self.top_datas[father_id].append(uid)
	except Exception as e:
		self.top_datas[father_id] = [uid]
# 此时即可保存成这样的数据啦{"上级id":[下级id1,下级id2]}

（3）.既然确定了是用字典保存，剩下就是算法，以下用一个图简略表示以下

输入一个用户id为11，寻找该用户的最大下级层级，下级人数。
第一步：找出上级为11的所有用户：22，33
第二步：找出上级包含为22,33的所有用户：44
第三步：找出上级为44的所有用户：55
第四步：找出上级为55的所有用户：无
由此可以看出，上一步的结果可以作为下一步的输入，当返回结果为无的时候，即到达了最低层。可以说递归即可完成，函数退出的接口即为返回值为无的时刻。

def get_low(self,uid,ceng,num):	# 找出目标下级
		user_ids = []
		flag = 1
		for i in uid:	# 遍历目标
			try:
				datas = self.top_datas[str(i)]
			except Exception as e:	# 没有以这个用户为上级的
				continue
			if datas == []:	# 没有以这个用户为上级的
				continue
			if flag:
				ceng += 1	# 统计层级
				flag = 0
			num += len(datas)	# 统计团队人数
		return user_ids,ceng,num

	def get_ceng(self):	# 统计层数
		uid = 11
		print("正在计算%s用户的信息" % uid)
		targets = [uid]
		ceng = 1	# 层数
		num = 0	# 下级人数
		while targets:
			targets,ceng,num = self.get_low(targets,ceng,num)
		
		print("%s用户最大层级为：%s，下级人数为：%s" % (uid,ceng,num))

2.统计每个用户总充值，总提现，根据充值类型进行分类计算
这个需求很常见，通常目标数据库都会有几十万数据，公司要求的也是快，快速统计完成的原理跟计算层级是一样的，用数据结构字典
（1）用一个变量保存所有充值或者提现数据，如：datas = {“uid”:[[],[],[]]}，一个用户uid里面可对应多个列表，因为一个用户不止一个充值或者提现数据。
（2）将用户数据保存进datas中

for user in users:
	uid = user[0]	# 用户id
	money = user[1]	# 充值或提现金额
	type = user[2]	# 充值类型或提现类型
	# 传销案子中充值类型和提现类型常见都有虚拟货币这样子，所以需要统计每个币种的总金额
	try:
		datas[uid].append([type,money])
	except Exception as e:
		datas[uid] = [[type,money]]

（3）遍历用户，即可找出每个用户相对应的充值订单信息，遍历过程取个变量保存，每次遍历时相加即可
（4）保存文件

至于为什么想把这个思路保存下来，是因为上一套算法找每个用户的订单数据：select * from recharge where uid = 1 and status = 1;
这一条语句只能找到某个用户的充值数据，对于几十万个用户就对应着几十万个请求获取数据，就会特别特别慢。而总结的算法只需要把recharge数据进行一次查询：select * from recharge;然后在本地运算，速度飞快。