xsdm dwd dws

INSERT INTO TABLE province_consumption_day_aggr;
SELECT   
  province_id,   
  province_name,   
  region_id,   
  region_name,   
  SUM(total_amount) AS total_amount,   
  COUNT(*) AS total_count,   
  ROW_NUMBER() OVER (PARTITION BY province_id, year, month ORDER BY SUM(total_amount) DESC) AS sequence  
FROM dwd_table  
GROUP BY province_id, province_name, region_id, region_name, year, month;

SELECT   
  province_id,   
  province_name,   
  region_id,   
  region_name,   
  SUM(CAST(total_amount AS BIGINT)) AS total_amount,   
  COUNT(*) AS total_count  
FROM province_consumption_day_aggr  
WHERE year = YEAR(CURRENT_DATE()) AND month = MONTH(CURRENT_DATE())  
GROUP BY province_id, province_name, region_id, region_name  
ORDER BY total_count DESC, total_amount DESC, province_id DESC  
LIMIT 5;

SELECT   
  province_id,   
  province_name,   
  region_id,   
  region_name,   
  CAST(SUM(total_amount) AS BIGINT) AS total_amount,   
  COUNT(*) AS total_count  
FROM province_consumption_day_aggr  
WHERE year = YEAR(CURRENT_DATE()) AND month = MONTH(CURRENT_DATE())  
GROUP BY province_id, province_name, region_id, region_name  
ORDER BY total_count DESC, total_amount DESC, province_id DESC  
LIMIT 5;

INSERT INTO provinceavgcmpregion  
SELECT   
  p.provinceid,   
  p.provincename,   
  AVG(o.total_amount) AS provinceavgconsumption,   
  r.regionid,   
  r.regionname,   
  AVG(o.total_amount) OVER (PARTITION BY r.regionid) AS regionavgconsumption,   
  CASE   
    WHEN AVG(o.total_amount) > AVG(o.total_amount) OVER (PARTITION BY r.regionid) THEN '高'   
    WHEN AVG(o.total_amount) < AVG(o.total_amount) OVER (PARTITION BY r.regionid) THEN '低'   
    ELSE '相同'   
  END AS comparison   
FROM dws.province_consumption_day_aggr o  
JOIN dws.provinces p ON o.province_id = p.province_id  
JOIN dws.regions r ON o.region_id = r.region_id  
WHERE o.year = 2020 AND o.month = 4;

SELECT * FROM provinceavgcmpregion ORDER BY provinceid DESC, provinceavgconsumption DESC, regionavgconsumption DESC LIMIT 5;