第10章 天气数据分析实战

# 1) 建 MySQL 接收表（字段与页面一致，先全用字符串接住）
CREATE TABLE nev.weather (
  id INT PRIMARY KEY AUTO_INCREMENT,
  city VARCHAR(20), wdate VARCHAR(20),
  high VARCHAR(10), low VARCHAR(10),
  weather VARCHAR(30), wind VARCHAR(30), power VARCHAR(20)
);

# 2) 采集后（脚本/Python），用 Sqoop 导入 Hive
sqoop import \
  --connect jdbc:mysql://hadoop1:3306/nev --username root --password 123456 \
  --table weather --hive-import --hive-database ods --hive-table weather_ods \
  --fields-terminated-by '\t' -m 1

-- 缺失值：补默认值或排除
SELECT nvl(high, '0') AS high, nvl(weather, '未知') AS weather FROM weather_ods;

-- 格式化："3℃" → 3；"2025年07月01日" → 2025-07-01
SELECT cast(regexp_replace(high, '[^0-9-]', '') AS INT) AS high_num,
       date_format(to_date(wdate, 'yyyy年MM月dd日'), 'yyyy-MM-dd') AS dt
FROM weather_ods;

-- 去重：按城市+日期保留一条（row_number 或 distinct）
INSERT OVERWRITE TABLE dwd.weather_clean PARTITION (y, m)
SELECT city, dt, high_num, low_num, weather,
       year(dt) AS y, month(dt) AS m
FROM (
  SELECT *, row_number() OVER (PARTITION BY city, dt ORDER BY id DESC) rn
  FROM tmp_weather_parsed
) t WHERE rn = 1;

SELECT m, round(avg(high_num), 1) AS avg_high,
       max(high_num) AS max_high, min(low_num) AS min_low
FROM dwd.weather_clean
WHERE y = '2025'
GROUP BY m ORDER BY m;

SELECT city, m, count(*) AS sunny_days
FROM dwd.weather_clean
WHERE y = '2025' AND weather LIKE '%晴%'
GROUP BY city, m
ORDER BY city, m;

SELECT m, count(*) AS haze_days,
       round(count(*) * 100 / 30, 1) AS haze_rate
FROM dwd.weather_clean
WHERE city = '北京' AND y = '2025' AND (weather LIKE '%霾%' OR weather LIKE '%雾%')
GROUP BY m ORDER BY m;