| --! qt:dataset:src |
| set hive.mapred.mode=nonstrict; |
| |
| CREATE TABLE TestV1_Staples ( |
| Item_Count INT, |
| Ship_Priority STRING, |
| Order_Priority STRING, |
| Order_Status STRING, |
| Order_Quantity DOUBLE, |
| Sales_Total DOUBLE, |
| Discount DOUBLE, |
| Tax_Rate DOUBLE, |
| Ship_Mode STRING, |
| Fill_Time DOUBLE, |
| Gross_Profit DOUBLE, |
| Price DOUBLE, |
| Ship_Handle_Cost DOUBLE, |
| Employee_Name STRING, |
| Employee_Dept STRING, |
| Manager_Name STRING, |
| Employee_Yrs_Exp DOUBLE, |
| Employee_Salary DOUBLE, |
| Customer_Name STRING, |
| Customer_State STRING, |
| Call_Center_Region STRING, |
| Customer_Balance DOUBLE, |
| Customer_Segment STRING, |
| Prod_Type1 STRING, |
| Prod_Type2 STRING, |
| Prod_Type3 STRING, |
| Prod_Type4 STRING, |
| Product_Name STRING, |
| Product_Container STRING, |
| Ship_Promo STRING, |
| Supplier_Name STRING, |
| Supplier_Balance DOUBLE, |
| Supplier_Region STRING, |
| Supplier_State STRING, |
| Order_ID STRING, |
| Order_Year INT, |
| Order_Month INT, |
| Order_Day INT, |
| Order_Date_ STRING, |
| Order_Quarter STRING, |
| Product_Base_Margin DOUBLE, |
| Product_ID STRING, |
| Receive_Time DOUBLE, |
| Received_Date_ STRING, |
| Ship_Date_ STRING, |
| Ship_Charge DOUBLE, |
| Total_Cycle_Time DOUBLE, |
| Product_In_Stock STRING, |
| PID INT, |
| Market_Segment STRING |
| ); |
| |
| explain |
| SELECT customer_name, SUM(customer_balance), SUM(order_quantity) FROM default.testv1_staples s1 GROUP BY customer_name HAVING ( |
| (COUNT(s1.discount) <= 822) AND |
| (SUM(customer_balance) <= 4074689.000000041) |
| ); |
| |
| explain |
| SELECT customer_name, SUM(customer_balance), SUM(order_quantity) FROM default.testv1_staples s1 GROUP BY customer_name HAVING ( |
| (SUM(customer_balance) <= 4074689.000000041) |
| AND (COUNT(s1.discount) <= 822) |
| ); |
| |
| explain |
| SELECT s1.customer_name FROM default.testv1_staples s1 join default.src s2 on s1.customer_name = s2.key |
| GROUP BY s1.customer_name |
| HAVING ( |
| (SUM(s1.customer_balance) <= 4074689.000000041) |
| AND (AVG(s1.discount) <= 822) |
| AND (COUNT(s2.value) > 4) |
| ); |
| |
| explain |
| SELECT s1.customer_name FROM default.testv1_staples s1 join default.src s2 on s1.customer_name = s2.key |
| GROUP BY s1.customer_name, s1.customer_name |
| HAVING ( |
| (SUM(s1.customer_balance) <= 4074689.000000041) |
| AND (AVG(s1.discount) <= 822) |
| AND (COUNT(s2.value) > 4) |
| ); |
| |
| explain |
| SELECT distinct s1.customer_name as x, s1.customer_name as y |
| FROM default.testv1_staples s1 join default.src s2 on s1.customer_name = s2.key |
| HAVING ( |
| (SUM(s1.customer_balance) <= 4074689.000000041) |
| AND (AVG(s1.discount) <= 822) |
| AND (COUNT(s2.value) > 4) |
| ); |