通过hive sql实现报表中的MTD,YTM,YTD

最新推荐文章于 2024-08-16 10:02:58 发布
最新推荐文章于 2024-08-16 10:02:58 发布
阅读量5.3k 收藏 7
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wf3612581/article/details/87968213
版权

MTD：例：20160918求20160901-20160918的amount的sum(累加)
YTD: 例：20160918求20160101-20160918的amount的sum(累加)
YTM: 例：20160918求2016年1-9月的amount的sum(累加)
解决方法：使用hive的开窗函数
#原始表(100条数据)
+------------------+-------------------+--+
| date_orig.ddate  | date_orig.amount  |
+------------------+-------------------+--+
| 20160918         | 106               |
| 20180707         | 225               |
| 20181003         | 126               |
| 20161222         | 178               |
| 20170607         | 198               |
| 20181105         | 280               |
| 20170706         | 260               |
| 20180817         | 291               |
| 20180206         | 104               |
| 20181128         | 267               |
| 20170313         | 154               |
| 20170313         | 191               |
| 20180921         | 108               |
| 20170926         | 106               |
| 20171116         | 131               |
| 20170816         | 161               |
| 20180514         | 143               |
| 20160216         | 138               |
| 20170317         | 194               |
| 20160609         | 255               |
| 20170202         | 213               |
| 20180505         | 173               |
| 20160302         | 238               |
| 20180328         | 218               |
| 20180221         | 176               |
| 20181222         | 116               |
| 20180104         | 139               |
| 20170802         | 297               |
| 20160723         | 206               |
| 20160825         | 283               |
| 20181203         | 217               |
| 20180530         | 252               |
| 20171120         | 262               |
| 20160129         | 163               |
| 20160628         | 269               |
| 20161011         | 179               |
| 20170820         | 185               |
| 20160913         | 262               |
| 20171130         | 190               |
| 20170821         | 172               |
| 20160908         | 267               |
| 20161120         | 250               |
| 20180901         | 260               |
| 20181124         | 110               |
| 20161012         | 184               |
| 20180525         | 177               |
| 20181218         | 221               |
| 20161202         | 120               |
| 20181009         | 260               |
| 20160821         | 278               |
| 20170330         | 121               |
| 20160429         | 288               |
| 20170430         | 250               |
| 20161127         | 198               |
| 20160801         | 150               |
| 20180906         | 266               |
| 20160321         | 298               |
| 20180813         | 111               |
| 20170914         | 147               |
| 20160610         | 270               |
| 20170512         | 169               |
| 20180325         | 221               |
| 20171215         | 295               |
| 20160329         | 184               |
| 20180507         | 110               |
| 20160518         | 223               |
| 20180711         | 191               |
| 20170529         | 188               |
| 20180106         | 133               |
| 20180529         | 149               |
| 20180913         | 114               |
| 20160413         | 120               |
| 20180507         | 179               |
| 20180426         | 233               |
| 20181204         | 155               |
| 20170915         | 284               |
| 20171006         | 255               |
| 20161103         | 132               |
| 20160105         | 154               |
| 20171025         | 226               |
| 20180726         | 108               |
| 20170217         | 266               |
| 20160917         | 204               |
| 20181205         | 277               |
| 20180212         | 221               |
| 20170413         | 262               |
| 20180729         | 154               |
| 20180311         | 290               |
| 20170105         | 193               |
| 20160907         | 212               |
| 20180909         | 192               |
| 20170501         | 272               |
| 20180421         | 197               |
| 20171204         | 217               |
| 20181105         | 223               |
| 20170730         | 240               |
| 20160727         | 279               |
| 20180924         | 197               |
| 20170224         | 222               |
| 20180902         | 293               |
+------------------+-------------------+--+

#hive sql语句
===========================================================================================
第一种：
select distinct ddate,amount,
substr(ddate,1,6) as mtd_month,
(sum(amount) over(partition by substr(ddate,1,6) order by ddate))as mtd,
(sum(amount) over(partition by substr(ddate,1,6) order by substr(ddate,1,6)))as ytm,
substr(ddate,1,4) as ytd_year,
(sum(amount) over(partition by substr(ddate,1,4) order by ddate))as ytd
from date_orig order by ddate;

