hive round函数不起作用_[Hive]Hive SQL案例, 对零售行业KPI的多维度分配, 年度复盘,计算同比环比....

某零售行业拥有多家连锁零售门店，现为12月份，我们现在需要调出历史零售数据，做销售表现的对比，复盘分析今年的销售表现，看去年制定的KPI现在达标情况、做同比环比。

数据表的关系如下图：

在hive中创建表：

CREATE 

导入数据：

LOAD 

先把表聚合起来，方便后续使用：

hive

显示创建成功后验证：

select 

能查看到五行记录

hive

我们去开始提取和计算这287942条数据：

这里假设去年老板用历史占比法去制定每个月的KPI。

历史占比法：

即根据前一年，或者前几年中的每个维度的销售额对于整体销售额的占比，去计算将来目标该如何分配到每个月。

举例来说，现在将目标拆分成一个月份维度，如下图第三列是2006年每个月的销售额，第四列是2006年整年销售额，老板希望2007年整体销售额提升20%，即：13682815 x 1.2 = 16419378，按2006年每个月占全年销售额的占比，用总KPI x 该占比分配到2007年每个月的KPI，即第六列next_year_kpi

一维分配KPI：

下面计算上面例子中的一维分配KPI：

Hive SQL实现代码：

hive

Hive SQL运行验证结果：

现在是2007年年末，已有2007年全年销售数据，要去计算每个月的达标率，这里还是会用到上面代码的结果，并用lag()函数做位置偏移，使2006年算的the_year_kpi均往下移至和2007年对齐，再算达标率：

预计效果如图：

Hive SQL实现代码：

hive

Hive SQL运行验证结果：

---

二维分配KPI：

假如我们希望再细分维度，希望对每个门店的每个月做分配KPI，同样是用历史占比法。

但要注意的是细分维度后，对于门店，可能有特殊情况，比如说：门店在2006年的某个月才开始开业，或者2007年的某个月或某几个月暂定停业。

这样会缺失对应的月份和门店数据，lag()偏移多少的参数无法确认。所以这里就要用两个表去链接：

先创建视图

hive

创建成功后，查看2006年整体销售额，并算出x1.2是多少。 因为hql不能select @变量=xxx，所以只能用set，先用select计算出数值，再用set定义。

这里可以对比一下MySQL和Hive SQL

Hive SQL：

hive

Hive SQL运行效果：

MySQL：

select 

MySQL在MySQLWorkbeach的效果如图：

同时可以用order by location,yearmonth去更方便去看每个门店在每个月的销售额和达标率，或者用where去筛选门店看某个门店的两年对比情况。

举个例子：

select 

这里可以看出RM这家店有可能是2006年6月新开业的，在2006奶奶1月到5月都没有数据，用历史占比发去计算2007年的6月-12月kpi，可以看出该门店的达标率情况不错。

---

计算每个月销售额同比、环比：

期望达到效果：

先存一个2006年和2007年的表：

hive

同样可以用lag() 去算同比、环比

hive

Hive SQL运行验证结果：

这里我们可以看到，除了一月和三月，其他都同比上升。 2月同比上升幅度最大，超过了50%。

注意千万不要用where筛选2007年的数据，同比就会显示不出来，因为算不了2006年的结果，如图：