Блог

Главная - Вопросы по программированию - агрегат postgresql по максимальной длине строки

агрегат postgresql по максимальной длине строки

#sql #postgresql

#sql #postgresql

Вопрос:

У меня есть отношение «один ко многим». В данном случае это 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                 |
|    |             |                          |                         |                     |

Ответ №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;

Комментарии:

1. не совсем, я понял, что в своем вопросе я забыл добавить group by предложение в примере запроса. Я выполняю агрегирование по многим столбцам, вот почему я включил другие столбцы в пример.

2. @Sehael . . . Вы возвращаете по одной строке на pipeline.id в вашем запросе, что и происходит. Похоже, это то, о чем вы просите.

3. взгляните на пример, который я опубликовал. Я хочу взять значения некоторых столбцов из разных записей.

Ответ №2:

Продолжайте нормализацию и вертикализацию местоположений / связанных скважин путем перекрестного объединения с серией целых чисел, а затем дважды сгруппируйте:

 WITH
segment(seg_id,pipeline_id,associated_wells,days_without_production,production_water_m3) AS (
          SELECT 1,1,'location1, location2',30,2.3
UNION ALL SELECT 2,1,'location1',15,1.4
UNION ALL SELECT 3,2,'location1',20,1.8
)
,
pipeline(pipeline_id,name) AS (
          SELECT 1,'Pipeline1'
UNION ALL SELECT 2,'Pipeline2'
)
,
i(i) AS (
          SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
)
,
location AS (
  SELECT
    seg_id
  , i AS loc_id
  , SPLIT_PART(associated_wells,', ',i) AS location
  FROM segment CROSS JOIN i
  WHERE SPLIT_PART(associated_wells,',' ,i) <>''
)
,
pregroup AS (
  SELECT
    segment.pipeline_id
  , location.location
  , MIN(days_without_production) AS days_without_production
  , MAX(production_water_m3)     AS production_water_m3
  FROM segment
  JOIN pipeline USING(pipeline_id)
  JOIN location USING(seg_id)
  GROUP BY 1,2 
)
SELECT
  pipeline_id
, STRING_AGG(location,',')     AS locations
, MIN(days_without_production) AS days_without_production
, MAX(production_water_m3)     AS production_water_m3
FROM pregroup
GROUP BY 1;
 pipeline_id |      locations      | days_without_production | production_water_m3 
------------- --------------------- ------------------------- ---------------------
           1 | location1,location2 |                      15 |                 2.3
           2 | location1           |                      20 |                 1.8