| --! qt:disabled:HIVE-25379 |
| --! qt:dataset:src |
| |
| CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput'; |
| |
| FROM src |
| SELECT |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE CATALOG_SALES ("cs_sold_date_sk" INTEGER, "cs_sold_time_sk" INTEGER, "cs_ship_date_sk" INTEGER, "cs_bill_customer_sk" INTEGER, |
| "cs_bill_cdemo_sk" INTEGER, "cs_bill_hdemo_sk" INTEGER, "cs_bill_addr_sk" INTEGER, "cs_ship_customer_sk" INTEGER, "cs_ship_cdemo_sk" INTEGER, |
| "cs_ship_hdemo_sk" INTEGER, "cs_ship_addr_sk" INTEGER, "cs_call_center_sk" INTEGER, "cs_catalog_page_sk" INTEGER, "cs_ship_mode_sk" INTEGER, |
| "cs_warehouse_sk" INTEGER, "cs_item_sk" INTEGER, "cs_promo_sk" INTEGER, "cs_order_number" INTEGER, "cs_quantity" INTEGER, "cs_wholesale_cost" DECIMAL(7,2), |
| "cs_list_price" DECIMAL(7,2), "cs_sales_price" DECIMAL(7,2), "cs_ext_discount_amt" DECIMAL(7,2), "cs_ext_sales_price" DECIMAL(7,2), |
| "cs_ext_wholesale_cost" DECIMAL(7,2), "cs_ext_list_price" DECIMAL(7,2), "cs_ext_tax" DECIMAL(7,2), "cs_coupon_amt" DECIMAL(7,2), |
| "cs_ext_ship_cost" DECIMAL(7,2), "cs_net_paid" DECIMAL(7,2), "cs_net_paid_inc_tax" DECIMAL(7,2), "cs_net_paid_inc_ship" DECIMAL(7,2), |
| "cs_net_paid_inc_ship_tax" DECIMAL(7,2), "cs_net_profit" DECIMAL(7,2))' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE CATALOG_RETURNS ("cr_returned_date_sk" INTEGER, "cr_returned_time_sk" INTEGER, "cr_item_sk" INTEGER, "cr_refunded_customer_sk" INTEGER, |
| "cr_refunded_cdemo_sk" INTEGER, "cr_refunded_hdemo_sk" INTEGER, "cr_refunded_addr_sk" INTEGER, "cr_returning_customer_sk" INTEGER, |
| "cr_returning_cdemo_sk" INTEGER, "cr_returning_hdemo_sk" INTEGER, "cr_returning_addr_sk" INTEGER, "cr_call_center_sk" INTEGER, |
| "cr_catalog_page_sk" INTEGER, "cr_ship_mode_sk" INTEGER, "cr_warehouse_sk" INTEGER, "cr_reason_sk" INTEGER, "cr_order_number" INTEGER, |
| "cr_return_quantity" INTEGER, "cr_return_amount" DECIMAL(7,2), "cr_return_tax" DECIMAL(7,2), "cr_return_amt_inc_tax" DECIMAL(7,2), |
| "cr_fee" DECIMAL(7,2), "cr_return_ship_cost" DECIMAL(7,2), "cr_refunded_cash" DECIMAL(7,2), "cr_reversed_charge" DECIMAL(7,2), |
| "cr_store_credit" DECIMAL(7,2), "cr_net_loss" DECIMAL(7,2))' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE STORE_SALES ("ss_sold_date_sk" INTEGER, "ss_sold_time_sk" INTEGER, "ss_item_sk" INTEGER, "ss_customer_sk" INTEGER, "ss_cdemo_sk" INTEGER, |
| "ss_hdemo_sk" INTEGER, "ss_addr_sk" INTEGER, "ss_store_sk" INTEGER, "ss_promo_sk" INTEGER, "ss_ticket_number" INTEGER, "ss_quantity" INTEGER, |
| "ss_wholesale_cost" DECIMAL(7,2), "ss_list_price" DECIMAL(7,2), "ss_sales_price" DECIMAL(7,2), "ss_ext_discount_amt" DECIMAL(7,2), |
| "ss_ext_sales_price" DECIMAL(7,2), "ss_ext_wholesale_cost" DECIMAL(7,2), "ss_ext_list_price" DECIMAL(7,2), "ss_ext_tax" DECIMAL(7,2), |
| "ss_coupon_amt" DECIMAL(7,2), "ss_net_paid" DECIMAL(7,2), "ss_net_paid_inc_tax" DECIMAL(7,2), "ss_net_profit" DECIMAL(7,2))' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE STORE_RETURNS ("sr_returned_date_sk" INTEGER, "sr_return_time_sk" INTEGER, "sr_item_sk" INTEGER, "sr_customer_sk" INTEGER, |
| "sr_cdemo_sk" INTEGER, "sr_hdemo_sk" INTEGER, "sr_addr_sk" INTEGER, "sr_store_sk" INTEGER, "sr_reason_sk" INTEGER, "sr_ticket_number" INTEGER, |
| "sr_return_quantity" INTEGER, "sr_return_amt" DECIMAL(7,2), "sr_return_tax" DECIMAL(7,2), "sr_return_amt_inc_tax" DECIMAL(7,2), |
| "sr_fee" DECIMAL(7,2), "sr_return_ship_cost" DECIMAL(7,2), "sr_refunded_cash" DECIMAL(7,2), "sr_reversed_charge" DECIMAL(7,2), |
| "sr_store_credit" DECIMAL(7,2), "sr_net_loss" DECIMAL(7,2))' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE WEB_SALES ("ws_sold_date_sk" INTEGER, "ws_sold_time_sk" INTEGER, "ws_ship_date_sk" INTEGER, "ws_item_sk" INTEGER, |
| "ws_bill_customer_sk" INTEGER, "ws_bill_cdemo_sk" INTEGER, "ws_bill_hdemo_sk" INTEGER, "ws_bill_addr_sk" INTEGER, "ws_ship_customer_sk" INTEGER, |
| "ws_ship_cdemo_sk" INTEGER, "ws_ship_hdemo_sk" INTEGER, "ws_ship_addr_sk" INTEGER, "ws_web_page_sk" INTEGER, "ws_web_site_sk" INTEGER, |
| "ws_ship_mode_sk" INTEGER, "ws_warehouse_sk" INTEGER, "ws_promo_sk" INTEGER, "ws_order_number" INTEGER, "ws_quantity" INTEGER, |
| "ws_wholesale_cost" DECIMAL(7,2), "ws_list_price" DECIMAL(7,2), "ws_sales_price" DECIMAL(7,2), "ws_ext_discount_amt" DECIMAL(7,2), |
| "ws_ext_sales_price" DECIMAL(7,2), "ws_ext_wholesale_cost" DECIMAL(7,2), "ws_ext_list_price" DECIMAL(7,2), "ws_ext_tax" DECIMAL(7,2), |
| "ws_coupon_amt" DECIMAL(7,2), "ws_ext_ship_cost" DECIMAL(7,2), "ws_net_paid" DECIMAL(7,2), "ws_net_paid_inc_tax" DECIMAL(7,2), |
| "ws_net_paid_inc_ship" DECIMAL(7,2), "ws_net_paid_inc_ship_tax" DECIMAL(7,2), "ws_net_profit" DECIMAL(7,2))' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE WEB_RETURNS ("wr_returned_date_sk" INTEGER, "wr_returned_time_sk" INTEGER, "wr_item_sk" INTEGER, "wr_refunded_customer_sk" INTEGER, |
| "wr_refunded_cdemo_sk" INTEGER, "wr_refunded_hdemo_sk" INTEGER, "wr_refunded_addr_sk" INTEGER, "wr_returning_customer_sk" INTEGER, |
| "wr_returning_cdemo_sk" INTEGER, "wr_returning_hdemo_sk" INTEGER, "wr_returning_addr_sk" INTEGER, "wr_web_page_sk" INTEGER, |
| "wr_reason_sk" INTEGER, "wr_order_number" INTEGER, "wr_return_quantity" INTEGER, "wr_return_amt" DECIMAL(7,2), "wr_return_tax" DECIMAL(7,2), |
| "wr_return_amt_inc_tax" DECIMAL(7,2), "wr_fee" DECIMAL(7,2), "wr_return_ship_cost" DECIMAL(7,2), "wr_refunded_cash" DECIMAL(7,2), |
| "wr_reversed_charge" DECIMAL(7,2), "wr_account_credit" DECIMAL(7,2), "wr_net_loss" DECIMAL(7,2))' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE CUSTOMER ("c_customer_sk" INTEGER, "c_customer_id" CHAR(16), "c_current_cdemo_sk" INTEGER, "c_current_hdemo_sk" INTEGER, |
| "c_current_addr_sk" INTEGER, "c_first_shipto_date_sk" INTEGER, "c_first_sales_date_sk" INTEGER, "c_salutation" CHAR(10), |
| "c_first_name" CHAR(20), "c_last_name" CHAR(30), "c_preferred_cust_flag" CHAR(1), "c_birth_day" INTEGER, "c_birth_month" INTEGER, |
| "c_birth_year" INTEGER, "c_birth_country" VARCHAR(20), "c_login" CHAR(13), "c_email_address" CHAR(50), "c_last_review_date" CHAR(10))' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE CUSTOMER_ADDRESS ("ca_address_sk" INTEGER, "ca_address_id" CHAR(16), "ca_street_number" CHAR(10), "ca_street_name" VARCHAR(60), |
| "ca_street_type" CHAR(15), "ca_suite_number" CHAR(10), "ca_city" VARCHAR(60), "ca_county" VARCHAR(30), "ca_state" CHAR(2), |
| "ca_zip" CHAR(10), "ca_country" VARCHAR(20), "ca_gmt_offset" DECIMAL(5,2), "ca_location_type" CHAR(20))' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE CUSTOMER_DEMOGRAPHICS ("cd_demo_sk" INTEGER, "cd_gender" CHAR(1), "cd_marital_status" CHAR(1), "cd_education_status" CHAR(20), |
| "cd_purchase_estimate" INTEGER, "cd_credit_rating" CHAR(10), "cd_dep_count" INTEGER, "cd_dep_employed_count" INTEGER, |
| "cd_dep_college_count" INTEGER)' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE INVENTORY ("inv_date_sk" INTEGER, "inv_item_sk" INTEGER, "inv_warehouse_sk" INTEGER, "inv_quantity_on_hand" INTEGER)' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE ITEM ("i_item_sk" INTEGER, "i_item_id" CHAR(16), "i_rec_start_date" DATE, "i_rec_end_date" DATE, |
| "i_item_desc" VARCHAR(200), "i_current_price" DECIMAL(7,2), "i_wholesale_cost" DECIMAL(7,2), "i_brand_id" INTEGER, |
| "i_brand" CHAR(50), "i_class_id" INTEGER, "i_class" CHAR(50), "i_category_id" INTEGER, "i_category" CHAR(50), |
| "i_manufact_id" INTEGER, "i_manufact" CHAR(50), "i_size" CHAR(20), "i_formulation" CHAR(20), "i_color" CHAR(20), |
| "i_units" CHAR(10), "i_container" CHAR(10), "i_manager_id" INTEGER, "i_product_name" CHAR(50))' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE WAREHOUSE ("w_warehouse_sk" INTEGER, "w_warehouse_id" CHAR(16), "w_warehouse_name" VARCHAR(20), |
| "w_warehouse_sq_ft" INTEGER, "w_street_number" CHAR(10), "w_street_name" VARCHAR(60), "w_street_type" CHAR(15), |
| "w_suite_number" CHAR(10), "w_city" VARCHAR(60), "w_county" VARCHAR(30), "w_state" CHAR(2), "w_zip" CHAR(10), |
| "w_country" VARCHAR(20), "w_gmt_offset" DECIMAL(5,2))' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE DATE_DIM ("d_date_sk" INTEGER, "d_date_id" CHAR(16), "d_date" DATE, "d_month_seq" INTEGER, "d_week_seq" INTEGER, |
| "d_quarter_seq" INTEGER, "d_year" INTEGER, "d_dow" INTEGER, "d_moy" INTEGER, "d_dom" INTEGER, "d_qoy" INTEGER, |
| "d_fy_year" INTEGER, "d_fy_quarter_seq" INTEGER, "d_fy_week_seq" INTEGER, "d_day_name" CHAR(9), "d_quarter_name" CHAR(6), |
| "d_holiday" CHAR(1), "d_weekend" CHAR(1), "d_following_holiday" CHAR(1), "d_first_dom" INTEGER, "d_last_dom" INTEGER, |
| "d_same_day_ly" INTEGER, "d_same_day_lq" INTEGER, "d_current_day" CHAR(1), "d_current_week" CHAR(1), "d_current_month" CHAR(1), |
| "d_current_quarter" CHAR(1), "d_current_year" CHAR(1))' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE STORE ("s_store_sk" INTEGER, "s_store_id" CHAR(16), "s_rec_start_date" DATE, "s_rec_end_date" DATE, |
| "s_closed_date_sk" INTEGER, "s_store_name" VARCHAR(50), "s_number_employees" INTEGER, "s_floor_space" INTEGER, |
| "s_hours" CHAR(20), "s_manager" VARCHAR(40), "s_market_id" INTEGER, "s_geography_class" VARCHAR(100), |
| "s_market_desc" VARCHAR(100), "s_market_manager" VARCHAR(40), "s_division_id" INTEGER, "s_division_name" VARCHAR(50), |
| "s_company_id" INTEGER, "s_company_name" VARCHAR(50), "s_street_number" VARCHAR(10), "s_street_name" VARCHAR(60), |
| "s_street_type" CHAR(15), "s_suite_number" CHAR(10), "s_city" VARCHAR(60), "s_county" VARCHAR(30), |
| "s_state" CHAR(2), "s_zip" CHAR(10), "s_country" VARCHAR(20), "s_gmt_offset" DECIMAL(5,2), "s_tax_precentage" DECIMAL(5,2))' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE HOUSEHOLD_DEMOGRAPHICS ("hd_demo_sk" INTEGER, "hd_income_band_sk" INTEGER, "hd_buy_potential" CHAR(15), |
| "hd_dep_count" INTEGER, "hd_vehicle_count" INTEGER)' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE TIME_DIM ("t_time_sk" INTEGER, "t_time_id" CHAR(16), "t_time" INTEGER, "t_hour" INTEGER, |
| "t_minute" INTEGER, "t_second" INTEGER, "t_am_pm" CHAR(2), "t_shift" CHAR(20), "t_sub_shift" CHAR(20), |
| "t_meal_time" CHAR(20))' ), |
| dboutput ('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;create=true','user1','passwd1', |
| 'CREATE TABLE REASON ("r_reason_sk" INTEGER, "r_reason_id" CHAR(16), "r_reason_desc" CHAR(100))' ) |
| limit 1; |
| |
| |
| CREATE EXTERNAL TABLE catalog_sales |
| ( |
| cs_sold_date_sk int , |
| cs_sold_time_sk int , |
| cs_ship_date_sk int , |
| cs_bill_customer_sk int , |
| cs_bill_cdemo_sk int , |
| cs_bill_hdemo_sk int , |
| cs_bill_addr_sk int , |
| cs_ship_customer_sk int , |
| cs_ship_cdemo_sk int , |
| cs_ship_hdemo_sk int , |
| cs_ship_addr_sk int , |
| cs_call_center_sk int , |
| cs_catalog_page_sk int , |
| cs_ship_mode_sk int , |
| cs_warehouse_sk int , |
| cs_item_sk int , |
| cs_promo_sk int , |
| cs_order_number int , |
| cs_quantity int , |
| cs_wholesale_cost decimal(7,2) , |
| cs_list_price decimal(7,2) , |
| cs_sales_price decimal(7,2) , |
| cs_ext_discount_amt decimal(7,2) , |
| cs_ext_sales_price decimal(7,2) , |
| cs_ext_wholesale_cost decimal(7,2) , |
| cs_ext_list_price decimal(7,2) , |
| cs_ext_tax decimal(7,2) , |
| cs_coupon_amt decimal(7,2) , |
| cs_ext_ship_cost decimal(7,2) , |
| cs_net_paid decimal(7,2) , |
| cs_net_paid_inc_tax decimal(7,2) , |
| cs_net_paid_inc_ship decimal(7,2) , |
| cs_net_paid_inc_ship_tax decimal(7,2) , |
| cs_net_profit decimal(7,2) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "CATALOG_SALES", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE catalog_returns |
| ( |
| cr_returned_date_sk int , |
| cr_returned_time_sk int , |
| cr_item_sk int , |
| cr_refunded_customer_sk int , |
| cr_refunded_cdemo_sk int , |
| cr_refunded_hdemo_sk int , |
| cr_refunded_addr_sk int , |
| cr_returning_customer_sk int , |
| cr_returning_cdemo_sk int , |
| cr_returning_hdemo_sk int , |
| cr_returning_addr_sk int , |
| cr_call_center_sk int , |
| cr_catalog_page_sk int , |
| cr_ship_mode_sk int , |
| cr_warehouse_sk int , |
| cr_reason_sk int , |
| cr_order_number int , |
| cr_return_quantity int , |
| cr_return_amount decimal(7,2) , |
| cr_return_tax decimal(7,2) , |
| cr_return_amt_inc_tax decimal(7,2) , |
| cr_fee decimal(7,2) , |
| cr_return_ship_cost decimal(7,2) , |
| cr_refunded_cash decimal(7,2) , |
| cr_reversed_charge decimal(7,2) , |
| cr_store_credit decimal(7,2) , |
| cr_net_loss decimal(7,2) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "CATALOG_RETURNS", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE store_sales |
| ( |
| ss_sold_date_sk int , |
| ss_sold_time_sk int , |
| ss_item_sk int , |
| ss_customer_sk int , |
| ss_cdemo_sk int , |
| ss_hdemo_sk int , |
| ss_addr_sk int , |
| ss_store_sk int , |
| ss_promo_sk int , |
| ss_ticket_number int , |
| ss_quantity int , |
| ss_wholesale_cost decimal(7,2) , |
| ss_list_price decimal(7,2) , |
| ss_sales_price decimal(7,2) , |
| ss_ext_discount_amt decimal(7,2) , |
| ss_ext_sales_price decimal(7,2) , |
| ss_ext_wholesale_cost decimal(7,2) , |
| ss_ext_list_price decimal(7,2) , |
| ss_ext_tax decimal(7,2) , |
| ss_coupon_amt decimal(7,2) , |
| ss_net_paid decimal(7,2) , |
| ss_net_paid_inc_tax decimal(7,2) , |
| ss_net_profit decimal(7,2) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "STORE_SALES", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE store_returns |
| ( |
| sr_returned_date_sk int , |
| sr_return_time_sk int , |
| sr_item_sk int , |
| sr_customer_sk int , |
| sr_cdemo_sk int , |
| sr_hdemo_sk int , |
| sr_addr_sk int , |
| sr_store_sk int , |
| sr_reason_sk int , |
| sr_ticket_number int , |
| sr_return_quantity int , |
| sr_return_amt decimal(7,2) , |
| sr_return_tax decimal(7,2) , |
| sr_return_amt_inc_tax decimal(7,2) , |
| sr_fee decimal(7,2) , |
| sr_return_ship_cost decimal(7,2) , |
| sr_refunded_cash decimal(7,2) , |
| sr_reversed_charge decimal(7,2) , |
| sr_store_credit decimal(7,2) , |
| sr_net_loss decimal(7,2) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "STORE_RETURNS", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE web_sales |
| ( |
| ws_sold_date_sk int , |
| ws_sold_time_sk int , |
| ws_ship_date_sk int , |
| ws_item_sk int , |
| ws_bill_customer_sk int , |
| ws_bill_cdemo_sk int , |
| ws_bill_hdemo_sk int , |
| ws_bill_addr_sk int , |
| ws_ship_customer_sk int , |
| ws_ship_cdemo_sk int , |
| ws_ship_hdemo_sk int , |
| ws_ship_addr_sk int , |
| ws_web_page_sk int , |
| ws_web_site_sk int , |
| ws_ship_mode_sk int , |
| ws_warehouse_sk int , |
| ws_promo_sk int , |
| ws_order_number int , |
| ws_quantity int , |
| ws_wholesale_cost decimal(7,2) , |
| ws_list_price decimal(7,2) , |
| ws_sales_price decimal(7,2) , |
| ws_ext_discount_amt decimal(7,2) , |
| ws_ext_sales_price decimal(7,2) , |
| ws_ext_wholesale_cost decimal(7,2) , |
| ws_ext_list_price decimal(7,2) , |
| ws_ext_tax decimal(7,2) , |
| ws_coupon_amt decimal(7,2) , |
| ws_ext_ship_cost decimal(7,2) , |
| ws_net_paid decimal(7,2) , |
| ws_net_paid_inc_tax decimal(7,2) , |
| ws_net_paid_inc_ship decimal(7,2) , |
| ws_net_paid_inc_ship_tax decimal(7,2) , |
| ws_net_profit decimal(7,2) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "WEB_SALES", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE web_returns |
| ( |
| wr_returned_date_sk int , |
| wr_returned_time_sk int , |
| wr_item_sk int , |
| wr_refunded_customer_sk int , |
| wr_refunded_cdemo_sk int , |
| wr_refunded_hdemo_sk int , |
| wr_refunded_addr_sk int , |
| wr_returning_customer_sk int , |
| wr_returning_cdemo_sk int , |
| wr_returning_hdemo_sk int , |
| wr_returning_addr_sk int , |
| wr_web_page_sk int , |
| wr_reason_sk int , |
| wr_order_number int , |
| wr_return_quantity int , |
| wr_return_amt decimal(7,2) , |
| wr_return_tax decimal(7,2) , |
| wr_return_amt_inc_tax decimal(7,2) , |
| wr_fee decimal(7,2) , |
| wr_return_ship_cost decimal(7,2) , |
| wr_refunded_cash decimal(7,2) , |
| wr_reversed_charge decimal(7,2) , |
| wr_account_credit decimal(7,2) , |
| wr_net_loss decimal(7,2) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "WEB_RETURNS", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE customer |
| ( |
| c_customer_sk int , |
| c_customer_id char(16) , |
| c_current_cdemo_sk int , |
| c_current_hdemo_sk int , |
| c_current_addr_sk int , |
| c_first_shipto_date_sk int , |
| c_first_sales_date_sk int , |
| c_salutation char(10) , |
| c_first_name char(20) , |
| c_last_name char(30) , |
| c_preferred_cust_flag char(1) , |
| c_birth_day int , |
| c_birth_month int , |
| c_birth_year int , |
| c_birth_country varchar(20) , |
| c_login char(13) , |
| c_email_address char(50) , |
| c_last_review_date char(10) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "CUSTOMER", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE customer_address |
| ( |
| ca_address_sk int , |
| ca_address_id char(16) , |
| ca_street_number char(10) , |
| ca_street_name varchar(60) , |
| ca_street_type char(15) , |
| ca_suite_number char(10) , |
| ca_city varchar(60) , |
| ca_county varchar(30) , |
| ca_state char(2) , |
| ca_zip char(10) , |
| ca_country varchar(20) , |
| ca_gmt_offset decimal(5,2) , |
| ca_location_type char(20) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "CUSTOMER_ADDRESS", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE customer_demographics |
| ( |
| cd_demo_sk int , |
| cd_gender char(1) , |
| cd_marital_status char(1) , |
| cd_education_status char(20) , |
| cd_purchase_estimate int , |
| cd_credit_rating char(10) , |
| cd_dep_count int , |
| cd_dep_employed_count int , |
| cd_dep_college_count int |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "CUSTOMER_DEMOGRAPHICS", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE inventory |
| ( |
| inv_date_sk int , |
| inv_item_sk int , |
| inv_warehouse_sk int , |
| inv_quantity_on_hand int |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "INVENTORY", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE item |
| ( |
| i_item_sk int , |
| i_item_id char(16) , |
| i_rec_start_date date , |
| i_rec_end_date date , |
| i_item_desc varchar(200) , |
| i_current_price decimal(7,2) , |
| i_wholesale_cost decimal(7,2) , |
| i_brand_id int , |
| i_brand char(50) , |
| i_class_id int , |
| i_class char(50) , |
| i_category_id int , |
| i_category char(50) , |
| i_manufact_id int , |
| i_manufact char(50) , |
| i_size char(20) , |
| i_formulation char(20) , |
| i_color char(20) , |
| i_units char(10) , |
| i_container char(10) , |
| i_manager_id int , |
| i_product_name char(50) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "ITEM", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE warehouse |
| ( |
| w_warehouse_sk int , |
| w_warehouse_id char(16) , |
| w_warehouse_name varchar(20) , |
| w_warehouse_sq_ft int , |
| w_street_number char(10) , |
| w_street_name varchar(60) , |
| w_street_type char(15) , |
| w_suite_number char(10) , |
| w_city varchar(60) , |
| w_county varchar(30) , |
| w_state char(2) , |
| w_zip char(10) , |
| w_country varchar(20) , |
| w_gmt_offset decimal(5,2) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "WAREHOUSE", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE date_dim |
| ( |
| d_date_sk int , |
| d_date_id char(16) , |
| d_date date , |
| d_month_seq int , |
| d_week_seq int , |
| d_quarter_seq int , |
| d_year int , |
| d_dow int , |
| d_moy int , |
| d_dom int , |
| d_qoy int , |
| d_fy_year int , |
| d_fy_quarter_seq int , |
| d_fy_week_seq int , |
| d_day_name char(9) , |
| d_quarter_name char(6) , |
| d_holiday char(1) , |
| d_weekend char(1) , |
| d_following_holiday char(1) , |
| d_first_dom int , |
| d_last_dom int , |
| d_same_day_ly int , |
| d_same_day_lq int , |
| d_current_day char(1) , |
| d_current_week char(1) , |
| d_current_month char(1) , |
| d_current_quarter char(1) , |
| d_current_year char(1) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "DATE_DIM", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE store |
| ( |
| s_store_sk int , |
| s_store_id char(16) , |
| s_rec_start_date date , |
| s_rec_end_date date , |
| s_closed_date_sk int , |
| s_store_name varchar(50) , |
| s_number_employees int , |
| s_floor_space int , |
| s_hours char(20) , |
| s_manager varchar(40) , |
| s_market_id int , |
| s_geography_class varchar(100) , |
| s_market_desc varchar(100) , |
| s_market_manager varchar(40) , |
| s_division_id int , |
| s_division_name varchar(50) , |
| s_company_id int , |
| s_company_name varchar(50) , |
| s_street_number varchar(10) , |
| s_street_name varchar(60) , |
| s_street_type char(15) , |
| s_suite_number char(10) , |
| s_city varchar(60) , |
| s_county varchar(30) , |
| s_state char(2) , |
| s_zip char(10) , |
| s_country varchar(20) , |
| s_gmt_offset decimal(5,2) , |
| s_tax_precentage decimal(5,2) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "STORE", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE household_demographics |
| ( |
| hd_demo_sk int , |
| hd_income_band_sk int , |
| hd_buy_potential char(15) , |
| hd_dep_count int , |
| hd_vehicle_count int |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "HOUSEHOLD_DEMOGRAPHICS", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE time_dim |
| ( |
| t_time_sk int , |
| t_time_id char(16) , |
| t_time int , |
| t_hour int , |
| t_minute int , |
| t_second int , |
| t_am_pm char(2) , |
| t_shift char(20) , |
| t_sub_shift char(20) , |
| t_meal_time char(20) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "TIME_DIM", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| CREATE EXTERNAL TABLE reason |
| ( |
| r_reason_sk int , |
| r_reason_id char(16) , |
| r_reason_desc char(100) |
| ) |
| STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' |
| TBLPROPERTIES ( |
| "hive.sql.database.type" = "DERBY", |
| "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", |
| "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf;collation=TERRITORY_BASED:PRIMARY", |
| "hive.sql.dbcp.username" = "user1", |
| "hive.sql.dbcp.password" = "passwd1", |
| "hive.sql.table" = "REASON", |
| "hive.sql.dbcp.maxActive" = "1" |
| ); |
| |
| |
| explain |
| select inv1.w_warehouse_sk, inv1.i_item_sk, inv1.d_moy, inv1.mean, inv1.cov, |
| inv2.w_warehouse_sk, inv2.i_item_sk, inv2.d_moy, inv2.mean, inv2.cov |
| from (select w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy, |
| stdev, mean, case mean when 0.0 |
| then null else stdev/mean end cov |
| from (select w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy, |
| sum(inv_quantity_on_hand) as stdev, |
| avg(inv_quantity_on_hand) as mean |
| from inventory |
| join item on inventory.inv_item_sk = item.i_item_sk |
| join warehouse on inventory.inv_warehouse_sk = warehouse.w_warehouse_sk |
| join date_dim on inventory.inv_date_sk = date_dim.d_date_sk |
| where d_year = 2001 |
| group by w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy) foo |
| where case mean when 0.0 |
| then 0.0 else stdev/mean end > 1) inv1 |
| join (select w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy, |
| stdev, mean, case mean when 0.0 |
| then null else stdev/mean end cov |
| from (select w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy, |
| sum(inv_quantity_on_hand) as stdev, |
| avg(inv_quantity_on_hand) as mean |
| from inventory |
| join item on inventory.inv_item_sk = item.i_item_sk |
| join warehouse on inventory.inv_warehouse_sk = warehouse.w_warehouse_sk |
| join date_dim on inventory.inv_date_sk = date_dim.d_date_sk |
| where d_year = 2001 |
| group by w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy) foo |
| where case mean when 0.0 |
| then 0.0 else stdev/mean end > 1) inv2 |
| on inv1.i_item_sk = inv2.i_item_sk |
| and inv1.w_warehouse_sk = inv2.w_warehouse_sk |
| where inv1.d_moy = 1 and inv2.d_moy = 1+1 |
| order by inv1.w_warehouse_sk, inv1.i_item_sk, inv1.d_moy, inv1.mean, inv1.cov, |
| inv2.d_moy, inv2.mean, inv2.cov; |
| select inv1.w_warehouse_sk, inv1.i_item_sk, inv1.d_moy, inv1.mean, inv1.cov, |
| inv2.w_warehouse_sk, inv2.i_item_sk, inv2.d_moy, inv2.mean, inv2.cov |
| from (select w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy, |
| stdev, mean, case mean when 0.0 |
| then null else stdev/mean end cov |
| from (select w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy, |
| sum(inv_quantity_on_hand) as stdev, |
| avg(inv_quantity_on_hand) as mean |
| from inventory |
| join item on inventory.inv_item_sk = item.i_item_sk |
| join warehouse on inventory.inv_warehouse_sk = warehouse.w_warehouse_sk |
| join date_dim on inventory.inv_date_sk = date_dim.d_date_sk |
| where d_year = 2001 |
| group by w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy) foo |
| where case mean when 0.0 |
| then 0.0 else stdev/mean end > 1) inv1 |
| join (select w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy, |
| stdev, mean, case mean when 0.0 |
| then null else stdev/mean end cov |
| from (select w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy, |
| sum(inv_quantity_on_hand) as stdev, |
| avg(inv_quantity_on_hand) as mean |
| from inventory |
| join item on inventory.inv_item_sk = item.i_item_sk |
| join warehouse on inventory.inv_warehouse_sk = warehouse.w_warehouse_sk |
| join date_dim on inventory.inv_date_sk = date_dim.d_date_sk |
| where d_year = 2001 |
| group by w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy) foo |
| where case mean when 0.0 |
| then 0.0 else stdev/mean end > 1) inv2 |
| on inv1.i_item_sk = inv2.i_item_sk |
| and inv1.w_warehouse_sk = inv2.w_warehouse_sk |
| where inv1.d_moy = 1 and inv2.d_moy = 1+1 |
| order by inv1.w_warehouse_sk, inv1.i_item_sk, inv1.d_moy, inv1.mean, inv1.cov, |
| inv2.d_moy, inv2.mean, inv2.cov; |
| |
| |
| explain |
| SELECT cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| Count(*) cnt1, |
| cd_purchase_estimate, |
| Count(*) cnt2, |
| cd_credit_rating, |
| Count(*) cnt3 |
| FROM customer c, |
| customer_address ca, |
| customer_demographics |
| WHERE c.c_current_addr_sk = ca.ca_address_sk |
| AND ca_state IN ( 'CO', 'IL', 'MN' ) |
| AND cd_demo_sk = c.c_current_cdemo_sk |
| AND EXISTS (SELECT * |
| FROM store_sales, |
| date_dim |
| WHERE c.c_customer_sk = ss_customer_sk |
| AND ss_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy BETWEEN 1 AND 1 + 2) |
| AND ( NOT EXISTS (SELECT * |
| FROM web_sales, |
| date_dim |
| WHERE c.c_customer_sk = ws_bill_customer_sk |
| AND ws_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy BETWEEN 1 AND 1 + 2) |
| AND NOT EXISTS (SELECT * |
| FROM catalog_sales, |
| date_dim |
| WHERE c.c_customer_sk = cs_ship_customer_sk |
| AND cs_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy BETWEEN 1 AND 1 + 2) ) |
| GROUP BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| ORDER BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| LIMIT 100; |
| SELECT cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| Count(*) cnt1, |
| cd_purchase_estimate, |
| Count(*) cnt2, |
| cd_credit_rating, |
| Count(*) cnt3 |
| FROM customer c, |
| customer_address ca, |
| customer_demographics |
| WHERE c.c_current_addr_sk = ca.ca_address_sk |
| AND ca_state IN ( 'CO', 'IL', 'MN' ) |
| AND cd_demo_sk = c.c_current_cdemo_sk |
| AND EXISTS (SELECT * |
| FROM store_sales, |
| date_dim |
| WHERE c.c_customer_sk = ss_customer_sk |
| AND ss_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy BETWEEN 1 AND 1 + 2) |
| AND ( NOT EXISTS (SELECT * |
| FROM web_sales, |
| date_dim |
| WHERE c.c_customer_sk = ws_bill_customer_sk |
| AND ws_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy BETWEEN 1 AND 1 + 2) |
| AND NOT EXISTS (SELECT * |
| FROM catalog_sales, |
| date_dim |
| WHERE c.c_customer_sk = cs_ship_customer_sk |
| AND cs_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy BETWEEN 1 AND 1 + 2) ) |
| GROUP BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| ORDER BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| LIMIT 100; |
| |
| |
| explain |
| SELECT cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| Count(*) cnt1, |
| cd_purchase_estimate, |
| Count(*) cnt2, |
| cd_credit_rating, |
| Count(*) cnt3 |
| FROM customer c, |
| customer_address ca, |
| customer_demographics |
| WHERE c.c_current_addr_sk = ca.ca_address_sk |
| AND ca_state IN ( 'CO', 'IL', 'MN' ) |
| AND cd_demo_sk = c.c_current_cdemo_sk |
| AND EXISTS (SELECT * |
| FROM store_sales, |
| date_dim |
| WHERE c.c_customer_sk = ss_customer_sk |
| AND ss_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy NOT BETWEEN 1 AND 1 + 2) |
| AND ( NOT EXISTS (SELECT * |
| FROM web_sales, |
| date_dim |
| WHERE c.c_customer_sk = ws_bill_customer_sk |
| AND ws_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy NOT BETWEEN 1 AND 1 + 2) |
| AND NOT EXISTS (SELECT * |
| FROM catalog_sales, |
| date_dim |
| WHERE c.c_customer_sk = cs_ship_customer_sk |
| AND cs_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy NOT BETWEEN 1 AND 1 + 2) ) |
| GROUP BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| ORDER BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| LIMIT 100; |
| SELECT cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| Count(*) cnt1, |
| cd_purchase_estimate, |
| Count(*) cnt2, |
| cd_credit_rating, |
| Count(*) cnt3 |
| FROM customer c, |
| customer_address ca, |
| customer_demographics |
| WHERE c.c_current_addr_sk = ca.ca_address_sk |
| AND ca_state IN ( 'CO', 'IL', 'MN' ) |
| AND cd_demo_sk = c.c_current_cdemo_sk |
| AND EXISTS (SELECT * |
| FROM store_sales, |
| date_dim |
| WHERE c.c_customer_sk = ss_customer_sk |
| AND ss_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy NOT BETWEEN 1 AND 1 + 2) |
| AND ( NOT EXISTS (SELECT * |
| FROM web_sales, |
| date_dim |
| WHERE c.c_customer_sk = ws_bill_customer_sk |
| AND ws_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy NOT BETWEEN 1 AND 1 + 2) |
| AND NOT EXISTS (SELECT * |
| FROM catalog_sales, |
| date_dim |
| WHERE c.c_customer_sk = cs_ship_customer_sk |
| AND cs_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy NOT BETWEEN 1 AND 1 + 2) ) |
| GROUP BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| ORDER BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| LIMIT 100; |
| |
| |
| explain |
| SELECT Count(*) |
| FROM (SELECT DISTINCT c_last_name, |
| c_first_name, |
| d_date |
| FROM store_sales, |
| date_dim, |
| customer |
| WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk |
| AND store_sales.ss_customer_sk = customer.c_customer_sk |
| AND d_month_seq BETWEEN 1212 AND 1212 + 11 |
| intersect |
| SELECT DISTINCT c_last_name, |
| c_first_name, |
| d_date |
| FROM catalog_sales, |
| date_dim, |
| customer |
| WHERE catalog_sales.cs_sold_date_sk = date_dim.d_date_sk |
| AND catalog_sales.cs_bill_customer_sk = customer.c_customer_sk |
| AND d_month_seq BETWEEN 1212 AND 1212 + 11 |
| intersect |
| SELECT DISTINCT c_last_name, |
| c_first_name, |
| d_date |
| FROM web_sales, |
| date_dim, |
| customer |
| WHERE web_sales.ws_sold_date_sk = date_dim.d_date_sk |
| AND web_sales.ws_bill_customer_sk = customer.c_customer_sk |
| AND d_month_seq BETWEEN 1212 AND 1212 + 11) hot_cust |
| LIMIT 100; |
| SELECT Count(*) |
| FROM (SELECT DISTINCT c_last_name, |
| c_first_name, |
| d_date |
| FROM store_sales, |
| date_dim, |
| customer |
| WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk |
| AND store_sales.ss_customer_sk = customer.c_customer_sk |
| AND d_month_seq BETWEEN 1212 AND 1212 + 11 |
| intersect |
| SELECT DISTINCT c_last_name, |
| c_first_name, |
| d_date |
| FROM catalog_sales, |
| date_dim, |
| customer |
| WHERE catalog_sales.cs_sold_date_sk = date_dim.d_date_sk |
| AND catalog_sales.cs_bill_customer_sk = customer.c_customer_sk |
| AND d_month_seq BETWEEN 1212 AND 1212 + 11 |
| intersect |
| SELECT DISTINCT c_last_name, |
| c_first_name, |
| d_date |
| FROM web_sales, |
| date_dim, |
| customer |
| WHERE web_sales.ws_sold_date_sk = date_dim.d_date_sk |
| AND web_sales.ws_bill_customer_sk = customer.c_customer_sk |
| AND d_month_seq BETWEEN 1212 AND 1212 + 11) hot_cust |
| LIMIT 100; |
| |
| |
| explain |
| WITH ss AS |
| ( |
| SELECT i_item_id, |
| Sum(ss_ext_sales_price) total_sales |
| FROM store_sales, |
| date_dim, |
| customer_address, |
| item |
| WHERE i_item_id IN |
| ( |
| SELECT i_item_id |
| FROM item |
| WHERE i_color IN ('orchid', |
| 'chiffon', |
| 'lace')) |
| AND ss_item_sk = i_item_sk |
| AND ss_sold_date_sk = d_date_sk |
| AND d_year = 2000 |
| AND d_moy = 1 |
| AND ss_addr_sk = ca_address_sk |
| AND ca_gmt_offset = -8 |
| GROUP BY i_item_id), cs AS |
| ( |
| SELECT i_item_id, |
| Sum(cs_ext_sales_price) total_sales |
| FROM catalog_sales, |
| date_dim, |
| customer_address, |
| item |
| WHERE i_item_id IN |
| ( |
| SELECT i_item_id |
| FROM item |
| WHERE i_color IN ('orchid', |
| 'chiffon', |
| 'lace')) |
| AND cs_item_sk = i_item_sk |
| AND cs_sold_date_sk = d_date_sk |
| AND d_year = 2000 |
| AND d_moy = 1 |
| AND cs_bill_addr_sk = ca_address_sk |
| AND ca_gmt_offset = -8 |
| GROUP BY i_item_id), ws AS |
| ( |
| SELECT i_item_id, |
| Sum(ws_ext_sales_price) total_sales |
| FROM web_sales, |
| date_dim, |
| customer_address, |
| item |
| WHERE i_item_id IN |
| ( |
| SELECT i_item_id |
| FROM item |
| WHERE i_color IN ('orchid', |
| 'chiffon', |
| 'lace')) |
| AND ws_item_sk = i_item_sk |
| AND ws_sold_date_sk = d_date_sk |
| AND d_year = 2000 |
| AND d_moy = 1 |
| AND ws_bill_addr_sk = ca_address_sk |
| AND ca_gmt_offset = -8 |
| GROUP BY i_item_id) |
| SELECT i_item_id , |
| Sum(total_sales) total_sales |
| FROM ( |
| SELECT * |
| FROM ss |
| UNION ALL |
| SELECT * |
| FROM cs |
| UNION ALL |
| SELECT * |
| FROM ws) tmp1 |
| GROUP BY i_item_id |
| ORDER BY total_sales limit 100; |
| WITH ss AS |
| ( |
| SELECT i_item_id, |
| Sum(ss_ext_sales_price) total_sales |
| FROM store_sales, |
| date_dim, |
| customer_address, |
| item |
| WHERE i_item_id IN |
| ( |
| SELECT i_item_id |
| FROM item |
| WHERE i_color IN ('orchid', |
| 'chiffon', |
| 'lace')) |
| AND ss_item_sk = i_item_sk |
| AND ss_sold_date_sk = d_date_sk |
| AND d_year = 2000 |
| AND d_moy = 1 |
| AND ss_addr_sk = ca_address_sk |
| AND ca_gmt_offset = -8 |
| GROUP BY i_item_id), cs AS |
| ( |
| SELECT i_item_id, |
| Sum(cs_ext_sales_price) total_sales |
| FROM catalog_sales, |
| date_dim, |
| customer_address, |
| item |
| WHERE i_item_id IN |
| ( |
| SELECT i_item_id |
| FROM item |
| WHERE i_color IN ('orchid', |
| 'chiffon', |
| 'lace')) |
| AND cs_item_sk = i_item_sk |
| AND cs_sold_date_sk = d_date_sk |
| AND d_year = 2000 |
| AND d_moy = 1 |
| AND cs_bill_addr_sk = ca_address_sk |
| AND ca_gmt_offset = -8 |
| GROUP BY i_item_id), ws AS |
| ( |
| SELECT i_item_id, |
| Sum(ws_ext_sales_price) total_sales |
| FROM web_sales, |
| date_dim, |
| customer_address, |
| item |
| WHERE i_item_id IN |
| ( |
| SELECT i_item_id |
| FROM item |
| WHERE i_color IN ('orchid', |
| 'chiffon', |
| 'lace')) |
| AND ws_item_sk = i_item_sk |
| AND ws_sold_date_sk = d_date_sk |
| AND d_year = 2000 |
| AND d_moy = 1 |
| AND ws_bill_addr_sk = ca_address_sk |
| AND ca_gmt_offset = -8 |
| GROUP BY i_item_id) |
| SELECT i_item_id , |
| Sum(total_sales) total_sales |
| FROM ( |
| SELECT * |
| FROM ss |
| UNION ALL |
| SELECT * |
| FROM cs |
| UNION ALL |
| SELECT * |
| FROM ws) tmp1 |
| GROUP BY i_item_id |
| ORDER BY total_sales limit 100; |
| |
| |
| explain |
| WITH sr_items AS |
| ( |
| SELECT i_item_id item_id, |
| Sum(sr_return_quantity) sr_item_qty |
| FROM store_returns, |
| item, |
| date_dim |
| WHERE sr_item_sk = i_item_sk |
| AND d_date IN |
| ( |
| SELECT d_date |
| FROM date_dim |
| WHERE d_week_seq IN |
| ( |
| SELECT d_week_seq |
| FROM date_dim |
| WHERE d_date IN ('1998-01-02', |
| '1998-10-15', |
| '1998-11-10'))) |
| AND sr_returned_date_sk = d_date_sk |
| GROUP BY i_item_id), cr_items AS |
| ( |
| SELECT i_item_id item_id, |
| Sum(cr_return_quantity) cr_item_qty |
| FROM catalog_returns, |
| item, |
| date_dim |
| WHERE cr_item_sk = i_item_sk |
| AND d_date IN |
| ( |
| SELECT d_date |
| FROM date_dim |
| WHERE d_week_seq IN |
| ( |
| SELECT d_week_seq |
| FROM date_dim |
| WHERE d_date IN ('1998-01-02', |
| '1998-10-15', |
| '1998-11-10'))) |
| AND cr_returned_date_sk = d_date_sk |
| GROUP BY i_item_id), wr_items AS |
| ( |
| SELECT i_item_id item_id, |
| Sum(wr_return_quantity) wr_item_qty |
| FROM web_returns, |
| item, |
| date_dim |
| WHERE wr_item_sk = i_item_sk |
| AND d_date IN |
| ( |
| SELECT d_date |
| FROM date_dim |
| WHERE d_week_seq IN |
| ( |
| SELECT d_week_seq |
| FROM date_dim |
| WHERE d_date IN ('1998-01-02', |
| '1998-10-15', |
| '1998-11-10'))) |
| AND wr_returned_date_sk = d_date_sk |
| GROUP BY i_item_id) |
| SELECT sr_items.item_id , |
| sr_item_qty , |
| sr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 sr_dev , |
| cr_item_qty , |
| cr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 cr_dev , |
| wr_item_qty , |
| wr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 wr_dev , |
| (sr_item_qty+cr_item_qty+wr_item_qty)/3.0 average |
| FROM sr_items , |
| cr_items , |
| wr_items |
| WHERE sr_items.item_id=cr_items.item_id |
| AND sr_items.item_id=wr_items.item_id |
| ORDER BY sr_items.item_id , |
| sr_item_qty limit 100; |
| |
| |
| explain |
| with ss as ( |
| select |
| i_manufact_id,sum(ss_ext_sales_price) total_sales |
| from |
| store_sales, |
| date_dim, |
| customer_address, |
| item |
| where |
| i_manufact_id in (select |
| i_manufact_id |
| from |
| item |
| where i_category in ('Books')) |
| and ss_item_sk = i_item_sk |
| and ss_sold_date_sk = d_date_sk |
| and d_year = 1999 |
| and d_moy = 3 |
| and ss_addr_sk = ca_address_sk |
| and ca_gmt_offset = -6 |
| group by i_manufact_id), |
| cs as ( |
| select |
| i_manufact_id,sum(cs_ext_sales_price) total_sales |
| from |
| catalog_sales, |
| date_dim, |
| customer_address, |
| item |
| where |
| i_manufact_id in (select |
| i_manufact_id |
| from |
| item |
| where i_category in ('Books')) |
| and cs_item_sk = i_item_sk |
| and cs_sold_date_sk = d_date_sk |
| and d_year = 1999 |
| and d_moy = 3 |
| and cs_bill_addr_sk = ca_address_sk |
| and ca_gmt_offset = -6 |
| group by i_manufact_id), |
| ws as ( |
| select |
| i_manufact_id,sum(ws_ext_sales_price) total_sales |
| from |
| web_sales, |
| date_dim, |
| customer_address, |
| item |
| where |
| i_manufact_id in (select |
| i_manufact_id |
| from |
| item |
| where i_category in ('Books')) |
| and ws_item_sk = i_item_sk |
| and ws_sold_date_sk = d_date_sk |
| and d_year = 1999 |
| and d_moy = 3 |
| and ws_bill_addr_sk = ca_address_sk |
| and ca_gmt_offset = -6 |
| group by i_manufact_id) |
| select i_manufact_id ,sum(total_sales) total_sales |
| from (select * from ss |
| union all |
| select * from cs |
| union all |
| select * from ws) tmp1 |
| group by i_manufact_id |
| order by total_sales |
| limit 100; |
| with ss as ( |
| select |
| i_manufact_id,sum(ss_ext_sales_price) total_sales |
| from |
| store_sales, |
| date_dim, |
| customer_address, |
| item |
| where |
| i_manufact_id in (select |
| i_manufact_id |
| from |
| item |
| where i_category in ('Books')) |
| and ss_item_sk = i_item_sk |
| and ss_sold_date_sk = d_date_sk |
| and d_year = 1999 |
| and d_moy = 3 |
| and ss_addr_sk = ca_address_sk |
| and ca_gmt_offset = -6 |
| group by i_manufact_id), |
| cs as ( |
| select |
| i_manufact_id,sum(cs_ext_sales_price) total_sales |
| from |
| catalog_sales, |
| date_dim, |
| customer_address, |
| item |
| where |
| i_manufact_id in (select |
| i_manufact_id |
| from |
| item |
| where i_category in ('Books')) |
| and cs_item_sk = i_item_sk |
| and cs_sold_date_sk = d_date_sk |
| and d_year = 1999 |
| and d_moy = 3 |
| and cs_bill_addr_sk = ca_address_sk |
| and ca_gmt_offset = -6 |
| group by i_manufact_id), |
| ws as ( |
| select |
| i_manufact_id,sum(ws_ext_sales_price) total_sales |
| from |
| web_sales, |
| date_dim, |
| customer_address, |
| item |
| where |
| i_manufact_id in (select |
| i_manufact_id |
| from |
| item |
| where i_category in ('Books')) |
| and ws_item_sk = i_item_sk |
| and ws_sold_date_sk = d_date_sk |
| and d_year = 1999 |
| and d_moy = 3 |
| and ws_bill_addr_sk = ca_address_sk |
| and ca_gmt_offset = -6 |
| group by i_manufact_id) |
| select i_manufact_id ,sum(total_sales) total_sales |
| from (select * from ss |
| union all |
| select * from cs |
| union all |
| select * from ws) tmp1 |
| group by i_manufact_id |
| order by total_sales |
| limit 100; |
| |
| |
| explain |
| select * |
| from (select count(*) as h8_30_to_9 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 8 |
| and time_dim.t_minute >= 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s1 join |
| (select count(*) as h9_to_9_30 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 9 |
| and time_dim.t_minute < 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s2 join |
| (select count(*) as h9_30_to_10 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 9 |
| and time_dim.t_minute >= 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s3 join |
| (select count(*) as h10_to_10_30 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 10 |
| and time_dim.t_minute < 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s4 join |
| (select count(*) as h10_30_to_11 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 10 |
| and time_dim.t_minute >= 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s5 join |
| (select count(*) as h11_to_11_30 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 11 |
| and time_dim.t_minute < 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s6 join |
| (select count(*) as h11_30_to_12 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 11 |
| and time_dim.t_minute >= 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s7 join |
| (select count(*) as h12_to_12_30 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 12 |
| and time_dim.t_minute < 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s8; |
| select * |
| from (select count(*) as h8_30_to_9 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 8 |
| and time_dim.t_minute >= 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s1 join |
| (select count(*) as h9_to_9_30 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 9 |
| and time_dim.t_minute < 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s2 join |
| (select count(*) as h9_30_to_10 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 9 |
| and time_dim.t_minute >= 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s3 join |
| (select count(*) as h10_to_10_30 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 10 |
| and time_dim.t_minute < 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s4 join |
| (select count(*) as h10_30_to_11 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 10 |
| and time_dim.t_minute >= 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s5 join |
| (select count(*) as h11_to_11_30 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 11 |
| and time_dim.t_minute < 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s6 join |
| (select count(*) as h11_30_to_12 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 11 |
| and time_dim.t_minute >= 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s7 join |
| (select count(*) as h12_to_12_30 |
| from store_sales |
| join household_demographics |
| on store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk |
| join time_dim |
| on store_sales.ss_sold_time_sk = time_dim.t_time_sk |
| join store |
| on store_sales.ss_store_sk = store.s_store_sk |
| where time_dim.t_hour = 12 |
| and time_dim.t_minute < 30 |
| and ((household_demographics.hd_dep_count = 4 |
| and household_demographics.hd_vehicle_count <= 3+2) |
| or (household_demographics.hd_dep_count = 2 |
| and household_demographics.hd_vehicle_count<=0+2) |
| or (household_demographics.hd_dep_count = 0 |
| and household_demographics.hd_vehicle_count<=1+2)) |
| and store.s_store_name = 'ese') s8; |
| |
| |
| explain |
| select substr(r_reason_desc, 1, 20) as r, |
| avg(ws_quantity) as wq, |
| avg(wr_refunded_cash) as ref, |
| avg(wr_fee) as fee |
| from web_sales |
| join web_returns |
| on web_sales.ws_item_sk = web_returns.wr_item_sk |
| and web_sales.ws_order_number = web_returns.wr_order_number |
| join customer_demographics cd1 |
| on cd1.cd_demo_sk = web_returns.wr_refunded_cdemo_sk |
| join customer_demographics cd2 |
| on cd2.cd_demo_sk = web_returns.wr_returning_cdemo_sk |
| join customer_address |
| on customer_address.ca_address_sk = web_returns.wr_refunded_addr_sk |
| join date_dim |
| on web_sales.ws_sold_date_sk = date_dim.d_date_sk |
| join reason |
| on reason.r_reason_sk = web_returns.wr_reason_sk |
| where d_year = 2000 |
| and ((cd1.cd_marital_status = 'M' |
| and cd1.cd_marital_status = cd2.cd_marital_status |
| and cd1.cd_education_status = 'Advanced Degree' |
| and cd1.cd_education_status = cd2.cd_education_status |
| and ws_sales_price between 100.00 and 150.00) |
| or |
| (cd1.cd_marital_status = 'S' |
| and cd1.cd_marital_status = cd2.cd_marital_status |
| and cd1.cd_education_status = 'College' |
| and cd1.cd_education_status = cd2.cd_education_status |
| and ws_sales_price between 50.00 and 100.00) |
| or |
| (cd1.cd_marital_status = 'W' |
| and cd1.cd_marital_status = cd2.cd_marital_status |
| and cd1.cd_education_status = '2 yr Degree' |
| and cd1.cd_education_status = cd2.cd_education_status |
| and ws_sales_price between 150.00 and 200.00)) |
| and ((ca_country = 'United States' |
| and ca_state in ('IN', 'OH', 'NJ') |
| and ws_net_profit between 100 and 200) |
| or |
| (ca_country = 'United States' |
| and ca_state in ('WI', 'CT', 'KY') |
| and ws_net_profit between 150 and 300) |
| or |
| (ca_country = 'United States' |
| and ca_state in ('LA', 'IA', 'AR') |
| and ws_net_profit between 50 and 250)) |
| group by r_reason_desc |
| order by r, wq, ref, fee |
| limit 100; |
| select substr(r_reason_desc, 1, 20) as r, |
| avg(ws_quantity) as wq, |
| avg(wr_refunded_cash) as ref, |
| avg(wr_fee) as fee |
| from web_sales |
| join web_returns |
| on web_sales.ws_item_sk = web_returns.wr_item_sk |
| and web_sales.ws_order_number = web_returns.wr_order_number |
| join customer_demographics cd1 |
| on cd1.cd_demo_sk = web_returns.wr_refunded_cdemo_sk |
| join customer_demographics cd2 |
| on cd2.cd_demo_sk = web_returns.wr_returning_cdemo_sk |
| join customer_address |
| on customer_address.ca_address_sk = web_returns.wr_refunded_addr_sk |
| join date_dim |
| on web_sales.ws_sold_date_sk = date_dim.d_date_sk |
| join reason |
| on reason.r_reason_sk = web_returns.wr_reason_sk |
| where d_year = 2000 |
| and ((cd1.cd_marital_status = 'M' |
| and cd1.cd_marital_status = cd2.cd_marital_status |
| and cd1.cd_education_status = 'Advanced Degree' |
| and cd1.cd_education_status = cd2.cd_education_status |
| and ws_sales_price between 100.00 and 150.00) |
| or |
| (cd1.cd_marital_status = 'S' |
| and cd1.cd_marital_status = cd2.cd_marital_status |
| and cd1.cd_education_status = 'College' |
| and cd1.cd_education_status = cd2.cd_education_status |
| and ws_sales_price between 50.00 and 100.00) |
| or |
| (cd1.cd_marital_status = 'W' |
| and cd1.cd_marital_status = cd2.cd_marital_status |
| and cd1.cd_education_status = '2 yr Degree' |
| and cd1.cd_education_status = cd2.cd_education_status |
| and ws_sales_price between 150.00 and 200.00)) |
| and ((ca_country = 'United States' |
| and ca_state in ('IN', 'OH', 'NJ') |
| and ws_net_profit between 100 and 200) |
| or |
| (ca_country = 'United States' |
| and ca_state in ('WI', 'CT', 'KY') |
| and ws_net_profit between 150 and 300) |
| or |
| (ca_country = 'United States' |
| and ca_state in ('LA', 'IA', 'AR') |
| and ws_net_profit between 50 and 250)) |
| group by r_reason_desc |
| order by r, wq, ref, fee |
| limit 100; |
| |
| |
| set hive.auto.convert.anti.join=false; |
| |
| explain |
| SELECT cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| Count(*) cnt1, |
| cd_purchase_estimate, |
| Count(*) cnt2, |
| cd_credit_rating, |
| Count(*) cnt3 |
| FROM customer c, |
| customer_address ca, |
| customer_demographics |
| WHERE c.c_current_addr_sk = ca.ca_address_sk |
| AND ca_state IN ( 'CO', 'IL', 'MN' ) |
| AND cd_demo_sk = c.c_current_cdemo_sk |
| AND EXISTS (SELECT * |
| FROM store_sales, |
| date_dim |
| WHERE c.c_customer_sk = ss_customer_sk |
| AND ss_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy BETWEEN 1 AND 1 + 2) |
| AND ( NOT EXISTS (SELECT * |
| FROM web_sales, |
| date_dim |
| WHERE c.c_customer_sk = ws_bill_customer_sk |
| AND ws_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy BETWEEN 1 AND 1 + 2) |
| AND NOT EXISTS (SELECT * |
| FROM catalog_sales, |
| date_dim |
| WHERE c.c_customer_sk = cs_ship_customer_sk |
| AND cs_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy BETWEEN 1 AND 1 + 2) ) |
| GROUP BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| ORDER BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| LIMIT 100; |
| SELECT cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| Count(*) cnt1, |
| cd_purchase_estimate, |
| Count(*) cnt2, |
| cd_credit_rating, |
| Count(*) cnt3 |
| FROM customer c, |
| customer_address ca, |
| customer_demographics |
| WHERE c.c_current_addr_sk = ca.ca_address_sk |
| AND ca_state IN ( 'CO', 'IL', 'MN' ) |
| AND cd_demo_sk = c.c_current_cdemo_sk |
| AND EXISTS (SELECT * |
| FROM store_sales, |
| date_dim |
| WHERE c.c_customer_sk = ss_customer_sk |
| AND ss_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy BETWEEN 1 AND 1 + 2) |
| AND ( NOT EXISTS (SELECT * |
| FROM web_sales, |
| date_dim |
| WHERE c.c_customer_sk = ws_bill_customer_sk |
| AND ws_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy BETWEEN 1 AND 1 + 2) |
| AND NOT EXISTS (SELECT * |
| FROM catalog_sales, |
| date_dim |
| WHERE c.c_customer_sk = cs_ship_customer_sk |
| AND cs_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy BETWEEN 1 AND 1 + 2) ) |
| GROUP BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| ORDER BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| LIMIT 100; |
| |
| |
| |
| explain |
| SELECT cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| Count(*) cnt1, |
| cd_purchase_estimate, |
| Count(*) cnt2, |
| cd_credit_rating, |
| Count(*) cnt3 |
| FROM customer c, |
| customer_address ca, |
| customer_demographics |
| WHERE c.c_current_addr_sk = ca.ca_address_sk |
| AND ca_state IN ( 'CO', 'IL', 'MN' ) |
| AND cd_demo_sk = c.c_current_cdemo_sk |
| AND EXISTS (SELECT * |
| FROM store_sales, |
| date_dim |
| WHERE c.c_customer_sk = ss_customer_sk |
| AND ss_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy NOT BETWEEN 1 AND 1 + 2) |
| AND ( NOT EXISTS (SELECT * |
| FROM web_sales, |
| date_dim |
| WHERE c.c_customer_sk = ws_bill_customer_sk |
| AND ws_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy NOT BETWEEN 1 AND 1 + 2) |
| AND NOT EXISTS (SELECT * |
| FROM catalog_sales, |
| date_dim |
| WHERE c.c_customer_sk = cs_ship_customer_sk |
| AND cs_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy NOT BETWEEN 1 AND 1 + 2) ) |
| GROUP BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| ORDER BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| LIMIT 100; |
| SELECT cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| Count(*) cnt1, |
| cd_purchase_estimate, |
| Count(*) cnt2, |
| cd_credit_rating, |
| Count(*) cnt3 |
| FROM customer c, |
| customer_address ca, |
| customer_demographics |
| WHERE c.c_current_addr_sk = ca.ca_address_sk |
| AND ca_state IN ( 'CO', 'IL', 'MN' ) |
| AND cd_demo_sk = c.c_current_cdemo_sk |
| AND EXISTS (SELECT * |
| FROM store_sales, |
| date_dim |
| WHERE c.c_customer_sk = ss_customer_sk |
| AND ss_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy NOT BETWEEN 1 AND 1 + 2) |
| AND ( NOT EXISTS (SELECT * |
| FROM web_sales, |
| date_dim |
| WHERE c.c_customer_sk = ws_bill_customer_sk |
| AND ws_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy NOT BETWEEN 1 AND 1 + 2) |
| AND NOT EXISTS (SELECT * |
| FROM catalog_sales, |
| date_dim |
| WHERE c.c_customer_sk = cs_ship_customer_sk |
| AND cs_sold_date_sk = d_date_sk |
| AND d_year = 1999 |
| AND d_moy NOT BETWEEN 1 AND 1 + 2) ) |
| GROUP BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| ORDER BY cd_gender, |
| cd_marital_status, |
| cd_education_status, |
| cd_purchase_estimate, |
| cd_credit_rating |
| LIMIT 100; |
| |
| explain |
| SELECT ranking |
| FROM |
| (SELECT rank() OVER (PARTITION BY ss_store_sk |
| ORDER BY sum(ss_net_profit)) AS ranking |
| FROM store_sales |
| GROUP BY ss_store_sk) tmp1 |
| WHERE ranking <= 5; |
| |
| SELECT ranking |
| FROM |
| (SELECT rank() OVER (PARTITION BY ss_store_sk |
| ORDER BY sum(ss_net_profit)) AS ranking |
| FROM store_sales |
| GROUP BY ss_store_sk) tmp1 |
| WHERE ranking <= 5; |
| |
| set hive.auto.convert.anti.join=true; |
| |
| DROP TABLE catalog_sales; |
| DROP TABLE catalog_returns; |
| DROP TABLE store_sales; |
| DROP TABLE store_returns; |
| DROP TABLE web_sales; |
| DROP TABLE web_returns; |
| DROP TABLE customer; |
| DROP TABLE customer_address; |
| DROP TABLE customer_demographics; |
| DROP TABLE inventory; |
| DROP TABLE item; |
| DROP TABLE warehouse; |
| DROP TABLE date_dim; |
| DROP TABLE store; |
| DROP TABLE household_demographics; |
| DROP TABLE time_dim; |
| DROP TABLE reason; |
| |
| FROM src |
| SELECT |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE CATALOG_SALES' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE CATALOG_RETURNS' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE STORE_SALES' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE STORE_RETURNS' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE WEB_SALES' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE WEB_RETURNS' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE CUSTOMER' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE CUSTOMER_ADDRESS' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE CUSTOMER_DEMOGRAPHICS' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE INVENTORY' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE ITEM' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE WAREHOUSE' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE DATE_DIM' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE STORE' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE HOUSEHOLD_DEMOGRAPHICS' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE TIME_DIM' ), |
| dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_perf','user1','passwd1', |
| 'DROP TABLE REASON' ) |
| limit 1; |
