PL/SQL Challenge 每日一题：2018-3-19 属性维度（ATTRIBUTE DIMENSION）

newkid

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处：
http://www.plsqlchallenge.com/

作者： Kim Berg Hansen

运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品

注：本题执行环境为12.2或更高

注意：假设执行本题代码的用户已经被赋予系统权限CREATE ATTRIBUTE DIMENSION 以及 CREATE HIERARCHY

我有一张表，保存着我们产品的多级别维度数据：

create table qz_prod_dim (
   product_id  integer      primary key
, product     varchar2(20) not null
, category_id integer      not null
, category    varchar2(20) not null
)
/

insert into qz_prod_dim values (11, 'Speakers'  , 41, 'Accessory')
/
insert into qz_prod_dim values (12, 'Mousepad'  , 41, 'Accessory')
/
insert into qz_prod_dim values (13, 'Drone'     , 42, 'Gadget'   )
/
insert into qz_prod_dim values (14, 'VR goggles', 42, 'Gadget'   )
/
commit
/

我们的BI部门想要以维度级别分别为 ALL, CATEGORY 和 PRODUCT来查看这些数据——从顶部显示聚合层并且向下钻取到更低的层次。

哪些选项包含的查询（可能还有DDL语句），使得选项中所有语句执行都不出错并且返回这个所需的输出：

LEVEL_NAME MEMBER_NAME
---------- --------------------
ALL        ALL PRODUCTS
CATEGORY   Accessory
PRODUCT    Mousepad
PRODUCT    Speakers
CATEGORY   Gadget
PRODUCT    Drone
PRODUCT    VR goggles

(A)
select case grouping_id(category_id, product_id)
          when 3 then 'ALL'
          when 1 then 'CATEGORY'
                 else 'PRODUCT'
       end as level_name
     , case grouping_id(category_id, product_id)
          when 3 then 'ALL PRODUCTS'
          when 1 then max(category)
                 else max(product)
       end as member_name
  from qz_prod_dim
group by rollup(category_id, product_id)
order by grouping(category_id) desc
        , max(category)
        , grouping(product_id) desc
        , max(product)
/

(B)
select case grouping_id(product_id, category_id)
          when 3 then 'ALL'
          when 1 then 'CATEGORY'
                 else 'PRODUCT'
       end as level_name
     , case grouping_id(product_id, category_id)
          when 3 then 'ALL PRODUCTS'
          when 1 then max(category)
                 else max(product)
       end as member_name
  from qz_prod_dim
group by rollup(product_id, category_id)
order by grouping(product_id) desc
        , max(product)
        , grouping(category_id) desc
        , max(category)
/

(C)
create attribute dimension qz_prod_attr_dim
   dimension type standard
   using qz_prod_dim
attributes (
   product_id  as prod_id
, product     as prod_name
, category_id as cat_id
, category    as cat_name
)
level product
   key         prod_id
   member name prod_name
   order by    prod_name
   determines  (cat_id)
level category
   key         cat_id
   member name cat_name
   order by    cat_name
all
   member name 'ALL PRODUCTS'
/

create hierarchy qz_prod_hier
using qz_prod_attr_dim
(
   product child of category
)
/

select level_name, member_name
  from qz_prod_hier
order by hier_order
/

(D)
create attribute dimension qz_prod_attr_dim
   dimension type standard
   using qz_prod_dim
attributes (
   product_id  as prod_id
, product     as prod_name
, category_id as cat_id
, category    as cat_name
)
level product
   key         product_id
   member name product
   order by    product
   determines  (category_id)
level category
   key         category_id
   member name category
   order by    category
all
   member name 'ALL PRODUCTS'
/

create hierarchy qz_prod_hier
using qz_prod_attr_dim
(
   product child of category
)
/

select level_name, member_name
  from qz_prod_hier
order by hier_order
/

(E)
create attribute dimension qz_prod_attr_dim
   dimension type standard
   using qz_prod_dim
attributes (
   product_id
, product
, category_id
, category
)
level product
   key         product_id
   member name product
   order by    product
   determines  (category_id)
