Aggregate columns in Athena

Question

I have these 3 columns:

Year	Month	Day
2023	5	1
2022	6	13

And I want to create one column for date

Date
2023-5-1
2022-6-13

I'm doing this in Athena.

CREATE VIEW my_view AS
SELECT
    year,
    month,
    day,
    array_join(array_agg('year','month','day'), '-') AS aggregated_dates , 
    count(*) AS aggregated_dates
FROM "my_table"

This was error:

Unexpected parameters (varchar(4), varchar(5), varchar(3)) for function array_agg. Expected: array_agg(T) T

I also tried

SELECT *, 
  translate(format('%t', array_agg(struct(year,month,day))),'()', '') AS dates
FROM "my_table"

But I got error that functions struct, format are not registered.

Do you have a better idea how to create that one column?

Answer 1

array_agg is aggregation function which can be used over single column either with group_by or in window function. Since you want to concat several columns in single row you can use either concatenation approach (based on the comment to the other answer since your data type is int you need to cast to varchar):

select  cast(year as varchar) || '-' || cast(month as varchar) || '-' || cast(day  as varchar) aggregated_dates;
from ...

or modify your array_join approach by just creating the array from 3 columns:

select array_join(array[year, month, day], '-') aggregated_dates;
from ...

P.S.

Note that 'year' is not a column, it is just a string containing word "year".