分类目录数据分析

PowerBI 连接HIVE数据库

  1. 由于目前PowerBI没有直接链接HIVE数据库的工具,因此首先需要下载两个驱动(第一个驱动貌似用不上,是适用于微软的hive数据库的),下载后分别安装:
    1. Microsoft® Hive ODBC Driver,下载地址:点击下载


    2. ClouderaHiveODBC6,下载地址:点击下载
  2. 在电脑中搜索ODBC,打开64-bit ODBC Administrator


  3. 选择”系统DSN”选项卡,点击”添加”,选择cloudera ODBC…,点击完成。


  4. 具体配置如下:


  5. 完成点击test,弹出对话框succeed说明配置成功了。
  6. 这时候在PowerBI中,点击获取数据→其他→ODBC,数据源选择刚才配置好的hive数据库


  7. 在弹出对话框中,数据库选项卡中输入用户名和密码,点击连接即可。

Python搭建本地爬虫数据库之-mysql8安装

1、去:https://dev.mysql.com/downloads/file/?id=476233下载mysql8

2、解压后然后将mysql-8.0.11-winx64重命名为mysql8,并将解压后的文件放到D:\Program Files

3、在我的电脑——属性——高级系统设置——环境变量中添加路径:D:\Program Files\mysql8\bin

4、在mysql根目录下新建my.ini初始化文件,粘贴以下代码保存。

  1. [mysqld]  
  2. # 设置3306端口  
  3. port=3306  
  4. # 设置mysql的安装目录  
  5. basedir=D:\\Program Files\\mysql8  
  6. # 设置mysql数据库的数据的存放目录  
  7. datadir=D:\\Program Files\\mysql8\\Data  
  8. # 允许最大连接数  
  9. max_connections=200  
  10. # 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统  
  11. max_connect_errors=10  
  12. # 服务端使用的字符集默认为UTF8  
  13. characterset-server=utf8  
  14. # 创建新表时将使用的默认存储引擎  
  15. default-storage-engine=INNODB  
  16. # 默认使用”mysql_native_password”插件认证  
  17. default_authentication_plugin=mysql_native_password  
  18. [mysql]  
  19. # 设置mysql客户端默认字符集  
  20. defaultcharacterset=utf8  
  21. [client]  
  22. # 设置mysql客户端连接服务端时默认使用的端口  
  23. port=3306  
  24. defaultcharacterset=utf8  

5、以管理员身份运行cmd,cd进入D:\Program Files\mysql8\bin

6、执行初始化命令: mysqld –initialize –console 这里注意保存图片中高亮的部分,后面登陆需要使用该密码,如果不小心关闭了窗口,删掉初始化的 datadir 目录,再执行一遍初始化命令,又会重新生成。


7、安装mysql服务。在cmd窗口执行:mysqld –install [服务名],服务名称可不填,如果安装了多个版本的mysql则可以将服务名称填写为区别原来的即可。

8、启动mysql服务: net start mysql


9、修改密码: mysql -u root -p 输入密码登陆,然后执行 :

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘新密码’; 

10、创建数据以备用:create database spyder_data;


MySQL实现移动平均计算

我们有这样的一个数据表test_sales,记录了每日的销售产品的销售数量数据。表中date表示销售日期,num表示销售数量,avg_5就是想要获得的5日移动平均销售量数据(这里直接列出是方便后期计算结果验证)。

Emmm,据说oracle有窗口函数可以直接出结果,体现出了付费产品的优势。Mysql则可以通过关联表本身,创造辅助列的办法来解决。

先看代码:

  1. SELECT t.date,t.num,t.avg_5,  
  2. case when t.date<‘2018-01-05’ then  else sum(t.avg_cnt)/count(1) end as avg_num  
  3. from   
  4. (select b.date,b.num,b.avg_5,a.num as avg_cnt from test_sales as a   
  5. right join test_sales b on b.date between a.date and date_add(a.date,interval 4 DAY)  
  6. order by b.date) t  
  7. group by t.date,t.num,t.avg_5  
  8. order by t.date;  

解释一下:

