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.
