【数据分析实战经验】入职5-12周 技术小结

入职第十二周了，趁今天想把入职到现在学到的技术上的一些点做个整理和总结，不能再拖延了小伙！！！！！！

一、EXCEL

1.宏

宏：简单来说，Excel 宏指的是，使用 Excel 内置的编程语言 VBA (Visual Basic for Applications）写的，能在 Excel 环境里运行的一系列操作指令。我们在 Excel里手动进行的几乎所有操作，宏（VBA）也都可以操作。

主要场景：用来解决大量多表重复操作
主要步骤：开启宏-设置宏的安全性-判断使不使用相对引用-选择起始位置或区域-录制宏-设置快捷键-选择待使用的位置或区域-使用宏
怎么判断使不使用相对引用：参考这个文章。https://www.jianshu.com/p/d993f9ff13f3
宏与透视表的配合使用：实战过程中发现，录制宏，不能把产出透视表这一步起录制下来，系统一直会跳转出错。
百度之后原因在于：如果你每运行一次都重新开启这个档案的话是可以的（产生的透视表不能保存，保存后也后出错），但是不能连续运行，因为它产生的透视表是一样名字的，所以会产生错误。
解决办法：一般做数据透视表都是先做好透视表，然后用宏去更新表格数据，以达到更新的目的透视表
2.趋势线

趋势线用来反映数据的属性走势。

主要有6类：

移动平均:不具备预测功能，只是将周期平移，例如将周期设置为2，则前两端的数据点平均值是趋势线中的第一个点，第2和第3个数据点的平均值是趋势线第2个点。
线性:主要用于线条比较平稳，数据点构成类似一条直线，通常表示事物以恒定比例增减。比如:人流量和入店率关系分析
对数:主要用于一开始增减的趋势比较快，又迅速趋于平缓。比如:季节性产品销量和时段关系
多项式:主要用于波动较大的图形，阶数通常由波动的次数或曲线中峰和谷的个数确定。二阶一般仅一个峰或谷，三阶1-2个。比如:股票价格分析
乘幂:主要用于以特定的速度持续增长或减少，幅度并不特别大的分析。比如:火车加速度和时间对比
指数:主要用于持续增长或减少，且增减速度越来越大。比如:成长型公司年度销售额分析

还有,在添加趋势线时,勾选添加趋势线-选项,下的显示R平方值来衡量趋势线的拟和程度,R平方值为0-1,越接近1就表示趋势线的误差越小!当然就可以选择该方案的趋势线!

---

二、postgresql

1.建临时表

  create temp table 自定义表名 as 
 	(select *,xxx from table A
 	......)

主要场景：当源表没法直接满足业务需求，且后续很多步骤会重复使用到自己根据源表处理后的二次表，这时候需要用到这个语句。
注意点：当数据库重新连接及sql窗口重新打开时，要重新加载一次。

2.删去临时表

drop table xxx

主要场景：1、自定义的表名需要复用。2、每次退出前

3、求平均付费间隔

已知玩家充值付费表A，主要字段，role_id,vip,order_date,

解法：
第一步：按role_id 原表自连接，left join ，并且连接条件设置a.付费日期<b.付费日期，
第二步：按所需字段进行聚合，vip、
role_id,a.order_date,min(b.order_date)-a.order_date，目的是求出每一个玩家付费日期与最近一起后续有付费的日期间的差距。
第三步：再聚合一次，求差距的avg。

select m.vip_lvv as vip等级,avg(gapp) as 平均购买间隔 from 
	(select c.vip_lv as vip_lvv,c.roleid as roleidd,avg(gap) as gapp from
		(select a.vip as vip_lv,a.role_id as roleid ,a.order_date,(min(b.order_date)-a.order_date)as gap from gos_pay_analyse_dws_6494 as a
			left join gos_pay_analyse_dws_6494 as b
			on a.role_id =b.role_id  and a.order_date <b.order_date 
		where (a.order_date between '2018-7-01' and '2018-9-30' )and(b.order_date between '2018-7-01' and '2018-9-30' )
		group by a.vip,a.role_id ,a.order_date)c
	group by c.vip_lv,c.roleid)m
group by m.vip_lvv;

4、贴标签 case when else end
需求：
根据玩家不同注册时长，进行玩家划分。

select t.register,count(distinct role_id),sum(num),sum(amount) from 
	(select role_id,vip,num,amount ,
	case 
	when (date('2020-09-30')-date(reg_log_time))<='30' then '距今1月内'
	when (date('2020-09-30')-date(reg_log_time))between'31'and '180' then '距今2-6月内'
	when  '180'<(date('2020-09-30')-date(reg_log_time)) then '距今7月上'
	else'' end as register from gos_pay_analyse_dws_6494
	where order_date between '2020-7-01' and '2020-9-30' )t
group by t.register ;

5、窗口函数
主要场景：涉及到聚合后进行累加，排名的情况

第一种：累加。
sum()over(partition by …order by …rows between unbounded preceding and current row)

select t.role_id,t.注册时间,t.充值时间,total ,
sum(total)over (partition by t.role_id order by t.充值时间 rows between unbounded preceding and current row) as 累计金额 ,abs (date(充值时间)-date(注册时间)) as 时间节点
from 
	(select role_id ,order_date  as 充值时间 ,min(reg_log_time) as 注册时间 ,sum(amount) as total from table_A
	group by role_id ,order_date )t;

注：其实累加也可以用left join 代替实现
取左边日期及以前的所有金额之和，就是累加的含义

select  x.role_id,x.vip,x.reg_date,x.order_date,sum(y.amount) from
(select role_id,vip,reg_date,order_date,sum(amount) from m
group by 1,2,3,4) x
left join (
select role_id,vip,reg_date,order_date,sum(amount) from m
group by 1,2,3,4)y
on x.role_id=y.role_id and x.order_date>=y.order_date
group by 1,2,3,4

第二种：排序
row_number()over(partition by …order by …rows between unbounded preceding and current row)

select t.vip,count(distinct t.role_id) from 
	(select *,
	row_number() over (partition by role_id order by order_time rows between unbounded preceding and current row) as a from  m1
	where amount >= 99.99)t
group by t.vip;

举个实际中比较复杂的实例

我们要得到玩家养成各个等级时候是距离注册多久的时间段。

注意：每个人的注册时间都不一样，且每个人生涯中会有多个等级，要逐个判断成为V1,V2,…的各个时间点，还有就是每个人的最终养成V段是不同的。
分析：先求每个玩家按时间顺序付费的订单金额，累计并用case when 进行逐次的判断，就是说每付完一笔判断一次是不是达到了某个V段，并且将此刻的时间定位出来，最终就是求出玩家最早到达某个V段的最快时间。
代码：

create temp table c  as
select role_id, vip, z.a,
is_vip_1, is_vip_2, is_vip_3，
min(z.order_date) as value1, (min(z.order_date)-min(z.reg_date)) as value2
from (
	select x.role_id, x.vip, x.reg_date, x.order_date,concat(extract(year from x.reg_date),extract(month from x.reg_date)) as a ,sum(y.amount) as s,
		case when sum(y.amount) >=4.99 then true else false end as is_vip_1,
		case when sum(y.amount) >=19.99 then true else false end as is_vip_2,
		case when sum(y.amount) >=49.99 then true else false end as is_vip_3,
	from (
		select role_id, vip, reg_date, order_date,sum(amount) as amount
		from m
		group by 1,2,3,4
	) x left join (
		select role_id, vip, reg_date, order_date,sum(amount) as amount
		from m
		group by 1,2,3,4
	) y on x.role_id=y.role_id and x.order_date >= y.order_date
	group by 1,2,3,4,5
) z
group by 1, 2, 3, 4, 5,6；

接着根据临时表进行筛选，满足V1时的最小平均间隔，V2.V3…，就可以得到想要的结果了。

6、时间类型的转换
以下面一个语句为例，extract(…from 列名)
可以是month，year，day，
其次有时候要提取年月的时候
可以直接用concat(a,b) 获取

concat(extract(year from x.reg_date),extract(month from x.reg_date))

7、比较join…on 后面加 and 和 where 的区别

1.where 是在两个表join完成后，再进行筛选过滤，结果会比较清晰正确。

2.然而 and 是在表连接前，过滤A或B表哪些记录符合and 后面带的条件，又因为会用到left join /right join。
如果左边表的某条记录不符合连接条件，那么它不进行连接，但是仍然留在结果集中（此时右边部分的连接结果为NULL）。on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
因此最终虽然结果都是对的，但 后面的表中会出现null值匹配的过滤条件，操作者不注意筛选会影响到判断。
建议过滤条件还是放在where 中来。
8、一些 少用or 和 in或not in
最近在操作BI时，意识到sql写简洁的重要性。
举个例子：我需要筛选出特定区服玩家中，那些只注册但是没有付费的玩家都是哪些。
下面这个是一个非常冗余的SQl
分别在充值表单中筛选了一次区域，又在注册表单筛选一次区域，逻辑很简单，但是bi跑不出来，对于千万甚至亿级别的表单，少用in /not in 这样的语句，用join 先把表连接起来代替

select logical_region_id,server_id,role_id from A
where (logical_region_id ='1020' and server_id >='1137') 
or (logical_region_id ='1022' and server_id >='10322')
or(logical_region_id='1023' and server_id>='20319')
and local_date BETWEEN '2020-1-3' and '2020-11-8'
and role_id not in (
	select DISTINCT role_id from B
	where (logical_region_id ='1020' and server_id >='1137') 
	or (logical_region_id ='1022' and server_id >='10322')
	or(logical_region_id='1023' and server_id>='20319')
	and order_time BETWEEN '2020-1-3' and '2020-11-8' )

改进之后，先连接两个表，再筛选1有的2没有的role_id，用到isnull,这也是之前我总结过的

这个图的实际案例。最终再筛选区域，整体下来在bi上比之前更有效的跑出结果。

select t1.logical_region_id,t1.server_id,t1.role_id,t1.local_date from A t1 
left join B t2
on t1.role_id = t2.role_id
where t2.role_id is null
and t1.local_date between to_date("2020-01-03") and to_date("2020-11-08") 
and ((t1.logical_region_id =1020 and t1.server_id >=1137) 
    or (t1.logical_region_id =1022 and t1.server_id >=10322) 
    or (t1.logical_region_id=1023 and t1.server_id>=20319))
group by 1,2,3,4

python

1. 正常只用到pandas包的常用开头

#-*-coding:utf-8-*-
import pandas as pd
import numpy as np
import datetime
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"

2. 时间类型转换
   第一种：遇到时间戳，去时分秒，只要年月日

df['sys_unlock_time'] = pd.to_datetime(df['sys_unlock_time'])#转换时间
a=pd.DatetimeIndex(df.sys_unlock_time).date#针对timestamp类型，去时分秒，保留日期即可
df.sys_unlock_time=pd.to_datetime(a) #转换时间

第二种：需要用到时间相减，但是又要保持数值模式，不是日期模式

a=pd.DatetimeIndex(df.reg_log_time).date
df.reg_log_time=pd.to_datetime(a)
df_V2['注册时长']=abs(df['order_date']-df['reg_log_time'])
df_V2['注册时长'].astype('timedelta64[D]')

如果不用astype再重新变换数据类型，时间之间相减就会成为下图，无法进行数值预算

3. 类似where 的筛选，用切片就可以完成

df_8 = df[(df['reg_date']>='2020-8-1')&(df['reg_date']<='2020-8-31')]

4. 结果集导出成文件

to_csv，to_excel都可以

df_9.to_csv(r'E:\wen\入职第七周\试炼\df_9.csv',index=False) #存档

5. 贴标签，类似case when

一般切片完注意重新把索引序号reset一下，
先建一个空列表a，通过循环进行每次判断，利用append函数将结果连接起来，最终把a赋值给一个新建的列，标签就这样贴好了

df_9=df_9.reset_index(drop=True)#重新排序索引值，此框可不看
list_1=[]
for i in range(len(df_9)):
    if df_9['battles_today'][i]==df_9['progress'][i]:
        list_1.append('正常')
    else:
        list_1.append('不正常')
df_9['判断']=list_1
df_9.head()

第二种：针对两列互相进行判断时用到两个循环
比如判断历史最高海域是不是大于最终所处的海域

new=[]
for i,j in zip(df['队伍历史最高海域'],df['活动结束队伍所处海域']):
    if i>j:
        new.append(1)
    else:
        new.append(0)

df['是否回去']=new
df.head()

例2：

list_time=[7,15,30,60,90,180,10000]

for j in list_time:
    diff=[]
    for i in range(len(df)):
        if df['chaju'][i]<j:
            diff.append(1)
        else:
            diff.append(0)
    df[j]=diff

6. pivot_table 类似 excel中的透视表
   nunique表示去重计数
   count计数
   mean平均值
   sun总和
   index表示行类别，columns表示列类别

df_9.pivot_table(index='logical_region_id',aggfunc='nunique',values='role_id')

下面表示的就是按大区，按时间段，对角色值求去重计数，将null用0填充好

df_8.pivot_table(index='logical_region_id',values='role_id',aggfunc='nunique',columns='时间段',fill_value=0)#平均解锁

7. 常用角色去重，为后续循环判断做铺垫
   下面表示的是对玩家的消费金额做累计动作，先找出一共有哪些玩家，再根据每个玩家的消费金额，进行累计。前提要先按玩家和消费时间排个序。

uni_roleid=df_V2['role_id'].unique().tolist()
uni_roleid
csum=[]
for i in uni_roleid:
    b=0
    a=df_V2[df_V2['role_id']==i]['amount'].tolist()
    for j in range(len(a)):
        b=b+a[j]
        csum.append(b)

进一步拓展，如果需要再判断平均付费间隔，实现代码如下：
首先定义每个玩家初始第一次是没有付费间隔的，默认为0，当消费次数大于1，且不是最后一次时，j+1 的时间减去j 的时间

timelist=[]
for i in uni_roleid:
    role_time=df_7[df_7['role_id']==i]['order_date'].tolist()
    timelist.append(0)
    if len(role_time)>1:        
        for j in range(len(role_time)):
            if j+1 < len(role_time):
                time_=role_time[j+1]-role_time[j]
                timelist.append(time_)  

9. 合并concat
   axis=0按纵向进行合并，

df_role=pd.concat([df4,df5,df6],axis=0)

10. merge，有点类似join
    因为左右表的玩家名和区域名，命名不同，但是表达意思相同，所以用到left_on,right_on

df3=pd.merge(df,df1,left_on=['RoleId','玩家历史到达最大海域'],right_on=['role_id','area_id'])

11. groupby 聚合函数

记得groupby 之后要告诉电脑针对哪一列操作什么函数

df3.groupby(by=['状态','玩家历史到达最大海域']).sum('self_attack')

12. 排序sort_values

df_V2=df_V2.sort_values(by=['role_id','order_date'])

13. 双循环判断数据属于哪一类哪一期

注意：切片出来要 .values[i] 进行数据判断
df1表示内容：

df2表示内容

最终代码

a=[]
b=[]
for i in range(len(df2)):
    for k in range(len(df1)):
        if (df2['order_date'].values[i]>=df1['起'].values[k]) and (df2['order_date'][i]<=df1['终'].values[k]):
            a.append(df1['类型'].values[k])
            b.append(df1['期数'].values[k])

14. 重命名列

df.rename(columns={'s':'累计金额',7:'7天',15:'15天',30:'30天',60:'60天',90:'90天',180:'180天',10000:'10000天'}, inplace = True)

15. 当循环的i遇到与字符串结合时，用str(i)

for j in list_data:
    m1=df[df[str(j)+'天']==1]
    m1.to_excel('E:\\wen\\历史情况表\\'+str(j)+'历史情况总表.xlsx',index=False)

保持渴求，不要沉寂