数据分析真实案例：excel&sql&pandas实现数据透视表功能

一、案例背景

• 接到需求

事情是这样的，一个眼光明媚的早上，领导看到昨天PV增长明显，于是就提出了如下需求：

ok,get到需求后，立马行动起来！

• 分析需求

为什么pv会增长明显呢？首先，依据拆分的思想，网站有很多个板块，所以将总体pv拆分成n个板块（可以根据经验选择主要的板块进行分析），对这n个板块逐个进行分析；接着，由于孤数不立的原则：单看一天数据，无法判断具体哪个板块增长，哪个板块降低，因此，主要从日期、板块这两个维度进行分析。

• 根据分析结果提数

”无情的提数机器“来了！首先确定需要用到的表：只需要访问日志表。

原始数据形式

简单看一下日志表中我们需要的两个主要字段（日期、网页链接）：

目标数据形式

我们需要提出来的效果是这样的（数据当然不能给别人看咯）：

• 提数sql

太年轻的小菜我，紧急情况下，没想到用数据透视表，像个沙雕一样，写了屎一样的sql。（为了让大家引以为戒，我脸也不要了，展示出来吧）



没错，查9天的数据，我用了8个left join…

• 数据可视化形成报告

为了让别人一眼就能从这近1000条数据中看出来谁增高了，谁降低了，那必须画个图，然后根据图进行描述分析，得出结果。（这段不重要，就略过了）

二、使用数据透视表

用又臭又长的sql得出报告后，总觉得心里不舒服，怎么能写这么烂的sql，于是，突然想到了数据透视表。

• excel实现数据透视表

excel实现数据透视表就比较理所当然啦，因为数据透视表就是excel里面的概念。

第一步：先直接简单的select查出近9天的数据并保存为excel

第二步：数据透视表的使用

好了，excel的数据透视表就是这么简单好用，顺便也出来图了，但是很丑，可以直接改改，或者copy数据自己用工具作图分析。

• pandas实现数据透视表

第一步：先直接简单的select查出近9天的数据并保存为excel(如果本地可以直接连生产的数据库，可以直接pd.read_sql()一步到位获取数据)

第二步：实现pandas的数据透视表功能

import pandas as pd
data = pd.read_excel('xxx.xlsx')
data_result = pd.pivot_table(data, index=["page"], columns=["date"], values=["page"], aggfunc={"page": "count"})
print(data_result)


• sql实现数据透视表功能

sql一步到位，为了保护隐私和更方便大家看方法，我就简化了一下sql，如下：

select page,
count(case when date = '2020-07-01' then page end) as "2020-07-01",
count(case when date = '2020-07-02' then page end) as "2020-07-02",
count(case when date = '2020-07-03' then page end) as "2020-07-03",
from table_test
group by page


over!