python与SQL学习比较

数据字段包含：自增id，订单时间ts，用户id，订单id，订单金额

查看全部数据，pandas中直接打印dataframe对象即可，此处是order_data。而在SQL中，需要执行的语句是select * from t_order;表示从t_order表中查询全部的数据，*号表示查询所有的字段

#python 
import pandas as pd
order_data = pd.read_csv('order.csv')
order_data #查看所有数据
order_data.head(10) #查看前10行

#MySQL
select * from t_order; #查看所有数据
select * from t_order limit 10; #查看前10行

如果只想查看前10行数据呢。pandas可以调用head(n)方法，n是行数。MySQL可以使用limit n，n同样表示行数

在pandas里可以使用中括号或者loc，iloc等多种方式进行列选择，可以选择一列或多列。loc方式可以直接写列名，iloc方式需要指定索引，即第几列。SQL里只需写相应的列名即可

#Python
order_data['orderid'] #查看指定的一列
order_data.loc[:,['orderid']] #查看指定的一列
order_data.iloc[:,[3]] #查看指定的一列
order_data[['orderid', 'amount']] #查看指定的几列
order_data.loc[:,['orderid', 'amount']] #查看指定的几列
order_data.iloc[:,[3, 4]] #查看指定的几列

#MySQL
select orderid from t_order; #查看指定的一列
select orderid, amount from t_order; #查看指定的几列

查看一共有多少人(去重过的)下过单。pandas里有unique方法，SQL里有distinct关键字。如下面图左侧代码所示。两种方式输出的结果都含有9个uid，并且知道是哪9个。如果仅仅想知道有多少个uid，不关注具体值的话，可以参考右边的SQL，pandas用nunique()方法实现，而SQL里就需要用到一个count聚合函数与distinct组合的方式，表示去重并计数。

#Python
order_data['uid'].unique() #去重
order_data['uid'].nunique() #去重计数

#MySQL
select distinct uid from t_order; #去重
select count(distinct uid) from t_order; #去重计数

4. 查询带条件的数据

1） 带1个条件

例如我们要查询uid为10003的所有记录。pandas需要使用布尔索引的方式，而SQL中需要使用where关键字。指定条件时，可以指定等值条件，也可以使用不等值条件，如大于小于等。但一定要注意数据类型。例如如果uid是字符串类型，就需要将10003加引号，这里是整数类型所以不用加。

#Python
order_data[order_data['uid']==10003]

#MySQL
select * from t_order where uid=10003;

2） 带多个条件

多个条件同时满足的情况：
在前一小结基础上，pandas需要使用&符号连接多个条件，每个条件需要加上小括号；SQL需要使用and关键字连接多个条件。例如我们查询uid为10003并且金额大于50的记录。

#Python
order_data[(order_data['uid']==10003) & (order_data['amount']>50)]

#MySQL
select * from t_order where uid=10003 and amount>50;

多个条件满足其中一个的情况：
与多个条件同时满足使用&相对应的，我们使用|符号表示一个条件满足的情况，而SQL中则用or关键字连接各个条件表示任意满足一个。

#Python
order_data[(order_data['uid']==10003) | (order_data['amount']>50)]

#MySQL
select * from t_order where uid=10003 or amount>50;

这里需要特别说明的是有一种情况是需要判断某字段是否为空值。pandas的空值用nan表示，其判断条件需要写成isna()，或者notna()。

#Python
#查找uid不为空的记录
order_data[order_data['uid'].notna()]
#查找uid为空的记录
order_data[order_data['uid'].isna()]

MySQL相应的判断语句需要写成 is null 或者is not null。

#MySQL
select * from t_order where uid is not null;
select * from t_order where uid is null;

使用groupby时，通常伴随着聚合操作，这时候需要用到聚合函数。前面提到的count是一种聚合函数，表示计数，除此外还有sum表示求和，max,min表示最大最小值等。pandas和SQL都支持聚合操作。例如我们求每个uid有多少订单量

#Python
order_data.groupby('uid')['orderid'].nunique() #分组计不重复的数（去重）
order_data.groupby('uid')['orderid'].count() #分组计数（包含重复的数）（不去重）
order_data.groupby('uid')['orderid'].size() #分组计数（包含重复的数）（不去重）

#MySQL
select uid, count(distinct orderid) from t_order group by uid; #去重
select uid, count(orderid) from t_order group by uid; #不去重

如果想要同时对不同的字段进行不同的聚合操作。例如目标变成：求每个uid的订单数量和订单总金额。

#Python
order_data.groupby('uid').agg({'order_id':np.size, 'amount':np.sum})

#MySQL
select uid, count(distinct orderid), sum(amount) from t_order group by uid;

更进一步的，我们可以对结果的数据集进行重新命名。pandas可以使用rename方法，MySQL可以使用as 关键字进行结果的重命名。

#Python
order_df=order_data.groupby('uid').agg({'order_id':np.size, 'amount':np.sum})
order_df.rename(columns={'order_id':'order_cnt', 'amount':'sum_amount'})

#MySQL
select uid, count(distinct orderid) as order_cnt, sum(amount) as sum_amount from t_order group by uid;

join相关的操作有inner join，left join，right join，full join，等。pandas中统一通过pd.merge方法，设置不同的参数即可实现不同的dataframe的连接。而SQL里就可以直接使用相应的关键字进行两个表的连接。为了演示，我们此处引入一个新的数据集，user.csv(对应数据库中的t_user表)。包含了用户的昵称，年龄信息。

1) left join
pandas的merge函数传入4个参数，第一个是连接的主表，第二个是连接从表，第三个连接的key值，第四个是连接的方式，how为left时表示是左连接。SQL操作时基本也是同样的逻辑，要指定主表，从表，连接方式和连接字段。此处我们使用user连接order并查询所有字段和所有记录。具体代码如下所示，由于我们的数据没有空值，所以体现不出左连接的特点。

#Python
user_data = pd.read_csv('user.csv')
pd.merge(user_data, order_data, on='uid', how='left')

#MySQL
select * from t_user a left join t_order b on a.uid=b.uid;

2) 其他连接方式

如果要实现inner join，outer join，right join，pandas中相应的how参数为inner（默认），outer，right。SQL也是同样直接使用对应的关键字即可。其中inner join 可以缩写为join。

#Python
pd.merge(user_data, order_data, on='uid', how='inner')

#MySQL
SELECT * FROM 
t_user a
inner join t_order b
on a.uid = b.uid;

union相关操作分为union和union all两种。二者通常用于将两份含有同样字段的数据纵向拼接起来的场景。但前者会进行去重。例如，我现在有一份order2的订单数据，包含的字段和order数据一致，想把两者合并到一个dataframe中。SQL场景下也是期望将order2表和order表合并输出。

#Python
order_union=pd.concat([order_data, order_data2])
order_union

#MySQL
select * from 
t_order
union all
select * from
t_order2;

以上是没有去重的情况，如果想要去重，SQL需要用union关键字。而pandas则需要加上去重操作。

#Python
order_union = pd.concat([order_data, order_data2]).drop_duplicates()

#MySQL
select * from 
t_order
union
select * from
t_order2;

在实际工作中经常需要按照某一列字段进行排序。pandas中的排序使用sort_values方法，SQL中的排序可以使用order_by关键字。我们用一个实例说明：按照每个uid的订单数从高到低排序。这是在前面聚合操作的基础上的进行的

#Python
order_data.groupby('uid')['orderid'].nunique().sort_values(ascending=False)

#MySQL
select uid, count(distinct orderid) from t_order
group by uid
order by count(distinct orderid) desc;

