Imaing we have data like this:
SQL
x
5
1
WITH Sequences AS2
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS prod_type, [1.1, 1.2, 2.1, 2.3, 3.3, 3.4] AS prod_price,3
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS prod_type, [1.3, 4.2, 2.1, 7.3, 5.3, 9.4] AS prod_price,4
UNION ALL SELECT 3 AS id, [5, 10] AS prod_type, [1.8, 4.9, 2.0, 7.6, 5.1, 8.4] AS prod_price)5
select * from sequences
How could I get the total price of each “prod_type” for every “id”?
First we need to unfold the “prod_type” and “prod_price” correspondingly:
SQL
1
9
1
WITH Sequences AS2
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS prod_type, [1.1, 1.2, 2.1, 2.3, 3.3, 3.4] AS prod_price,3
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS prod_type, [1.3, 4.2, 2.1, 7.3, 5.3, 9.4] AS prod_price,4
UNION ALL SELECT 3 AS id, [5, 10] AS prod_type, [1.8, 4.9, 2.0, 7.6, 5.1, 8.4] AS prod_price)5
SELECT id, prod_type, prod_price6
from7
sequences,8
unnest(prod_type) AS prod_type,9
unnest(prod_price) AS prod_price;
and then use “group by” to calculate total price:
SQL
1
12
12
1
...2
SELECT3
id,4
prod_type,5
SUM(prod_price)6
FROM7
sequences,8
UNNEST(prod_type) AS prod_type,9
UNNEST(prod_price) AS prod_price10
GROUP BY11
id,12
prod_type