Hive之同比环比的计算
数据分析挖掘与算法
共 4045字,需浏览 9分钟
· 2022-02-17
关注公众号:大数据技术派
,回复: 资料
,领取1024G
资料。
目录
同比环比的计算
测试数据
销售量的月年占比
同比环比
同比环比的计算
测试数据
1,2020-04-20,420
2,2020-04-04,800
3,2020-03-28,500
4,2020-03-13,100
5,2020-02-27,300
6,2020-01-07,450
7,2019-04-07,800
8,2019-03-15,1200
9,2019-02-17,200
10,2019-02-07,600
11,2019-01-13,300
CREATE TABLE ods_saleorder (
order_id int ,
order_time date ,
order_num int
)ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
;
LOAD DATA LOCAL INPATH '/Users/liuwenqiang/workspace/hive/saleorder.txt' OVERWRITE INTO TABLE ods.ods_saleorder;
销售量的月年占比
关联实现
select
a.m_num,a.cmonth,b.y_num,b.cyear,round( m_num / y_num, 2 ) AS ratio
from(
select
sum(order_num) as m_num,
DATE_FORMAT(order_time,'yyyy-MM') as cmonth
from
ods_saleorder
group by
DATE_FORMAT(order_time,'yyyy-MM')
) a
inner join
(
select
sum(order_num) as y_num,
DATE_FORMAT(order_time,'yyyy') as cyear
from
ods_saleorder
group by
DATE_FORMAT(order_time,'yyyy')
) b
on
substring(a.cmonth,1,4)=b.cyear
;
窗口实现
SELECT
order_month,
num,
total,
round( num / total, 2 ) AS ratio
FROM
(
select
substr(order_time, 1, 7) as order_month,
sum(order_num) over (partition by substr(order_time, 1, 7)) as num,
sum(order_num) over (partition by substr( order_time, 1, 4 ) ) total,
row_number() over (partition by substr(order_time, 1, 7)) as rk
from ods_saleorder
) temp
where rk = 1;
同比环比
与上年度数据对比称"同比",与上月数据对比称"环比"。相关公式如下:
同比增长率计算公式
(当年值-上年值)/上年值x100%
环比增长率计算公式
(当月值-上月值)/上月值x100%
####lead lag 的实现
这里我们就用环比做个例子,同比类似
select
now_month,
now_num,
last_num,
round( (now_num-last_num) / last_num, 2 ) as ratio
FROM(
select
now_month,
now_num,
lag( t1.now_num, 1) over (order by t1.now_month ) as last_num
from
(
select
substr(order_time, 1, 7) as now_month,
sum(order_num) as now_num
from ods_saleorder
group by
substr(order_time, 1, 7)
) t1
) t2;
我们看到有null 值,这里我们可以使用,lag的默认值做一次优化
select
now_month,
now_num,
last_num,
-- 分母是0的话返回值是null
nvl(round( (now_num-last_num) / last_num, 2 ),0)as ratio
FROM(
select
now_month,
now_num,
lag( t1.now_num, 1,0) over (order by t1.now_month ) as last_num
from
(
select
substr(order_time, 1, 7) as now_month,
sum(order_num) as now_num
from ods_saleorder
group by
substr(order_time, 1, 7)
) t1
) t2;
其实到这里我们就处理完了,但是这样真的对吗,我们看到'2020-01' 的last_num 是800 也就是'2019-04',其实到这里我们就明白了,我们的数据是不连续的,所以我们这样计算是不行的,如果每个月都齐全,都有数据lag(num,12)就可以。
那就只能做自关联了,这样的话我们可以对时间做精准的限制
自关联的实现
with a as (
select
now_month,
now_num,
substr(date(concat(now_month,'-','01')) - INTERVAL '1' month, 1, 7) as last_month
from(
select
substr(order_time, 1, 7) as now_month,
sum(order_num) as now_num
from ods_saleorder
group by
substr(order_time, 1, 7)
) tmp
)
select
a1.now_month,a1.now_num,a1.last_month,a2.now_num,
nvl(round( (a1.now_num-a2.now_num) / a2.now_num, 2 ),0) as ratio
from
a a1
inner join
a a2
on
a1.last_month=a2.now_month
;
这里的时间计算INTERVAL 你也可以换成其他函数
with a as (
select
now_month,
now_num,
substr(add_months(concat(now_month,'-','01'),-1), 1, 7) as last_month
from(
select
substr(order_time, 1, 7) as now_month,
sum(order_num) as now_num
from ods_saleorder
group by
substr(order_time, 1, 7)
) tmp
)
select
a1.now_month,a1.now_num,a1.last_month,nvl(a2.now_num,0),
nvl(round( (a1.now_num-a2.now_num) / a2.now_num, 2 ),0) as ratio
from
a a1
left join
a a2
on
a1.last_month=a2.now_month
;
猜你喜欢
评论
如何画出漂亮的神经网络图?神经网络可视化工具集锦搜集
点击上方“小白学视觉”,选择加"星标"或“置顶”重磅干货,第一时间送达1. draw_convnet一个用于画卷积神经网络的Python脚本https://github.com/gwding/draw_convnet2. NNSVGhttp://alexlenail.me/NN-SVG/LeNet.
小白学视觉
0
三个优秀的PyTorch实现语义分割框架
点击上方“小白学视觉”,选择加"星标"或“置顶”重磅干货,第一时间送达转自 | 机器学习AI算法工程使用的VOC数据集链接开放在文章中,预训练模型已上传Github,环境我使用Colab pro,大家下载模型做预测即可。代码链接: https://github.com/lixiang007
小白学视觉
0
如何看待稚晖君的时间管理水平?
点击上方“小白学视觉”,选择加"星标"或“置顶”重磅干货,第一时间送达计算机视觉life整理原文链接:https://www.zhihu.com/question/491456524/answer/2183081310稚晖君究竟是如何安排业余时间去做这么多高水平的项目?而且每个项目的用时也很少,普通
小白学视觉
2
如何确定神经网络的层数和隐藏层神经元数量?
点击上方“小白学视觉”,选择加"星标"或“置顶”重磅干货,第一时间送达公众号:尤而小屋作者:呦呦鹿鸣编辑:Peter大家好,我是Peter~关于神经网络中隐藏层的层数和神经元个数充满了疑惑。刚好看到一篇文章很好地答疑了,分享给大家~https://zhuanlan.zhihu.com/p/10041
小白学视觉
0
“海优最初弄出来可并不是为了给大佬弟子开后门的, 学术界现在几乎不存在任何套利空间,不要相信有什么办法可以投机取巧”
点击上方“小白学视觉”,选择加"星标"或“置顶”重磅干货,第一时间送达知乎问题:为什么部分同学倾向土博+国外博后而不是直接国外读博?感觉身边土博越来越多,基本都是土博➕海博后,申国外phd的明显减少(相比较疫情之前几年)知乎Tianteman: 这个问题下,可以看得出一部分国内硕博信息检索能力差的离
小白学视觉
0
神经网络之CNN与RNN的关系
点击上方“小白学视觉”,选择加"星标"或“置顶”重磅干货,第一时间送达1、CNN介绍CNN是一种利用卷积计算的神经网络。它可以通过卷积计算将原像素很大的图片保留主要特征变成很小的像素图片。本文以李宏毅老师ppt内容展开具体介绍。1.1 Why CNN for Image①为什么引入CNN ?图片示意
小白学视觉
0
真高!比亚迪员工爆料比亚迪在越南的薪资水平:基本工资480万,全勤奖35万,交通补助20万,餐补110万,每周6天,每天10小时
上一篇:某大公司为逼迫员工离职,竟然把他的工位安排到厕所旁,没想到他直接开始记录领导的如厕时间,还发到公司大群...对此,你怎么看?--完--PS:欢迎在留言区留下你的观点,一起讨论提高。如果今天的文章让你有新的启发,欢迎转发分享给更多人。全文完,感谢你的耐心阅读。如果你还想看到我的文章,请一定给本
开发者全社区
0
太敢穿了!透视纱裙!性感火辣的身材
绝了呀今天的厂花:吴宣仪1995年1月26日,吴宣仪出生于海南省海口市,中国内地流行乐女歌手、影视演员。2016年2月,吴宣仪随宇宙少女发行首张迷你专辑正式出道。2018年4月,她参加《创造101》综艺选秀,获得第二名,成功加入火箭少女101组合。吴宣仪的颜值一直备受称赞,她的五官立体精致,皮肤白皙
逆锋起笔
0