TPC BENCHMARK (TM) H (779138), страница 9
Текст из файла (страница 9)
If several suppliers in that region offer the desired part type and size at the same(minimum) cost, the query lists the parts from suppliers with the 100 highest account balances. For each supplier,the query lists the supplier's account balance, name and nation; the part's number and manufacturer; the supplier'saddress, phone number and comment information.2.4.2.2 Functional Query DefinitionReturn the first 100 selected rowsselects_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_commentfrompart,supplier,partsupp,nation,regionwherep_partkey = ps_partkeyand s_suppkey = ps_suppkeyand p_size = [SIZE]and p_type like '%[TYPE]'and s_nationkey = n_nationkeyand n_regionkey = r_regionkeyand r_name = '[REGION]'and ps_supplycost = (selectTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 30min(ps_supplycost)frompartsupp, supplier,nation, regionwherep_partkey = ps_partkeyand s_suppkey = ps_suppkeyand s_nationkey = n_nationkeyand n_regionkey = r_regionkeyand r_name = '[REGION]')order bys_acctbal desc,n_name,s_name,p_partkey;TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 312.4.2.3 Substitution ParametersValues for the following substitution parameter must be generated and used to build the executable query text:1.SIZE is randomly selected within [1.
50];2.TYPE is randomly selected within the list Syllable 3 defined for Types in Clause 4.2.2.13;3.REGION is randomly selected within the list of values defined for R_NAME in 4.2.3.2.4.2.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.SIZE = 15;2.TYPE = BRASS;3.REGION = EUROPE.2.4.2.5 Sample OutputS_ACCTBALS_NAMEN_NAMEP_PARTKEYP_MFGR9938.53Supplier#000005359UNITED KINGDOM185358Manufacturer#4S_ADDRESSS_PHONES_COMMENTQKuHYh,vZGiwu2FWEJoLDx0433-429-790-6131uriously regular requests hagTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 322.4.3Shipping Priority Query (Q3)This query retrieves the 10 unshipped orders with the highest value.2.4.3.1 Business QuestionThe Shipping Priority Query retrieves the shipping priority and potential revenue, defined as the sum ofl_extendedprice * (1-l_discount), of the orders having the largest revenue among those that had not been shipped asof a given date.
Orders are listed in decreasing order of revenue. If more than 10 unshipped orders exist, only the 10orders with the largest revenue are listed.2.4.3.2 Functional Query DefinitionReturn the first 10 selected rowsselectl_orderkey,sum(l_extendedprice*(1-l_discount)) as revenue,o_orderdate,o_shippriorityfromcustomer,orders,lineitemwherec_mktsegment = '[SEGMENT]'and c_custkey = o_custkeyand l_orderkey = o_orderkeyand o_orderdate < date '[DATE]'and l_shipdate > date '[DATE]'group byl_orderkey,o_orderdate,o_shippriorityorder byrevenue desc,o_orderdate;2.4.3.3 Substitution ParametersValues for the following substitution parameters must be generated and used to build the executable query text:1.SEGMENT is randomly selected within the list of values defined for Segments in Clause 4.2.2.13;2.DATE is a randomly selected day within [1995-03-01 ..
1995-03-31].2.4.3.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.SEGMENT = BUILDING;2.DATE = 1995-03-15.2.4.3.5 Sample OutputL_ORDERKEYREVENUEO_ORDERDATEO_SHIPPRIORITY2456423406181.011995-03-050TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 33TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 342.4.4Order Priority Checking Query (Q4)This query determines how well the order priority system is working and gives an assessment of customer satisfaction.2.4.4.1 Business QuestionThe Order Priority Checking Query counts the number of orders ordered in a given quarter of a given year in whichat least one lineitem was received by the customer later than its committed date.
The query lists the count of suchorders for each order priority sorted in ascending priority order.2.4.4.2 Functional Query Definitionselecto_orderpriority,count(*) as order_countfromorderswhereo_orderdate >= date '[DATE]'and o_orderdate < date '[DATE]' + interval '3' monthand exists (select*fromlineitemwherel_orderkey = o_orderkeyand l_commitdate < l_receiptdate)group byo_orderpriorityorder byo_orderpriority;2.4.4.3 Substitution ParametersValues for the following substitution parameter must be generated and used to build the executable query text:1.DATE is the first day of a randomly selected month between the first month of 1993 and the 10th month of1997.2.4.4.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.DATE = 1993-07-01.2.4.4.5 Sample OutputO_ORDERPRIORITYORDER_COUNT1-URGENT10594TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 352.4.5Local Supplier Volume Query (Q5)This query lists the revenue volume done through local suppliers.2.4.5.1 Business QuestionThe Local Supplier Volume Query lists for each nation in a region the revenue volume that resulted from lineitemtransactions in which the customer ordering parts and the supplier filling them were both within that nation.
Thequery is run in order to determine whether to institute local distribution centers in a given region. The query considers only parts ordered in a given year. The query displays the nations and revenue volume in descending order byrevenue. Revenue volume for all qualifying lineitems in a particular nation is defined as sum(l_extendedprice * (1 l_discount)).2.4.5.2 Functional Query Definitionselectn_name,sum(l_extendedprice * (1 - l_discount)) as revenuefromcustomer,orders,lineitem,supplier,nation,regionwherec_custkey = o_custkeyand l_orderkey = o_orderkeyand l_suppkey = s_suppkeyand c_nationkey = s_nationkeyand s_nationkey = n_nationkeyand n_regionkey = r_regionkeyand r_name = '[REGION]'and o_orderdate >= date '[DATE]'and o_orderdate < date '[DATE]' + interval '1' yeargroup byn_nameorder byrevenue desc;2.4.5.3 Substitution ParametersValues for the following substitution parameters must be generated and used to build the executable query text:1.REGION is randomly selected within the list of values defined for R_NAME in C;aise 4.2.3;2.DATE is the first of January of a randomly selected year within [1993 ..
1997].2.4.5.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.REGION = ASIA;2.DATE = 1994-01-01.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 362.4.5.5 Sample OutputN_NAMEREVENUEINDONESIA55502041.17TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 372.4.6Forecasting Revenue Change Query (Q6)This query quantifies the amount of revenue increase that would have resulted from eliminating certain companywide discounts in a given percentage range in a given year. Asking this type of "what if" query can be used to lookfor ways to increase revenues.2.4.6.1 Business QuestionThe Forecasting Revenue Change Query considers all the lineitems shipped in a given year with discounts betweenDISCOUNT-0.01 and DISCOUNT+0.01.
The query lists the amount by which the total revenue would haveincreased if these discounts had been eliminated for lineitems with l_quantity less than quantity. Note that thepotential revenue increase is equal to the sum of [l_extendedprice * l_discount] for all lineitems with discounts andquantities in the qualifying range.2.4.6.2 Functional Query Definitionselectsum(l_extendedprice*l_discount) as revenuefromlineitemwherel_shipdate >= date '[DATE]'and l_shipdate < date '[DATE]' + interval '1' yearand l_discount between [DISCOUNT] - 0.01 and [DISCOUNT] + 0.01and l_quantity < [QUANTITY];2.4.6.3 Substitution ParametersValues for the following substitution parameters must be generated and used to build the executable query text:1.DATE is the first of January of a randomly selected year within [1993 ..
1997];2.DISCOUNT is randomly selected within [0.02 .. 0.09];3.QUANTITY is randomly selected within [24 .. 25].2.4.6.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.DATE = 1994-01-01;2.DISCOUNT = 0.06;3.QUANTITY = 24.2.4.6.5 Sample OutputREVENUE123141078.23TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 382.4.7Volume Shipping Query (Q7)This query determines the value of goods shipped between certain nations to help in the re-negotiation of shippingcontracts.2.4.7.1 Business QuestionThe Volume Shipping Query finds, for two given nations, the gross discounted revenues derived from lineitems inwhich parts were shipped from a supplier in either nation to a customer in the other nation during 1995 and 1996.The query lists the supplier nation, the customer nation, the year, and the revenue from shipments that took place inthat year.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
