我在 oracle 12.1 EE 数据库中有一个产品评论表，该表具有从 1 到 5 等级的不同属性。如何查询此表以计算所有评论并按产品和 1-5 评级分组。
示例数据
WITH DATA_SET
AS
( SELECT 1 AS REVIEW_ID,
'a' AS PRODUCT_ID,
5 AS OVERALL,
4 AS COMFORT,
5 AS FIT,
4 AS APPEARANCE
FROM DUAL
UNION ALL
SELECT 2 AS REVIEW_ID,
'a' AS PRODUCT_ID,
4 AS OVERALL,
4 AS COMFORT,
4 AS FIT,
4 AS APPEARANCE
FROM DUAL
UNION ALL
SELECT 3 AS REVIEW_ID,
'b' AS PRODUCT_ID,
4 AS OVERALL,
5 AS COMFORT,
4 AS FIT,
5 AS APPEARANCE
FROM DUAL
UNION ALL
SELECT 4 AS REVIEW_ID,
'c' AS PRODUCT_ID,
3 AS OVERALL,
2 AS COMFORT,
2 AS FIT,
4 AS APPEARANCE
FROM DUAL
UNION ALL
SELECT 5 AS REVIEW_ID,
'c' AS PRODUCT_ID,
2 AS OVERALL,
1 AS COMFORT,
2 AS FIT,
1 AS APPEARANCE
FROM DUAL
)
SELECT *
FROM DATA_SET;
review_id | product_id | overall | comfort | fit | appearance
----------------------------------------------------------------
1 a 5 4 5 4
2 a 4 4 4 4
3 b 4 5 4 5
4 c 3 2 2 4
5 c 2 1 2 1
我看过做数据透视和各种分析查询。但我不能完全得到下面想要的输出。我确定我做得太难了。但对于我的生活，我不能把这些放在一起。任何帮助/方向将不胜感激！
示例输出
product_id | rating | overall | comfort | fit | appearance
--------------------------------------------------------------
a 1 0 0 0 0
a 2 0 0 0 0
a 3 0 0 0 0
a 4 1 2 1 2
a 5 1 0 1 0
b 1 0 0 0 0
b 2 0 0 0 0
b 3 0 0 0 0
b 4 1 0 1 1
b 5 0 1 0 0
c 1 0 1 0 1
c 2 1 1 2 0
c 3 1 0 0 0
c 4 0 0 0 1
c 5 0 0 0 0