============================================================================================
第二种：
select d.ddate,d.amount,
substr(d.ddate,1,6) as mtd_month,
(sum(d.amount) over(partition by substr(d.ddate,1,6) order by d.ddate))as mtd,
(sum(d.amount) over(partition by substr(d.ddate,1,6) order by substr(d.ddate,1,6)))as ytm,
substr(d.ddate,1,4) as ytd_year,
(sum(d.amount) over(partition by substr(d.ddate,1,4) order by d.ddate))as ytd
from
(select distinct ddate,amount from date_orig) d order by d.ddate;

============================================================================================

#得到的结果
+-----------+-----------+------------+-------+-------+-----------+-------+--+
|  d.ddate  | d.amount  | mtd_month  |  mtd  |  ytm  | ytd_year  |  ytd  |
+-----------+-----------+------------+-------+-------+-----------+-------+--+
| 20160105  | 154       | 201601     | 154   | 317   | 2016      | 154   |
| 20160129  | 163       | 201601     | 317   | 317   | 2016      | 317   |
| 20160216  | 138       | 201602     | 138   | 138   | 2016      | 455   |
| 20160302  | 238       | 201603     | 238   | 720   | 2016      | 693   |
| 20160321  | 298       | 201603     | 536   | 720   | 2016      | 991   |
| 20160329  | 184       | 201603     | 720   | 720   | 2016      | 1175  |
| 20160413  | 120       | 201604     | 120   | 408   | 2016      | 1295  |
| 20160429  | 288       | 201604     | 408   | 408   | 2016      | 1583  |
| 20160518  | 223       | 201605     | 223   | 223   | 2016      | 1806  |
| 20160609  | 255       | 201606     | 255   | 794   | 2016      | 2061  |
| 20160610  | 270       | 201606     | 525   | 794   | 2016      | 2331  |
| 20160628  | 269       | 201606     | 794   | 794   | 2016      | 2600  |
| 20160723  | 206       | 201607     | 206   | 485   | 2016      | 2806  |
| 20160727  | 279       | 201607     | 485   | 485   | 2016      | 3085  |
| 20160801  | 150       | 201608     | 150   | 711   | 2016      | 3235  |
| 20160821  | 278       | 201608     | 428   | 711   | 2016      | 3513  |
| 20160825  | 283       | 201608     | 711   | 711   | 2016      | 3796  |
| 20160907  | 212       | 201609     | 212   | 1051  | 2016      | 4008  |
| 20160908  | 267       | 201609     | 479   | 1051  | 2016      | 4275  |
| 20160913  | 262       | 201609     | 741   | 1051  | 2016      | 4537  |
| 20160917  | 204       | 201609     | 945   | 1051  | 2016      | 4741  |
| 20160918  | 106       | 201609     | 1051  | 1051  | 2016      | 4847  |
| 20161011  | 179       | 201610     | 179   | 363   | 2016      | 5026  |
| 20161012  | 184       | 201610     | 363   | 363   | 2016      | 5210  |
| 20161103  | 132       | 201611     | 132   | 580   | 2016      | 5342  |
| 20161120  | 250       | 201611     | 382   | 580   | 2016      | 5592  |
| 20161127  | 198       | 201611     | 580   | 580   | 2016      | 5790  |
| 20161202  | 120       | 201612     | 120   | 298   | 2016      | 5910  |
| 20161222  | 178       | 201612     | 298   | 298   | 2016      | 6088  |
| 20170105  | 193       | 201701     | 193   | 193   | 2017      | 193   |
| 20170202  | 213       | 201702     | 213   | 701   | 2017      | 406   |
| 20170217  | 266       | 201702     | 479   | 701   | 2017      | 672   |
| 20170224  | 222       | 201702     | 701   | 701   | 2017      | 894   |
| 20170313  | 154       | 201703     | 345   | 660   | 2017      | 1239  |
| 20170313  | 191       | 201703     | 345   | 660   | 2017      | 1239  |
| 20170317  | 194       | 201703     | 539   | 660   | 2017      | 1433  |
| 20170330  | 121       | 201703     | 660   | 660   | 2017      | 1554  |
| 20170413  | 262       | 201704     | 262   | 512   | 2017      | 1816  |
| 20170430  | 250       | 201704     | 512   | 512   | 2017      | 2066  |
| 20170501  | 272       | 201705     | 272   | 629   | 2017      | 2338  |
| 20170512  | 169       | 201705     | 441   | 629   | 2017      | 2507  |
| 20170529  | 188       | 201705     | 629   | 629   | 2017      | 2695  |
| 20170607  | 198       | 201706     | 198   | 198   | 2017      | 2893  |
| 20170706  | 260       | 201707     | 260   | 500   | 2017      | 3153  |
| 20170730  | 240       | 201707     | 500   | 500   | 2017      | 3393  |
| 20170802  | 297       | 201708     | 297   | 815   | 2017      | 3690  |
| 20170816  | 161       | 201708     | 458   | 815   | 2017      | 3851  |
| 20170820  | 185       | 201708     | 643   | 815   | 2017      | 4036  |
| 20170821  | 172       | 201708     | 815   | 815   | 2017      | 4208  |
| 20170914  | 147       | 201709     | 147   | 537   | 2017      | 4355  |
| 20170915  | 284       | 201709     | 431   | 537   | 2017      | 4639  |
| 20170926  | 106       | 201709     | 537   | 537   | 2017      | 4745  |
| 20171006  | 255       | 201710     | 255   | 481   | 2017      | 5000  |
| 20171025  | 226       | 201710     | 481   | 481   | 2017      | 5226  |
| 20171116  | 131       | 201711     | 131   | 583   | 2017      | 5357  |
| 20171120  | 262       | 201711     | 393   | 583   | 2017      | 5619  |
| 20171130  | 190       | 201711     | 583   | 583   | 2017      | 5809  |
| 20171204  | 217       | 201712     | 217   | 512   | 2017      | 6026  |
| 20171215  | 295       | 201712     | 512   | 512   | 2017      | 6321  |
| 20180104  | 139       | 201801     | 139   | 272   | 2018      | 139   |
| 20180106  | 133       | 201801     | 272   | 272   | 2018      | 272   |
| 20180206  | 104       | 201802     | 104   | 501   | 2018      | 376   |
| 20180212  | 221       | 201802     | 325   | 501   | 2018      | 597   |
| 20180221  | 176       | 201802     | 501   | 501   | 2018      | 773   |
| 20180311  | 290       | 201803     | 290   | 729   | 2018      | 1063  |
| 20180325  | 221       | 201803     | 511   | 729   | 2018      | 1284  |
| 20180328  | 218       | 201803     | 729   | 729   | 2018      | 1502  |
| 20180421  | 197       | 201804     | 197   | 430   | 2018      | 1699  |
| 20180426  | 233       | 201804     | 430   | 430   | 2018      | 1932  |
| 20180505  | 173       | 201805     | 173   | 1183  | 2018      | 2105  |
| 20180507  | 179       | 201805     | 462   | 1183  | 2018      | 2394  |
| 20180507  | 110       | 201805     | 462   | 1183  | 2018      | 2394  |
| 20180514  | 143       | 201805     | 605   | 1183  | 2018      | 2537  |
| 20180525  | 177       | 201805     | 782   | 1183  | 2018      | 2714  |
| 20180529  | 149       | 201805     | 931   | 1183  | 2018      | 2863  |
| 20180530  | 252       | 201805     | 1183  | 1183  | 2018      | 3115  |
| 20180707  | 225       | 201807     | 225   | 678   | 2018      | 3340  |
| 20180711  | 191       | 201807     | 416   | 678   | 2018      | 3531  |
| 20180726  | 108       | 201807     | 524   | 678   | 2018      | 3639  |
| 20180729  | 154       | 201807     | 678   | 678   | 2018      | 3793  |
| 20180813  | 111       | 201808     | 111   | 402   | 2018      | 3904  |
| 20180817  | 291       | 201808     | 402   | 402   | 2018      | 4195  |
| 20180901  | 260       | 201809     | 260   | 1430  | 2018      | 4455  |
| 20180902  | 293       | 201809     | 553   | 1430  | 2018      | 4748  |
| 20180906  | 266       | 201809     | 819   | 1430  | 2018      | 5014  |
| 20180909  | 192       | 201809     | 1011  | 1430  | 2018      | 5206  |
| 20180913  | 114       | 201809     | 1125  | 1430  | 2018      | 5320  |
| 20180921  | 108       | 201809     | 1233  | 1430  | 2018      | 5428  |
| 20180924  | 197       | 201809     | 1430  | 1430  | 2018      | 5625  |
| 20181003  | 126       | 201810     | 126   | 386   | 2018      | 5751  |
| 20181009  | 260       | 201810     | 386   | 386   | 2018      | 6011  |
| 20181105  | 280       | 201811     | 503   | 880   | 2018      | 6514  |
| 20181105  | 223       | 201811     | 503   | 880   | 2018      | 6514  |
| 20181124  | 110       | 201811     | 613   | 880   | 2018      | 6624  |
| 20181128  | 267       | 201811     | 880   | 880   | 2018      | 6891  |
| 20181203  | 217       | 201812     | 217   | 986   | 2018      | 7108  |
| 20181204  | 155       | 201812     | 372   | 986   | 2018      | 7263  |
| 20181205  | 277       | 201812     | 649   | 986   | 2018      | 7540  |
| 20181218  | 221       | 201812     | 870   | 986   | 2018      | 7761  |
| 20181222  | 116       | 201812     | 986   | 986   | 2018      | 7877  |
+-----------+-----------+------------+-------+-------+-----------+-------+--+

