Query

聚合屬性

  • March 3, 2020

我在 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

如何

您的查詢需要執行以下操作：

  1. UNPIVOT數據
  2. PIVOT得到的數據COUNT()
  3. 添加缺失的行
  • LEFT OUTER JOIN所有行計算數據的子查詢
  • then not-well-knownMODEL子句的使用
  1. 當然，該ORDER BY條款

範例 1（子查詢）

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
   )
,unpivot_data as (
 SELECT  product_id, score, catagory
 FROM    DATA_SET
   UNPIVOT ( score
             for catagory in (
               overall, comfort, fit, appearance
             )
           )
 )
,count_values as (  
 select product_id, score, overall, comfort, fit, appearance
 from unpivot_data
 pivot (
   count(catagory)
   for catagory in ( 'OVERALL' AS overall, 'COMFORT' as comfort, 'FIT' as fit, 'APPEARANCE' as appearance)
 )
)
,all_rows as (
 select *
 from (select distinct product_id from data_set),
      (select level as score from dual connect by level <= 5)
)
select a.product_id, a.product_id
 ,nvl( b.overall, 0) overall
 ,nvl( b.comfort, 0) comfort
 ,nvl( b.fit, 0) fit
 ,nvl( b.appearance, 0) appearance
from all_rows a
 left outer join count_values b
   on (a.product_id=b.product_id and a.score=b.score)
order by a.product_id, a.score

範例 2（模型）

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
   )
,unpivot_data as (
 SELECT  product_id, score, catagory
 FROM    DATA_SET
   UNPIVOT ( score
             for catagory in (
               overall, comfort, fit, appearance
             )
           )
 )
,count_values as (  
 select product_id, score, overall, comfort, fit, appearance
 from unpivot_data
 pivot (
   count(catagory)
   for catagory in ( 'OVERALL' AS overall, 'COMFORT' as comfort, 'FIT' as fit, 'APPEARANCE' as appearance)
 )
)
select *
from count_values
model return all rows
 DIMENSION by (product_id,score)
 measures ( overall, comfort, fit, appearance )
 rules (
   overall[ for product_id in ( select distinct product_id from data_set), for score in (1,2,3,4,5)] =
     nvl( overall[ cv(product_id), cv(score) ], 0),
   comfort[ for product_id in (select distinct product_id from data_set), for score in (1,2,3,4,5)] =
     nvl( comfort[ cv(product_id), cv(score) ], 0),
   fit[ for product_id in (select distinct product_id from data_set), for score in (1,2,3,4,5)] =
     nvl( fit[ cv(product_id), cv(score) ], 0),
   appearance[ for product_id in (select distinct product_id from data_set), for score in (1,2,3,4,5)] =
     nvl( appearance[ cv(product_id), cv(score) ], 0)
   )
order by product_id, score

引用自：https://dba.stackexchange.com/questions/261109

相關問答

Mysql

MySQL - 通過汽車製造獲取汽車模型的計數

  • January 3, 2022
檢視問答
Mysql

需要幫助來編寫查詢連接 3 個表

  • November 22, 2021
檢視問答
Postgresql

如何在交叉表中動態設置列名？

  • November 9, 2021
檢視問答
Sql-Server

SQLServer - 同一列上的多個 PIVOT

  • October 12, 2021
檢視問答
Mysql

MySQL中的數據透視表

  • August 4, 2021
檢視問答
Postgresql

建構一個包含來自不同表的聚合值的選擇查詢

  • August 19, 2020
檢視問答