ITPUB论坛-中国最专业的IT技术社区

 手机号登录  找回密码
 注册
查看: 1783|回复: 1

[每日一题] PL/SQL Challenge 每日一题:2018-3-19 属性维度(ATTRIBUTE DIMENSION)

[复制链接]
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
发表于 2018-3-22 04:37 | 显示全部楼层 |阅读模式

最先答对且答案未经编辑的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
/

论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
 楼主| 发表于 2018-3-23 05:43 | 显示全部楼层
答案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值,从而“修复”了输出。这个做法并不推荐,但确实可行。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表