排序时，asc表示升序，desc表示降序，能看到两种方法都指定了排序方式，原因是默认是会按照升序排列。在此基础上，可以做到对多个字段的排序。pandas里，dataframe的多字段排序需要用by指定排序字段，SQL只要将多个字段依次写在order by之后即可。例如，输出uid，订单数，订单金额三列，并按照uid降序，订单金额升序排列。

#Python
order_df=order_data.groupby('uid').agg({'order_id':np.size, 'amount':np.sum})
order_df.rename(columns={'orderid':'order_cnt','amount':'sum_amount'}, inplace=True)
order_df.sort_values(by=['uid', 'sum_amount'], ascending=[False, True])

在pandas中可能有一些细节需要注意，比如我们将聚合结果先赋值，然后重命名，并指定了inplace=True替换原来的命名，最后才进行排序，这样写虽然有点绕，但整体思路比较清晰。

#MySQL
select uid, count(distinct orderid),sum(amount) from t_order
group by uid
order by uid desc, sum(amount);

相比于其他操作，case when 操作可能不是那么“通用”。它更常见于SQL场景中，可能会用于分组，可能会用于赋值，也可能用于其他场景。分组，比如按照一定的分数区间分成优良中差。赋值，比如当数值小于0时，按照0计算。我们来举例看一下分组的场景。将每个uid按照总金额分为[0-300)，[300,600),[600,900)三组。

#Python
def func(x):
	if x<300:
		return '[0-300)'
	elif x<600:
		return '[300-600)'
	else:
		return '[600-900)'
order_df['amt_interval']=order_df['sum_amount'].map(func)
order_df

#MySQL
select uid, order_cnt,
case when sum_amount<300 then '[0-300)'
	 when sum_amount>=300 and sum_amount<600 then '[300-600)'
	 when sum_amount>=600 and sum_amount<900 then '[600-900)'
else 'other' end as amt_interval
from
(
select uid, count(distinct orderid) order_cnt, sum(amount) as sum_amount
from t_order
group by uid
order by uid desc, sum(amount)
) a

熟悉pandas的朋友应该能想到，pandas的这种分组操作有一种专门的术语叫“分箱”，相应的函数为cut,qcut，能实现同样的效果。为了保持和SQL操作的一致性，此处采用了map函数的方式。

1）更新
更新和删除都是要改变原有数据的操作。对于更新操作，操作的逻辑是：先选出需要更新的目标行，再进行更新。pandas中，可以使用前文提到的方式进行选择操作，之后可以直接对目标列进行赋值，SQL中需要使用update关键字进行表的更新。示例如下：将年龄小于20的用户年龄改为20。

#Python
user_data
user_data.loc[user_data['age']<20, 'age']=20
user_data

#MySQL
select * from t_user;
update t_user set age=20 where age<20;
select * from t_user;

2）删除
删除操作可以细分为删除行的操作和删除列的操作。对于删除行操作，pandas的删除行可以转换为选择不符合条件进行操作。SQL需要使用delete关键字。例如删除年龄为30岁的用户。

#Python
user_data[user_data['age']！=30]
user_data

#MySQL
delete from t_user where age=30;
select * from t_user;

对于删除列的操作。pandas需要使用drop方法。SQL也需要使用drop关键字。

#Python
user_data.drop(['uid'], inplace=True, axis=1)
user_data

#MySQL
alter table t_user drop column uid;
select * from t_user;

总结：

hive方面我们新建了一张表，并把同样的数据加载进了表中。

#hive
#建表
create table t_order（
id int comment "id",
ts string comment '订单时间',
uid string comment '用户id',
orderid string comment '订单id',
amount float comment '订单金额'
）
row format delimited fields terminated by ','
stored as textfile;
#加载数据
load data local inpath 'order.csv' overwrite into table t_order;
#加载数据
select * from t_order;

对于原始数据集中的一列，我们常常要截取其字串作为新的列来使用。例如我们想求出每一条订单对应的日期。需要从订单时间ts或者orderid中截取。在pandas中，我们可以将列转换为字符串，截取其子串，添加为新的列。我们使用.str将原字段视为字符串，从ts中截取了前10位，从orderid中截取了前8位。经验表明有时在.str之前需要加astype。
对于字符串截取的操作，Hive SQL中有substr函数，它在MySQL和Hive中的用法是一样的substr(string A，int start，int len)表示从字符串A中截取起始位置为start，长度为len的子串，其中起始位置从1开始算。

#python
import pandas as pd
order = pd.read_csv('order.csv', names=['id', 'ts', 'uid', 'orderid', 'amount'])
order.head()
order['dt'] = order['ts'].str[:10]
order.head()
order['dt2'] = order['orderid'].astype(str).str[:8]
order.head()

#Hive SQL
select *, substr(ts, 1, 10) as dt, substr(orderid, 1, 8) as dt2 
from t_order;

提取包含特定字符的字段，在pandas中我们可以使用字符串的contains，extract，replace方法，支持正则表达式。而在hive SQL中，既有简易的Like关键字匹配特定的字符，也可以使用regexp_extract，regexp_replace这两个函数更灵活地实现目标。
假设要实现筛选订单时间中包含“08-01”的订单。pandas和SQL代码如下所示，使用like时，%是通配符，表示匹配任意长度的字符。

#python
order_08_01 = order[order['ts'].astype(str).str.contains('08-01')]
order_08_01

#Hive SQL
select * from t_order where ts like "%08-01%"; 

假设要实现提取ts中的日期信息(前10位)，pandas里支持正则表达式的extract函数，而hive里除了前文提到的substr函数可以实现外，这里我们可以使用regexp_extract函数，通过正则表达式实现。

#python
order['dt3'] = order['ts'].astype(str).str.extract('(\d{4}-\d{2}-\d{2}).*')
#这个正则表达式表示"4位数字横杠两位数字横杠两位数字"，后面是任意字符，.是通配符，*表示0次或多次，
#我们提取的目标要放在小括号里
order.head()

#Hive SQL
select *, regexp_extract(ts, '(\\d{4}-\\d{2}-\\d{2}).*', 1) as dt3
from t_order;
#我们的目标同样是在小括号里，1表示取第一个匹配的结果

假设我们要去掉ts中的横杠，即替换ts中的“-”为空，在pandas中可以使用字符串的replace方法，hive中可以使用regexp_replace函数。

#python
order['dt4'] = order['ts'].astype(str).str.replace('-', '')
order.head()

#Hive SQL
select *, regexp_replace(ts, '-', '') as dt4
from t_order;

我们想统计：ts中含有‘2019-08-01’的不重复订单有多少，ts中含有‘2019-08-02’的不重复订单有多少。

#Hive SQL
select count(distinct case when ts like '%2019-08-01%' then orderid end) as 0801_cnt, 
count(distinct case when ts like '%2019-08-02%' then orderid end) as 0802_cnt
from t_order;
#运行结果：
5    11

pandas中实现：
定义两个函数，第一个函数给原数据增加一列，标记我们的条件，第二个函数再增加一列，当满足条件时，给出对应的orderid，然后要对整个dataframe应用这两个函数。对于我们不关心的行，这两列的值都为nan。第三步再进行去重计数操作。

#python
#第一步：构造一个辅助列
def func_1(x):
    if '2019-08-01' in x['ts']:
        return '2019-08-01'#这个地方可以返回其他标记
    elif '2019-08-02' in x['ts']:
        return '2019-08-02'
    else:
        return None

#第二步：将符合条件的order作为新的一列
def func_2(x):
    if '2019-08-01' in x['ts']:
        return str(x['orderid'])
    elif '2019-08-02' in x['ts']:
        return str(x['orderid'])
    else:
        return None

