github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats_quality/tpch/q14 (about) 1 import file=tpch_schema 2 ---- 3 4 import file=tpch_stats 5 ---- 6 7 # -------------------------------------------------- 8 # Q14 9 # Promotion Effect 10 # Monitors the market response to a promotion such as TV advertisements or a 11 # special campaign. 12 # 13 # Determines what percentage of the revenue in a given year and month was 14 # derived from promotional parts. The query considers only parts actually 15 # shipped in that month and gives the percentage. Revenue is defined as 16 # (l_extendedprice * (1-l_discount)). 17 # -------------------------------------------------- 18 save-tables database=tpch save-tables-prefix=q14 19 SELECT 20 100.00 * sum(CASE 21 WHEN p_type LIKE 'PROMO%' 22 THEN l_extendedprice * (1 - l_discount) 23 ELSE 0 24 END) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue 25 FROM 26 lineitem, 27 part 28 WHERE 29 l_partkey = p_partkey 30 AND l_shipdate >= DATE '1995-09-01' 31 AND l_shipdate < DATE '1995-09-01' + INTERVAL '1' MONTH; 32 ---- 33 project 34 ├── save-table-name: q14_project_1 35 ├── columns: promo_revenue:30(float) 36 ├── cardinality: [1 - 1] 37 ├── immutable, side-effects 38 ├── stats: [rows=1, distinct(30)=1, null(30)=0] 39 ├── key: () 40 ├── fd: ()-->(30) 41 ├── scalar-group-by 42 │ ├── save-table-name: q14_scalar_group_by_2 43 │ ├── columns: sum:27(float) sum:29(float) 44 │ ├── cardinality: [1 - 1] 45 │ ├── stats: [rows=1, distinct(27)=1, null(27)=0, distinct(29)=1, null(29)=0, distinct(27,29)=1, null(27,29)=0] 46 │ ├── key: () 47 │ ├── fd: ()-->(27,29) 48 │ ├── project 49 │ │ ├── save-table-name: q14_project_3 50 │ │ ├── columns: column26:26(float!null) column28:28(float!null) 51 │ │ ├── stats: [rows=82726.8788, distinct(26)=82726.8788, null(26)=0, distinct(28)=52210.2591, null(28)=0] 52 │ │ ├── inner-join (hash) 53 │ │ │ ├── save-table-name: q14_inner_join_4 54 │ │ │ ├── columns: l_partkey:2(int!null) l_extendedprice:6(float!null) l_discount:7(float!null) l_shipdate:11(date!null) p_partkey:17(int!null) p_type:21(varchar!null) 55 │ │ │ ├── stats: [rows=82726.8788, distinct(2)=67871.4531, null(2)=0, distinct(6)=51381.824, null(6)=0, distinct(7)=11, null(7)=0, distinct(11)=30, null(11)=0, distinct(17)=67871.4531, null(17)=0, distinct(21)=150, null(21)=0, distinct(6,7)=52210.2591, null(6,7)=0, distinct(6,7,21)=82726.8788, null(6,7,21)=0] 56 │ │ │ ├── fd: (17)-->(21), (2)==(17), (17)==(2) 57 │ │ │ ├── scan part 58 │ │ │ │ ├── save-table-name: q14_scan_5 59 │ │ │ │ ├── columns: p_partkey:17(int!null) p_type:21(varchar!null) 60 │ │ │ │ ├── stats: [rows=200000, distinct(17)=199241, null(17)=0, distinct(21)=150, null(21)=0] 61 │ │ │ │ │ histogram(17)= 0 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 62 │ │ │ │ │ <--- 27 ----- 1110 ----- 2241 ----- 3086 ----- 4134 ----- 5302 ----- 6222 ----- 7308 ----- 8249 ----- 9171 ----- 10049 ----- 10958 ----- 11832 ----- 13025 ----- 14063 ----- 14953 ----- 16249 ----- 17419 ----- 18363 ----- 19412 ----- 20257 ----- 21190 ----- 22110 ----- 23045 ----- 23956 ----- 24962 ----- 25942 ----- 26990 ----- 27934 ----- 28876 ----- 29513 ----- 30326 ----- 31259 ----- 32300 ----- 33577 ----- 34550 ----- 35562 ----- 36498 ----- 37475 ----- 38584 ----- 39641 ----- 40548 ----- 41605 ----- 42527 ----- 43612 ----- 44702 ----- 45701 ----- 46726 ----- 47795 ----- 48935 ----- 50152 ----- 51183 ----- 52001 ----- 52904 ----- 53868 ----- 54808 ----- 55986 ----- 57155 ----- 58516 ----- 59526 ----- 60557 ----- 61547 ----- 62369 ----- 63672 ----- 64583 ----- 65360 ----- 66147 ----- 67201 ----- 68142 ----- 69145 ----- 70209 ----- 71141 ----- 71923 ----- 73031 ----- 73987 ----- 74974 ----- 76170 ----- 77138 ----- 77849 ----- 78931 ----- 79832 ----- 80761 ----- 81843 ----- 82834 ----- 84032 ----- 85072 ----- 86287 ----- 87302 ----- 88422 ----- 89432 ----- 90550 ----- 91463 ----- 92249 ----- 93385 ----- 94789 ----- 96013 ----- 96893 ----- 98000 ----- 99008 ----- 100166 ----- 101263 ----- 102351 ----- 103236 ----- 104121 ----- 105363 ----- 106329 ----- 107325 ----- 108231 ----- 109054 ----- 110019 ----- 111185 ----- 112112 ----- 112908 ----- 113904 ----- 114785 ----- 115410 ----- 116526 ----- 117559 ----- 118310 ----- 119073 ----- 120034 ----- 120817 ----- 121744 ----- 122566 ----- 123720 ----- 124813 ----- 125835 ----- 126622 ----- 127651 ----- 128328 ----- 129315 ----- 130244 ----- 131450 ----- 132439 ----- 133288 ----- 134164 ----- 135298 ----- 136347 ----- 137243 ----- 138256 ----- 139427 ----- 140374 ----- 141371 ----- 142302 ----- 143322 ----- 144335 ----- 145333 ----- 146212 ----- 147321 ----- 148591 ----- 149594 ------ 150514 ------ 151361 ------ 152059 ------ 153070 ------ 154059 ------ 155259 ------ 156473 ------ 157690 ------ 158703 ------ 159675 ------ 160597 ------ 161668 ------ 162737 ------ 163955 ------ 164942 ------ 165924 ------ 167059 ------ 167866 ------ 169034 ------ 169935 ------ 170712 ------ 171806 ------ 172841 ------ 174078 ------ 175347 ------ 176430 ------ 177346 ------ 178566 ------ 179515 ------ 180677 ------ 181729 ------ 182983 ------ 183814 ------ 184892 ------ 185696 ------ 186611 ------ 187744 ------ 188974 ------ 189911 ------ 190671 ------ 191607 ------ 192820 ------ 193789 ------ 195057 ------ 196224 ------ 197231 ------ 198281 ------ 199119 ------ 199999 63 │ │ │ │ ├── key: (17) 64 │ │ │ │ └── fd: (17)-->(21) 65 │ │ │ ├── index-join lineitem 66 │ │ │ │ ├── save-table-name: q14_index_join_6 67 │ │ │ │ ├── columns: l_partkey:2(int!null) l_extendedprice:6(float!null) l_discount:7(float!null) l_shipdate:11(date!null) 68 │ │ │ │ ├── stats: [rows=82412.9303, distinct(2)=67871.4531, null(2)=0, distinct(6)=79374.1678, null(6)=0, distinct(7)=11, null(7)=0, distinct(11)=30, null(11)=0, distinct(6,7)=82412.9303, null(6,7)=0] 69 │ │ │ │ │ histogram(11)= 0 0 22564 3600 28205 2400 23077 2564.1 70 │ │ │ │ │ <--- '1995-08-31' ------- '1995-09-09' ------- '1995-09-20' ------- '1995-09-30' 71 │ │ │ │ └── scan lineitem@l_sd 72 │ │ │ │ ├── save-table-name: q14_scan_7 73 │ │ │ │ ├── columns: l_orderkey:1(int!null) l_linenumber:4(int!null) l_shipdate:11(date!null) 74 │ │ │ │ ├── constraint: /11/1/4: [/'1995-09-01' - /'1995-09-30'] 75 │ │ │ │ ├── stats: [rows=82412.9303, distinct(1)=80769.8604, null(1)=0, distinct(4)=7, null(4)=0, distinct(11)=30, null(11)=0] 76 │ │ │ │ │ histogram(1)= 0 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 403.81 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 412.06 8.2396 77 │ │ │ │ │ <--- 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 78 │ │ │ │ │ histogram(11)= 0 0 22564 3600 28205 2400 23077 2564.1 79 │ │ │ │ │ <--- '1995-08-31' ------- '1995-09-09' ------- '1995-09-20' ------- '1995-09-30' 80 │ │ │ │ ├── key: (1,4) 81 │ │ │ │ └── fd: (1,4)-->(11) 82 │ │ │ └── filters 83 │ │ │ └── l_partkey:2 = p_partkey:17 [type=bool, outer=(2,17), constraints=(/2: (/NULL - ]; /17: (/NULL - ]), fd=(2)==(17), (17)==(2)] 84 │ │ └── projections 85 │ │ ├── CASE WHEN p_type:21 LIKE 'PROMO%' THEN l_extendedprice:6 * (1.0 - l_discount:7) ELSE 0.0 END [as=column26:26, type=float, outer=(6,7,21)] 86 │ │ └── l_extendedprice:6 * (1.0 - l_discount:7) [as=column28:28, type=float, outer=(6,7)] 87 │ └── aggregations 88 │ ├── sum [as=sum:27, type=float, outer=(26)] 89 │ │ └── column26:26 [type=float] 90 │ └── sum [as=sum:29, type=float, outer=(28)] 91 │ └── column28:28 [type=float] 92 └── projections 93 └── (sum:27 * 100.0) / sum:29 [as=promo_revenue:30, type=float, outer=(27,29), immutable, side-effects] 94 95 stats table=q14_project_1 96 ---- 97 column_names row_count distinct_count null_count 98 {promo_revenue} 1 1 0 99 ~~~~ 100 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 101 {promo_revenue} 1.00 1.00 1.00 1.00 0.00 1.00 102 103 stats table=q14_scalar_group_by_2 104 ---- 105 column_names row_count distinct_count null_count 106 {sum_1} 1 1 0 107 {sum} 1 1 0 108 ~~~~ 109 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 110 {sum} 1.00 1.00 1.00 1.00 0.00 1.00 111 {sum_1} 1.00 1.00 1.00 1.00 0.00 1.00 112 113 stats table=q14_project_3 114 ---- 115 column_names row_count distinct_count null_count 116 {column26} 75983 12638 0 117 {column28} 75983 76207 0 118 ~~~~ 119 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 120 {column26} 82727.00 1.09 82727.00 6.55 <== 0.00 1.00 121 {column28} 82727.00 1.09 52210.00 1.46 0.00 1.00 122 123 stats table=q14_inner_join_4 124 ---- 125 column_names row_count distinct_count null_count 126 {l_discount} 75983 11 0 127 {l_extendedprice} 75983 72627 0 128 {l_partkey} 75983 63035 0 129 {l_shipdate} 75983 30 0 130 {p_partkey} 75983 63035 0 131 {p_type} 75983 150 0 132 ~~~~ 133 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 134 {l_discount} 82727.00 1.09 11.00 1.00 0.00 1.00 135 {l_extendedprice} 82727.00 1.09 51382.00 1.41 0.00 1.00 136 {l_partkey} 82727.00 1.09 67871.00 1.08 0.00 1.00 137 {l_shipdate} 82727.00 1.09 30.00 1.00 0.00 1.00 138 {p_partkey} 82727.00 1.09 67871.00 1.08 0.00 1.00 139 {p_type} 82727.00 1.09 150.00 1.00 0.00 1.00 140 141 stats table=q14_scan_5 142 ---- 143 column_names row_count distinct_count null_count 144 {p_partkey} 200000 199241 0 145 {p_type} 200000 150 0 146 ~~~~ 147 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 148 {p_partkey} 200000.00 1.00 199241.00 1.00 0.00 1.00 149 {p_type} 200000.00 1.00 150.00 1.00 0.00 1.00 150 151 stats table=q14_index_join_6 152 ---- 153 column_names row_count distinct_count null_count 154 {l_discount} 75983 11 0 155 {l_extendedprice} 75983 72627 0 156 {l_partkey} 75983 63035 0 157 {l_shipdate} 75983 30 0 158 ~~~~ 159 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 160 {l_discount} 82413.00 1.08 11.00 1.00 0.00 1.00 161 {l_extendedprice} 82413.00 1.08 79374.00 1.09 0.00 1.00 162 {l_partkey} 82413.00 1.08 67871.00 1.08 0.00 1.00 163 {l_shipdate} 82413.00 1.08 30.00 1.00 0.00 1.00