github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats_quality/tpch/q22 (about) 1 import file=tpch_schema 2 ---- 3 4 import file=tpch_stats 5 ---- 6 7 # -------------------------------------------------- 8 # Q22 9 # Global Sales Opportunity 10 # Identifies geographies where there are customers who may be likely to make a 11 # purchase. 12 # 13 # This query counts how many customers within a specific range of country codes 14 # have not placed orders for 7 years but who have a greater than average 15 # “positive” account balance. It also reflects the magnitude of that balance. 16 # Country code is defined as the first two characters of c_phone. 17 # -------------------------------------------------- 18 save-tables database=tpch save-tables-prefix=q22 19 SELECT 20 cntrycode, 21 count(*) AS numcust, 22 sum(c_acctbal) AS totacctbal 23 FROM ( 24 SELECT 25 substring(c_phone FROM 1 FOR 2) AS cntrycode, 26 c_acctbal 27 FROM 28 customer 29 WHERE 30 substring(c_phone FROM 1 FOR 2) in 31 ('13', '31', '23', '29', '30', '18', '17') 32 AND c_acctbal > ( 33 SELECT 34 avg(c_acctbal) 35 FROM 36 customer 37 WHERE 38 c_acctbal > 0.00 39 AND substring(c_phone FROM 1 FOR 2) in 40 ('13', '31', '23', '29', '30', '18', '17') 41 ) 42 AND NOT EXISTS ( 43 SELECT 44 * 45 FROM 46 orders 47 WHERE 48 o_custkey = c_custkey 49 ) 50 ) AS custsale 51 GROUP BY 52 cntrycode 53 ORDER BY 54 cntrycode; 55 ---- 56 sort 57 ├── save-table-name: q22_sort_1 58 ├── columns: cntrycode:27(string) numcust:28(int!null) totacctbal:29(float!null) 59 ├── immutable 60 ├── stats: [rows=1e-10, distinct(27)=1e-10, null(27)=0, distinct(28)=1e-10, null(28)=0, distinct(29)=1e-10, null(29)=0] 61 ├── key: (27) 62 ├── fd: (27)-->(28,29) 63 ├── ordering: +27 64 └── group-by 65 ├── save-table-name: q22_group_by_2 66 ├── columns: cntrycode:27(string) count_rows:28(int!null) sum:29(float!null) 67 ├── grouping columns: cntrycode:27(string) 68 ├── immutable 69 ├── stats: [rows=1e-10, distinct(27)=1e-10, null(27)=0, distinct(28)=1e-10, null(28)=0, distinct(29)=1e-10, null(29)=0] 70 ├── key: (27) 71 ├── fd: (27)-->(28,29) 72 ├── project 73 │ ├── save-table-name: q22_project_3 74 │ ├── columns: cntrycode:27(string) c_acctbal:6(float!null) 75 │ ├── immutable 76 │ ├── stats: [rows=1e-10, distinct(6)=1e-10, null(6)=0, distinct(27)=1e-10, null(27)=0] 77 │ ├── anti-join (lookup orders@o_ck) 78 │ │ ├── save-table-name: q22_lookup_join_4 79 │ │ ├── columns: c_custkey:1(int!null) c_phone:5(char!null) c_acctbal:6(float!null) 80 │ │ ├── key columns: [1] = [19] 81 │ │ ├── immutable 82 │ │ ├── stats: [rows=1e-10, distinct(1)=1e-10, null(1)=0, distinct(5)=1e-10, null(5)=0, distinct(6)=1e-10, null(6)=0] 83 │ │ ├── key: (1) 84 │ │ ├── fd: (1)-->(5,6) 85 │ │ ├── select 86 │ │ │ ├── save-table-name: q22_select_5 87 │ │ │ ├── columns: c_custkey:1(int!null) c_phone:5(char!null) c_acctbal:6(float!null) 88 │ │ │ ├── immutable 89 │ │ │ ├── stats: [rows=16666.6667, distinct(1)=16658.9936, null(1)=0, distinct(5)=16666.6667, null(5)=0, distinct(6)=16666.6667, null(6)=0] 90 │ │ │ ├── key: (1) 91 │ │ │ ├── fd: (1)-->(5,6) 92 │ │ │ ├── scan customer 93 │ │ │ │ ├── save-table-name: q22_scan_6 94 │ │ │ │ ├── columns: c_custkey:1(int!null) c_phone:5(char!null) c_acctbal:6(float!null) 95 │ │ │ │ ├── stats: [rows=150000, distinct(1)=148813, null(1)=0, distinct(5)=150000, null(5)=0, distinct(6)=140628, null(6)=0] 96 │ │ │ │ │ histogram(1)= 0 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 97 │ │ │ │ │ <--- 2 ----- 776 ----- 1422 ----- 2189 ----- 2973 ----- 3583 ----- 4390 ----- 5154 ----- 5962 ----- 6965 ----- 7596 ----- 8303 ----- 9167 ----- 9833 ----- 10695 ----- 11397 ----- 11979 ----- 12651 ----- 13397 ----- 14144 ----- 14951 ----- 15698 ----- 16460 ----- 17203 ----- 17846 ----- 18462 ----- 19390 ----- 20189 ----- 20852 ----- 21642 ----- 22379 ----- 23009 ----- 23856 ----- 24734 ----- 25473 ----- 26231 ----- 26978 ----- 27654 ----- 28276 ----- 29054 ----- 29727 ----- 30527 ----- 31177 ----- 32126 ----- 32984 ----- 33684 ----- 34316 ----- 35070 ----- 35703 ----- 36397 ----- 37156 ----- 37709 ----- 38488 ----- 39131 ----- 39740 ----- 40736 ----- 41459 ----- 42388 ----- 42999 ----- 43844 ----- 44571 ----- 45428 ----- 46283 ----- 46979 ----- 47712 ----- 48708 ----- 49487 ----- 50275 ----- 51131 ----- 51836 ----- 52652 ----- 53389 ----- 54179 ----- 54861 ----- 55609 ----- 56492 ----- 57284 ----- 57917 ----- 58793 ----- 59665 ----- 60285 ----- 60840 ----- 61523 ----- 62354 ----- 63178 ----- 63933 ----- 64642 ----- 65282 ----- 65864 ----- 66755 ----- 67407 ----- 68099 ----- 68875 ----- 69638 ----- 70304 ----- 71016 ----- 71830 ----- 72742 ----- 73590 ----- 74434 ----- 75274 ----- 75861 ----- 76547 ----- 77252 ----- 77978 ----- 78650 ----- 79313 ----- 79925 ----- 80677 ----- 81497 ----- 82205 ----- 82962 ----- 83879 ----- 84815 ----- 85521 ----- 86272 ----- 87140 ----- 87759 ----- 88634 ----- 89452 ----- 90192 ----- 90920 ----- 91756 ----- 92690 ----- 93299 ----- 93950 ----- 94812 ----- 95569 ----- 96295 ----- 96904 ----- 97499 ----- 98144 ----- 98764 ----- 99582 ----- 100453 ----- 101098 ----- 101892 ----- 102700 ----- 103419 ----- 104297 ----- 105040 ----- 105864 ----- 106498 ----- 107196 ----- 108022 ----- 108731 ----- 109398 ----- 110145 ----- 110849 ----- 111758 ----- 112501 ----- 113222 ----- 114019 ----- 114904 ----- 115693 ----- 116350 ----- 116955 ----- 117581 ----- 118366 ----- 119159 ----- 119902 ----- 120535 ----- 121321 ----- 121993 ----- 122769 ----- 123504 ----- 124225 ----- 124992 ----- 125632 ----- 126685 ----- 127641 ----- 128303 ----- 129042 ----- 129589 ----- 130548 ----- 131374 ----- 132325 ----- 133042 ----- 133883 ----- 134716 ----- 135520 ----- 136173 ----- 136858 ----- 137584 ----- 138381 ----- 139162 ----- 139923 ----- 140738 ----- 141557 ----- 142287 ----- 143002 ----- 143794 ----- 144420 ----- 145276 ----- 146100 ----- 146977 ----- 147821 ----- 148440 ----- 149247 ----- 149978 98 │ │ │ │ ├── key: (1) 99 │ │ │ │ └── fd: (1)-->(5,6) 100 │ │ │ └── filters 101 │ │ │ ├── substring(c_phone:5, 1, 2) IN ('13', '17', '18', '23', '29', '30', '31') [type=bool, outer=(5), immutable] 102 │ │ │ └── gt [type=bool, outer=(6), immutable, subquery, constraints=(/6: (/NULL - ])] 103 │ │ │ ├── c_acctbal:6 [type=float] 104 │ │ │ └── subquery [type=float] 105 │ │ │ └── scalar-group-by 106 │ │ │ ├── save-table-name: q22_scalar_group_by_7 107 │ │ │ ├── columns: avg:17(float) 108 │ │ │ ├── cardinality: [1 - 1] 109 │ │ │ ├── immutable 110 │ │ │ ├── stats: [rows=1, distinct(17)=1, null(17)=0] 111 │ │ │ ├── key: () 112 │ │ │ ├── fd: ()-->(17) 113 │ │ │ ├── select 114 │ │ │ │ ├── save-table-name: q22_select_8 115 │ │ │ │ ├── columns: c_phone:13(char!null) c_acctbal:14(float!null) 116 │ │ │ │ ├── immutable 117 │ │ │ │ ├── stats: [rows=16666.6667, distinct(13)=16666.6667, null(13)=0, distinct(14)=16666.6667, null(14)=0] 118 │ │ │ │ ├── scan customer 119 │ │ │ │ │ ├── save-table-name: q22_scan_9 120 │ │ │ │ │ ├── columns: c_phone:13(char!null) c_acctbal:14(float!null) 121 │ │ │ │ │ └── stats: [rows=150000, distinct(13)=150000, null(13)=0, distinct(14)=140628, null(14)=0] 122 │ │ │ │ └── filters 123 │ │ │ │ ├── c_acctbal:14 > 0.0 [type=bool, outer=(14), constraints=(/14: [/5e-324 - ]; tight)] 124 │ │ │ │ └── substring(c_phone:13, 1, 2) IN ('13', '17', '18', '23', '29', '30', '31') [type=bool, outer=(13), immutable] 125 │ │ │ └── aggregations 126 │ │ │ └── avg [as=avg:17, type=float, outer=(14)] 127 │ │ │ └── c_acctbal:14 [type=float] 128 │ │ └── filters (true) 129 │ └── projections 130 │ └── substring(c_phone:5, 1, 2) [as=cntrycode:27, type=string, outer=(5), immutable] 131 └── aggregations 132 ├── count-rows [as=count_rows:28, type=int] 133 └── sum [as=sum:29, type=float, outer=(6)] 134 └── c_acctbal:6 [type=float] 135 136 stats table=q22_sort_1 137 ---- 138 column_names row_count distinct_count null_count 139 {cntrycode} 7 7 0 140 {numcust} 7 7 0 141 {totacctbal} 7 7 0 142 ~~~~ 143 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 144 {cntrycode} 0.00 +Inf <== 0.00 +Inf <== 0.00 1.00 145 {numcust} 0.00 +Inf <== 0.00 +Inf <== 0.00 1.00 146 {totacctbal} 0.00 +Inf <== 0.00 +Inf <== 0.00 1.00 147 148 stats table=q22_group_by_2 149 ---- 150 column_names row_count distinct_count null_count 151 {cntrycode} 7 7 0 152 {count_rows} 7 7 0 153 {sum} 7 7 0 154 ~~~~ 155 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 156 {cntrycode} 0.00 +Inf <== 0.00 +Inf <== 0.00 1.00 157 {count_rows} 0.00 +Inf <== 0.00 +Inf <== 0.00 1.00 158 {sum} 0.00 +Inf <== 0.00 +Inf <== 0.00 1.00 159 160 stats table=q22_project_3 161 ---- 162 column_names row_count distinct_count null_count 163 {c_acctbal} 6384 6304 0 164 {cntrycode} 6384 7 0 165 ~~~~ 166 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 167 {c_acctbal} 0.00 +Inf <== 0.00 +Inf <== 0.00 1.00 168 {cntrycode} 0.00 +Inf <== 0.00 +Inf <== 0.00 1.00 169 170 stats table=q22_lookup_join_4 171 ---- 172 column_names row_count distinct_count null_count 173 {c_acctbal} 6384 6304 0 174 {c_custkey} 6384 6359 0 175 {c_phone} 6384 6428 0 176 ~~~~ 177 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 178 {c_acctbal} 0.00 +Inf <== 0.00 +Inf <== 0.00 1.00 179 {c_custkey} 0.00 +Inf <== 0.00 +Inf <== 0.00 1.00 180 {c_phone} 0.00 +Inf <== 0.00 +Inf <== 0.00 1.00 181 182 stats table=q22_select_5 183 ---- 184 column_names row_count distinct_count null_count 185 {c_acctbal} 19000 18527 0 186 {c_custkey} 19000 19097 0 187 {c_phone} 19000 19095 0 188 ~~~~ 189 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 190 {c_acctbal} 16667.00 1.14 16667.00 1.11 0.00 1.00 191 {c_custkey} 16667.00 1.14 16659.00 1.15 0.00 1.00 192 {c_phone} 16667.00 1.14 16667.00 1.15 0.00 1.00 193 194 stats table=q22_scan_6 195 ---- 196 column_names row_count distinct_count null_count 197 {c_acctbal} 150000 140628 0 198 {c_custkey} 150000 148813 0 199 {c_phone} 150000 150872 0 200 ~~~~ 201 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 202 {c_acctbal} 150000.00 1.00 140628.00 1.00 0.00 1.00 203 {c_custkey} 150000.00 1.00 148813.00 1.00 0.00 1.00 204 {c_phone} 150000.00 1.00 150000.00 1.01 0.00 1.00 205 206 stats table=q22_scalar_group_by_7 207 ---- 208 column_names row_count distinct_count null_count 209 {avg} 1 1 0 210 ~~~~ 211 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 212 {avg} 1.00 1.00 1.00 1.00 0.00 1.00 213 214 stats table=q22_select_8 215 ---- 216 column_names row_count distinct_count null_count 217 {c_acctbal} 38120 37172 0 218 {c_phone} 38120 38046 0 219 ~~~~ 220 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 221 {c_acctbal} 16667.00 2.29 <== 16667.00 2.23 <== 0.00 1.00 222 {c_phone} 16667.00 2.29 <== 16667.00 2.28 <== 0.00 1.00 223 224 stats table=q22_scan_9 225 ---- 226 column_names row_count distinct_count null_count 227 {c_acctbal} 150000 140628 0 228 {c_phone} 150000 150872 0 229 ~~~~ 230 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 231 {c_acctbal} 150000.00 1.00 140628.00 1.00 0.00 1.00 232 {c_phone} 150000.00 1.00 150000.00 1.01 0.00 1.00