主页 / dba / 问题 / 261109
Accepted
Nick S
Asked: 2020-03-04 08:29:06 +0800 CST

聚合属性

  • 772

我在 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
query oracle-12c

1 个回答

  1. Best Answer

    如何

    您的查询需要执行以下操作：

    1. UNPIVOT数据
    2. PIVOT得到的数据COUNT()
    3. 添加缺失的行
      • LEFT OUTER JOIN所有行计算数据的子查询
      • then not-well-knownMODEL子句的使用
    4. 当然，该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
    • 1

相关问题