sort_array order by a different column, Hive

Question

I have two columns, one of products, and one of the dates they were bought. I am able to order the dates by applying the sort_array(dates) function, but I want to be able to sort_array(products) by the purchase date. Is there a way to do that in Hive?

Tablename is

ClientID    Product    Date
100    Shampoo    2016-01-02
101    Book    2016-02-04
100    Conditioner    2015-12-31
101    Bookmark    2016-07-10
100    Cream    2016-02-12
101    Book2    2016-01-03

Then, getting one row per customer:

select
clientID,
COLLECT_LIST(Product) as Prod_List,
sort_array(COLLECT_LIST(date)) as Date_Order
from tablename
group by 1;

As:

ClientID    Prod_List    Date_Order
100    ["Shampoo","Conditioner","Cream"]    ["2015-12-31","2016-01-02","2016-02-12"]
101    ["Book","Bookmark","Book2"]    ["2016-01-03","2016-02-04","2016-07-10"]

But what I want is the order of the products to be tied to the correct chronological order of purchases.

Do you have to use sort_array? Why not SORT BY products,dates? — nobody, Apr 14 '17 at 17:10
Ah yes, the first step is grouping by the clientID, and then collapsing all the products they ever bought into an array (so it can be 1 row per customer). I want to ensure the products bought are in chronological order. select clientid, COLLECT_LIST(product), sort_array(COLLECT_LIST(date)) from tablename group by 1; — IDK, Apr 14 '17 at 17:48
@inquisitive_mind thanks! But sort by does not collapse all items into 1 row — IDK, Apr 14 '17 at 17:49
@DuduMarkovitz adjusted the initial question to include a sample. Thanks! — IDK, Apr 14 '17 at 18:11

score 5 · Accepted Answer · answered Apr 14 '17 at 18:27

It is possible to do it using only built-in functions, but it is not a pretty site :-)

select      clientid
           ,split(regexp_replace(concat_ws(',',sort_array(collect_list(concat_ws(':',cast(date as string),product)))),'[^:]*:([^,]*(,|$))','$1'),',') as prod_list
           ,sort_array(collect_list(date)) as date_order

from        tablename 

group by    clientid
;

+----------+-----------------------------------+------------------------------------------+
| clientid |             prod_list             |                date_order                |
+----------+-----------------------------------+------------------------------------------+
|      100 | ["Conditioner","Shampoo","Cream"] | ["2015-12-31","2016-01-02","2016-02-12"] |
|      101 | ["Book2","Book","Bookmark"]       | ["2016-01-03","2016-02-04","2016-07-10"] |
+----------+-----------------------------------+------------------------------------------+

this worked like a charm! Over 4M records now ordered! Thank you! — IDK, Apr 14 '17 at 20:00

sort_array order by a different column, Hive

1 Answers1