把握青春1234
关注
Hive SQL实现日期区间列表
weixin_42385450的博客
04-04 3048
Hive SQL 获取日期区间中的日期列表 select start_date ,end_date ,date_add(start_date,idx) AS cur_date from （ select start_date -- 如果需要从一张表中获取开始日期、结束日期，可在子查询中查找 ,end_date from tmp_date ) a -- 此子查询只能有一条数据，否则会日期列表会出现多次出现多 late
mysqlsql怎么比较当前月与去年的这个月的同比_YTD（年初至今）日期同比分析
weixin_39605706的博客
12-21 2843
商业情景YTD分析属于同比分析类，其特点在于对比汇总值，即从年初第一日值一直至今的值累加。作用在于分析企业中长期的经营绩效。在YTD日期同比中需要注意的是取等量日期单位作为参照物。举例而言，假设当前日期是2018年7月21日，其同比计算则应为去年7月1号至21号的销售值对比，而不是对比2018年7月整个月份的值。可视化实现折线图和区域图组合，折线图适用于呈现日期变化下不同年份的绩效走势，而区域图适...
java项目数据统计功能怎么做
最新发布
weixin_37083373的博客
08-16 32
我整理的一些关于【Java】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/bLN8S1Java项目数据统计功能实现 在现代Java开发中，数据统计功能是核心组件之一。其可用于监控项目的使用情况、性能指标、用户行为等。本文将详细介绍如何实现一个数据统计功能，包括设计思路、代码...
Hive或者Mysql 输出累计报表的Sql实现
qq_40068214的博客
04-23 312
解决sql实现累计报表问题。 需求 有如下访客访问次数统计表 t_access_times 访客 月份 访问次数 A 2015-01 5 A 2015-01 15 B 20...
固定资产YTD折旧额SQL
weixin_46130307的博客
01-02 1651
问题描述： 报表中要求取到固定资产YTD折旧额，即资产从年初到当前月份的折旧额 解决方法： 用如下SQL FUNCTION get_ytd_deprn(p_asset_id IN NUMBER, p_book_type_code IN VARCHAR2, p_rate_sou...
同环比与MTD/QTD/YTD月季年累计在查询引擎中的实现
hjw199089的博客
04-30 9790
一、实现环比/同比计算 （1）利用窗函数实现同比环比计算 计算每月的销量情况的场景中，我们可以窗口函数Lag计算获得上个月的销量数据： LAG(value, offset, DEFAULT) OVER () 这个函数的功能就是返回与当前行向前偏移n行的目标行的数值，如LAG(sum(price), 1) OVER () 即可以获得前一行的销量数据。 而月环比的计算公式为 （当月销量-上月销量）／...
Hive 累积和的计算
qq_19240703的博客
03-01 8235
使用窗口函数Sum eg：统计1-12月的累积销量，即1月为1月份的值，2月为1.2月份值的和，3月为123月份的和，12月为1-12月份值的和 SELECT  month, SUM(amount) month_amount, SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING A
Hive sql实现随机取值
weixin_42385450的博客
04-27 3514
hive中不能直接实现randomchoice 需要借助数组，思路： 1.将字符串根据’,'切割，变为数组 2.floor(rand()*size(arr))获取数组长度范围内的随机数，此处数组长度为4，取0-4 之前的随机数 3.根据下标取值 代码如下 select arr[cast(floor(rand()*size(arr)) as int)] ran from ( select split(col,',') arr from
hive sql 怎么实现循环_用HiveSQL计算连续天数问题的方法
weixin_39935571的博客
12-20 1929
点击右侧关注，大数据开发领域最强公众号！ 点击右侧关注，暴走大数据！ 在日常工作中，可能经常会接到业务方类似这样的需求：统计今年每个用户最长连续签到的天数；统计最近一个月连续有回帖超过5天的话题；统计本季度中连续3天以上单日销量超过100的商品。这种“连续天数”问题看似简单，但实际上对思维能力和编写复杂SQL语句的能力要求比较高。下面以我们曾经接到的一个需求为例，提出解决办法。...
hive sql 怎么实现循环_shell 传参实现hive循环跑数
weixin_39670246的博客
12-20 1341
在自动调度系统没有建设成熟的环境里，数据分析师如果要提取历史很长一段时间数据，且代码比较复杂，常规操作需要我们手动执行；但是借助shell传参给hive，可以实现hive自动循环跑数，减少手动执行，大幅提升效率。以下两段code为例，说明一下跑数时间长度为每天和时间长度为一段时间两种执行情况。1、跑数时间为每天start_date=20180101end_date=20180131while [[...
Hive SQL语句操作
需要远程指导仿真实验、代码有问题的，请后台私信或者关注公众号
06-25 2217
本实验任务主要完成基于Hive环境执行一些常用的Hive SQL语句操作，通过完成本实验任务，要求学生熟练掌握Hive SQL的常用语句，掌握Hive的基础和操作。掌握Hive的数据类型   掌握hive的数据类型的使用   掌握hive的DDL语句的操作本次环境是：Ubuntu16.04+hadoop-2.7.3.+apache-hive-1.2.2-bin数据量巨大且保存时间长； 在大量数据上进行复杂的批量运算； 数据在计算之前已经完全到位，不会发生变化； 能够方便的查询批量计算的结果； 不像在线计算当
【分析函数】一文遍识开窗函数
Axlsss的博客
12-28 1197
【数据库分析函数】一文遍识开窗函数
【Hive SQL】常用日期函数汇总
u013631103的博客
09-28 7059
【Hive SQL】常用日期函数汇总： 1.时间戳和日期相互转换函数 2.转特定日期单位的函数 3.获取特定日期函数 4.实用案例
【Java 动态数据统计图】动态数据统计思路案例（动态，排序，动态数组（重点推荐））七（129）
qq_42139049的博客
08-30 770
1.动态数据统计思路案例（动态，排序，动态数组（重点推荐））七
同比、环比、YTD、MTD
热门推荐
AICCS的博客
06-11 1万+
YTD:年初至今 MTD:月初至今 同比：与去年或着以前年度比 环比：某一时期内相邻的两个等长时段相比，例：去年五月同去年四月相比，今年第三季度同今年第二季度相比 同比=用今年减去年再除以去年/(本期数-同期数)÷同期数×100% 环比=用本月减上月再除以上月/(本期数-上期数)/上期数×100% ...
HIVE实战处理（八） hive窗口函数
sheep8521的博客
02-09 843
1、 count、sum、avg、max、min # 按照 year 来分组，统计每一年的总和 # 结果：每个月的值都是本年的总和 sum(val) over(partition by year) # 按照 year 来分组，按照 month 来排序 # 结果：n 月的值是本年 1月到 n 月的累计值 sum(val) over(partition by year order by month) 通过 explain select ... 来查看语句解析，可以简单理解为，在每一次 order by 之后
HANA 存储过程 YTD
joan_nan的博客
10-24 2071
YTD 现有合同表一份, 要求计算任意指定范围内的每个月的YTD (全部同时列出). 输出字段: 年月, YTD金额。 合同表数据： insert into “HANABI”.“CONTRACTS” VALUES(1,‘2012-04-01’, ‘2013-06-30’,100); insert into “HANABI”.“CONTRACTS” VALUES(2,‘2013-07-01’, ‘2...
Hive SQL性能优化：MapReduce阶段策略
"数据仓库中的SQL性能优化（Hive篇）" 在数据仓库中，SQL性能优化对于提升大数据处理效率至关重要，特别是在使用Hive这样的分布式数据处理框架时。Hive基于MapReduce运算模型，其查询执行涉及到多个MapReduce作业，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值