city STRING
)ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’;
– 插入数据到weather_data表(示例数据)
INSERT INTO TABLE weather_data VALUES
(1, ‘2022-01-01 周六’, ‘6°’, ‘-7°’, ‘晴’, ‘西北风3级’, ‘北京’),
(2, ‘2022-01-02 周日’, ‘2°’, ‘-7°’, ‘多云’, ‘南风2级’, ‘北京’);
– 创建etl_weather_data表
CREATE TABLE IF NOT EXISTS etl_weather_data (
date
STRING,
day_of_week STRING,
high_temperature INT,
low_temperature INT,
weather STRING,
wind_direction STRING,
wind_speed STRING,
city STRING
);
– 插入数据到etl_weather_data表
INSERT INTO TABLE etl_weather_data
SELECT
SUBSTR(date
, 1, INSTR(date
, ’ ') - 1) AS date
,
SUBSTR(date
, INSTR(date
, ’ ') + 1) AS day_of_week,
CAST(SUBSTR(high_temperature, 1, INSTR(high_temperature, ‘°’) - 1) AS INT) AS high_temperature,
CAST(SUBSTR(low_temperature, 1, INSTR(low_temperature, ‘°’) - 1) AS INT) AS low_temperature,
weather,
REGEXP_REPLACE(SUBSTR(wind_direction, 1, INSTR(wind_direction, ‘级’) - 1), ‘[0-9]’, ‘’) AS wind_direction,
SUBSTR(SUBSTR(wind_direction, INSTR(wind_direction, ‘风’) + 1),1,1) AS wind_speed,
city
FROM
weather_data;
– 1.统计一年中每个城市晴天个数的top10
CREATE TABLE IF NOT EXISTS top_sunny_cities (
city STRING,
sunny_days_count INT
);