首先,看第4-6行,我们用test_sales关联自身,join的时候使用right join on between,可以理解为将a表的每一条数据都增加了4条记录,这四条记录的日期分别被加上了1/2/3/4天。此时,再使用right join时,从1月5日起,一条b表的记录都可以对应到5条a表的1月5日的记录。

接下来,对每一个b表的date而言,每天都会有对应5天内的avg_cnt数据。因此,按b.date对avg_cnt求均值即为五日移动平均值。

这是结果,avg_num为计算出的5日移动平均值,avg_5是原始数据,二者结果一致(由于保留小数位数问题有些不同)。

其中:case when t.date<‘2018-01-05’ then 是为了排除没有5日内移动均值的数据,因为数据从1月1日起,因此前4个日期是没有5日移动均值的。

Excel获取不重复数据结果(二)

Excel获取不重复数据结果(一)中介绍了利用公式获取不重复用户名的方法。通常情况下,我们希望更进一步:利用这些获取到的名称,来作为筛选项,然后查看每个筛选项的细项指标结果。例如,查看每个用户下单的订单明细:

操作方法:

1、在文件→选项→自定义功能区中,勾选开发工具选项

2、在开发工具选项卡中选择插入→列表框

3、选择列表框,右键选择设置控件格式,输入内容如下(即我们将数据来源中的数据放到控件列表中,控件将根据我们的选择将列表中人名对应的数字映射到E1单元格):

4、在A列添加数据序号,F1单元格输入:=VLOOKUP(E1,$A$2:$B$20,2,0)。用以返回选择的人名。

5、组织H1:L2表格,在H3单元格输入:=IFERROR(INDEX(订单明细!$A$1:$A$32,SMALL(IF(订单明细!$B$1:$B$32=$F$1,ROW(订单明细!$A$1:$A$32),99999),ROW(A1))),””) ,按ctrl+shift+enter结束。下拉即可获取F1单元格人名所对应的订单号明细。

6、由于订单号唯一,我们就可以利用简单点的offset函数获取订单对应的其他信息,在I3输入:=IFERROR(OFFSET(订单明细!$A$1,MATCH($H3,订单明细!$A$2:$A$32,0),COLUMN(A1),1,1),””) ,填充其他单元格即可。

主要难点为第5步中的公式,解读如下:

1、利用公式IF(订单明细!$B$1:$B$32=$F$1,ROW(订单明细!$A$1:$A$32),99999),返回一个列表LIST,即如果订单明细!$B$1:$B$32中的名称和F1相同,则返回该名称所在的行数,否则返回一个很大的值

2、再利用small函数,获取上面LIST中的第k个最小值,即该名称第k次出现的行号

3、利用index返回订单明细表中的该位置的订单号数据。

4、使用iferror函数进行美化。

百度网盘,文件下载:

链接: https://pan.baidu.com/s/10eWwu6KuvAahrIXT_eXBFQ

密码: jmbh

Excel获取不重复数据结果(一)

为一致为获取不重复的数据记录,可以利用Excel的高级筛选、数据透视表等功能进行操作。当高级筛选功能的好处在于快速,易操作,可以操作多列;坏处在于复用性差,数据量变更后得重新筛选。

我们可以利用公式完成对数据的去重操作,如下表中获取用户名称列表。

操作方法:

1、上表中,明细数据表命名为”订单明细”

2、在结果表中,B2中,输入=IFERROR(INDEX(订单明细!$B$2:$B$32,MATCH(,COUNTIF($B$1:B1,订单明细!$B$2:$B$32),)),””),按ctrl+shift+enter结束,向下拉到结果显示为空时,表示已经获取了所有用户名称。

以上公式的原理在于:

首先使用COUNTIF计算在$B$2:$B$32区域中是否存在$B$1:B1区域的值,如果有则返回1,没有则返回0。此处返回一个1和0匹配列表LIST,1表示这个用户名已经出现在结果区域了

再利用match去匹配第一个0在LIST中的位置,返回具体的位置代号

最后使用INDEX函数,获取订单明细!$B$2:$B$32对应LIST位置的未出现过的用户名

IFERROR为美化修饰函数,使表格看起来更整洁。

文档下载请参见第二篇。

Excel获取不重复数据结果(二)

Excel制作二级联动菜单