#应用两个函数，查看结果
#注意这里必须加上axis=1，你可以尝试下不加会怎样
order['cnt_condition'] = order.apply(func_1, axis=1)
order['cnt'] = order.apply(func_2, axis=1)
order[order['cnt'].notnull()]

#进行分组计数
order.groupby('cnt_condition').agg({'cnt': 'nunique'})

row_number() over函数的基本用法
语法：row_number() over (partition by column1 order by column2)
详解：根据column1进行分组，在分组内部根据column2排序，而此函数计算的值就表示每组内部排序后的顺序编号（每组编号从1开始增加，该编号在组内是连续并且唯一的)。
比如我们对每个uid的订单按照订单时间倒序排列，获取其排序的序号。

#pandas中我们需要借助groupby和rank函数来实现同样的效果。
#改变rank中的method参数可以实现Hive中其他的排序
#由于我们的ts字段是字符串类型，先转换为datetime类型
order['ts2'] =  pd.to_datetime(order['ts'], format='%Y-%m-%d %H:%M:%S')

#进行分组排序，按照uid分组，按照ts2降序，序号默认为小数，需要转换为整数
#并添加为新的一列rk
order['rk'] = order.groupby(['uid'])['ts2'].rank(ascending=False, method='first').astype(int)

#为了便于查看rk的效果,对原来的数据按照uid和时间进行排序，结果和SQL一致
order.sort_values(['uid','ts'], ascending=[True, False])

select *, row_number() over (partition by uid order by ts desc) as rk
from t_order;

#ag和lead函数也是Hive SQL中常用的窗口函数，他们的格式为：
lag(字段名,N) over(partition by 分组字段 order by 排序字段 排序方式) 
lead(字段名,N) over(partition by 分组字段 order by 排序字段 排序方式) 

lag函数表示，取分组排序之后比该条记录序号小N的对应记录的指定字段的值。lead刚好相反，是比当前记录大N的对应记录的指定字段值

select *, 
lag(ts, 1) over (partition by uid order by ts desc) as lag,
lead(ts, 1) over (partition by uid order by ts desc) as lead
from t_order;

例子中的lag表示分组排序后，前一条记录的ts，lead表示后一条记录的ts。不存在的用NULL填充。

pandas中我们也有相应的shift函数来实现这样的需求。shift的参数为正数时，表示lag，为负数时，表示lead。

order['lag'] =  order.groupby(['uid'])['ts2'].shift(-1)
order['lead'] =  order.groupby(['uid'])['ts2'].shift(1)
#依然是为了看效果，对原来的数据按照uid和时间进行排序，结果和SQL一致
order.sort_values(['uid','ts'], ascending=[True, False])

在我们的数据中，一个uid会对应多个订单，目前这多个订单id是分多行显示的。现在我们要做的是让多个订单id显示在同一行，用逗号分隔开。在pandas中，我们采用的做法是先把原来orderid列转为字符串形式，并在每一个id末尾添加一个逗号作为分割符，然后采用字符串相加的方式，将每个uid对应的字符串类型的订单id拼接到一起。代码和效果如下所示。为了减少干扰，我们将order数据重新读入，并设置了pandas的显示方式。

#Python
import pandas as pd
pd.set_option('display.max_columns', None) #显示所有列
pd.set_option('display.max_rows', None) #显示所有行
pd.set_option('display.max_colwidth', 100) #列的显示长度最大为100
order=pd.read_csv('order.csv', names=['id', 'ts', 'uid', 'orderid', 'amount'])
order.head()

#Python
order['orderid']=order['orderid'].astype('str')
order['orderid']=order['orderid'].apply(lambda x: x+',')
order_group=order.groupby('uid').agg('orderid':'sum')
order_group

可以看到，同一个uid对应的订单id已经显示在同一行了，订单id之间以逗号分隔。
在Hive中实现同样的效果要方便多了，我们可以使用collect_set/collect_list函数,，二者的区别在于前者在聚合时会进行去重，别忘了加上group by。

#Hive
select uid, collect_set(orderid) as order_list
from t_order group by uid;

可以看出hive实现的效果中，将同一个uid的orderid作为一个“数组”显示出来。

UDTF(User-Defined Table-Generating Functions)是用来解决输入一行输出多行的需求函数。
lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。
语法： lateral view UDTF(expression) tableAliasName as colAliasName
其中UDTF(expression)是行转列的函数，即一行变为多行的函数，比如explode。
tableAliasName表示表的别名,colAliasName表示表的列的别名。
原理：通过lateral view UDTF(expression)函数把一行转换为多行，会生成一个临时表，把这些数据放入这个临时表中。
1） 单个lateral view

select A,B from table_1 lateral view explode(B) mytable as B;

2） 多个lateral view
源表basetable，包含col1和col2两列数据，数据表如下所示：

select mycol1, mycol2 from basetable
lateral view explode(col1) mytable1 as mycol1
lateral view explode(col2) mytable2 as mycol2;

3） 复杂方式

select datenu, des, t from tb_split 
lateral view explode(split(des,"\\|")) tb1 as des
lateral view explode(split(t,"\\|")) tb2 as t;

4） 将上一小节的结果还原为每个orderid显示一行的形式

with tmp as 
(
select uid, collect_set(orderid) as order_list
from t_order
group by uid
)
select uid, o_list
from tmp lateral view explode(order_list) t as o_list;

当我们书写一些结构相对复杂的SQL语句时，可能某个子查询在多个层级多个地方存在重复使用的情况，这个时候我们可以使用 with as 语句将其独立出来，极大提高SQL可读性，简化SQL~
注：目前 oracle、sql server、hive等均支持 with as 用法，但 mysql并不支持！

python实现方式如下所示：

order_group = order_group.reset_index() #将索引重置
order_group
order_group1 = pd.concat([pd.Series(row['uid'], row['orderid'].split(',')) for _ , row in order_group.iterrows()]).reset_index()
order_group1

这样的结果中会有一个空行，这是因为用逗号分隔的时候，最后一个元素为空。

加载新的数据


先来看pandas中实现，这里我们需要用到literal_eval这个包，能够自动识别以字符串形式存储的数组。我定义了一个解析函数，将arr列应用该函数多次，解析出的结果作为新的列:

def extract_num(x, i):
	from ast import literal_eval
	return literal_eval(x)[i]
new_data['arr_1']=new_data.arr.apply(extract_num, args=(0,)) #0代表第一个数字
new_data['arr_2']=new_data.arr.apply(extract_num, args=(1,)) #1代表第二个数字
new_data['arr_3']=new_data.arr.apply(extract_num, args=(2,)) #2代表第三个数字
new_data['arr_4']=new_data.arr.apply(extract_num, args=(3,)) #3代表第四个数字
new_data['arr_5']=new_data.arr.apply(extract_num, args=(4,)) #4代表第五个数字
new_data['arr_6']=new_data.arr.apply(extract_num, args=(5,)) #5代表第六个数字
#new_data['arr_1'] =  new_data.arr.apply(extract_num, args=(0,)).astype(int) 
#解析出的结果是object类型的，如果想让它们参与数值计算，需要再转换为int类型
new_data

回到Hive SQL，实现起来比较容易。我们可以通过split函数将原来的字符串形式变为数组，然后依次取数组的元素即可，但是要注意使用substr函数处理好前后的中括号[]

可以看到最终我们得到的结果是字符串的形式，如果想要得到数值，可以再进行一步截取

可以看到，我们这里得到的依然是字符串类型，和pandas中的强制转换类似，hive SQL中也有类型转换的函数cast，使用它可以强制将字符串转为整数，使用方法如下面代码所示

小节：

