Eu tenho uma tabela de revisão de produtos em um banco de dados oracle 12.1 EE que possui diferentes atributos classificados em uma escala de 1 a 5. Como faço para consultar esta tabela para contar todas as revisões e agrupar por produto e a classificação de 1 a 5.
Dados de exemplo
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
Eu olhei para fazer pivôs e várias consultas analíticas. Mas não consigo obter a saída desejada abaixo. Tenho certeza de que estou tornando isso muito difícil. Mas para a vida de mim eu não posso colocar isso junto. Qualquer ajuda/direção seria apreciada!
Saída de exemplo
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