サンプルStarスキーマ・ベンチマーク(SSB)の問合せおよび分析ビュー
SSBスキーマには、lineorder、customer、supplier、partおよびdwdateという表が含まれます。次のリストに、SSBスキーマに使用できるサンプル問合せおよび分析ビューを示します。問合せでは、表名にスキーマ名SSBの接頭辞を付ける必要があることに注意してください。
ノート
SHとSSBは両方ともスキーマ専用ユーザーとして提供されるため、これらのユーザーをロック解除または削除したり、パスワードを設定することはできません。サンプル・データセットのストレージは、データベース・ストレージにはカウントされません。
SHとSSBは両方ともスキーマ専用ユーザーとして提供されるため、これらのユーザーをロック解除または削除したり、パスワードを設定することはできません。サンプル・データセットのストレージは、データベース・ストレージにはカウントされません。
Starスキーマベンチマーククエリー
Starスキーマベンチマーククエリー
select sum(lo_extendedprice*lo_discount) as revenue
from ssb.lineorder, ssb.dwdate
where lo_orderdate = d_datekey
and d_yearmonthnum = 199401
and lo_discount between 4 and 6
and lo_quantity between 26 and 35;
select sum(lo_extendedprice*lo_discount) as revenue
from ssb.lineorder, ssb.dwdate
where lo_orderdate = d_datekey
and d_year = 1993
and lo_discount between 1 and 3
and lo_quantity < 25;
select sum(lo_extendedprice*lo_discount) as revenue
from ssb.lineorder, ssb.dwdate
where lo_orderdate = d_datekey
and d_yearmonthnum = 199401
and lo_discount between 4 and 6
and lo_quantity between 26 and 35;
select sum(lo_extendedprice*lo_discount) as revenue
from ssb.lineorder, ssb.dwdate
where lo_orderdate = d_datekey
and d_weeknuminyear = 6
and d_year = 1994
and lo_discount between 5 and 7
and lo_quantity between 26 and 35;
select sum(lo_revenue), d_year, p_brand1
from ssb.lineorder, ssb.dwdate, ssb.part, ssb.supplier
where lo_orderdate = d_datekey
and lo_partkey = p_partkey
and lo_suppkey = s_suppkey
and p_category = 'MFGR#12'
and s_region = 'AMERICA'
group by d_year, p_brand1
order by d_year, p_brand1;
select sum(lo_revenue), d_year, p_brand1
from ssb.lineorder, ssb.dwdate, ssb.part, ssb.supplier
where lo_orderdate = d_datekey
and lo_partkey = p_partkey
and lo_suppkey = s_suppkey
and p_brand1 between 'MFGR#2221' and 'MFGR#2228'
and s_region = 'ASIA'
group by d_year, p_brand1
order by d_year, p_brand1;
select sum(lo_revenue), d_year, p_brand1
from ssb.lineorder, ssb.dwdate, ssb.part, ssb.supplier
where lo_orderdate = d_datekey
and lo_partkey = p_partkey
and lo_suppkey = s_suppkey
and p_brand1 = 'MFGR#2221'
and s_region = 'EUROPE'
group by d_year, p_brand1
order by d_year, p_brand1;
select c_nation, s_nation, d_year, sum(lo_revenue) as revenue
from ssb.customer, ssb.lineorder, ssb.supplier, ssb.dwdate
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_orderdate = d_datekey
and c_region = 'ASIA' and s_region = 'ASIA'
and d_year >= 1992 and d_year <= 1997
group by c_nation, s_nation, d_year
order by d_year asc, revenue desc;
select c_city, s_city, d_year, sum(lo_revenue) as revenue
from ssb.customer, ssb.lineorder, ssb.supplier, ssb.dwdate
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_orderdate = d_datekey
and c_nation = 'UNITED STATES'
and s_nation = 'UNITED STATES'
and d_year >= 1992 and d_year <= 1997
group by c_city, s_city, d_year
order by d_year asc, revenue desc;
select c_city, s_city, d_year, sum(lo_revenue) as revenue
from ssb.customer, ssb.lineorder, ssb.supplier, ssb.dwdate
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_orderdate = d_datekey
and (c_city='UNITED KI1' or c_city='UNITED KI5')
and (s_city='UNITED KI1' or s_city='UNITED KI5')
and d_year >= 1992 and d_year <= 1997
group by c_city, s_city, d_year
order by d_year asc, revenue desc;
select c_city, s_city, d_year, sum(lo_revenue) as revenue
from ssb.customer, ssb.lineorder, ssb.supplier, ssb.dwdate
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_orderdate = d_datekey
and (c_city='UNITED KI1' or c_city='UNITED KI5')
and (s_city='UNITED KI1' or s_city='UNITED KI5')
and d_yearmonth = 'Dec1997'
group by c_city, s_city, d_year
order by d_year asc, revenue desc;
select d_year, c_nation, sum(lo_revenue - lo_supplycost) as profit
from ssb.dwdate, ssb.customer, ssb.supplier, ssb.part, ssb.lineorder
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_partkey = p_partkey
and lo_orderdate = d_datekey
and c_region = 'AMERICA'
and s_region = 'AMERICA'
and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
group by d_year, c_nation
order by d_year, c_nation;
select d_year, s_nation, p_category, sum(lo_revenue - lo_supplycost) as profit
from ssb.dwdate, ssb.customer, ssb.supplier, ssb.part, ssb.lineorder
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_partkey = p_partkey
and lo_orderdate = d_datekey
and c_region = 'AMERICA'
and s_region = 'AMERICA'
and (d_year = 1997 or d_year = 1998)
and (p_mfgr = 'MFGR#1'
or p_mfgr = 'MFGR#2')
group by d_year, s_nation, p_category order by d_year, s_nation, p_category;
select d_year, s_city, p_brand1, sum(lo_revenue - lo_supplycost) as profit
from ssb.dwdate, ssb.customer, ssb.supplier, ssb.part, ssb.lineorder
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_partkey = p_partkey
and lo_orderdate = d_datekey
and c_region = 'AMERICA'
and s_nation = 'UNITED STATES'
and (d_year = 1997 or d_year = 1998)
and p_category = 'MFGR#14'
group by d_year, s_city, p_brand1 order by d_year, s_city, p_brand1;
Starスキーマ・ベンチマーク分析ビュー
SSB分析ビュー
分析ビューにより、階層ビジネス・モデル、集計および測定の計算ルール、プレゼンテーションおよびアプリケーション固有のメタデータを含むスター・スキーマを容易に拡張して、データ・セットのコンテンツの強化や、ビジネス・インテリジェンス・アプリケーションの開発の簡素化に使用できます。SSBスキーマには、スター・スキーマの表を使用する分析ビューおよび4つの階層が含まれます。次の問合せを使用して、サンプルSSBの分析ビューを問い合せます。分析ビューはSSBスキーマ内にあることに注意してください。
SELECT
dwdate_hier.member_name as year,
part_hier.member_name as part,
customer_hier.c_region,
customer_hier.member_name as customer,
lo_quantity,
lo_revenue
FROM ssb.ssb_av
HIERARCHIES (
dwdate_hier,
part_hier,
customer_hier)
WHERE
dwdate_hier.d_year = '1998'
AND dwdate_hier.level_name = 'MONTH'
AND part_hier.level_name = 'MANUFACTURER'
AND customer_hier.c_region = 'AMERICA'
AND customer_hier.level_name = 'NATION'
ORDER BY
dwdate_hier.hier_order,
part_hier.hier_order,
customer_hier.hier_order;
SELECT
dwdate_hier.member_name as time,
part_hier.member_name as part,
customer_hier.member_name as customer,
supplier_hier.member_name as supplier,
lo_quantity,
lo_supplycost
FROM ssb.ssb_av
HIERARCHIES (
dwdate_hier,
part_hier,
customer_hier,
supplier_hier)
WHERE
dwdate_hier.d_year = '1998'
AND dwdate_hier.level_name = 'MONTH'
AND part_hier.level_name = 'MANUFACTURER'
AND customer_hier.c_region = 'AMERICA'
AND customer_hier.c_nation = 'CANADA'
AND customer_hier.level_name = 'CITY'
AND supplier_hier.s_region = 'ASIA'
AND supplier_hier.level_name = 'REGION'
ORDER BY
dwdate_hier.hier_order,
part_hier.hier_order,
customer_hier.hier_order,
supplier_hier.hier_order;
SELECT
dwdate_hier.member_name as year,
part_hier.member_name as part,
customer_hier.member_name as customer,
supplier_hier.member_name as supplier,
lo_quantity,
lo_revenue,
lo_supplycost
FROM ssb.ssb_av
HIERARCHIES (
dwdate_hier,
part_hier,
customer_hier,
supplier_hier)
WHERE
dwdate_hier.d_yearmonth = 'Apr1998'
AND dwdate_hier.level_name = 'DAY'
AND part_hier.level_name = 'MANUFACTURER'
AND customer_hier.c_region = 'AMERICA'
AND customer_hier.c_nation = 'CANADA'
AND customer_hier.level_name = 'CITY'
AND supplier_hier.level_name = 'REGION'
ORDER BY
dwdate_hier.hier_order,
part_hier.hier_order,
customer_hier.hier_order,
supplier_hier.hier_order;
SELECT
dwdate_hier.member_name as year,
part_hier.member_name as part,
supplier_hier.member_name as supplier,
lo_quantity,
lo_extendedprice,
lo_ordtotalprice,
lo_revenue,
lo_supplycost
FROM ssb.ssb_av
HIERARCHIES (
dwdate_hier,
part_hier,
supplier_hier)
WHERE
dwdate_hier.level_name = 'YEAR'
AND part_hier.level_name = 'MANUFACTURER'
AND supplier_hier.level_name = 'SUPPLIER'
AND supplier_hier.s_suppkey = '23997';
SELECT
dwdate_hier.member_name as time,
part_hier.p_container,
part_hier.member_name as part,
lo_quantity,
lo_extendedprice,
lo_ordtotalprice,
lo_revenue,
lo_supplycost
FROM ssb.ssb_av
HIERARCHIES (
dwdate_hier,
part_hier)
WHERE
dwdate_hier.member_name = 'June 10, 1998 '
AND dwdate_hier.level_name = 'DAY'
AND part_hier.level_name = 'PART'
AND part_hier.p_size = 32;
SELECT
dwdate_hier.member_name as time,
part_hier.member_name as part,
part_hier.p_name,
part_hier.p_color,
lo_quantity,
lo_revenue,
lo_supplycost,
lo_revenue - lo_supplycost as profit
FROM ssb.ssb_av
HIERARCHIES (
dwdate_hier,
part_hier)
WHERE
dwdate_hier.d_yearmonth = 'Aug1996'
AND dwdate_hier.d_dayofweek = 'Friday '
AND dwdate_hier.level_name = 'DAY'
AND part_hier.level_name = 'PART'
AND part_hier.p_color in ('ivory','coral')
ORDER BY
dwdate_hier.hier_order,
part_hier.hier_order;