County：指美国的郡县名State：指对应郡县所属的州FIPS Code：联邦信息处理标准FIPS现称为联邦信息处理系列Population：记录当前郡县人口数量Area：记录当前郡县的土地面积。Density：记录当前郡县的人口密度利用hive语句1计算某一州面积最大的郡县2统计各州总人数、总面积、人口密度；3统计面积Top-10、人口top-10的州。并将每一个问题的结果存为csv文件

(1) 计算某一州面积最大的郡县

SELECT state, county, area FROM ( SELECT state, county, area, ROW_NUMBER() OVER (PARTITION BY state ORDER BY area DESC) as rn FROM counties ) as t WHERE rn = 1 ORDER BY state;

结果存为csv文件：

INSERT OVERWRITE LOCAL DIRECTORY '/path/to/output' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' SELECT state, county, area FROM ( SELECT state, county, area, ROW_NUMBER() OVER (PARTITION BY state ORDER BY area DESC) as rn FROM counties ) as t WHERE rn = 1 ORDER BY state;

(2) 统计各州总人数、总面积、人口密度

SELECT state, SUM(population) as total_population, SUM(area) as total_area, SUM(population)/SUM(area) as density FROM counties GROUP BY state ORDER BY state;

结果存为csv文件：

INSERT OVERWRITE LOCAL DIRECTORY '/path/to/output' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' SELECT state, SUM(population) as total_population, SUM(area) as total_area, SUM(population)/SUM(area) as density FROM counties GROUP BY state ORDER BY state;

(3) 统计面积Top-10、人口top-10的州

-- 面积Top-10的州 SELECT state, total_area FROM ( SELECT state, SUM(area) as total_area, ROW_NUMBER() OVER (ORDER BY SUM(area) DESC) as rn FROM counties GROUP BY state ) as t WHERE rn <= 10 ORDER BY total_area DESC;

-- 人口top-10的州 SELECT state, total_population FROM ( SELECT state, SUM(population) as total_population, ROW_NUMBER() OVER (ORDER BY SUM(population) DESC) as rn FROM counties GROUP BY state ) as t WHERE rn <= 10 ORDER BY total_population DESC;

结果存为csv文件：

-- 面积Top-10的州 INSERT OVERWRITE LOCAL DIRECTORY '/path/to/output' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' SELECT state, total_area FROM ( SELECT state, SUM(area) as total_area, ROW_NUMBER() OVER (ORDER BY SUM(area) DESC) as rn FROM counties GROUP BY state ) as t WHERE rn <= 10 ORDER BY total_area DESC;

-- 人口top-10的州 INSERT OVERWRITE LOCAL DIRECTORY '/path/to/output' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' SELECT state, total_population FROM ( SELECT state, SUM(population) as total_population, ROW_NUMBER() OVER (ORDER BY SUM(population) DESC) as rn FROM counties GROUP BY state ) as t WHERE rn <= 10 ORDER BY total_population DESC