查询计算每一天的累计销售金额以及与前一天相比的销售金额增长额

• 请编写一个Hive SQL查询，对于每个店铺，计算每一天的累计销售金额以及与前一天相比的销售金额增长额（如果是第一天，则增长额为0）。结果表应包含shop_id,sale_date,sale_amount,cumulative_sale_amount和sale_amount_increase这几列
  建表语句

CREATE TABLE sales_data (
  shop_id STRING,
  sale_date STRING,
  sale_amount DOUBLE
)
STORED AS PARQUET;  -- 可以选择不同的存储格式，如 Parquet, ORC, etc.
INSERT INTO sales_data VALUES
  ('shop_1', '2023-01-01', 100.0),
  ('shop_1', '2023-01-02', 150.0),
  ('shop_1', '2023-01-03', 200.0),
  ('shop_2', '2023-01-01', 80.0),
  ('shop_2', '2023-01-02', 120.0),
  ('shop_2', '2023-01-03', 160.0);

代码

select 
shop_id,
sale_date,
sale_amount,
sum(sale_amount) over(partition by shop_id order by sale_date) as cumulative_sale_amount,
if(lag(sale_amount) over(partition by shop_id order by sale_amount) is null , 0,
   sale_amount - lag(sale_amount) over(partition by shop_id order by sale_amount)
) as sale_amount_increase
from sales_data

结果