本附录详细介绍了 VMart 示例数据库的架构、表和脚本。
VMart 示例数据库含有三种不同的架构：
public
store
online_sales
“架构”一词在 Vertica 中具有若干相互关联的含义：
在 SQL 语句中，架构是指某个逻辑架构的命名空间。
逻辑架构是指一组表和约束。
物理架构是指一组投影。
表 标识 VMart 数据库中的三个架构和所有数据表。每个架构都含有在数据库安装期间创建和加载的表。有关表列表及其内容，请参阅架构映射：
示例脚本 描述了包含 SQL 命令的示例脚本，这些命令表示可能在使用类似 VMart 的数据库的实际业务中使用的查询。一但您顺利运行示例查询，则可能希望编写自己的查询。
VMart 数据库中的三个架构包括以下表：
public 架构是一种雪花型架构。下图说明了 public 架构及其与 online_sales 和 store 架构中表的关系。
后续小节描述了数据库表。
此表含有每个库存产品的信息。
此表含有所有零售连锁店客户的信息。
此表含有日期信息。它根据含有正确日期/时间数据的文件生成。
此表含有零售连锁店的所有雇员的信息。
此表介绍了百货连锁店销售的所有产品的信息。
此表介绍了零售连锁店过去开展的每次促销的信息。
此表含有零售连锁店使用的货运公司的信息。
此表含有通过零售连锁店销售产品的每个供应商的信息。
此表含有每个连锁店仓库的信息。
store 架构是一种雪花型架构，含有零售连锁店旗下实体店的信息。下图演示了 store 架构及其与 public 架构中表的关系。
后续小节描述了数据库表。
此表含有公司实体店的所有订单信息。
此表含有公司实体店的所有销售信息。
此表含有零售连锁店中每个实体店的信息。
online_sales 架构是一种雪花型架构，含有零售连锁店的信息。下图演示了 online_sales 架构及其与 public 架构中表的关系。
后续小节描述了数据库表。
此表介绍了通过网店购买的所有商品。
此表介绍了所有连锁店的呼叫中心。
此表介绍了网店中的所有页面。
您可以创建自己的查询，但 VMart 示例目录含有示例查询脚本文件，可用来帮助您快速入门。
您可以在 /opt/vertica/examples/VMart_Schema 路径找到以下示例脚本。
要运行任何脚本，请输入
=> \i <script_name>
或者，在脚本文件中手动键入命令。
-- vmart_query_01.sql
-- FROM clause subquery
-- Return the values for five products with the
-- lowest-fat content in the Dairy department
SELECT fat_content
FROM (
SELECT DISTINCT fat_content
FROM product_dimension
WHERE department_description
IN ('Dairy') ) AS food
ORDER BY fat_content
LIMIT 5;
输出
fat_content
-------------
80
81
82
83
84
(5 rows)
-- vmart_query_02.sql
-- WHERE clause subquery
-- Asks for all orders placed by stores located in Massachusetts
-- and by vendors located elsewhere before March 1, 2003:
SELECT order_number, date_ordered
FROM store.store_orders_fact orders
WHERE orders.store_key IN (
SELECT store_key
FROM store.store_dimension
WHERE store_state = 'MA')
AND orders.vendor_key NOT IN (
SELECT vendor_key
FROM public.vendor_dimension
WHERE vendor_state = 'MA')
AND date_ordered < '2012-03-01';
输出
order_number | date_ordered
-------------+--------------
53019 | 2012-02-10
222168 | 2012-02-05
160801 | 2012-01-08
106922 | 2012-02-07
246465 | 2012-02-10
234218 | 2012-02-03
263119 | 2012-01-04
73015 | 2012-01-01
233618 | 2012-02-10
85784 | 2012-02-07
146607 | 2012-02-07
296193 | 2012-02-05
55052 | 2012-01-05
144574 | 2012-01-05
117412 | 2012-02-08
276288 | 2012-02-08
185103 | 2012-01-03
282274 | 2012-01-01
245300 | 2012-02-06
143526 | 2012-01-04
59564 | 2012-02-06
...
-- vmart_query_03.sql
-- noncorrelated subquery
-- Requests female and male customers with the maximum
-- annual income from customers
SELECT customer_name, annual_income
FROM public.customer_dimension
WHERE (customer_gender, annual_income) IN (
SELECT customer_gender, MAX(annual_income)
FROM public.customer_dimension
GROUP BY customer_gender);
输出
customer_name | annual_income
------------------+---------------
James M. McNulty | 999979
Emily G. Vogel | 999998
(2 rows)
-- vmart_query_04.sql
-- IN predicate
-- Find all products supplied by stores in MA
SELECT DISTINCT s.product_key, p.product_description
FROM store.store_sales_fact s, public.product_dimension p
WHERE s.product_key = p.product_key
AND s.product_version = p.product_version AND s.store_key IN (
SELECT store_key
FROM store.store_dimension
WHERE store_state = 'MA')
ORDER BY s.product_key;
输出
product_key | product_description
-------------+----------------------------------------
1 | Brand #1 butter
1 | Brand #2 bagels
2 | Brand #3 lamb
2 | Brand #4 brandy
2 | Brand #5 golf clubs
2 | Brand #6 chicken noodle soup
3 | Brand #10 ground beef
3 | Brand #11 vanilla ice cream
3 | Brand #7 canned chicken broth
3 | Brand #8 halibut
3 | Brand #9 camera case
4 | Brand #12 rash ointment
4 | Brand #13 low fat milk
4 | Brand #14 chocolate chip cookies
4 | Brand #15 silver polishing cream
5 | Brand #16 cod
5 | Brand #17 band aids
6 | Brand #18 bananas
6 | Brand #19 starch
6 | Brand #20 vegetable soup
6 | Brand #21 bourbon
...
-- vmart_query_05.sql
-- EXISTS predicate
-- Get a list of all the orders placed by all stores on
-- January 2, 2003 for the vendors with records in the
-- vendor_dimension table
SELECT store_key, order_number, date_ordered
FROM store.store_orders_fact
WHERE EXISTS (
SELECT 1
FROM public.vendor_dimension
WHERE public.vendor_dimension.vendor_key = store.store_orders_fact.vendor_key)
AND date_ordered = '2012-01-02';
输出
store_key | order_number | date_ordered
-----------+--------------+--------------
98 | 151837 | 2012-01-02
123 | 238372 | 2012-01-02
242 | 263973 | 2012-01-02
150 | 226047 | 2012-01-02
247 | 232273 | 2012-01-02
203 | 171649 | 2012-01-02
129 | 98723 | 2012-01-02
80 | 265660 | 2012-01-02
231 | 271085 | 2012-01-02
149 | 12169 | 2012-01-02
141 | 201153 | 2012-01-02
1 | 23715 | 2012-01-02
156 | 98182 | 2012-01-02
44 | 229465 | 2012-01-02
178 | 141869 | 2012-01-02
134 | 44410 | 2012-01-02
141 | 129839 | 2012-01-02
205 | 54138 | 2012-01-02
113 | 63358 | 2012-01-02
99 | 50142 | 2012-01-02
44 | 131255 | 2012-01-02
...
-- vmart_query_06.sql
-- EXISTS predicate
-- Orders placed by the vendor who got the best deal
-- on January 4, 2004
SELECT store_key, order_number, date_ordered
FROM store.store_orders_fact ord, public.vendor_dimension vd
WHERE ord.vendor_key = vd.vendor_key
AND vd.deal_size IN (
SELECT MAX(deal_size)
FROM public.vendor_dimension)
AND date_ordered = '2013-01-04';
输出
store_key | order_number | date_ordered
-----------+--------------+--------------
45 | 202416 | 2013-01-04
24 | 250295 | 2013-01-04
121 | 251417 | 2013-01-04
198 | 75716 | 2013-01-04
166 | 36008 | 2013-01-04
27 | 150241 | 2013-01-04
148 | 182207 | 2013-01-04
9 | 188567 | 2013-01-04
113 | 66017 | 2013-01-04
...
-- vmart_query_07.sql
-- Multicolumn subquery
-- Which products have the highest cost,
-- grouped by category and department
SELECT product_description, sku_number, department_description
FROM public.product_dimension
WHERE (category_description, department_description, product_cost) IN (
SELECT category_description, department_description,
MAX(product_cost) FROM product_dimension
GROUP BY category_description, department_description);
输出
product_description | sku_number | department_description
---------------------------+-----------------------+---------------------------------
Brand #601 steak | SKU-#601 | Meat
Brand #649 brooms | SKU-#649 | Cleaning supplies
Brand #677 veal | SKU-#677 | Meat
Brand #1371 memory card | SKU-#1371 | Photography
Brand #1761 catfish | SKU-#1761 | Seafood
Brand #1810 frozen pizza | SKU-#1810 | Frozen Goods
Brand #1979 canned peaches | SKU-#1979 | Canned Goods
Brand #2097 apples | SKU-#2097 | Produce
Brand #2287 lens cap | SKU-#2287 | Photography
...
-- vmart_query_08.sql
-- between online_sales_fact and online_page_dimension
SELECT page_description, page_type, start_date, end_date
FROM online_sales.online_sales_fact f, online_sales.online_page_dimension d
WHERE f.online_page_key = d.online_page_key
AND page_number IN
(SELECT MAX(page_number)
FROM online_sales.online_page_dimension)
AND page_type = 'monthly' AND start_date = '2012-06-02';
输出
page_description | page_type | start_date | end_date
---------------------------+-----------+------------+-----------
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
(12 rows)
-- vmart_query_09.sql
-- Equi join
-- Joins online_sales_fact table and the call_center_dimension
-- table with the ON clause
SELECT sales_quantity, sales_dollar_amount, transaction_type, cc_name
FROM online_sales.online_sales_fact
INNER JOIN online_sales.call_center_dimension
ON (online_sales.online_sales_fact.call_center_key
= online_sales.call_center_dimension.call_center_key
AND sale_date_key = 156)
ORDER BY sales_dollar_amount DESC;
输出
sales_quantity | sales_dollar_amount | transaction_type | cc_name
----------------+---------------------+------------------+-------------------
7 | 589 | purchase | Central Midwest
8 | 589 | purchase | South Midwest
8 | 589 | purchase | California
1 | 587 | purchase | New England
1 | 586 | purchase | Other
1 | 584 | purchase | New England
4 | 584 | purchase | New England
7 | 581 | purchase | Mid Atlantic
5 | 579 | purchase | North Midwest
8 | 577 | purchase | North Midwest
4 | 577 | purchase | Central Midwest
2 | 575 | purchase | Hawaii/Alaska
4 | 573 | purchase | NY Metro
4 | 572 | purchase | Central Midwest
1 | 570 | purchase | Mid Atlantic
9 | 569 | purchase | Southeastern
1 | 569 | purchase | NY Metro
5 | 567 | purchase | Other
7 | 567 | purchase | Hawaii/Alaska
9 | 567 | purchase | South Midwest
1 | 566 | purchase | New England
...