图中有错误：序号7中：正确的是 shift函数，正数为lag，负数为lead

import pandas as pd
data = pd.read_excel('order.xlsx')
#data2 = pd.read_excel('order.xlsx', parse_dates=['ts'])
data.head()
data.dtypes

需要指出，pandas读取数据对于日期类型有特殊的支持。
无论是在read_csv中还是在read_excel中，都有parse_dates参数，可以把数据集中的一列或多列转成pandas中的日期格式。
data是使用默认的参数读取的，在data.dtypes的结果中ts列是datetime64[ns]格式，而data2是显式指定了ts为日期列，因此data2的ts类型也是datetime[ns]。
如果在使用默认方法读取时，日期列没有成功转换，就可以使用类似data2这样显式指定的方式。

create table `t_order`(
`id` int,
`ts` string,
`uid` string,
`orderid` string,
`amount` float
)
row format delimited fields terminated by ','
stored as textfile;

load data local inpath 't_order.csv' overwrite into table t_order;
select * from t_order limit 20;

在hive中加载数据我们需要先建立表，然后把文本文件中的数据load到表中，结果如下图所示。

1）日期获取

a) 获取当前日期，年月日时分秒
pandas中可以使用now()函数获取当前时间，但需要再进行一次格式化操作来调整显示的格式。我们在数据集上新加一列当前时间的操作

#pandas
data['current_dt'] = pd.datetime.now()
data['current_dt'] = data['current_dt'].apply(lambda x : x.strftime('%Y-%m-%d %H:%M:%S'))
data.head()
#也可以data['current_dt'] = pd.datetime.now().strftime('%Y-%m-%d %H:%M:%S')一步到位

MySQL有多个函数可以获取当前时间：
now()，current_timestamp，current_timestamp()，sysdate()，localtime()，localtime，localtimestamp，localtimestamp()等。

#MySQL 
SELECT *, now(),current_timestamp(),current_timestamp
FROM `t_order`;
SELECT *, sysdate(),localtime(),localtime
FROM `t_order`;
SELECT *, localtimestamp, localtimestamp()
FROM `t_order`;

hive中获取当前时间，可以使用 current_timestamp()， current_timestamp，得到的是带有毫秒的，如果想保持和上面同样的格式，需要使用substr截取一下。如下图所示：

#HiveQL
select *, substr(current_timestamp, 1, 19), substr(current_timestamp(), 1, 19) 
from t_order limit 20;

b) 获取当前时间，年月日

沿用上一节思路，进行格式转换得到当前日期

#pandas
data['dt_date'] = pd.datetime.now().strftime('%Y-%m-%d')
data.head()

#MySQL
SELECT *, curdate() FROM t_order;

#HiveQL
select *, current_date() from t_order limit 20;

c）提取日期中的相关信息

日期中包含有年月日时分秒，我们可以用相应的函数进行分别提取。下面我们提取一下ts字段中的天,时间,年,月,日,时,分,秒信息。

#pandas
data['dt_day'] = data['ts'].dt.date#提取年月日
data['year'] = data['ts'].dt.year#提取年份
data['month'] = data['ts'].dt.month#提取月份
data['day'] = data['ts'].dt.day#提取天数
data['dt_time'] = data['ts'].dt.time#提取时间
data['hour'] = data['ts'].dt.hour#提取小时
data['minute'] = data['ts'].dt.minute#提取分钟
data['second'] = data['ts'].dt.second#提取秒
data.head()

在MySQL和Hive中，由于ts字段是字符串格式存储的，我们只需使用字符串截取函数即可。两者的代码是一样的，只需要注意截取的位置和长度即可

#MySQL
select ts, substr(ts, 1, 10), substr(ts, 1, 4), substr(ts, 6, 2), 
substr(ts, 9, 2), substr(ts, 12, 8), substr(ts, 12, 2), 
substr(ts, 15, 2), substr(ts, 18, 2)
from t_order;

#HiveQL
select ts, substr(ts, 1, 10), substr(ts, 1, 4), substr(ts, 6, 2), 
substr(ts, 9, 2), substr(ts, 12, 8), substr(ts, 12, 2), 
substr(ts, 15, 2), substr(ts, 18, 2)
from t_order limit 20;

2）日期转换

a）可读日期转换为unix时间戳

#python
def transfer_time_format(x):
    import time
    tmp_time = time.strptime(x, '%Y-%m-%d %H:%M:%S')
    res_time = int(time.mktime(tmp_time))
    return res_time
'''
Python time mktime() 函数执行与gmtime(), localtime()相反的操作，它接收struct_time对象作为参数，返回用秒数来表示时间的浮点数。
mktime()方法语法：time.mktime(t)
参数：t -- 结构化的时间或者完整的9位元组元素。
返回值：返回用秒数来表示时间的浮点数。
如果输入的值不是一个合法的时间，将触发 OverflowError 或 ValueError。
'''
data['str_ts'] = data['ts'].dt.strftime('%Y-%m-%d %H:%M:%S')
data['str_timestamp'] = data['str_ts'].apply(transfer_time_format)
data.head()
#使用匿名函数的写法
#data['str_timestamp'] = data['str_ts'].apply(lambda x: int(time.mktime(time.strptime(x, '%Y-%m-%d %H:%M:%S'))))

MySQL和Hive中可以使用时间戳转换函数进行这项操作，其中MySQL得到的是小数形式，需要进行一下类型转换，Hive不需要。

#MySQL
select *, cast(unix_timestamp(ts) as int) from t_order;

#Hive
select *, unix_timestamp(ts) from t_order limit 20;

b）unix时间戳转换为可读日期
在pandas中，我们看一下如何将str_timestamp列转换为原来的ts列，这里依然采用time模块中的方法来实现。

#pandas:
def transfer_time_format2(x):
    import time
    time_local = time.localtime(x) #将timestamp转换为本地时间
    res_time = time.strftime('%Y-%m-%d %H:%M:%S', time_local) #将时间进行格式化，转换为时间格式
    return res_time
data['ori_ts'] = data['str_timestamp'].apply(transfer_time_format2)
data.head()

回到MySQL和Hive，依然只是用一个函数就解决了。

#MySQL
select *, from_unixtime(cast(unix_timestamp(ts) as int))
from t_order;

#Hive
select *, from_unixtime(unix_timestamp(ts)) from t_order limit 20;

c）10位日期转8位
对于初始是ts列这样年月日时分秒的形式，我们通常需要先转换为10位年月日的格式，再把中间的横杠替换掉，就可以得到8位的日期了。
由于打算使用字符串替换，我们先要将ts转换为字符串的形式，在前面的转换中，我们生成了一列str_ts，该列的数据类型是object，相当于字符串，可以在此基础上进行这里的转换。

#pandas
data['str_ts_8'] = data['str_ts'].astype(str).str[:10].apply(lambda x: x.replace('-',''))
data.head()

MySQL和Hive中也是同样的套路，截取和替换几乎是最简便的方法了。

select replace(substr(ts, 1, 10), '-', '') from t_order;

#Hive
select *, regexp_replace(substr(ts, 1, 10),'-','')
from t_order limit 20;

另外一种方法：先将字符串转为unix时间戳的形式，再格式化为8位的日期。

def transfer_time_format3(x):
    import time
    time_local = time.localtime(x)
    res_time = time.strftime('%Y%m%d', time_local)
    return res_time
data['str_ts_8_2'] = data['str_timestamp'].apply(transfer_time_format3)
data.head()

#MySQL
select *, from_unixtime(cast(unix_timestamp(ts) as int), '%Y%M%d')
from t_order;
#Hive
select *, from_unixtime(unix_timestamp(ts),'yyyyMMdd') from t_order limit 20;

