github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query88.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 from 19 (select count(*) h8_30_to_9 20 from store_sales, household_demographics , time_dim, store 21 where ss_sold_time_sk = time_dim.t_time_sk 22 and ss_hdemo_sk = household_demographics.hd_demo_sk 23 and ss_store_sk = s_store_sk 24 and time_dim.t_hour = 8 25 and time_dim.t_minute >= 30 26 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or 27 (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or 28 (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) 29 and store.s_store_name = 'ese') s1, 30 (select count(*) h9_to_9_30 31 from store_sales, household_demographics , time_dim, store 32 where ss_sold_time_sk = time_dim.t_time_sk 33 and ss_hdemo_sk = household_demographics.hd_demo_sk 34 and ss_store_sk = s_store_sk 35 and time_dim.t_hour = 9 36 and time_dim.t_minute < 30 37 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or 38 (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or 39 (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) 40 and store.s_store_name = 'ese') s2, 41 (select count(*) h9_30_to_10 42 from store_sales, household_demographics , time_dim, store 43 where ss_sold_time_sk = time_dim.t_time_sk 44 and ss_hdemo_sk = household_demographics.hd_demo_sk 45 and ss_store_sk = s_store_sk 46 and time_dim.t_hour = 9 47 and time_dim.t_minute >= 30 48 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or 49 (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or 50 (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) 51 and store.s_store_name = 'ese') s3, 52 (select count(*) h10_to_10_30 53 from store_sales, household_demographics , time_dim, store 54 where ss_sold_time_sk = time_dim.t_time_sk 55 and ss_hdemo_sk = household_demographics.hd_demo_sk 56 and ss_store_sk = s_store_sk 57 and time_dim.t_hour = 10 58 and time_dim.t_minute < 30 59 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or 60 (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or 61 (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) 62 and store.s_store_name = 'ese') s4, 63 (select count(*) h10_30_to_11 64 from store_sales, household_demographics , time_dim, store 65 where ss_sold_time_sk = time_dim.t_time_sk 66 and ss_hdemo_sk = household_demographics.hd_demo_sk 67 and ss_store_sk = s_store_sk 68 and time_dim.t_hour = 10 69 and time_dim.t_minute >= 30 70 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or 71 (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or 72 (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) 73 and store.s_store_name = 'ese') s5, 74 (select count(*) h11_to_11_30 75 from store_sales, household_demographics , time_dim, store 76 where ss_sold_time_sk = time_dim.t_time_sk 77 and ss_hdemo_sk = household_demographics.hd_demo_sk 78 and ss_store_sk = s_store_sk 79 and time_dim.t_hour = 11 80 and time_dim.t_minute < 30 81 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or 82 (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or 83 (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) 84 and store.s_store_name = 'ese') s6, 85 (select count(*) h11_30_to_12 86 from store_sales, household_demographics , time_dim, store 87 where ss_sold_time_sk = time_dim.t_time_sk 88 and ss_hdemo_sk = household_demographics.hd_demo_sk 89 and ss_store_sk = s_store_sk 90 and time_dim.t_hour = 11 91 and time_dim.t_minute >= 30 92 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or 93 (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or 94 (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) 95 and store.s_store_name = 'ese') s7, 96 (select count(*) h12_to_12_30 97 from store_sales, household_demographics , time_dim, store 98 where ss_sold_time_sk = time_dim.t_time_sk 99 and ss_hdemo_sk = household_demographics.hd_demo_sk 100 and ss_store_sk = s_store_sk 101 and time_dim.t_hour = 12 102 and time_dim.t_minute < 30 103 and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or 104 (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or 105 (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) 106 and store.s_store_name = 'ese') s8