sql - postgresql 按最大字符串长度聚合

Question

我有一对多的关系。在这种情况下，它是一个pipelines可以有许多segments. 该segments实体有一列列出与该管道关联的井。此列纯粹是信息性的，仅作为逗号分隔列表从监管来源更新，因此数据类型为text.

我想要做的是列出所有pipelines并显示segment具有最相关井的列。每口井都用标准化的土地位置标识（每口井的文本长度相同）。我还在 上做其他聚合函数segments，所以我的查询看起来像这样（我必须简化它，因为它非常大）：

SELECT pipelines.*, max(segments.associated_wells), min(segments.days_without_production), max(segments.production_water_m3)
FROM pipelines
JOIN segments ON segments.pipeline_id = pipelines.id
GROUP BY pipelines.id

这会选择associated_wells具有最高字母值的，这是有道理的，但不是我想要的。

max(length(segments.associated_wells))将选择我想要的记录，但只显示长度。我需要显示列值。

如何根据字符串长度进行聚合但显示值？

这是我所期待的一个例子：

细分实体：

| id | pipeline_id | associated_wells         | days_without_production | production_water_m3 |
|----|-------------|--------------------------|-------------------------|---------------------|
| 1  | 1           | 'location1', 'location2' | 30                      | 2.3                 |
| 2  | 1           | 'location1'              | 15                      | 1.4                 |
| 3  | 2           | 'location1'              | 20                      | 1.8                 |

管道实体：

| id | name        |
|----|-------------|
| 1  | 'Pipeline1' |
| 2  | 'Pipeline2' |
|    |             |

期望的查询结果：

| id | name        | associated_wells         | days_without_production | production_water_m3 |
|----|-------------|--------------------------|-------------------------|---------------------|
| 1  | 'Pipeline1' | 'location1', 'location2' | 15                      | 2.3                 |
| 2  | 'Pipeline2' | 'location1'              | 20                      | 1.8                 |
|    |             |                          |                         |                     |

标签： sqlpostgresql

Answer 1

如果我理解正确，你想要DISTINCT ON：

SELECT DISTINCT ON (p.id) p.*, s.*
FROM pipelines p JOIN
     segments s
     ON s.pipeline_id = p.id
ORDER BY p.id, LENGTH(s.associated_wells) DESC;