--Create Detail Table HiveQL Statement
CREATE TABLE
  IF NOT EXISTS emall.dwd_trade_order_ordercreate_productsales (
    order_id INT COMMENT 'Order ID, primary key',
    product_id INT COMMENT 'Product ID',
    product_name STRING COMMENT 'Product Name',
    category_id INT COMMENT 'Product Category ID',
    category_name STRING COMMENT 'Product Category Name',
    quantity INT COMMENT 'Quantity, positive integer',
    unit_price DECIMAL(10, 2) COMMENT 'Unit Price, with two decimal places',
    amount DECIMAL(10, 2) COMMENT 'Subtotal Amount, i.e., quantity times unit price',
    order_time TIMESTAMP COMMENT 'Order Time, accurate to the minute',
    shipping_city_id INT COMMENT 'Shipping City ID, foreign key',
    shipping_city_name STRING COMMENT 'City Name',
    shipping_address STRING COMMENT 'Shipping Address, including province, city, district, and detailed address'
  ) 
  COMMENT 'Product Sales Detail Table, records the sales details of each order' 
  PARTITIONED BY (pt_date STRING) 
  row format delimited fields terminated by '\t' 
  STORED AS PARQUET;

--Insert data into the Detail Table HiveQL statement
SET hive.exec.dynamic.partition.mode=nonstrict;
INSERT INTO TABLE emall.dwd_trade_order_ordercreate_productsales PARTITION (pt_date)
SELECT
    o.order_id,
    o.product_id,
    p.product_name,
    p.category_id,
    ca.category_name,
    o.quantity,
    o.unit_price,
    o.amount,
    o.order_time,
    o.shipping_city_id,
    ci.city_name,
    o.shipping_address,
    o.pt_date
FROM
    emall.ods_order_order o
JOIN
    emall.ods_product_product p ON o.product_id = p.product_id
JOIN
    emall.ods_product_category ca ON p.category_id = ca.category_id
JOIN
    emall.ods_order_city ci ON o.shipping_city_id = ci.city_id
WHERE o.pt_date = '${yyyy-MM-dd-1d}';

--Create summary table HiveQL statement
CREATE TABLE IF NOT EXISTS emall.dws_trade_order_productsales_1d (
    order_date DATE COMMENT 'Date of statistics, primary key',
    city_id INT COMMENT 'City ID',
    city_name STRING COMMENT 'City name',
    category_id INT COMMENT 'Product category ID',
    category_name STRING COMMENT 'Product category name',
    quantity INT COMMENT 'Total product sales, positive integer',
    amount DECIMAL(10, 2) COMMENT 'Total product sales in monetary terms, two decimal places kept'
)
COMMENT 'Daily summary table of product sales situations'
PARTITIONED BY (pt_date STRING)
row format delimited fields terminated by '\t' 
STORED AS PARQUET;

--Insert data into the Summary Table HiveQL statement
SET hive.exec.dynamic.partition.mode=nonstrict;
INSERT INTO TABLE emall.dws_trade_order_productsales_1d PARTITION (pt_date)
SELECT
    p.pt_date AS order_date,
    p.shipping_city_id,
    p.shipping_city_name,
    p.category_id,
    p.category_name,
    SUM(p.quantity) AS quantity,
    SUM(p.amount) AS amount,
    p.pt_date
FROM
    emall.dwd_trade_order_ordercreate_productsales p
WHERE p.pt_date = '${yyyy-MM-dd-1d}'
GROUP BY
    p.pt_date,
    p.shipping_city_id,
    p.shipping_city_name,
    p.category_id,
    p.category_name;