在利用Excel制作交互式报表时,通常可能会用到二级联动菜单。如,以下根据辖区的选择,区域会出现各自辖区下的不同区域以供选择。

操作方法:

1、组织好码表数据和目标区域,如下:

2、在辖区选择区域G5设置数据有效性,数据→数据验证→数据验证→有效性条件允许中选择序列,来源选择辖区数据所在单元格。点击确定以完成设置。

3、设置联动的二级菜单:在选择区域部分重复操作2的步骤,来源输入公式:=OFFSET($B$4,1,MATCH($G$5,$B$4:$D$4,0)-1,50,1)。确定即制作完成。

此公式的五个参数分别表示:

$B$4:从$B$4单元格设置偏移

1:向下偏移1个单元格,作为开始取值的区域

MATCH($G$5,$B$4:$D$4,0)-1:匹配G5单元格所选内容所在的位置以确定向右的偏移单元格数

50:取偏移后50行的数据,这里还可以根据G5的内容去判断要向下取多少行,简便起见可以设置一个大于任何一个二级菜单数量的值

1:去偏移后1列的数据

百度网盘,文件下载:

链接https://yun.baidu.com/share/link?shareid=2081359417&uk=2248492829

密码:

使用Excel公式建立报表目录

Excel在做报告方面如果组织得当,可能不输于PPT,特别是在数据类报告(报表)方面。

但当数据报告内容太多时,难免会翻花眼。例如做调研问卷共调查了100个题目,现在对这100个题目进行频数统计,如果想要快速查看某个题目的统计结果,制作一个包含每个题目的目录链接就显得非常必要了。先看结果:目录表中每个标题点击后都可以跳转到相对应的题目处去。

操作方法:

1、首先制作类似结果表的数据报告表,并在每一项内容前添加能辨别该区域的标题。如上图中的”0、代号含义额对照表”和”1、代号含义对照表2″,注意标题中需要包含一个特殊字符(如”、”,不会出现在正文内容中的一个字符),方便后续进行定位。

2、新建表格,命名为目录表。在B列中输入,=IF(ISNUMBER(FIND(“、”,结果表!A1)),ROW(结果表!A1),””),公式表示:去结果表中A列相同的行查找”、”:如果找到,则返回当前行行号;如果没有找到就返回空。

3、将找到的行号集中起来。在C列中输入,=IFERROR(SMALL(B:B,ROW()),””),表示SMALL函数用于找到第k个最小值,因此C2就表示找第二小的值。

