按照部门划分,求每个部门下员工所在城市数量最多的城市信息,部门在一张表,城市和街道在其他表,用hive语法
时间: 2023-07-14 09:13:33 浏览: 33
假设部门表为`dept`,包含字段`dept_id`和`dept_name`,城市表为`city`,包含字段`city_id`和`city_name`,员工表为`employee`,包含字段`emp_id`、`emp_name`、`dept_id`和`city_id`。
首先,需要使用`JOIN`将三张表连接起来:
```
SELECT d.dept_id, d.dept_name, c.city_name
FROM dept d
JOIN employee e ON d.dept_id = e.dept_id
JOIN city c ON e.city_id = c.city_id;
```
这样就可以得到每个部门下每个员工所在的城市了。接下来,需要使用`GROUP BY`将结果按照部门和城市进行分组,并计算每个分组中城市数量:
```
SELECT d.dept_id, d.dept_name, c.city_name, COUNT(*) AS city_count
FROM dept d
JOIN employee e ON d.dept_id = e.dept_id
JOIN city c ON e.city_id = c.city_id
GROUP BY d.dept_id, d.dept_name, c.city_name;
```
最后,需要使用`ROW_NUMBER`函数对每个部门的城市数量进行排名,并只保留排名为1的记录:
```
SELECT dept_id, dept_name, city_name, city_count
FROM (
SELECT d.dept_id, d.dept_name, c.city_name, COUNT(*) AS city_count,
ROW_NUMBER() OVER (PARTITION BY d.dept_id ORDER BY COUNT(*) DESC) AS rn
FROM dept d
JOIN employee e ON d.dept_id = e.dept_id
JOIN city c ON e.city_id = c.city_id
GROUP BY d.dept_id, d.dept_name, c.city_name
) t
WHERE rn = 1;
```
这样就可以得到每个部门下城市数量最多的城市信息了。