来自数组的不同值？ [英] Distinct values from an array?
问题描述
以下表：
CREATE TEMPORARY TABLE guys ( guy_id integer primary key, guy text );
CREATE TEMPORARY TABLE sales ( log_date date, sales_guys integer[], sales smallint );
INSERT INTO guys VALUES(1,'john'),(2,'joe');
INSERT INTO sales VALUES('2016-01-01', '{1,2}', 2),('2016-01-02','{1,2}',4);
以下查询非常适合显示给定日期的名称：
Following query works great to show names on a given date:
SELECT log_date, sales_guys, ARRAY_AGG(guy), sales
FROM sales
JOIN guys ON
guys.guy_id = ANY(sales.sales_guys)
GROUP BY log_date, sales_guys, sales
ORDER BY log_date ASC;
log_date | sales_guys | array_agg | sales
------------+------------+------------+-------
2016-01-01 | {1,2} | {john,joe} | 2
2016-01-02 | {1,2} | {john,joe} | 4
以下查询问题给我每个人每个日期的名字，因此这里每个名字两次，依此类推on）：
Following query problematically gives me a name per date per guy, so here each name twice, and so on):
SELECT sales_guys, ARRAY_AGG(guy), SUM(sales) AS sales
FROM sales
JOIN guys ON guys.guy_id = ANY(sales.sales_guys)
GROUP BY sales_guys;
收益率：
sales_guys | array_agg | sales
------------+---------------------+-------
{1,2} | {john,joe,john,joe} | 12
是否可以通过某种方式减少 ARRAY_AGG 调用仅给出唯一名称？
Is there a way to somehow reduce the ARRAY_AGG call to give only the unique names?
推荐答案
没有
There is no kind of order you can trust without ORDER BY. Except that elements of arrays, when unnested, come in array order. If your query does more with the result, it may be re-ordered, though.
您只需添加 ORDER BY 转换为Postgres中的任何聚合函数：
You an simply add ORDER BY to any aggregate function in Postgres:
SELECT s.sales_guys, ARRAY_AGG(DISTINCT g.guy ORDER BY g.guy) AS names, SUM(s.sales) AS sum_sales
FROM sales s
JOIN guys g ON g.guy_id = ANY(s.sales_guys)
GROUP BY s.sales_guys;但这显然不是数组元素的原始顺序。而且查询还有其他问题... IN 或 = ANY（）都不关心集合中元素的顺序，列表或右侧的数组：
But that's obviously not the original order of array elements. And the query has other issues ... Neither IN nor = ANY() care about order of elements in the set, list or array on the right side:
- How to use ANY instead of IN in a WHERE clause with Rails?
对于此任务（请注意细节！）：
For this task (attention to the details!):
获得每个数组 sales 的总销售额 sales_guys ，元素的顺序有所不同（数组'{1,2}'和'{2,1}'是不同的）和 sales_guys 既没有重复元素也没有NULL元素。
Get the total sales per array sales_guys, where the order of elements makes a difference (arrays '{1,2}' and '{2,1}' are not the same) and sales_guys has neither duplicate nor NULL elements. Add an array of resolved names in matching order.
将 unnest（）与<$ c一起使用$ c>具有顺序。并在您解析名称之前聚集数组 ，这样更便宜且更不易出错。
Use unnest() with WITH ORDINALITY. and aggregate arrays before you resolve names, that's cheaper and less error prone.
SELECT s.*, g.
FROM (
SELECT sales_guys, sum (sales) AS total_sales -- aggregate first in subquery
FROM sales
GROUP BY 1
) s
, LATERAL (
SELECT array_agg(guy ORDER BY ord) AS names -- order by original order
FROM unnest(s.sales_guys) WITH ORDINALITY sg(guy_id, ord) -- with order of elements
LEFT JOIN guys g USING (guy_id) -- LEFT JOIN to add NULL for missing guy_id
) g;
LATERAL 子查询可以与无条件联接 CROSS JOIN -逗号（，）是简写形式-因为子查询 guarantees 每行的结果。否则，您将使用 LEFT JOIN LATERAL ..启用true 。
The LATERAL subquery can be joined with unconditional CROSS JOIN - comma (,) is shorthand notation - because the aggregate in the subquery guarantees a result for every row. Else you'd use LEFT JOIN LATERAL .. ON true.
详细说明：
- PostgreSQL unnest() with element number
- What is the difference between LATERAL and a subquery in PostgreSQL?
- Create two arrays for two fields, keeping sort order of arrays in sync (without subquery)
这篇关于来自数组的不同值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
