github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats_quality/tpch/q06 (about) 1 import file=tpch_schema 2 ---- 3 4 import file=tpch_stats 5 ---- 6 7 # -------------------------------------------------- 8 # Q6 9 # Forecasting Revenue Change 10 # Quantifies the amount of revenue increase that would have resulted from 11 # eliminating certain companywide discounts in a given percentage range in a 12 # given year. Asking this type of "what if" query can be used to look for ways 13 # to increase revenues. 14 # 15 # Considers all the lineitems shipped in a given year with discounts between 16 # DISCOUNT-0.01 and DISCOUNT+0.01. The query lists the amount by which the total 17 # revenue would have increased if these discounts had been eliminated for 18 # lineitems with l_quantity less than quantity. Note that the potential revenue 19 # increase is equal to the sum of [l_extendedprice * l_discount] for all 20 # lineitems with discounts and quantities in the qualifying range. 21 # -------------------------------------------------- 22 save-tables database=tpch save-tables-prefix=q6 23 SELECT 24 sum(l_extendedprice * l_discount) AS revenue 25 FROM 26 lineitem 27 WHERE 28 l_shipdate >= DATE '1994-01-01' 29 AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR 30 AND l_discount BETWEEN 0.06 - 0.01 AND 0.06 + 0.01 31 AND l_quantity < 24; 32 ---- 33 scalar-group-by 34 ├── save-table-name: q6_scalar_group_by_1 35 ├── columns: revenue:18(float) 36 ├── cardinality: [1 - 1] 37 ├── stats: [rows=1, distinct(18)=1, null(18)=0] 38 ├── key: () 39 ├── fd: ()-->(18) 40 ├── project 41 │ ├── save-table-name: q6_project_2 42 │ ├── columns: column17:17(float!null) 43 │ ├── stats: [rows=34745.8339, distinct(17)=34745.8339, null(17)=0] 44 │ ├── select 45 │ │ ├── save-table-name: q6_select_3 46 │ │ ├── columns: l_quantity:5(float!null) l_extendedprice:6(float!null) l_discount:7(float!null) l_shipdate:11(date!null) 47 │ │ ├── stats: [rows=34745.8339, distinct(5)=16.6666667, null(5)=0, distinct(6)=34199.2567, null(6)=0, distinct(7)=1.22222222, null(7)=0, distinct(11)=365, null(11)=0, distinct(6,7)=34745.8339, null(6,7)=0, distinct(5,7,11)=7435.18519, null(5,7,11)=0] 48 │ │ │ histogram(11)= 0 0 116.69 155.56 955.78 133.34 955.78 133.34 978 133.34 1066.9 44.446 1022.4 88.892 1000.2 133.34 1066.9 44.446 1000.2 177.78 1044.7 111.12 933.55 177.78 1066.9 177.78 1066.9 22.223 1044.7 44.446 911.29 244.49 911.29 177.78 1044.7 133.34 1022.4 133.34 1000.2 155.56 1044.7 88.892 1044.7 155.56 1066.9 44.446 1044.7 66.669 1044.7 44.446 1044.7 111.12 1000.2 177.78 1066.9 22.223 1000.2 133.34 955.78 200.04 1000.2 88.892 1044.7 111.12 426.02 85.204 49 │ │ │ <--- '1993-12-31' -------- '1994-01-02' -------- '1994-01-16' -------- '1994-01-30' ----- '1994-02-08' -------- '1994-02-18' -------- '1994-03-04' -------- '1994-03-15' -------- '1994-03-29' -------- '1994-04-10' -------- '1994-04-24' -------- '1994-05-03' -------- '1994-05-13' -------- '1994-05-24' -------- '1994-06-04' -------- '1994-06-13' -------- '1994-06-22' -------- '1994-07-05' -------- '1994-07-16' -------- '1994-07-29' -------- '1994-08-09' -------- '1994-08-22' -------- '1994-09-04' -------- '1994-09-15' -------- '1994-09-29' -------- '1994-10-10' -------- '1994-10-20' -------- '1994-11-03' -------- '1994-11-16' -------- '1994-11-29' -------- '1994-12-11' -------- '1994-12-25' -------- '1994-12-31' 50 │ │ ├── index-join lineitem 51 │ │ │ ├── save-table-name: q6_index_join_4 52 │ │ │ ├── columns: l_quantity:5(float!null) l_extendedprice:6(float!null) l_discount:7(float!null) l_shipdate:11(date!null) 53 │ │ │ ├── stats: [rows=938137.516, distinct(5)=50, null(5)=0, distinct(6)=618257.092, null(6)=0, distinct(7)=11, null(7)=0, distinct(11)=365, null(11)=0] 54 │ │ │ └── scan lineitem@l_sd 55 │ │ │ ├── save-table-name: q6_scan_5 56 │ │ │ ├── columns: l_orderkey:1(int!null) l_linenumber:4(int!null) l_shipdate:11(date!null) 57 │ │ │ ├── constraint: /11/1/4: [/'1994-01-01' - /'1994-12-31'] 58 │ │ │ ├── stats: [rows=938137.516, distinct(1)=744144.6, null(1)=0, distinct(4)=7, null(4)=0, distinct(11)=365, null(11)=0] 59 │ │ │ │ histogram(1)= 0 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4596.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 4690.7 93.795 60 │ │ │ │ <--- 326 --------- 28929 -------- 50503 -------- 89793 -------- 115938 -------- 146944 -------- 176768 -------- 211201 -------- 237860 -------- 266885 -------- 297604 -------- 330021 -------- 365889 -------- 398951 -------- 426117 -------- 451328 -------- 472134 -------- 499590 -------- 529284 -------- 557254 -------- 589154 -------- 619394 -------- 642951 -------- 670113 -------- 692931 -------- 721157 -------- 751687 -------- 777766 -------- 804582 -------- 836740 -------- 868868 -------- 898912 -------- 922500 -------- 946403 -------- 984870 -------- 1007936 -------- 1030117 -------- 1062275 -------- 1093572 -------- 1120709 -------- 1150981 -------- 1182786 -------- 1206406 -------- 1234116 -------- 1260961 -------- 1290502 -------- 1329510 -------- 1355426 -------- 1381313 -------- 1409796 -------- 1445254 -------- 1479233 -------- 1504935 -------- 1531079 -------- 1559650 -------- 1583616 -------- 1617504 -------- 1655749 -------- 1685185 -------- 1718183 -------- 1747716 -------- 1772131 -------- 1802372 -------- 1833315 -------- 1862403 -------- 1897894 -------- 1922819 -------- 1954405 -------- 1979329 -------- 2009859 -------- 2041670 -------- 2070851 -------- 2093828 -------- 2127973 -------- 2167777 -------- 2194883 -------- 2227814 -------- 2262437 -------- 2296353 -------- 2321024 -------- 2346051 -------- 2376257 -------- 2404932 -------- 2446273 -------- 2474081 -------- 2504515 -------- 2535302 -------- 2561413 -------- 2592737 -------- 2616801 -------- 2646112 -------- 2676546 -------- 2702116 -------- 2732454 -------- 2765382 -------- 2799495 -------- 2828866 -------- 2868737 -------- 2910625 -------- 2938464 -------- 2963140 -------- 3003302 -------- 3043264 -------- 3069123 -------- 3095909 -------- 3126693 -------- 3160485 -------- 3196039 -------- 3229504 -------- 3259712 -------- 3286439 -------- 3318852 -------- 3346821 -------- 3370119 -------- 3395204 -------- 3425888 -------- 3448611 -------- 3476130 -------- 3502372 -------- 3529474 -------- 3556390 -------- 3583553 -------- 3612550 -------- 3647875 -------- 3679140 -------- 3702661 -------- 3738017 -------- 3778050 -------- 3806114 -------- 3839074 -------- 3872805 -------- 3905697 -------- 3926212 -------- 3959841 -------- 3997281 -------- 4033861 -------- 4063591 -------- 4097831 -------- 4124807 -------- 4158656 -------- 4195748 -------- 4234274 -------- 4269952 -------- 4298949 -------- 4332806 -------- 4364705 -------- 4398246 -------- 4430695 -------- 4466403 -------- 4494662 -------- 4524420 -------- 4558561 -------- 4601092 -------- 4632871 -------- 4658694 -------- 4690501 -------- 4728066 -------- 4758657 -------- 4788294 -------- 4818597 -------- 4855874 -------- 4890913 -------- 4915366 -------- 4940709 -------- 4972357 -------- 4995298 -------- 5019523 -------- 5043329 -------- 5077376 -------- 5109920 -------- 5136582 -------- 5161152 -------- 5191846 -------- 5219973 -------- 5251015 -------- 5282021 -------- 5312355 -------- 5343207 -------- 5381318 -------- 5416163 -------- 5445382 -------- 5476933 -------- 5509185 -------- 5539237 -------- 5566818 -------- 5588739 -------- 5620481 -------- 5644001 -------- 5667010 -------- 5689476 -------- 5724709 -------- 5755398 -------- 5790598 -------- 5819425 -------- 5846341 -------- 5874656 -------- 5908067 -------- 5933572 -------- 5962659 -------- 5999971 61 │ │ │ │ histogram(11)= 0 0 3150.6 4200 25805 3600 25805 3600 26405 3600 28805 1200 27605 2400 27005 3600 28805 1200 27005 4800 28205 3000 25205 4800 28805 4800 28805 600 28205 1200 24604 6601 24604 4800 28205 3600 27605 3600 27005 4200 28205 2400 28205 4200 28805 1200 28205 1800 28205 1200 28205 3000 27005 4800 28805 600 27005 3600 25805 5401 27005 2400 28205 3000 11502 2300.4 62 │ │ │ │ <--- '1993-12-31' -------- '1994-01-02' ------- '1994-01-16' ------- '1994-01-30' ------- '1994-02-08' ------- '1994-02-18' ------- '1994-03-04' ------- '1994-03-15' ------- '1994-03-29' ------- '1994-04-10' ------- '1994-04-24' ------- '1994-05-03' ------- '1994-05-13' ------- '1994-05-24' ------- '1994-06-04' ------- '1994-06-13' ------- '1994-06-22' ------- '1994-07-05' ------- '1994-07-16' ------- '1994-07-29' ------- '1994-08-09' ------- '1994-08-22' ------- '1994-09-04' ------- '1994-09-15' ------- '1994-09-29' ------- '1994-10-10' ------- '1994-10-20' ------- '1994-11-03' ------- '1994-11-16' ------- '1994-11-29' ------- '1994-12-11' ------- '1994-12-25' ------- '1994-12-31' 63 │ │ │ ├── key: (1,4) 64 │ │ │ └── fd: (1,4)-->(11) 65 │ │ └── filters 66 │ │ ├── (l_discount:7 >= 0.05) AND (l_discount:7 <= 0.07) [type=bool, outer=(7), constraints=(/7: [/0.05 - /0.07]; tight)] 67 │ │ └── l_quantity:5 < 24.0 [type=bool, outer=(5), constraints=(/5: (/NULL - /23.999999999999996]; tight)] 68 │ └── projections 69 │ └── l_extendedprice:6 * l_discount:7 [as=column17:17, type=float, outer=(6,7)] 70 └── aggregations 71 └── sum [as=sum:18, type=float, outer=(17)] 72 └── column17:17 [type=float] 73 74 stats table=q6_scalar_group_by_1 75 ---- 76 column_names row_count distinct_count null_count 77 {revenue} 1 1 0 78 ~~~~ 79 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 80 {revenue} 1.00 1.00 1.00 1.00 0.00 1.00 81 82 stats table=q6_project_2 83 ---- 84 column_names row_count distinct_count null_count 85 {column17} 114160 108866 0 86 ~~~~ 87 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 88 {column17} 34746.00 3.29 <== 34746.00 3.13 <== 0.00 1.00 89 90 stats table=q6_select_3 91 ---- 92 column_names row_count distinct_count null_count 93 {l_discount} 114160 3 0 94 {l_extendedprice} 114160 98751 0 95 {l_quantity} 114160 23 0 96 {l_shipdate} 114160 365 0 97 ~~~~ 98 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 99 {l_discount} 34746.00 3.29 <== 1.00 3.00 <== 0.00 1.00 100 {l_extendedprice} 34746.00 3.29 <== 34199.00 2.89 <== 0.00 1.00 101 {l_quantity} 34746.00 3.29 <== 17.00 1.35 0.00 1.00 102 {l_shipdate} 34746.00 3.29 <== 365.00 1.00 0.00 1.00 103 104 stats table=q6_index_join_4 105 ---- 106 column_names row_count distinct_count null_count 107 {l_discount} 909455 11 0 108 {l_extendedprice} 909455 565291 0 109 {l_quantity} 909455 50 0 110 {l_shipdate} 909455 365 0 111 ~~~~ 112 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 113 {l_discount} 938138.00 1.03 11.00 1.00 0.00 1.00 114 {l_extendedprice} 938138.00 1.03 618257.00 1.09 0.00 1.00 115 {l_quantity} 938138.00 1.03 50.00 1.00 0.00 1.00 116 {l_shipdate} 938138.00 1.03 365.00 1.00 0.00 1.00