d）8位日期转10位
方法一：

#python
data['str_ts_10'] = data['str_ts_8'].apply(lambda x : x[:4] + "-" + x[4:6] + "-" + x[6:])
data.head()

#MySQL
select id, ts, concat(substr(dt8, 1, 4), '-', substr(dt8, 5, 2), '-', substr(dt8, 7,2))
from
(
select *,  replace(substr(ts, 1, 10), '-', '')  as dt8
from t_order
) a 

#Hive
select id, ts, concat(substr(dt8, 1, 4), '-', substr(dt8, 5, 2), '-', substr(dt8, 7,2))
from
(
select *, regexp_replace(substr(ts, 1, 10),'-','') as dt8
from t_order
) a 
limit 20;

方法二：
在pandas中，借助unix时间戳转换并不方便，我们可以使用datetime模块的格式化函数来实现，如下所示。

#pandas
def transfer_time_format4(x):
    from datetime import datetime
    tmp_time = datetime.strptime('20190801', '%Y%m%d')
    res_time = datetime.strftime(tmp_time, '%Y-%m-%d')
    return res_time
data['str_ts_10_2'] = data['str_ts_8'].apply(transfer_time_format4)
data.head()

Mysql和Hive中unix_timestamp接收的参数不一样，前者必须输入为整数，后者可以为字符串。我们的目标是输入一个8位的时间字符串，输出一个10位的时间字符串。由于原始数据集中没有8位时间，我们临时构造了一个

#MySQL
select *, 
replace(substr(ts, 1, 10),'-', ''),
from_unixtime(unix_timestamp(cast(replace(substr(ts, 1, 10),'-', '')as int)),'%Y-%m-%d')
from t_order;

#Hive
select *, 
regexp_replace(substr(ts, 1, 10),'-', ''),
from_unixtime(unix_timestamp(regexp_replace(substr(ts, 1, 10),'-', ''), 'yyyyMMdd'),'yyyy-MM-dd')
from t_order
limit 20;

#总结：hive 时间戳函数之unix_timestamp，from_unixtime

一. 日期>>>>时间戳

1.unix_timestamp()   获取当前时间戳

例如：select unix_timestamp()   --1565858389

2.unix_timestamp(string timestame)   输入的时间戳格式必须为'yyyy-MM-dd HH:mm:ss',如不符合则返回null

例如：
select unix_timestamp('2019-08-15 16:40:00')   --1565858400
select unix_timestamp('2019-08-15')  --null

3.unix_timestamp(string date,string pattern)   将指定时间字符串格式字符串转化成unix时间戳,如不符合则返回null

例如：
select unix_timestamp('2019-08-15','yyyy-MM-dd')   --1565798400

select unix_timestamp('2019-08-15 16:40:00','yyyy-MM-dd HH:mm:ss')   --1565858400

select unix_timestamp('2019-08-15','yyyy-MM-dd HH:mm:ss')   --null

二. 时间戳>>>>日期

1.from_unixtime(bigint unixtime,string format)  将时间戳秒数转化为UTC时间，并用字符串表示，可通过format规定的时间格式，指定输出的时间格式，其中unixtime 是10位的时间戳值，而13位的所谓毫秒的是不可以的。

例如：
select from_unixtime(1565858389,'yyyy-MM-dd HH:mm:ss')  --2019-08-15 16:39:49

select from_unixtime(1565858389,'yyyy-MM-dd')   --2019-08-15

2.如果unixtime为13位的，需要先转成10位

select from_unixtime(cast(1553184000488/1000 as int),'yyyy-MM-dd HH:mm:ss')   --2019-03-22 00:00:00

select from_unixtime(cast(substr(1553184000488,1,10) as int),'yyyy-MM-dd HH:mm:ss')  --2019-03-22 00:00:00
 三.获取当前时间
 
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')   -- 2019-08-15 17:18:55

3）日期计算

日期计算主要包括日期间隔(加减一个数变为另一个日期)和计算两个日期之间的差值。

a）日期间隔
pandas中对于日期间隔的计算需要借助datetime 模块。我们来看一下如何计算ts之后5天和之前3天。

使用timedelta函数既可以实现天为单位的日期间隔，也可以按周，分钟，秒等进行计算。

在MySQL和Hive中有相应的日期间隔函数date_add，date_sub函数，但使用的格式略有差异。


需要注意的是Hive计算的结果没有时分秒，如果需要，依然可以使用拼接的方式获得。

b）日期差
在pandas中，如果事件类型是datetime64[ns]类型，直接作差就可以得出日期差，但是得到的数据后面还有一个"days"的单位，这其实就是上一小节提到的timedelta类型。

为了便于使用，我们使用map函数获取其days属性，得到我们想要的数值的差

如果不是datetime格式，可以先用下面的代码进行一次转换。

#str_ts是字符串格式，转换出的dt_ts是datetime64[ns]格式
data['dt_ts'] = pd.to_datetime(data['str_ts'], format='%Y-%m-%d %H:%M:%S')

Hive和MySQL中的日期差有相应的函数datediff。但需要注意它的输入格式。


可以看到输入的形式既可以是具体到时分秒的格式，也可以是年月日格式。但是要注意Hive中输入的日期必须是10位的格式，否则得不到正确的结果，比如输入8位的，结果会是NULL，而MySQL则可以进行8位日期的计算。

总结：

数据样例如下所示，从左到右依次表示，id，日期，当日销售额，数据周期从2019-11-01到2019-12-31。

import pandas as pd
import datetime
orderamt = pd.read_excel('orderamt.xlsx')
orderamt.head()

CREATE TABLE `t_orderamt`(
  `id` int, 
  `dt` string, 
  `orderamt` float)
row format delimited fields terminated by ','
stored as textfile;

load data local inpath 'orderamt.txt' overwrite into table t_orderamt;
select * from t_orderamt limit 20;

按照上面的代码建表，然后把orderamt.txt的内容加载到表中即可，最终数据如上图所示。
方法一：自关联，关联条件是日期差分别是1和7，分别求出当天，昨天，7天前的数据，用三列形式展示，之后就可以进行作差和相除求得百分比。

select a.*, b.orderamt as ld_amt, c.orderamt as lw_amt #as字段重新命名，有时可以省略as
from t_orderamt a #t_orderamt表命名为a
left join t_orderamt b
on datediff(a.dt, b.dt)=1 #求时间间隔为1天，a.dt从2019-11-01开始，则b.dt从2019-11-02开始
left join t_orderamt c
on datediff(a.dt, c.dt)=7
order by dt;
#关联两次，条件分别是日期相差1天和日期相差7天。关联不上的则留空。

方法二：不进行关联，直接查询当前日期前一天和前七天的数据，同样以3列的形式展示。

#MySQL
select *,
(select orderamt from t_order where dt=date_add(a.dt, interval 1 day)) ld_amt,
(select orderamt from t_order where dt=date_add(a.dt, interval 7 day)) lw_amt
from t_order a;

方法三：采用窗口函数，lag

select *,
lag(orderamt, 1) over(order by dt)ld_amt,
lag(orderamt, 7) over(order by dt)lw_amt
from t_orderamt;

以上面的代码为基础，稍加修改，增加计算百分比的代码，就可以分别得到周同比和日环比。

--第一段修改
select a.*, concat(round(((a.orderamt - b.orderamt) / b.orderamt) * 100,2), '%') as ld_pct,
concat(round(((a.orderamt - c.orderamt) / c.orderamt) * 100,2), '%') as lw_pct
from t_orderamt a
left join t_orderamt b
on DATEDIFF(a.dt, b.dt) = 1
left join t_orderamt c
on DATEDIFF(a.dt, c.dt) = 7
order by dt
;