4、有了3中标题所在的行号,就可以利用HYPERLINK+ INDIRECT函数制作目录了,在D列中输入,=IF(ISNUMBER(C1),HYPERLINK(“#结果表!A”&C1,INDIRECT(“结果表!A”&C1)),””),所有行下拉即可。

最后进行一些格式美化的工作即可。

百度网盘,文件下载:

链接:https://pan.baidu.com/s/1sj6OE8P

密码:无

Python产品组合售卖策略算法设计

假设我们有ABCDEF共6个产品售卖,利润率分别如下表。假设我们的客户统一都是只有50的资产,那么我们在保证每个客户贡献有35%的利润率的情况下,可以给客户哪些产品组合(即限定客户只能买某些产品多少件,以避免客户购买利润率低的产品过多导致单个客户利润率不足)?

首先我们考虑上述限定条件的数学逻辑,以上条件符合以下限定条件:

Xi表示每个产品的价格。

pi表示购买的产品数量。

ri表示每个产品的利润率。

这是一个线性规划的问题,但我们先不考虑用运筹学的方法来解决问题。而通过手写算法来实现。

1、考虑目标利润35%,那么产品组合方案中必定存在利润率在35%或以上的产品。因此将产品数据分为2组:35%及以上为一组,35%以下为第二组。

2、计算单个产品在50元以内可以卖多少个,并把个数和总价信息分别补充到一组和二组中,形成单个产品在50元价格内的可购买数量列表。

3、通过逐一添加一组的数据和二组的数据组合,判断其是否满足前面提到的限定条件,如果满足则返回当前的产品组合。保存并存储即可。

将数据整理如下并存储在data工作表中:

使用pandas将数据读取为data,设定客户资产balance和利润率目标rate:

定义一个函数data_cls,完成1和2的操作。返回的结果u_data和o_data分别表示小于和大于等于rate目标利润率的条件下,在帐户资产为balance的条件下可以购买的所有单个产品的数量和总价组合信息。Max_n是50块可以买到的最多多少个产品,以此来确定我们最后组合产品的数量最大为多少时,循环迭代停止。

完成了单个产品的组合分类,接下来就是对每个分类的组合进行判断。定义一个判断函数cal_comb来计算组合策略是否符合预期结果。

然后就是进行穷举计算了:

以上就利用最笨的办法完成了对产品组合售卖策略的算法设计。逻辑上没问题,计算上老费力了。我的老爷机根本算不出来,想想根本58取25的组合,百亿不止的数据集。

百度网盘,文件下载:

链接: https://pan.baidu.com/s/1qkLMVY3UTN5yglUHGuuMpg

密码: 568t

Excel条件格式制作甘特图

我们知道Excel条件格式可以做很多预警、热力图等相关数据可视化,因此在预警的一个特殊领域——项目管理中,条件格式也可以发挥其作用,可以利用条件格式制作一个功能简便的甘特图。

结果如下:

操作方法:

1、根据项目需要编制项目时间表如下:

2、首先填充计划时间的进度条,选中H3:AK11区域,点击开始→条件格式→新建规则→使用公式确定要设置格式的单元格。

3、在”为符合此公式的值设置格式”中,输入:=AND(H$2>=$C3,H$2<=$D3),设置格式填充为淡绿色,以确定计划时间段

4、重复2和3的步骤,在”为符合此公式的值设置格式”中,输入:=AND(H$2>=$E3,H$2<=$F3),设置格式填充为较深的绿色

以上即为最终的效果,但我们发现一个问题:项目3中,实际结束时间超过了计划时间,这种情况下我们希望将超过的时间突出展示,可以执行以下操作:

5、新建规则,公式输入:=AND($F3>$D3,H$2>$D3,H$2<=$F3),设定填充为橘红色。即可突出展示出6月26和27日超过计划时间

百度网盘,文件下载:

地址:https://pan.baidu.com/s/1gG6s2P_WAmmolQhpSXaN0w

提取码:itxx

Excel基于历史月份数据的条件格式预警

在市场研究或产品监测中,通常会遇到对同一个数据连续多期的表现的预警监测。如,连续两期低于某个值、连续三期低于某个值等等。

仍以”单一目标值的条件格式预警“中的区域和产品为例。假设我们连续监测了8个区域5个产品在1-3月的销售情况,对于各个产品,如果某区域的连续2个月销量低于各区域当月平均值则用黄色预警,如果连续3个月均低于当月平均值则用红色预警。最终结果如下:

操作方法:

1、选中3月的数据区域C21:G26,点击开始→条件格式→新建规则→使用公式确定要设置格式的单元格

2、在”为符合此公式的值设置格式”中,输入:=SUM(IF(C3<C$9,1,0),IF(C12<C$18,5,0),IF(C21<C$27,10,0))=15,点击格式,设置填充为黄色。用以设置在2月和3月连续两个月均低于平均值的数据预警

3、上述步骤确定完成后,再重复步骤1,在设置公式时输入:=SUM(IF(C3<C$9,1,0),IF(C12<C$18,5,0),IF(C21<C$27,10,0))=16,点击格式,设置填充为红色。用以设置在1-3月均低于平均值的数据预警

至此我们可以观察验证公式设置是否正确。以上问题的解决思路主要为:

我们判断某个月的,某区域的产品销售量是否小于所有区域的均值,如果小于则返回一个特定值,否则返回0。我们通过对不同月份的判断结果返回不同的值,然后对这些值相加即可知道某个区域是在哪几个月份销售额小于均值。

如我们设置某区域一月销售额小于均值则返回1,二月小于均值则返回5,三月则返回10,将三个月返回值相加,得到最终结果。通过对最终结果的判断,我们知道15则表示二三月份同时低于均值,16则表示一二三月份同时低于均值。

文件下载,百度网盘:https://pan.baidu.com/s/1NGSqr663kRUCvOUuCb8JtQ 提取码:umk6