TPC BENCHMARK (TM) H (779138), страница 10
Текст из файла (страница 10)
The query orders the answer by Supplier nation, Customer nation, and year (all ascending).2.4.7.2 Functional Query Definitionselectsupp_nation,cust_nation,l_year, sum(volume) as revenuefrom (selectn1.n_name as supp_nation,n2.n_name as cust_nation,extract(year from l_shipdate) as l_year,l_extendedprice * (1 - l_discount) as volumefromsupplier,lineitem,orders,customer,nation n1,nation n2wheres_suppkey = l_suppkeyand o_orderkey = l_orderkeyand c_custkey = o_custkeyand s_nationkey = n1.n_nationkeyand c_nationkey = n2.n_nationkeyand ((n1.n_name = '[NATION1]' and n2.n_name = '[NATION2]')or (n1.n_name = '[NATION2]' and n2.n_name = '[NATION1]'))and l_shipdate between date '1995-01-01' and date '1996-12-31') as shippinggroup bysupp_nation,cust_nation,l_yearorder bysupp_nation,cust_nation,l_year;2.4.7.3 Substitution ParametersValues for the following substitution parameters must be generated and used to build the executable query text:1.NATION1 is randomly selected within the list of values defined for N_NAME in Clause 4.2.3;2.NATION2 is randomly selected within the list of values defined for N_NAME in Clause 4.2.3 and must be different from the value selected for NATION1 in item 1 above.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 392.4.7.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.NATION1 = FRANCE;2.NATION2 = GERMANY.2.4.7.5 Sample OutputSUPP_NATIONCUST_NATIONYEARREVENUEFRANCEGERMANY199554639732.73TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 402.4.8National Market Share Query (Q8)This query determines how the market share of a given nation within a given region has changed over two years fora given part type.2.4.8.1 Business QuestionThe market share for a given nation within a given region is defined as the fraction of the revenue, the sum of[l_extendedprice * (1-l_discount)], from the products of a specified type in that region that was supplied by suppliers from the given nation.
The query determines this for the years 1995 and 1996 presented in this order.2.4.8.2 Functional Query Definitionselecto_year,sum(casewhen nation = '[NATION]'then volumeelse 0end) / sum(volume) as mkt_sharefrom (selectextract(year from o_orderdate) as o_year,l_extendedprice * (1-l_discount) as volume,n2.n_name as nationfrompart,supplier,lineitem,orders,customer,nation n1,nation n2,regionwherep_partkey = l_partkeyand s_suppkey = l_suppkeyand l_orderkey = o_orderkeyand o_custkey = c_custkeyand c_nationkey = n1.n_nationkeyand n1.n_regionkey = r_regionkeyand r_name = '[REGION]'and s_nationkey = n2.n_nationkeyand o_orderdate between date '1995-01-01' and date '1996-12-31'and p_type = '[TYPE]') as all_nationsgroup byo_yearorder byo_year;2.4.8.3 Substitution ParametersValues for the following substitution parameters must be generated and used to build the executable query text:1.NATION is randomly selected within the list of values defined for N_NAME in Clause 4.2.3;2.REGION is the value defined in Clause 4.2.3 for R_NAME where R_REGIONKEY corresponds toN_REGIONKEY for the selected NATION in item 1 above;3.TYPE is randomly selected within the list of 3-syllable strings defined for Types in Clause 4.2.2.13.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 412.4.8.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.NATION = BRAZIL;2.REGION = AMERICA;3.TYPE = ECONOMY ANODIZED STEEL.2.4.8.5 Sample OutputYEARMKT_SHARE1995.03TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 422.4.9Product Type Profit Measure Query (Q9)This query determines how much profit is made on a given line of parts, broken out by supplier nation and year.2.4.9.1 Business QuestionThe Product Type Profit Measure Query finds, for each nation and each year, the profit for all parts ordered in thatyear that contain a specified substring in their names and that were filled by a supplier in that nation.
The profit isdefined as the sum of [(l_extendedprice*(1-l_discount)) - (ps_supplycost * l_quantity)] for all lineitems describingparts in the specified line. The query lists the nations in ascending alphabetical order and, for each nation, the yearand profit in descending order by year (most recent first).2.4.9.2 Functional Query Definitionselectnation,o_year,sum(amount) as sum_profitfrom (selectn_name as nation,extract(year from o_orderdate) as o_year,l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amountfrompart,supplier,lineitem,partsupp,orders,nationwheres_suppkey = l_suppkeyand ps_suppkey = l_suppkeyand ps_partkey = l_partkeyand p_partkey = l_partkeyand o_orderkey = l_orderkeyand s_nationkey = n_nationkeyand p_name like '%[COLOR]%') as profitgroup bynation,o_yearorder bynation,o_year desc;2.4.9.3 Substitution ParametersValues for the following substitution parameter must be generated and used to build the executable query text:1.COLOR is randomly selected within the list of values defined for the generation of P_NAME in Clause 4.2.3.2.4.9.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.COLOR = green.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 432.4.9.5 Sample OutputNATIONYEARSUM_PROFITALGERIA199831342867.24TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 442.4.10Returned Item Reporting Query (Q10)The query identifies customers who might be having problems with the parts that are shipped to them.2.4.10.1 Business questionThe Returned Item Reporting Query finds the top 20 customers, in terms of their effect on lost revenue for a givenquarter, who have returned parts.
The query considers only parts that were ordered in the specified quarter. Thequery lists the customer's name, address, nation, phone number, account balance, comment information and revenuelost. The customers are listed in descending order of lost revenue. Revenue lost is defined assum(l_extendedprice*(1-l_discount)) for all qualifying lineitems.2.4.10.2 Functional Query DefinitionReturn the first 20 selected rowsselectc_custkey,c_name,sum(l_extendedprice * (1 - l_discount)) as revenue,c_acctbal,n_name,c_address,c_phone,c_commentfromcustomer,orders,lineitem,nationwherec_custkey = o_custkeyand l_orderkey = o_orderkeyand o_orderdate >= date '[DATE]'and o_orderdate < date '[DATE]' + interval '3' monthand l_returnflag = 'R'and c_nationkey = n_nationkeygroup byc_custkey,c_name,c_acctbal,c_phone,n_name,c_address,c_commentorder byrevenue desc;2.4.10.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 from the second month of 1993 to the first month of 1995.2.4.10.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-10-01.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 452.4.10.5 Sample OutputC_CUSTKEYC_NAMEREVENUEC_ACCTBALN_NAME57040Customer#000057040734235.24632.87JAPANC_ADDRESSC_PHONEC_COMMENTEioyzjf4pp22-895-641-3466sits.
slyly regular requests sleep alongsideof the regular instTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 462.4.11Important Stock Identification Query (Q11)This query finds the most important subset of suppliers' stock in a given nation.2.4.11.1 Business QuestionThe Important Stock Identification Query finds, from scanning the available stock of suppliers in a given nation, allthe parts that represent a significant percentage of the total value of all available parts. The query displays the partnumber and the value of those parts in descending order of value.2.4.11.2 Functional Query Definitionselectps_partkey,sum(ps_supplycost * ps_availqty) as valuefrompartsupp,supplier,nationwhereps_suppkey = s_suppkeyand s_nationkey = n_nationkeyand n_name = '[NATION]'group byps_partkey havingsum(ps_supplycost * ps_availqty) > (selectsum(ps_supplycost * ps_availqty) * [FRACTION]frompartsupp,supplier,nationwhereps_suppkey = s_suppkeyand s_nationkey = n_nationkeyand n_name = '[NATION]')order byvalue desc;2.4.11.3 Substitution ParametersValues for the following substitution parameter must be generated and used to build the executable query text:1.NATION is randomly selected within the list of values defined for N_NAME in Clause 4.2.3;2.FRACTION is chosen as 0.0001 / SF.2.4.11.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.NATION = GERMANY;2.FRACTION = 0.0001.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 472.4.11.5 Sample OutputPS_PARTKEYVALUE12976017538456.86TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 482.4.12Shipping Modes and Order Priority Query (Q12)This query determines whether selecting less expensive modes of shipping is negatively affecting the critical-priority orders by causing more parts to be received by customers after the committed date.2.4.12.1 Business QuestionThe Shipping Modes and Order Priority Query counts, by ship mode, for lineitems actually received by customers ina given year, the number of lineitems belonging to orders for which the l_receiptdate exceeds the l_commitdate fortwo different specified ship modes.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
