I'm fairly new to programming and I'm struggling with an SQL Query.
I want, that the purchased articles (article_id) are grouped in an array, so they are connected to the date column. For a further process, they might be combined ( so date x is combined with articles_id [A,B,C])
To make it clearer I want to show what I've done below..
Starting point is this example table:
Date | Customer_id | article_id |
---|---|---|
123 | 1 | A |
123 | 1 | C |
125 | 1 | B |
124 | 2 | A |
126 | 2 | C |
With
SELECT
customer_id,
array_agg(UNIX_SECONDS(Date)) AS Date,
STRING_AGG(((CAST(article_id AS STRING)))) AS article_id
FROM table
GROUP BY customer_id;
I managed to transform the table to
Customer_id | date | article_id |
---|---|---|
1 | [123, 125] | A, B, C |
2 | [124, 126] | A, C |
No finally the question!
is it possible
To get to :
Customer_id | date | article_id |
---|---|---|
1 | [123, 125] | [[A, C], [B]] |
2 | [124, 126] | [[A], [C]] |
And if so, how?
I'm thankful for every piece of advice or wording to continue my search. And of cause thank you for your time.
CodePudding user response:
Try doing it in 2 steps. Step 1, group by customer_id and date, and stringagg your article_id. That gets you the [A, C] you want for date 123.
Then using that query as that FROM for the outer query, you should be able to get what you're looking for. (or you could use a WITH clause if you prefer that format)
CodePudding user response:
what about this? Aggregate twice :)
with input as (
select 123 date_time, 1 customer_id, 'A' article_id
UNION ALL
select 123 date_time, 1 customer_id, 'C' article_id
UNION ALL
select 125 date_time, 1 customer_id, 'B' article_id
UNION ALL
select 124 date_time, 2 customer_id, 'A' article_id
UNION ALL
select 126 date_time, 2 customer_id, 'C' article_id)
select customer_id, array_agg(distinct date_time) date_time, array_agg(STRUCT(article_id)) article_id from (
select customer_id,date_time, array_agg(article_id) article_id from input
group by customer_id, date_time
) group by customer_id