--第二段修改
select 
b.id, b.dt, b.orderamt,
concat(round(((b.orderamt - ld_amt) / ld_amt) * 100,2), '%') as ld_pct,
concat(round(((b.orderamt - lw_amt) / lw_amt) * 100,2), '%') as lw_pct
from
(
select *, 
(select orderamt from t_orderamt where dt = date_add(a.dt, interval -1 day)) ld_amt,
(select orderamt from t_orderamt where dt = date_add(a.dt, interval -7 day)) lw_amt
from t_orderamt a
) b
;

--第三段修改
select 
b.id, b.dt, b.orderamt,
concat(round(((b.orderamt - ld_amt) / ld_amt) * 100,2), '%') as ld_pct,
concat(round(((b.orderamt - lw_amt) / lw_amt) * 100,2), '%') as lw_pct
from
(
select *, lag(orderamt, 1) over(order by dt) ld_amt, 
lag(orderamt, 7) over(order by dt) lw_amt
from t_orderamt
) b 

采用pandas进行计算
方法一：日期关联的方法

import pandas as pd
import datetime
orderamt = pd.read_excel('orderamt.xlsx')
#orderamt['dt'] = orderamt['dt'].apply(lambda x: datetime.datetime.strptime(x, '%Y-%m-%d'))#为了便于日期加减，将dt转换为datetime64[ns]的格式，视情况运行该句

#分别构造两个dateframe用于关联
orderamt_plus_1 = orderamt.copy()
orderamt_plus_7 = orderamt.copy()

orderamt_plus_1['dt'] = orderamt_plus_1['dt'] + datetime.timedelta(days=1) #天数加1
orderamt_plus_7['dt'] = orderamt_plus_7['dt'] + datetime.timedelta(days=7)
orderamt_1 = pd.merge(orderamt, orderamt_plus_1, on=['dt'],how='left') #进行合并
orderamt_1_7 = pd.merge(orderamt_1, orderamt_plus_7, on=['dt'],how='left')
orderamt_all = orderamt_1_7[['id_x', 'dt', 'amt_x', 'amt_y', 'amt']]

#接方法一代码
orderamt_all['ld_pct'] = (orderamt_all['amt_x'] - orderamt_all['amt_y']) / orderamt_all['amt_y']
orderamt_all['lw_pct'] = (orderamt_all['amt_x'] - orderamt_all['amt']) / orderamt_all['amt']
orderamt_all

方法二：应用shift函数，直接选取前面n行的方法

orderamt = pd.read_excel('orderamt.xlsx')
orderamt['ld_amt'] = orderamt['amt'].shift(1)
orderamt['lw_amt'] = orderamt['amt'].shift(7)
orderamt

orderamt['ld_pct'] = (orderamt['amt'] - orderamt['ld_amt']) / orderamt['ld_amt']
orderamt['lw_pct'] = (orderamt['amt'] - orderamt['lw_amt']) / orderamt['lw_amt']
orderamt

方法三：在pandas中，还有专门的计算同环比的函数pct_change。

orderamt = pd.read_excel('orderamt.xlsx')
orderamt['ld_pct'] = orderamt['amt'].pct_change()
orderamt['lw_pct'] = orderamt['amt'].pct_change(7)
orderamt

#格式化字符串
orderamt['ld_pct'] = orderamt['ld_pct'].apply(lambda x: format(x, '.2%'))
orderamt['lw_pct'] = orderamt['lw_pct'].apply(lambda x: format(x, '.2%'))
orderamt

• MySQL计算累计百分比

1）不分组情况

select a.id, a.dt, a.orderamt, 
sum(b.orderamt) as cum #计算总额，采用sum函数
from t_orderamt a
join t_orderamt b
on a.dt >= b.dt #非等值连接，由于是累计求和，所以必须是>=，若是=的话，求得就是orderamt的总和了
group by a.id, a.dt, a.orderamt;

#图中的cum列即是我们想要求的累加值。而所有销售金额的总计值，我们可以直接使用sum求出。
select sum(orderamt) as total 
from t_orderamt

结合上面的两段SQL，就可以求得累计的百分比，注意连接条件我们使用了1=1这种恒成立的方式。代码和结果如下

select c.id, c.orderamt, c.cum, 
concat(round((c.cum / d.total) * 100, 2), '%') as cum_pct
from 
(
select a.id, a.dt, a.orderamt, sum(b.orderamt) as cum
from t_orderamt a
join t_orderamt b
on a.dt >= b.dt
group by a.id, a.dt, a.orderamt
) c 
left join 
(
select sum(orderamt) as total
from t_orderamt
) d on 1 = 1 

2）分组情况
现在需要计算每天的累计销售额分别占当月的百分比

首先仍然是求累计金额，但要分月累计。在上面的基础上加上月份相等条件即可，从结果中可以看到，在11月和12月cum列是分别累计的。

select substr(a.dt, 1, 7) as mon, a.dt, a.orderamt, sum(b.orderamt) as cum
from t_orderamt a
join t_orderamt b
on a.dt >= b.dt and 
substr(a.dt, 1, 7) = substr(b.dt, 1, 7) #增加了这个条件
group by substr(a.dt, 1, 7), a.dt, a.orderamt

求每月总计金额的代码比较简单：

select substr(a.dt, 1, 7) as mon, sum(orderamt) as total
from t_orderamt a
group by substr(a.dt, 1, 7)

同样的，我们把两段代码进行合并，就得到每月的累计百分比情况：

select c.mon, c.dt, c.orderamt, c.cum, d.total,
concat(round((c.cum / d.total) * 100, 2), '%') as cum_pct
from
(
select substr(a.dt, 1, 7) as mon, a.dt, a.orderamt, sum(b.orderamt) as cum
from t_orderamt a
join t_orderamt b
on a.dt >= b.dt and substr(a.dt, 1, 7) = substr(b.dt, 1, 7)
group by substr(a.dt, 1, 7), a.dt, a.orderamt
) c 
left join
(
select substr(a.dt, 1, 7) as mon, sum(orderamt) as total
from t_orderamt a
group by substr(a.dt, 1, 7)
) d on c.mon = d.mon

• hive计算累计百分比

1）不分组情况
Hive 不支持在on中写不等号的连接条件，虽然可以采用where的方式改造一下，代码如下所示。但这并不是最优的方案。我们可以使用Hive中的窗口函数，很方便的计算累计值。

#where方法
select a.id, a.dt, a.orderamt, 
sum(b.orderamt) as cum--对b表的金额进行求和
from t_orderamt a
join t_orderamt b
on 1=1
where a.dt >= b.dt--使用不等值连接
group by a.id, a.dt, a.orderamt

#窗口函数
select *, sum(orderamt) over(order by dt) as cum from t_orderamt;

执行结果相同

接下来我们重点看窗口函数的方式。在计算总计值的时候和前面MySQL的方式类似，累计百分比的计算也是需要把两部分代码结合在一起。

select c.id, c.dt, c.orderamt, c.cum, 
concat(round((c.cum / d.total) * 100, 2), '%') as cum_pct
from
(
select *, sum(orderamt) over(order by dt) as cum 
from t_orderamt
) c
left join
(
select sum(orderamt) as total
from t_orderamt
) d 
on 1 = 1--在Hive中这个条件可以不写

2）分组情况
分组的情况，在窗口函数里是可以用partition by直接指定分组的，见如下代码

select id, substr(dt, 1, 7) as mon, 
dt, orderamt, 
sum(orderamt) over(partition by substr(dt, 1, 7) order by dt) as cum
from t_orderamt;

