TPC BENCHMARK (TM) H (779138), страница 12
Текст из файла (страница 12)
5];2.CONTAINER is randomly selected within the list of 2-syllable strings defined for Containers in Clause4.2.2.13.2.4.17.4 Query ValidationFor validation against the qualification database the query must be executed using the following values for substitution parameters and must produce the following output data:Values for substitution parameters:1.BRAND = Brand#23;2.CONTAINER = MED BOX.2.4.17.5 Sample OutputAVG_YEARLY348406.052.4.18TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 572.4.18Large Volume Customer Query (Q18)The Large Volume Customer Query ranks customers based on their having placed a large quantity order.
Largequantity orders are defined as those orders whose total quantity is above a certain level.2.4.18.1 Business QuestionThe Large Volume Customer Query finds a list of the top 100 customers who have ever placed large quantity orders.The query lists the customer name, customer key, the order key, date and total price and the quantity for the order.2.4.18.2 Functional Query DefinitionReturn the first 100 selected rowsselectc_name,c_custkey,o_orderkey,o_orderdate,o_totalprice,sum(l_quantity)fromcustomer,orders,lineitemwhereo_orderkey in (selectl_orderkeyfromlineitemgroup byl_orderkey havingsum(l_quantity) > [QUANTITY])and c_custkey = o_custkeyand o_orderkey = l_orderkeygroup byc_name,c_custkey,o_orderkey,o_orderdate,o_totalpriceorder byo_totalprice desc,o_orderdate;2.4.18.3 Substitution ParametersValues for the following substitution parameter must be generated and used to build the executable query text:1.QUANTITY is randomly selected within [312..315].2.4.18.4 Query ValidationFor validation against the qualification database the query must be executed using the following values for substitution parameters and must produce the following output data:Values for substitution parameters:1.QUANTITY = 300TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 582.4.18.5 Sample OutputC_NAMEC_CUSTKEYO_ORDERKEYO_ORDERDATEO_TOTALPRICESum(L_QUANTITY)Customer#00012812012812047220211994-04-07544089.09323.00TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 592.4.19Discounted Revenue Query (Q19)The Discounted Revenue Query reports the gross discounted revenue attributed to the sale of selected parts handledin a particular manner.
This query is an example of code such as might be produced programmatically by a datamining tool.2.4.19.1 Business QuestionThe Discounted Revenue query finds the gross discounted revenue for all orders for three different types of partsthat were shipped by air and delivered in person. Parts are selected based on the combination of specific brands, alist of containers, and a range of sizes.2.4.19.2 Functional Query Definitionselectsum(l_extendedprice * (1 - l_discount) ) as revenuefromlineitem,partwhere(p_partkey = l_partkeyand p_brand = ‘[BRAND1]’and p_container in ( ‘SM CASE’, ‘SM BOX’, ‘SM PACK’, ‘SM PKG’)and l_quantity >= [QUANTITY1] and l_quantity <= [QUANTITY1] + 10and p_size between 1 and 5and l_shipmode in (‘AIR’, ‘AIR REG’)and l_shipinstruct = ‘DELIVER IN PERSON’)or(p_partkey = l_partkeyand p_brand = ‘[BRAND2]’and p_container in (‘MED BAG’, ‘MED BOX’, ‘MED PKG’, ‘MED PACK’)and l_quantity >= [QUANTITY2] and l_quantity <= [QUANTITY2] + 10and p_size between 1 and 10and l_shipmode in (‘AIR’, ‘AIR REG’)and l_shipinstruct = ‘DELIVER IN PERSON’)or(p_partkey = l_partkeyand p_brand = ‘[BRAND3]’and p_container in ( ‘LG CASE’, ‘LG BOX’, ‘LG PACK’, ‘LG PKG’)and l_quantity >= [QUANTITY3] and l_quantity <= [QUANTITY3] + 10and p_size between 1 and 15and l_shipmode in (‘AIR’, ‘AIR REG’)and l_shipinstruct = ‘DELIVER IN PERSON’);2.4.19.3 Substitution Parameters1.QUANTITY1 is randomly selected within [1..10].2.QUANTITY2 is randomly selected within [10..20].3.QUANTITY3 is randomly selected within [20..30].4.BRAND1, BRAND2, BRAND3 = 'Brand#MN' where each MN is a two character string representing two numbers randomly and independently selected within [1 ..
5]TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 602.4.19.4 Query ValidationFor validation against the qualification database the query must be executed using the following values for substitution parameters and must produce the following output data:Values for substitution parameters:1.QUANTITY1 = 1.2.QUANTITY2 = 10.3.QUANTITY3 = 20.4.BRAND1 = Brand#12.5.BRAND2 = Brand#23.6.BRAND3 = Brand#34.2.4.19.5 Sample OutputREVENUE3083843.05TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 612.4.20Potential Part Promotion Query (Q20)The Potential Part Promotion Query identifies suppliers in a particular nation having selected parts that may be candidates for a promotional offer.2.4.20.1 Business QuestionThe Potential Part Promotion query identifies suppliers who have an excess of a given part available; an excess isdefined to be more than 50% of the parts like the given part that the supplier shipped in a given year for a givennation.
Only parts whose names share a certain naming convention are considered.2.4.20.2 Functional Query Definitionselects_name,s_addressfromsupplier, nationwheres_suppkey in (selectps_suppkeyfrompartsuppwhereps_partkey in (selectp_partkeyfrompartwherep_name like '[COLOR]%')and ps_availqty > (select0.5 * sum(l_quantity)fromlineitemwherel_partkey = ps_partkeyand l_suppkey = ps_suppkeyand l_shipdate >= date('[DATE]’)and l_shipdate < date('[DATE]’) + interval ‘1’ year))and s_nationkey = n_nationkeyand n_name = '[NATION]'order bys_name;2.4.20.3 Substitution Parameters1.COLOR is randomly selected within the list of values defined for the generation of P_NAME.2.DATE is the first of January of a randomly selected year within 1993..1997.3.NATION is randomly selected within the list of values defined for N_NAME in Clause 4.2.3.2.4.20.4 Query ValidationTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 62For validation against the qualification database the query must be executed using the following values for substitution parameters and must produce the following output data:Values for substitution parameters:1.COLOR = forest.2.DATE = 1994-01-01.3.NATION = CANADA.2.4.20.5 Sample OutputS_NAMES_ADDRESSSupplier#000000020iybAE,RmTymrZVYaFZva2SH,jTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 632.4.21Suppliers Who Kept Orders Waiting Query (Q21)This query identifies certain suppliers who were not able to ship required parts in a timely manner.2.4.21.1 Business QuestionThe Suppliers Who Kept Orders Waiting query identifies suppliers, for a given nation, whose product was part of amulti-supplier order (with current status of 'F') where they were the only supplier who failed to meet the committeddelivery date.2.4.21.2 Functional Query DefinitionReturn the first 100 selected rows.selects_name,count(*) as numwaitfromsupplier,lineitem l1,orders,nationwheres_suppkey = l1.l_suppkeyand o_orderkey = l1.l_orderkeyand o_orderstatus = 'F'and l1.l_receiptdate > l1.l_commitdateand exists (select*fromlineitem l2wherel2.l_orderkey = l1.l_orderkeyand l2.l_suppkey <> l1.l_suppkey)and not exists (select*fromlineitem l3wherel3.l_orderkey = l1.l_orderkeyand l3.l_suppkey <> l1.l_suppkeyand l3.l_receiptdate > l3.l_commitdate)and s_nationkey = n_nationkeyand n_name = '[NATION]'group bys_nameorder bynumwait desc,s_name;2.4.21.3 Substitution Parameters1.NATION is randomly selected within the list of values defined for N_NAME in Clause 4.2.3.2.4.21.4 Query ValidationTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 64For validation against the qualification database the query must be executed using the following values for substitution parameters and must produce the following output data:Values for substitution parameters:1.NATION = SAUDI ARABIA.2.4.21.5 Sample OutputS_NAMENUMWAITSupplier#00000282920TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 652.4.22Global Sales Opportunity Query (Q22)The Global Sales Opportunity Query identifies geographies where there are customers who may be likely to make apurchase.2.4.22.1 Business QuestionThis query counts how many customers within a specific range of country codes have not placed orders for 7 yearsbut who have a greater than average “positive” account balance.
It also reflects the magnitude of that balance.Country code is defined as the first two characters of c_phone.2.4.22.2 Functional Query Definitionselectcntrycode,count(*) as numcust,sum(c_acctbal) as totacctbalfrom (selectsubstring(c_phone from 1 for 2) as cntrycode,c_acctbalfromcustomerwheresubstring(c_phone from 1 for 2) in('[I1]','[I2]’,'[I3]','[I4]','[I5]','[I6]','[I7]')and c_acctbal > (selectavg(c_acctbal)fromcustomerwherec_acctbal > 0.00and substring (c_phone from 1 for 2) in('[I1]','[I2]','[I3]','[I4]','[I5]','[I6]','[I7]'))and not exists (select*fromorderswhereo_custkey = c_custkey)) as custsalegroup bycntrycodeorder bycntrycode;2.4.22.3 Substitution Parameters1.I1 … I7 are randomly selected without repetition from the possible values for Country code as defined in Clause4.2.2.9.2.4.22.4 Query ValidationFor validation against the qualification database the query must be executed using the following substitution parameters and must produce the following output data:TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 661.I1 = 13.2.I2 = 31.3.I3 = 23.4.I4 = 29.5.I5 = 30.6.I6 = 18.7.I7 = 17.2.4.22.5 Sample OutputCNTRYCODENUMCUSTTOTACCTBAL138886737713.99TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 672.5General Requirements for Refresh functions2.5.1Refresh Function OverviewEach refresh function is defined by the following components:2.5.22.5.3The business rationale, which illustrates the business context in which the refresh functions could be used;The refresh function definition, which defines in pseudo-code the function to be performed by the refreshfunction;The refresh data set, which defines the set of rows to be inserted or deleted by each execution of the refreshfunction into or from the ORDERS and LINEITEM tables.
This set of rows represents 0.1% of the initialpopulation of these two tables (see Table 4: LINEITEM Cardinality).Transaction Requirements for Refresh functionsThe execution of each refresh function (RF1 or RF2) can be decomposed into any number of database transactionsas long as the following conditions are met:All ACID properties are met;Each atomic transaction includes a sufficient number of data modifications to maintain the logical databaseconsistency. For example, when adding or deleting a new order, the LINEITEM and the ORDERS tablesare both modified within the same transaction;An output message is sent when the last transaction of the refresh function has completed successfully.Refresh Function Compliance2.5.3.1 The benchmark specification does not place any requirements on the implementation of the refresh functions otherthan their functional equivalence to the refresh function definition and compliance with Clause 2.5.2.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
