Query:
SELECT department_id "Dept", hire_date "Date", last_name "Name",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
OVER (PARTITION BY department_id) as "Emp_list"
FROM employees
WHERE hire_date < '01-SEP-2003'
ORDER BY "Dept", "Date", "Name";
Dept Date Name Emp_list
----- --------- --------------- --------------------------------
30 07-DEC-02 Raphaely Raphaely; Khoo
30 18-MAY-03 Khoo Raphaely; Khoo
40 07-JUN-02 Mavris Mavris
50 01-MAY-03 Kaufling Kaufling; Ladwig
50 14-JUL-03 Ladwig Kaufling; Ladwig
70 07-JUN-02 Baer Baer
90 13-JAN-01 De Haan De Haan; King
90 17-JUN-03 King De Haan; King
100 16-AUG-02 Faviet Faviet; Greenberg
100 17-AUG-02 Greenberg Faviet; Greenberg
110 07-JUN-02 Gietz Gietz; Higgins
110 07-JUN-02 Higgins Gietz; Higgins
Doubt: What is significance of Partition over here?
怀疑:分区在这里有什么意义?
1 个解决方案
#1
0
Here is a short answer. You should read the documentation on analytic functions, though.
这是一个简短的答案。但是,您应该阅读有关分析函数的文档。
The function list_agg() -- as with many Oracle functions -- can be either an aggregation function or an analytic function. I assume you know what an aggregation function is. The group by clause puts all rows with the same value of the aggregation variables and summarizes them into a single row.
函数list_agg() - 与许多Oracle函数一样 - 可以是聚合函数或分析函数。我假设您知道聚合函数是什么。 group by子句将所有行放在聚合变量的相同值中,并将它们汇总到一行中。
Analytic functions are similar, but different of course. They also find groups of rows that are similar to each other. However, they don't summarize them on one row. They simply add a new column with that information. The partition by clause specifies how the groups are created.
分析函数类似,但当然不同。他们还发现彼此相似的行组。但是,他们没有在一行中总结它们。他们只需添加一个包含该信息的新列。 partition by子句指定组的创建方式。
So, this query is combining all the last_names in rows with the same department_id. The last_names are ordered by hiring date and then the name. This list is then attached to every line in the same table. You can compare your query to a similar aggregation query:
因此,此查询将行中的所有last_names与相同的department_id组合在一起。 last_names按招聘日期排序,然后是名称。然后将此列表附加到同一表中的每一行。您可以将查询与类似的聚合查询进行比较:
SELECT department_id "Dept",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
FROM employees
WHERE hire_date < '01-SEP-2003'
GROUP BY department_id;
#1
0
Here is a short answer. You should read the documentation on analytic functions, though.
这是一个简短的答案。但是,您应该阅读有关分析函数的文档。
The function list_agg() -- as with many Oracle functions -- can be either an aggregation function or an analytic function. I assume you know what an aggregation function is. The group by clause puts all rows with the same value of the aggregation variables and summarizes them into a single row.
函数list_agg() - 与许多Oracle函数一样 - 可以是聚合函数或分析函数。我假设您知道聚合函数是什么。 group by子句将所有行放在聚合变量的相同值中,并将它们汇总到一行中。
Analytic functions are similar, but different of course. They also find groups of rows that are similar to each other. However, they don't summarize them on one row. They simply add a new column with that information. The partition by clause specifies how the groups are created.
分析函数类似,但当然不同。他们还发现彼此相似的行组。但是,他们没有在一行中总结它们。他们只需添加一个包含该信息的新列。 partition by子句指定组的创建方式。
So, this query is combining all the last_names in rows with the same department_id. The last_names are ordered by hiring date and then the name. This list is then attached to every line in the same table. You can compare your query to a similar aggregation query:
因此,此查询将行中的所有last_names与相同的department_id组合在一起。 last_names按招聘日期排序,然后是名称。然后将此列表附加到同一表中的每一行。您可以将查询与类似的聚合查询进行比较:
SELECT department_id "Dept",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
FROM employees
WHERE hire_date < '01-SEP-2003'
GROUP BY department_id;