可以看到，同前面的分组情况一样，在11月和12月cum列是分别累计的。

接下来也很容易就写出分组计算累计百分比的代码，结果和上面也是一致的。

select c.mon, c.dt, c.orderamt, c.cum, d.total,
concat(round((c.cum / d.total) * 100, 2), '%') as cum_pct
from
(
select id, substr(dt, 1, 7) as mon, dt, orderamt, sum(orderamt) over(partition by substr(dt, 1, 7) order by dt) as cum
from t_orderamt
) c 
left join
(
select substr(dt, 1, 7) as mon, sum(orderamt) as total
from t_orderamt 
group by substr(dt, 1, 7)
) d on c.mon = d.mon

• python计算累计百分比

在pandas中，提供了专门的函数来计算累计值，分别是cumsum函数，expanding函数，rolling函数。我们一起来看一下使用三种函数计算分组和不分组累计百分比的方法。
1）不分组情况
a）cumsum函数
cumsum是pandas中专门用于计算累计和的函数。类似的函数还有cumprod计算累计积，cummax计算前n个值的最大值，cummin计算前n个值的最小值。

import pandas as pd
orderamt = pd.read_excel('orderamt.xlsx')
orderamt['cum_amt'] = orderamt['amt'].cumsum()
orderamt.head(15)

orderamt['cum_amt_pct'] = orderamt['cum_amt'] / orderamt['amt'].sum()
orderamt.head(15)

b）expanding函数

pandas中的expanding函数是窗口函数的一种，它不固定窗口的大小，而是进行累计的计算。类似于cumsum()，但更强大。

orderamt = pd.read_excel('orderamt.xlsx')
orderamt['mon'] = orderamt['dt'].dt.strftime('%Y-%m')#得到字符串形式的月份
orderamt['cum_expand'] = orderamt.expanding(min_periods=1)['amt'].sum()
orderamt.head(15)

参数min_periods表示最小的观测窗口，默认为1，可以设置为其他值，但如果窗口内记录数不足该值，则会显示NA。

有了累计值，计算累计的百分比，可以按照cumsum中的方法进行，此处省略。

c）rolling函数
rolling函数与expanding相比，主要是固定了窗口大小。当窗口超过dataframe的长度时，可以实现与expanding同样的效果。上面的代码使用rolling函数的方式可以改写如下，注意指定了window参数为len(orderamt)：

orderamt = pd.read_excel('orderamt.xlsx')
orderamt['mon'] = orderamt['dt'].dt.strftime('%Y-%m')#得到字符串形式的月份
orderamt['cum_roll'] = orderamt.rolling(window=len(orderamt), min_periods=1)['amt'].sum()
orderamt.head(15)

2）分组情况
a）cumsum函数

#添加pandas显示设置，显示所有行
pd.set_option('display.max_rows', None)

orderamt = pd.read_excel('orderamt.xlsx')
orderamt['mon'] = orderamt['dt'].dt.strftime('%Y-%m')

#分组后对amt求累计和
orderamt['cum_mon'] = orderamt.groupby('mon')['amt'].cumsum()
orderamt

接下来计算分组的总计值，这里用到了pandas中的transform函数，可以把分组后计算的总计值写入原dataframe。

orderamt['mon_total'] = orderamt.groupby('mon')["amt"].transform('sum')
orderamt['grp_cum_pct'] = orderamt['cum_mon'] / orderamt['mon_total']
orderamt

b）expanding函数
分组情况下使用expanding函数需要和groupby结合，注意得到的结果是多重索引，需要取values才能赋值给原dataframe。

orderamt = pd.read_excel('orderamt.xlsx')
orderamt['mon'] = orderamt['dt'].dt.strftime('%Y-%m')
orderamt_mon_group = orderamt.groupby('mon').expanding(min_periods=1)['amt'].sum()
#这里的orderamt_mon_group索引会有两重，我们直接取values的值就可以和原dataframe拼接在一起
orderamt['orderamt_mon_group'] = orderamt_mon_group.values
orderamt

c）rolling函数
rolling函数与expanding函数的代码几乎一样，需要加上window参数。

orderamt = pd.read_excel('orderamt.xlsx')
orderamt['mon'] = orderamt['dt'].dt.strftime('%Y-%m')
orderamt_mon_group_roll = orderamt.groupby('mon').rolling(len(orderamt),min_periods=1)['amt'].sum()
#这里的orderamt_mon_group_roll索引会有两重，我们直接取values的值就可以和原dataframe拼接在一起
orderamt['orderamt_mon_group_roll'] = orderamt_mon_group_roll.values
orderamt

本文数据于：
https://www.kaggle.com/nikhil04/login-time-for-users 。

数据格式比较简单：id：自增id，uid：用户唯一id。ts：用户登录的时间(精确到秒)，数据样例如下图所示。

这里我们约定日活是指每天登录的user_id去重数，从我们的数据来看，计算方式非常简单。

• SQL计算日活

早在系列第一篇中我们就学习过group by聚合操作。只需要按天分组，将uid去重计数，即可得到答案。代码如下：

select substr(ts, 1, 10) as dt, count(distinct uid) as dau
from t_login
group by substr(ts, 1, 10);

• pandas计算日活

import pandas as pd
login_data = pd.read_csv('login_data.txt', sep='\t', parse_dates=['ts'])
login_data.head()

login_data['day'] = login_data['ts'].map(lambda x: x.strftime('%Y-%m-%d'))
uid_count = login_data.groupby('day').aggregate({'uid': lambda x: x.nunique()})
uid_count.reset_index(inplace=True)
uid_count

留存是一个动态的概念，指的是某段时间使用了产品的用户，在一段时间之后仍然在使用产品的用户，二者相比可以求出留存率。常见的留存率有次日留存率，7日留存率，30日留存率等。次日留存是指今天活跃的用户，在明天还剩下多少仍然活跃的用户。留存率越高，说明产品的粘性越好。

留存率是指一段时间后仍然登录的用户占第一天登录用户的比例，由于2017-01-07登录的用户太少，我们选择2017-01-12作为第一天。分别计算次日留存率，7日，14日留存率。

• SQL计算留存
  a）次日留存计算
  左表计数求出初始活跃用户，右表计数求出留存用户，之后就可以求出留存率

select substr(a.ts, 1, 10) as dt,
count(distinct a.uid), count(distinct b.uid),
concat(round((count(distinct a.uid) / count(distinct b.uid))*100, 2), %)
from t_login a
left join t_login b
on a.uid=b.uid
and date_add(substr(a.ts, 1, 10), interval 1 day)=substr(b.ts, 1, 10)
group by substr(a.ts, 1, 10);

b）多日留存计算
当数据量比较大时，join效率会比较低下

select sunstr(a.dt, 1, 10) as dt,
count(distinct a.uid),
count(distinct if(datediff(substr(b.ts, 1, 10), substr(a.ts, 1, 10))=1, b.uid, null)) as 1_day_remain_uid,
count(distinct if(datediff(substr(b.ts, 1, 10), substr(a.ts, 1, 10))=6, b.uid, null)) as 7_day_remain_uid,
count(distinct if(datediff(substr(b.ts, 1, 10), substr(a.ts, 1, 10))=13, b.uid, null)) as 14_day_remain_uid
from t_login a
left join t_login b
on a.uid=b.uid
group by substr(a.ts, 1, 10)

如代码所示，在关联时先不限制日期，最外层查询时根据自己的目标限定日期差，可以算出相应的留存用户数，第一天的活跃用户也可以看作是日期差为0时的情况。这样就可以一次性计算多日留存了。结果如下，如果要计算留存率，只需转换为对应的百分比即可，参考前面的代码，此处略。