level category
   key         category_id
   member name category
   order by    category
all
   member name 'ALL PRODUCTS'
/

create hierarchy qz_prod_hier
using qz_prod_attr_dim
(
   product child of category
)
/

select level_name, member_name
  from qz_prod_hier
order by hier_order
/

(F)
create attribute dimension qz_prod_attr_dim
   dimension type standard
   using qz_prod_dim
attributes (
   product_id
, product
, category_id
, category
)
level prod_level
   key         product_id
   member name product
   order by    product
   determines  (category_id)
level cat_level
   key         category_id
   member name category
   order by    category
all
   member name 'ALL PRODUCTS'
/

create hierarchy qz_prod_hier
using qz_prod_attr_dim
(
   prod_level child of cat_level
)
/

select level_name, member_name
  from qz_prod_hier
order by hier_order
/

(G)
create attribute dimension qz_prod_attr_dim
   dimension type standard
   using qz_prod_dim
attributes (
   product_id
, product
, category_id
, category
)
level prod_level
   key         product_id
   member name product
   order by    product
   determines  (category_id)
level cat_level
   key         category_id
   member name category
   order by    category
all
   member name 'ALL PRODUCTS'
/

create hierarchy qz_prod_hier
using qz_prod_attr_dim
(
   prod_level child of cat_level
)
/

select case depth
          when 0 then 'ALL'
          when 1 then 'CATEGORY'
          when 2 then 'PRODUCT'
       end as level_name
     , member_name
  from qz_prod_hier
order by hier_order
/

newkid

答案ACEG, 本期无人得奖。

A: 对ROLLUP, GROUPING, GROUPING_ID 和 ORDER BY的恰当使用使得我们能够输出所需的维度层次。
B: 但是此处我们在ROLLUP和其他地方包含了(PRODUCT_ID,CATEGORY_ID)，这就意味着我们得到了按产品分组的类别，而不是按类别分组的产品。我们会得到这个错误输出：

LEVEL_NAME MEMBER_NAME
---------- --------------------
ALL        ALL PRODUCTS
CATEGORY   Gadget
PRODUCT    Drone
CATEGORY   Accessory
PRODUCT    Mousepad
CATEGORY   Accessory
PRODUCT    Speakers
CATEGORY   Gadget
PRODUCT    VR goggles

C: 如果我们创建一个属性维度（ATTRIBUTE DIMENSION）我们就可以明确地维度的各个层次，然后创建一个HIERARCHY来将所有的东西定义好，所以我们只需从HIERARCHY做一个简单的查询就可以得到所需的输出。
D: 在属性维度（ATTRIBUTE DIMENSION）的层次定义中，你需要使用属性的名称，而不是基表的列名。所以第一个语句报错了：
ORA-18340: Attribute "PRODUCT_ID" referenced in a key on level "PRODUCT" does not exist.
然后第二个语句也出错：
ORA-18301: attribute dimension "SCOTT"."QZ_PROD_ATTR_DIM" does not exist.
查询报这个错：
ORA-00942: table or view does not exist.
E: 此处我们就如同前一选项那样，在层次定义中使用了列名，但是因为我们没有使用AS来为属性取别名，属性就被命名为和列名相同，所以这个选项可以工作。
F: 此处我们将层次分别取名为PRODUCT 和 CATEGORY,而是用了PROD_LEVEL 和 CAT_LEVEL。这些名字被HIERARCHY的LEVEL_NAME列所输出，所以我们会得到这个错误输出，其层次名称是错的：

LEVEL_NAME MEMBER_NAME
---------- --------------------
ALL        ALL PRODUCTS
CAT_LEVEL  Accessory
PROD_LEVEL Mousepad
PROD_LEVEL Speakers
CAT_LEVEL  Gadget
PROD_LEVEL Drone
PROD_LEVEL VR goggles

G: （不推荐）
此处我们用了和前一选项同样“错误”的层次名，但是我们在输出中没有用HIERARCHY内置的LEVEL_NAME，而是用了内置列DEPTH来构建我们自己的LEVEL_NAME值，从而“修复”了输出。这个做法并不推荐，但确实可行。