Hi All, Can someone look into this and revert if possible?
Thanks. On Thu, 12 Jul 2018 12:56 Anup Tiwari, <anupsdtiw...@gmail.com> wrote: > Hi All, > > We have a use case where we want to assign a row number to a table based > on 3 column ( uid, update_date, flag) i.e. if value of any of the column > gets changed, we want to reset this number. Please find below sample input > data and expected output data. > > Also please note that we have tried row_number() over(partition by uid, > update_date, flag order by update_time asc) but due to this actual input > ordering got break due to I believe partition by clause because it seems > partition by creates group within column specified and then it start row > number and due to this actual ordering is breaking. So i just wanted to > know that is there any function available in hive which can give us below > result OR we are missing something in window function? > > > *Input Data :- * > > *uid* *update_date* *update_time* *flag* > 468730 2017-07-12 12/07/2017 22:59:17 1 > 468730 2017-07-12 12/07/2017 23:02:14 0 > 468730 2017-07-12 12/07/2017 23:07:40 0 > 468730 2017-07-12 12/07/2017 23:12:41 0 > 468730 2017-07-12 12/07/2017 23:22:06 0 > 468730 2017-07-12 12/07/2017 23:38:35 0 > 468730 2017-07-12 12/07/2017 23:44:19 0 > 468730 2017-07-12 12/07/2017 23:47:49 1 > 468730 2017-07-12 12/07/2017 23:48:49 1 > 468730 2017-07-12 12/07/2017 23:53:31 0 > 468730 2017-07-12 12/07/2017 23:57:01 1 > 468730 2017-07-13 13/07/2017 00:03:10 1 > 468730 2017-07-13 13/07/2017 00:06:35 0 > 468730 2017-07-13 13/07/2017 00:07:29 1 > 468731 2017-07-13 12/07/2017 12:59:17 1 > 468731 2017-07-13 12/07/2017 13:02:14 0 > 468731 2017-07-13 12/07/2017 13:07:40 0 > 468731 2017-07-13 12/07/2017 13:12:41 0 > > > *Output Data :-* > > *uid* *update_date* *update_time* *flag* *required_row_num* > 468730 2017-07-12 12/07/2017 22:59:17 1 1 > 468730 2017-07-12 12/07/2017 23:02:14 0 1 > 468730 2017-07-12 12/07/2017 23:07:40 0 2 > 468730 2017-07-12 12/07/2017 23:12:41 0 3 > 468730 2017-07-12 12/07/2017 23:22:06 0 4 > 468730 2017-07-12 12/07/2017 23:38:35 0 5 > 468730 2017-07-12 12/07/2017 23:44:19 0 6 > 468730 2017-07-12 12/07/2017 23:47:49 1 1 > 468730 2017-07-12 12/07/2017 23:48:49 1 2 > 468730 2017-07-12 12/07/2017 23:53:31 0 1 > 468730 2017-07-12 12/07/2017 23:57:01 1 1 > 468730 2017-07-13 13/07/2017 00:03:10 1 1 > 468730 2017-07-13 13/07/2017 00:06:35 0 1 > 468730 2017-07-13 13/07/2017 00:07:29 1 1 > 468731 2017-07-13 12/07/2017 12:59:17 1 1 > 468731 2017-07-13 12/07/2017 13:02:14 0 1 > 468731 2017-07-13 12/07/2017 13:07:40 0 2 > 468731 2017-07-13 12/07/2017 13:12:41 0 3 > *FYI :* We are one Hive 2.3.1. >