• pandas计算留存
  a）次日留存

导入数据并添加两列日期，分别是字符串格式和datetime64格式，便于后续日期计算

import pandas as pd
from datetime import datetime
login_data=pd.read_csv('login_data.txt', sep='\t', parse_dates=['ts'])
login_data['day']=login_data['ts'].map(lambda x: x.strftime('%Y-%m-%d'))
login_data['dt_ts']=pd.to_datetime(login_data['day'], format='%Y-%m-%d'))
login_data.head()

构造新的dataframe，计算日期，之后与原数据进行连接。

data_1=login_data.copy()
data_1['dt_ts_1']data['dt_ts']+timedelta(-1)
data_1.head()

合并前面的两个数据，使用uid和dt_ts 关联，dt_ts_1是当前日期减一天，左边是第一天活跃的用户，右边是第二天活跃的用户

merge_1=pd.merge(login_data, data_1, left_on=['uid', 'dt_ts'], right_on=['uid', 'dt_ts_1'], how='left')
merge_1.head()

计算第一天活跃的用户数

init_user = merge_1.groupby('day_x').aggregate({'uid': lambda x: x.nunique()})
init_user.reset_index(inplace=True)
init_user.head()

计算次日活跃的用户数

one_day_remain_user = merge_1[merge_1['day_y'].notnull()].groupby('day_x').aggregate({'uid': lambda x: x.nunique()})
one_day_remain_user.reset_index(inplace=True)
one_day_remain_user.head()

合并前面两步的结果，计算最终留存

merge_one_day = pd.merge(init_user, one_day_remain_user, on=['day_x'])
merge_one_day['one_remain_rate'] = merge_one_day['uid_y'] / merge_one_day['uid_x']
merge_one_day['one_remain_rate'] = merge_one_day['one_remain_rate'].apply(lambda x: format(x, '.2%'))
merge_one_day.head(20)

b）多日留存计算
方法一：
计算日期差，为后续做准备

merge_all = pd.merge(login_data, login_data, on=['uid'], how='left')
merge_all['diff'] = (merge_all['dt_ts_y'] - merge_all['dt_ts_x']).map(lambda x: x.days)#使用map取得具体数字
merge_all.head()

计算第n天的留存人数，n=0,1,6,13。需要先进行筛选再进行计数，仍然使用nunique

diff_0 = merge_all[merge_all['diff'] == 0].groupby('day_x')['uid'].nunique()
diff_1 = merge_all[merge_all['diff'] == 1].groupby('day_x')['uid'].nunique()
diff_6 = merge_all[merge_all['diff'] == 6].groupby('day_x')['uid'].nunique()
diff_13 = merge_all[merge_all['diff'] == 13].groupby('day_x')['uid'].nunique()
diff_0 = diff_0.reset_index()#groupby计数后得到的是series格式，reset得到dataframe
diff_1 = diff_1.reset_index()
diff_6 = diff_6.reset_index()
diff_13 = diff_13.reset_index()

对多个dataframe进行一次合并

liucun = pd.merge(pd.merge(pd.merge(diff_0, diff_1, on=['day_x'], how='left'), diff_6, on=['day_x'], how='left'), diff_13, on=['day_x'], how='left')
liucun.head()

对结果重命名，并用0填充na值

liucun.columns=['day', 'init', 'one_day_remain', 'seven_day_remain', 'fifteen_day_remain']
liucun.fillna(0, inplace=True)
liucun.head(20)

得到的结果和SQL计算的一致，同样省略了百分比转换的代码。

方法二：

def cal_n_day_remain(df, n):
    dates = pd.Series(login_data.dt_ts.unique()).sort_values()[:-n]#取截止到n天的日期，保证有n日留存
    users = [] #定义列表存放初始用户数
    remains = []#定义列表存放留存用户数
    for d in dates:
        user = login_data[login_data['dt_ts'] == d]['uid'].unique()#当日活跃用户
        user_n_day = login_data[login_data['dt_ts']==d+timedelta(n)]['uid'].unique()#n日后活跃用户
        remain = [x for x in user_n_day if x in user]#取交集
        users.append(len(user))
        remains.append(len(remain))
    #一次循环计算一天的n日留存 
    #循环结束后构造dataframe并返回
    remain_df = pd.DataFrame({'days': dates, 'user': users, 'remain': remains})
    return remain_df

one_day_remain = cal_n_day_remain(login_data, 1)
seven_day_remain = cal_n_day_remain(login_data, 6)
fifteen_day_remain = cal_n_day_remain(login_data, 13)

liucun2 = pd.merge(pd.merge(one_day_remain, seven_day_remain[['days', 'remain']], on=['days'], how='left'), fifteen_day_remain[['days', 'remain']], on=['days'], how='left')
liucun2.head(20)

简介：
pandasql是由Yhat编写的模拟R包sqldf的python第三方库，能够让我们用SQL的方式操作pandas的数据结构。

安装：
在命令行中使用pip install pandasql即可实现安装。

使用：
从pandasql包中可以导入sqldf，这是我们核心要使用的接口。它接收两个参数，第一个是合法的SQL语句。SQL具有的功能，例如聚合，条件查询，联结，where条件，子查询等等，它都支持。第二个是locals()或者globals()表示环境变量，它会识别目前已有的dataframe作为第一个参数中的表名。

import pandas as pd
from pandasql import sqldf#d导入sqldf

data = pd.read_excel('orderamt.xlsx')#读取文件获得dataftame,也可以用其他方式取得

sql = "select * from data limit 10"#SQL语句，表名就是dataframe的名字
result = sqldf(sql, locals())#result也是dataframe类型的，
result.head()

按月聚合：

sql2 = "select strftime('%Y-%m', dt) as mon,\
sum(amt) as amt from data group by strftime('%Y-%m', dt)"
result2 = sqldf(sql2, locals())
result2

官方文档中说为了避免冗余的调用可以对sqldf进行一层封装，用pysqldf代替，只需对其传入一个SQL语句参数即可，如下面代码所示。但我试了试不封装也是可以的。前面的代码可以省略locals()或者globals()。

pysqldf = lambda q: sqldf(q, globals())
result = pysqldf(sql)#传入合法的SQL语句

用自定义的SQL获取dataframe之后，可以继续用pandas处理，也可以直接进行保存。

1）pandas操作MySQL数据库

read_sql：
这个函数的作用是，对数据库中的表运行SQL语句，将查询结果以dataframe的格式返回。另外还有两个read_sql_table，read_sql_query，通常使用read_sql就够了。主要的两个参数是合法的SQL语句和数据库连接。数据链接可以使用SQLAlchemy或者字符串。其他可选参数可以参考官方文档。

to_sql：
这个函数的作用是，将dataframe的结果写入数据库。提供表名和连接名即可，不需要新建MySQL表。
用操作MySQL举例如下，需提前安装好sqlalchemy，pymysql，直接pip安装即可，需要注意engine的格式。

#read_sql举例
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/test")
#数据库的用户名：root，密码：123456，host:127.0.0.1，端口：3306，数据库名：test
#engine = create_engine("dialect+driver://username:password@host:port/database")
sql = "select * from t_user"
df = pd.read_sql(sql, engine)
df

#to_sql举例
df2 = df.head()
df2.to_sql('t_user_2', engine, index=None)

t_user_2是结果表名，不用事先在数据库中建立，否则会报错，表的字段名就是dataframe的列名。engine是上文创建的连接。df2就是期望写入的数据，这里只选取了上文df的前五行。需要注意如果不加index=None参数，会把索引也写进去，多一列index。