Can someone look into this and revert if possible?
Regards, Anup Tiwari On Sat, Jul 14, 2018 at 12:28 AM, Anup Tiwari <anupsdtiw...@gmail.com> wrote: > 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. >> >
