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