Collect_set With Case in Hive

Question

是否有任何方法重写下面的case语句，而不是写Collect_set[0] 4次，我可以使用单个Collect_set获得相同的结果。

select id,collect_set(name)[0] as name,sum(salary),
cASE WHEN month(to_date(from_unixtime(unix_timestamp(collect_set(date1)[0], 'dd-MM-yyyy')))) 
IN (01,02,03) THEN 
CONCAT(CONCAT(year(to_date(from_unixtime(unix_timestamp(collect_set(date1)[0], 'dd-MM-yyyy'))))-1,'-'),
substr(year(to_date(from_unixtime(unix_timestamp(collect_set(date1)[0], 'dd-MM-yyyy')))),3,4))
ELSE CONCAT(CONCAT(year(to_date(from_unixtime(unix_timestamp(collect_set(date1)[0], 'dd-MM-yyyy')))),'-'),
SUBSTR(year(to_date(from_unixtime(unix_timestamp(collect_set(date1)[0], 'dd-MM-yyyy'))))+1,3,4)) 
END as fy from testing_1.collect_set_test group by id;

I am Writing Below Query.

select collect_set(CASE WHEN month(to_date(from_unixtime(unix_timestamp(date1), 'dd-MM-yyyy'))) 
IN (01,02,03) THEN CONCAT(CONCAT(year(to_date(from_unixtime(unix_timestamp(date1), 'dd-MM-yyyy')))-1,'-'),
substr(year(to_date(from_unixtime(unix_timestamp(date1), 'dd-MM-yyyy'))),3,4)) 
ELSE
CONCAT(CONCAT(year(to_date(from_unixtime(unix_timestamp(date1), 'dd-MM-yyyy'))),'-'),
SUBSTR(year(to_date(from_unixtime(unix_timestamp(date1), 'dd-MM-yyyy')))+1,3,4))) [0]
END as fy from testing_1.collect_set_test group by id;

但是它的Giving Below Error

FAILED: ParseException line 1:446 missing KW_END at ')' near ']' in selection target
line 1:452 cannot recognize input near 'END' 'as' 'fy' in selection target

有没有人可以指导我如何重写相同的。

Answer 1

将所有具有组和日期转换by的聚合移动到子查询中，在上面的子查询中计算fy:

select id, name, salary,
cASE WHEN month(date1) 
IN (01,02,03) THEN CONCAT(CONCAT(year(date1))-1,'-'),
substr(year(date1),3,4))
ELSE CONCAT(CONCAT(year(date1),'-'),
SUBSTR(year(date1)+1,3,4)) 
END as fy 
from 
(select to_date(from_unixtime(unix_timestamp(collect_set(date1)[0], 'dd-MM-yyyy'))) as date1, 
collect_set(name)[0]  as name, 
sum(salary) as salary, 
id 
from testing_1.collect_set_test group by id) s
;