【大数据哔哔集20210128】使用Hive计算环比和同比

大数据真好玩 2021-02-23 16:42:42
hive 数据 使用 计算 大数


什么是绝对值同比

本期数据-同期数据/|同期数据| 例:2019年1月1日的gmv -2018年1月1日的gmv/|2018年1月1日的gmv|

什么是绝对值环比

本期数据-上期数据/|上期数据| 例:2019年2月2日的gmv -2018年2月1日的gmv/|2018年2月1日的gmv|

数据集准备

建表语句

create table new_table(
dt string,
area string,
province string,
saleroom int
);

数据准备:

insert into new_table values('2017-12-01', 'hd', 'sh','3600000');
insert into new_table values('2017-12-02', 'hd', 'js','2800000');
insert into new_table values('2017-12-03', 'hd', 'zj','4500000');
insert into new_table values('2017-12-04', 'hb', 'bj','3000000');
insert into new_table values('2017-12-05', 'hb', 'tj','2800000');
insert into new_table values('2018-12-01', 'hd', 'sh','3000000');
insert into new_table values('2018-12-02', 'hd', 'js','2000000');
insert into new_table values('2018-12-03', 'hd', 'zj','2500000');
insert into new_table values('2018-12-04', 'hb', 'bj','2600000');
insert into new_table values('2018-12-05', 'hb', 'tj','1500000');

同比计算

with tmp as (
select
dt,
area,
province,
saleroom,
lag(saleroom,1,0) over(partition by concat(month(dt),"-",day(dt)),area,province order by dt asc) pre_sale
from new_table)
select
dt,area,province,saleroom,pre_sale,
if(round((saleroom-pre_sale)/abs(pre_sale)*1.00,2) is null,100,round((saleroom-pre_sale)/abs(pre_sale)*1.00,2)*100)
from tmp;

环比计算

with tmp as (
select
dt,
area,
province,
saleroom,
lag(saleroom,1,0) over(partition by concat(month(dt),"-",day(dt)),area,province order by dt asc) pre_sale
from new_table)
select
dt,area,province,saleroom,pre_sale,
if(round((saleroom-pre_sale)/abs(pre_sale)*1.00,2) is null,100,round((saleroom-pre_sale)/abs(pre_sale)*1.00,2)*100)
from tmp ;

本文分享自微信公众号 - 大数据真好玩(havefun_bigdata)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间: 2021-01-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

版权声明
本文为[大数据真好玩]所创,转载请带上原文链接,感谢
https://cloud.tencent.com/developer/article/1791915

  1. J2EE
  2. Vue uses SDK to upload seven cows
  3. k8s-dns
  4. JavaScript mailbox verification - regular verification
  5. k8s-dashboard
  6. How many questions can you answer?
  7. Spring annotation -- transactional
  8. [k8s cluster] construction steps
  9. k8s-kubeadm
  10. k8s-etcd
  11. Using HashMap to improve search performance in Java
  12. There is no class problem when Maven publishes jar package
  13. JavaScriptBOM操作
  14. J2EE
  15. k8s-prometheus-memory
  16. k8s-prometheus disk
  17. k8s-prometheus
  18. JavaScript BOM operation
  19. k8s-prometheus-memory
  20. k8s-prometheus disk
  21. k8s-prometheus
  22. Linux Disk Command
  23. Linux FS
  24. 使用docker-compose &WordPress建站
  25. Linux Command
  26. This time, thoroughly grasp the depth of JavaScript copy
  27. Linux Disk Command
  28. Linux FS
  29. Using docker compose & WordPress to build a website
  30. Linux Command
  31. 摊牌了,我 HTTP 功底贼好!
  32. shiro 报 Submitted credentials for token
  33. It's a showdown. I'm good at it!
  34. Shiro submitted credentials for token
  35. Linux Stress test
  36. Linux Root Disk Extension
  37. Linux Stress test
  38. Linux Root Disk Extension
  39. Redis高级客户端Lettuce详解
  40. springboot学习-综合运用(一)
  41. 忘记云服务器上MySQL数据库的root密码时如何重置密码?
  42. Detailed explanation of lettuce, an advanced client of redis
  43. Springboot learning integrated application (1)
  44. Linux File Recover
  45. Linux-Security
  46. How to reset the password when you forget the root password of MySQL database on the cloud server?
  47. Linux File Recover
  48. Linux-Security
  49. LiteOS:盘点那些重要的数据结构
  50. Linux Memory
  51. Liteos: inventory those important data structures
  52. Linux Memory
  53. 手把手教你使用IDEA2020创建SpringBoot项目
  54. Hand in hand to teach you how to create a springboot project with idea2020
  55. spring boot 整合swagger2生成API文档
  56. Spring boot integrates swagger2 to generate API documents
  57. linux操作系统重启后 解决nginx的pid消失问题
  58. Solve the problem of nginx PID disappearing after Linux operating system restart
  59. JAVA版本号含义
  60. The meaning of java version number