TPC BENCHMARK (TM) H (779138), страница 11
Текст из файла (страница 11)
Only lineitems that were actually shipped before the l_commitdate are considered. The late lineitems are partitioned into two groups, those with priority URGENT or HIGH, and those with apriority other than URGENT or HIGH.2.4.12.2 Functional Query Definitionselectl_shipmode,sum(casewhen o_orderpriority ='1-URGENT'or o_orderpriority ='2-HIGH'then 1else 0end) as high_line_count,sum(casewhen o_orderpriority <> '1-URGENT'and o_orderpriority <> '2-HIGH'then 1else 0end) as low_line_countfromorders,lineitemwhereo_orderkey = l_orderkeyand l_shipmode in ('[SHIPMODE1]', '[SHIPMODE2]')and l_commitdate < l_receiptdateand l_shipdate < l_commitdateand l_receiptdate >= date '[DATE]'and l_receiptdate < date '[DATE]' + interval '1' yeargroup byl_shipmodeorder byl_shipmode;2.4.12.3 Substitution ParametersValues for the following substitution parameters must be generated and used to build the executable query text:1.SHIPMODE1 is randomly selected within the list of values defined for Modes in Clause 4.2.2.13;2.SHIPMODE2 is randomly selected within the list of values defined for Modes in Clause 4.2.2.13 and must bedifferent from the value selected for SHIPMODE1 in item 1;3.DATE is the first of January of a randomly selected year within [1993 ..
1997].2.4.12.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.SHIPMODE1 = MAIL;TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 492.SHIPMODE2 = SHIP;3.DATE = 1994-01-01.2.4.12.5 Sample OutputL_SHIPMODEHIGH_LINE_COUNTLOW_LINE_COUNTMAIL62029324TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 502.4.13Customer Distribution Query (Q13)This query seeks relationships between customers and the size of their orders.2.4.13.1 Business QuestionThis query determines the distribution of customers by the number of orders they have made, including customerswho have no record of orders, past or present. It counts and reports how many customers have no orders, how manyhave 1, 2, 3, etc.
A check is made to ensure that the orders counted do not fall into one of several special categoriesof orders. Special categories are identified in the order comment column by looking for a particular pattern.2.4.13.2 Functional Query Definitionselectc_count, count(*) as custdistfrom (selectc_custkey,count(o_orderkey)fromcustomer left outer join orders onc_custkey = o_custkeyand o_comment not like ‘%[WORD1]%[WORD2]%’group byc_custkey)as c_orders (c_custkey, c_count)group byc_countorder bycustdist desc,c_count desc;2.4.13.3 Substitution Parameters1.WORD1 is randomly selected from 4 possible values: special, pending, unusual, express.2.WORD2 is randomly selected from 4 possible values: packages, requests, accounts, deposits.2.4.13.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:Values for substitution parameters:1.WORD1 = special.2.WORD2 = requests.2.4.13.5 Sample OutputC_COUNTCUSTDIST96641TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 512.4.14Promotion Effect Query (Q14)This query monitors the market response to a promotion such as TV advertisements or a special campaign.2.4.14.1 Business QuestionThe Promotion Effect Query determines what percentage of the revenue in a given year and month was derived frompromotional parts.
The query considers only parts actually shipped in that month and gives the percentage. Revenueis defined as (l_extendedprice * (1-l_discount)).2.4.14.2 Functional Query Definitionselect100.00 * sum(casewhen p_type like 'PROMO%'then l_extendedprice*(1-l_discount)else 0end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenuefromlineitem,partwherel_partkey = p_partkeyand l_shipdate >= date '[DATE]'and l_shipdate < date '[DATE]' + interval '1' month;2.4.14.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 month randomly selected from a random year within [1993 ..
1997].2.4.14.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 = 1995-09-01.2.4.14.5 Sample OutputPROMO_REVENUE16.38TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 522.4.15Top Supplier Query (Q15)This query determines the top supplier so it can be rewarded, given more business, or identified for special recognition.2.4.15.1 Business QuestionThe Top Supplier Query finds the supplier who contributed the most to the overall revenue for parts shipped duringa given quarter of a given year.
In case of a tie, the query lists all suppliers whose contribution was equal to themaximum, presented in supplier number order.2.4.15.2 Functional Query Definitioncreate view revenue[STREAM_ID] (supplier_no, total_revenue) asselectl_suppkey,sum(l_extendedprice * (1 - l_discount))fromlineitemwherel_shipdate >= date '[DATE]'and l_shipdate < date '[DATE]' + interval '3' monthgroup byl_suppkey;selects_suppkey,s_name,s_address,s_phone,total_revenuefromsupplier,revenue[STREAM_ID]wheres_suppkey = supplier_noand total_revenue = (selectmax(total_revenue)fromrevenue[STREAM_ID])order bys_suppkey;drop view revenue[STREAM_ID];2.4.15.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.15.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 = 1996-01-01.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 532.4.15.5 Sample OutputS_SUPPKEYS_NAMES_ADDRESSS_PHONETOTAL_REVENUE8449Supplier#000008449Wp34zim9qYFbVctdW20-469-856-88731772627.21TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 542.4.16Parts/Supplier Relationship Query (Q16)This query finds out how many suppliers can supply parts with given attributes.
It might be used, for example, todetermine whether there is a sufficient number of suppliers for heavily ordered parts.2.4.16.1 Business QuestionThe Parts/Supplier Relationship Query counts the number of suppliers who can supply parts that satisfy a particularcustomer's requirements. The customer is interested in parts of eight different sizes as long as they are not of a giventype, not of a given brand, and not from a supplier who has had complaints registered at the Better Business Bureau.Results must be presented in descending count and ascending brand, type, and size.2.4.16.2 Functional Query Definitionselectp_brand,p_type,p_size,count(distinct ps_suppkey) as supplier_cntfrompartsupp,partwherep_partkey = ps_partkeyand p_brand <> '[BRAND]'and p_type not like '[TYPE]%'and p_size in ([SIZE1], [SIZE2], [SIZE3], [SIZE4], [SIZE5], [SIZE6], [SIZE7], [SIZE8])and ps_suppkey not in (selects_suppkeyfromsupplierwheres_comment like '%Customer%Complaints%')group byp_brand,p_type,p_sizeorder bysupplier_cnt desc,p_brand,p_type,p_size;2.4.16.3 Substitution ParametersValues for the following substitution parameters must be generated and used to build the executable query text:1.BRAND = Brand#MN where M and N are two single character strings representing two numbers randomly andindependently selected within [1 ..
5];2.TYPE is made of the first 2 syllables of a string randomly selected within the list of 3-syllable strings definedfor Types in Clause 4.2.2.13;3.SIZE1 is randomly selected as a set of eight different values within [1 .. 50];4.SIZE2 is randomly selected as a set of eight different values within [1 .. 50];5.SIZE3 is randomly selected as a set of eight different values within [1 .. 50];6.SIZE4 is randomly selected as a set of eight different values within [1 .. 50];TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 557.SIZE5 is randomly selected as a set of eight different values within [1 .. 50];8.SIZE6 is randomly selected as a set of eight different values within [1 ..
50];9.SIZE7 is randomly selected as a set of eight different values within [1 .. 50];10. SIZE8 is randomly selected as a set of eight different values within [1 .. 50].2.4.16.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#45.2.TYPE = MEDIUM POLISHED .3.SIZE1 = 494.SIZE2 = 145.SIZE3 = 236.SIZE4 = 457.SIZE5 = 198.SIZE6 = 39.SIZE7 = 3610. SIZE8 = 9.2.4.16.5 Sample OutputP_BRANDP_TYPEP_SIZESUPPLIER_CNTBrand#41MEDIUM BRUSHED TIN328TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 562.4.17Small-Quantity-Order Revenue Query (Q17)This query determines how much average yearly revenue would be lost if orders were no longer filled for smallquantities of certain parts.
This may reduce overhead expenses by concentrating sales on larger shipments.2.4.17.1 Business QuestionThe Small-Quantity-Order Revenue Query considers parts of a given brand and with a given container type anddetermines the average lineitem quantity of such parts ordered for all orders (past and pending) in the 7-year database. What would be the average yearly gross (undiscounted) loss in revenue if orders for these parts with a quantityof less than 20% of this average were no longer taken?2.4.17.2 Functional Query Definitionselectsum(l_extendedprice) / 7.0 as avg_yearlyfromlineitem,partwherep_partkey = l_partkeyand p_brand = '[BRAND]'and p_container = '[CONTAINER]'and l_quantity < (select0.2 * avg(l_quantity)fromlineitemwherel_partkey = p_partkey);2.4.17.3 Substitution ParametersValues for the following substitution parameter must be generated and used to build the executable query text:1.BRAND = 'Brand#MN' where MN is a two character string representing two numbers randomly and independently selected within [1 ..
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
