通过hivesql实现报表中的MTD,YTM,YTD 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 |
| 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 | | 20180
507 | 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 |
+------------------+-------------------+--+
#hive sql语句
===========================================================================================第⼀种：养兵千日
lect 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;
步骤图
===========================================================================================第⼆种：
lect 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
(lect 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 |
| 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 | 20170
体育特长班9 | 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 |
家人群名字叫什么好| 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 | +-----------+-----------+------------+-------+-------+-----------+-------+--+
