主页 / dba / 问题 / 185962
Accepted
Randomize
Asked: 2017-09-15 05:49:22 +0800 CST

使用 PostgreSQL 压缩聚合行

  • 772

给定一个这样的表my_data：

 id | name | surname | age
----+------+---------+------
 1  | john | smith   | NULL
 1  | NULL | smith   | 32
 1  | NULL | NULL    | NULL
 1  | john | smith   | NULL
 1  | john | NULL    | 32

CREATE TABLE my_data(id,name,surname,age)
AS ( VALUES 
  (1::int, 'john', 'smith' ,NULL::int),
  (1, NULL,   'smith' ,32),
  (1, NULL,   NULL    ,NULL),
  (1, 'john', 'smith' ,NULL),
  (1, 'john', NULL    ,32)
);

对于相同id的，各个列中的值（如果存在）始终相同，因此我如何“压缩”它们以获得：

 id | name | surname | age
----+------+---------+------
 1  | john | smith   | 32

我的尝试

A cross join lateralfor each column 是我目前唯一的想法，但我怀疑它是否好：

select 
distinct column1, c2.value, c3.value, c4.value
from my_data md
cross join lateral (select column2 from my_data where column1 = md.column1 and column2 is not null limit 1) as c2(value)
cross join lateral (select column3 from my_data where column1 = md.column1 and column3 is not null limit 1) as c3(value)
cross join lateral (select column4 from my_data where column1 = md.column1 and column4 is not null limit 1) as c4(value);
postgresql aggregate

2 个回答

  1. Best Answer

    使用percentile_disc

    我认为这样的事情是最快的，

    SELECT id,
  percentile_disc(0) WITHIN GROUP (ORDER BY name NULLS LAST)    AS name,
  percentile_disc(0) WITHIN GROUP (ORDER BY surname NULLS LAST) AS surname,
  percentile_disc(0) WITHIN GROUP (ORDER BY age NULLS LAST)     AS age
FROM my_data
GROUP BY id;

 id | name | surname | age 
----+------+---------+-----
  1 | john | smith   |  32
(1 row)

    这里我们使用percentile_disc一个Ordered-Set Aggregate描述为“离散百分位数：返回其在排序中的位置等于或超过指定分数的第一个输入值”。所以，

    • 如果那个位置是“0”，它基本上first_value在被聚合的行之上。
    • 如果 NULLS 是最后一个 ( NULLS LAST)，则第一个值不会为 null，这就是这里最重要的（因为你说“对于相同的 id，各个列中的值（如果存在）总是相同的”）

    我认为，您也可以first_value在窗口函数中执行此操作，然后使用DISTINCT ON.

    使用mode

    如果你想要非价值观的共识，我们也可以做到。我假设percentile_disc这是 OP 想要的。另一种选择是使用mode()不同的Ordered-Set Aggregate Function。它被描述为“返回最频繁的输入值（如果有多个相同频率的结果，则任意选择第一个）”。看起来像，

    SELECT id,
  mode() WITHIN GROUP (ORDER BY name)    AS name,
  mode() WITHIN GROUP (ORDER BY surname) AS surname,
  mode() WITHIN GROUP (ORDER BY age)     AS age
FROM my_data
GROUP BY id;
    • 2

  2. Evan Carroll 的有趣解决方案获得了 +1（甚至从未听说过percentile_disc！）。

    但是，我有另一种可能的解决方案，它具有通用性的优点（对于那些RDBMS有CTEs 的 s - 即WITH子句）。

    它不需要任何特殊的 PostgreSQL 特定功能 - 除了LIMIT- 在其他服务器中具有相应的关键字。

    （完全重写！）

    我做了以下事情：

    创建我的表和数据：

    CREATE TABLE person
(
  person_id INTEGER,
  person_firstname VARCHAR (25),
  person_surname VARCHAR (25),
  person_age INTEGER
);

INSERT INTO person VALUES
( 1, 'john', 'smith', NULL),
( 1,   NULL, 'smith',   32),
( 1,   NULL,    NULL, NULL),
( 1, 'john', 'smith', NULL),
( 1, 'john',    NULL,   32),
( 1, 'John', 'Smith', 3456);  <-- note capitalised names and outlier age!

    然后运行以下命令：

    WITH fname AS
(
  SELECT person_id, person_firstname
  FROM person
  GROUP BY 1, 2
  ORDER BY COUNT(*) DESC
  LIMIT 1
),
lname AS
(
  SELECT person_id, person_surname
  FROM person
  GROUP BY 1, 2
  ORDER BY COUNT(person_surname) DESC
  LIMIT 1
),
age AS
(
  SELECT person_id, person_age
  FROM person
  GROUP BY 1, 2
  ORDER BY COUNT(person_age) DESC
  LIMIT 1
)
SELECT fname.person_firstname, lname.person_surname, age.person_age
FROM fname
JOIN lname on fname.person_id = lname.person_id
JOIN age   on lname.person_id = age.person_id;

    与（期望的）结果（对数据正确）：

     person_firstname | person_surname | 
------------------+----------------+-----
 john             | smith          |  32   <--- average good - outlier ignored!
(1 row)

    即使存在异常值，此解决方案也能提供正确的解决方案 -（请参阅此帖子之前的编辑以了解问题！） - 它确实取决于大多数答案是否正确。使用该UPPER()功能还将消除任何大写问题。

    当然，更好的解决方案是在数据到达 HDD 附近之前NOT NULL进行数据清理 -例如，放入声明将是一个好的开始！

    • 1

相关问题