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
