github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query66.sql (about) 1 -- Licensed to the Apache Software Foundation (ASF) under one 2 -- or more contributor license agreements. See the NOTICE file 3 -- distributed with this work for additional information 4 -- regarding copyright ownership. The ASF licenses this file 5 -- to you under the Apache License, Version 2.0 (the 6 -- "License"); you may not use this file except in compliance 7 -- with the License. You may obtain a copy of the License at 8 -- 9 -- http://www.apache.org/licenses/LICENSE-2.0 10 -- 11 -- Unless required by applicable law or agreed to in writing, software 12 -- distributed under the License is distributed on an "AS IS" BASIS, 13 -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 14 -- See the License for the specific language governing permissions and 15 -- limitations under the License. 16 17 select 18 w_warehouse_name 19 ,w_warehouse_sq_ft 20 ,w_city 21 ,w_county 22 ,w_state 23 ,w_country 24 ,ship_carriers 25 ,`year` 26 ,sum(jan_sales) as jan_sales 27 ,sum(feb_sales) as feb_sales 28 ,sum(mar_sales) as mar_sales 29 ,sum(apr_sales) as apr_sales 30 ,sum(may_sales) as may_sales 31 ,sum(jun_sales) as jun_sales 32 ,sum(jul_sales) as jul_sales 33 ,sum(aug_sales) as aug_sales 34 ,sum(sep_sales) as sep_sales 35 ,sum(oct_sales) as oct_sales 36 ,sum(nov_sales) as nov_sales 37 ,sum(dec_sales) as dec_sales 38 ,sum(jan_sales/w_warehouse_sq_ft) as jan_sales_per_sq_foot 39 ,sum(feb_sales/w_warehouse_sq_ft) as feb_sales_per_sq_foot 40 ,sum(mar_sales/w_warehouse_sq_ft) as mar_sales_per_sq_foot 41 ,sum(apr_sales/w_warehouse_sq_ft) as apr_sales_per_sq_foot 42 ,sum(may_sales/w_warehouse_sq_ft) as may_sales_per_sq_foot 43 ,sum(jun_sales/w_warehouse_sq_ft) as jun_sales_per_sq_foot 44 ,sum(jul_sales/w_warehouse_sq_ft) as jul_sales_per_sq_foot 45 ,sum(aug_sales/w_warehouse_sq_ft) as aug_sales_per_sq_foot 46 ,sum(sep_sales/w_warehouse_sq_ft) as sep_sales_per_sq_foot 47 ,sum(oct_sales/w_warehouse_sq_ft) as oct_sales_per_sq_foot 48 ,sum(nov_sales/w_warehouse_sq_ft) as nov_sales_per_sq_foot 49 ,sum(dec_sales/w_warehouse_sq_ft) as dec_sales_per_sq_foot 50 ,sum(jan_net) as jan_net 51 ,sum(feb_net) as feb_net 52 ,sum(mar_net) as mar_net 53 ,sum(apr_net) as apr_net 54 ,sum(may_net) as may_net 55 ,sum(jun_net) as jun_net 56 ,sum(jul_net) as jul_net 57 ,sum(aug_net) as aug_net 58 ,sum(sep_net) as sep_net 59 ,sum(oct_net) as oct_net 60 ,sum(nov_net) as nov_net 61 ,sum(dec_net) as dec_net 62 from ( 63 (select 64 w_warehouse_name 65 ,w_warehouse_sq_ft 66 ,w_city 67 ,w_county 68 ,w_state 69 ,w_country 70 ,'DIAMOND' || ',' || 'AIRBORNE' as ship_carriers 71 ,d_year as `year` 72 ,sum(case when d_moy = 1 73 then ws_sales_price* ws_quantity else 0 end) as jan_sales 74 ,sum(case when d_moy = 2 75 then ws_sales_price* ws_quantity else 0 end) as feb_sales 76 ,sum(case when d_moy = 3 77 then ws_sales_price* ws_quantity else 0 end) as mar_sales 78 ,sum(case when d_moy = 4 79 then ws_sales_price* ws_quantity else 0 end) as apr_sales 80 ,sum(case when d_moy = 5 81 then ws_sales_price* ws_quantity else 0 end) as may_sales 82 ,sum(case when d_moy = 6 83 then ws_sales_price* ws_quantity else 0 end) as jun_sales 84 ,sum(case when d_moy = 7 85 then ws_sales_price* ws_quantity else 0 end) as jul_sales 86 ,sum(case when d_moy = 8 87 then ws_sales_price* ws_quantity else 0 end) as aug_sales 88 ,sum(case when d_moy = 9 89 then ws_sales_price* ws_quantity else 0 end) as sep_sales 90 ,sum(case when d_moy = 10 91 then ws_sales_price* ws_quantity else 0 end) as oct_sales 92 ,sum(case when d_moy = 11 93 then ws_sales_price* ws_quantity else 0 end) as nov_sales 94 ,sum(case when d_moy = 12 95 then ws_sales_price* ws_quantity else 0 end) as dec_sales 96 ,sum(case when d_moy = 1 97 then ws_net_paid_inc_tax * ws_quantity else 0 end) as jan_net 98 ,sum(case when d_moy = 2 99 then ws_net_paid_inc_tax * ws_quantity else 0 end) as feb_net 100 ,sum(case when d_moy = 3 101 then ws_net_paid_inc_tax * ws_quantity else 0 end) as mar_net 102 ,sum(case when d_moy = 4 103 then ws_net_paid_inc_tax * ws_quantity else 0 end) as apr_net 104 ,sum(case when d_moy = 5 105 then ws_net_paid_inc_tax * ws_quantity else 0 end) as may_net 106 ,sum(case when d_moy = 6 107 then ws_net_paid_inc_tax * ws_quantity else 0 end) as jun_net 108 ,sum(case when d_moy = 7 109 then ws_net_paid_inc_tax * ws_quantity else 0 end) as jul_net 110 ,sum(case when d_moy = 8 111 then ws_net_paid_inc_tax * ws_quantity else 0 end) as aug_net 112 ,sum(case when d_moy = 9 113 then ws_net_paid_inc_tax * ws_quantity else 0 end) as sep_net 114 ,sum(case when d_moy = 10 115 then ws_net_paid_inc_tax * ws_quantity else 0 end) as oct_net 116 ,sum(case when d_moy = 11 117 then ws_net_paid_inc_tax * ws_quantity else 0 end) as nov_net 118 ,sum(case when d_moy = 12 119 then ws_net_paid_inc_tax * ws_quantity else 0 end) as dec_net 120 from 121 web_sales 122 ,warehouse 123 ,date_dim 124 ,time_dim 125 ,ship_mode 126 where 127 ws_warehouse_sk = w_warehouse_sk 128 and ws_sold_date_sk = d_date_sk 129 and ws_sold_time_sk = t_time_sk 130 and ws_ship_mode_sk = sm_ship_mode_sk 131 and d_year = 2002 132 and t_time between 49530 and 49530+28800 133 and sm_carrier in ('DIAMOND','AIRBORNE') 134 group by 135 w_warehouse_name 136 ,w_warehouse_sq_ft 137 ,w_city 138 ,w_county 139 ,w_state 140 ,w_country 141 ,d_year 142 ) 143 union all 144 (select 145 w_warehouse_name 146 ,w_warehouse_sq_ft 147 ,w_city 148 ,w_county 149 ,w_state 150 ,w_country 151 ,'DIAMOND' || ',' || 'AIRBORNE' as ship_carriers 152 ,d_year as `year` 153 ,sum(case when d_moy = 1 154 then cs_ext_sales_price* cs_quantity else 0 end) as jan_sales 155 ,sum(case when d_moy = 2 156 then cs_ext_sales_price* cs_quantity else 0 end) as feb_sales 157 ,sum(case when d_moy = 3 158 then cs_ext_sales_price* cs_quantity else 0 end) as mar_sales 159 ,sum(case when d_moy = 4 160 then cs_ext_sales_price* cs_quantity else 0 end) as apr_sales 161 ,sum(case when d_moy = 5 162 then cs_ext_sales_price* cs_quantity else 0 end) as may_sales 163 ,sum(case when d_moy = 6 164 then cs_ext_sales_price* cs_quantity else 0 end) as jun_sales 165 ,sum(case when d_moy = 7 166 then cs_ext_sales_price* cs_quantity else 0 end) as jul_sales 167 ,sum(case when d_moy = 8 168 then cs_ext_sales_price* cs_quantity else 0 end) as aug_sales 169 ,sum(case when d_moy = 9 170 then cs_ext_sales_price* cs_quantity else 0 end) as sep_sales 171 ,sum(case when d_moy = 10 172 then cs_ext_sales_price* cs_quantity else 0 end) as oct_sales 173 ,sum(case when d_moy = 11 174 then cs_ext_sales_price* cs_quantity else 0 end) as nov_sales 175 ,sum(case when d_moy = 12 176 then cs_ext_sales_price* cs_quantity else 0 end) as dec_sales 177 ,sum(case when d_moy = 1 178 then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jan_net 179 ,sum(case when d_moy = 2 180 then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as feb_net 181 ,sum(case when d_moy = 3 182 then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as mar_net 183 ,sum(case when d_moy = 4 184 then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as apr_net 185 ,sum(case when d_moy = 5 186 then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as may_net 187 ,sum(case when d_moy = 6 188 then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jun_net 189 ,sum(case when d_moy = 7 190 then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jul_net 191 ,sum(case when d_moy = 8 192 then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as aug_net 193 ,sum(case when d_moy = 9 194 then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as sep_net 195 ,sum(case when d_moy = 10 196 then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as oct_net 197 ,sum(case when d_moy = 11 198 then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as nov_net 199 ,sum(case when d_moy = 12 200 then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as dec_net 201 from 202 catalog_sales 203 ,warehouse 204 ,date_dim 205 ,time_dim 206 ,ship_mode 207 where 208 cs_warehouse_sk = w_warehouse_sk 209 and cs_sold_date_sk = d_date_sk 210 and cs_sold_time_sk = t_time_sk 211 and cs_ship_mode_sk = sm_ship_mode_sk 212 and d_year = 2002 213 and t_time between 49530 AND 49530+28800 214 and sm_carrier in ('DIAMOND','AIRBORNE') 215 group by 216 w_warehouse_name 217 ,w_warehouse_sq_ft 218 ,w_city 219 ,w_county 220 ,w_state 221 ,w_country 222 ,d_year 223 ) 224 ) x 225 group by 226 w_warehouse_name 227 ,w_warehouse_sq_ft 228 ,w_city 229 ,w_county 230 ,w_state 231 ,w_country 232 ,ship_carriers 233 ,`year` 234 order by w_warehouse_name 235 limit 100