Как отфильтровать упорядоченную таблицу до первой строки, в которой достигнуто пороговое значение?

Я использую PostgreSQL, и у меня есть table со следующими столбцами: id, distance и length. Я хочу упорядочить таблицу по distance и создать новый столбец с именем cum_length, используя оконную функцию. Я также хочу отфильтровать строки, чтобы в окончательный результат включались только строки до тех пор, пока значение cum_length не превысит определенный порог.

Пример ввода table:

идентификатор расстояние длина 1 10 1 2 5 2 3 8 1 4 1 3 5 3 2 6 9 2

Желаемый результат для порога 6:

идентификатор расстояние длина cum_length 4 1 3 3 5 3 2 5 2 5 2 7

Это SQL, который я придумал:

WITH ordered_table AS (
  SELECT id,
    distance,
    length,
    SUM(length) OVER (ORDER BY distance) AS cum_length
  FROM table)
SELECT *
FROM ordered_table
WHERE cum_length <= 6

Но это опускает последнюю строку желаемого результата.

Почему вы ожидаете, что cum_length = 7 в желаемом выводе, если порог равен 6?

a_horse_with_no_name 09.01.2023 14:04

@a_horse_with_no_name порог минимальный. Мне нужно cum_length, чтобы пересечь порог, но после этого мне нужен только первый ряд.

Pahbloo Marks 09.01.2023 14:08

Ну, а если это минимум, то не стоит ли использовать >= 6?

a_horse_with_no_name 09.01.2023 14:10

Очень сложно это четко донести... 🤔 Я хочу все ряды, пока у меня не будет хотя бы порога, не меньше.

Pahbloo Marks 09.01.2023 14:12
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
2
4
58
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Вот он, основанный на вашем запросе ordered_table и дополнительной оконной функции lag для вычисления previous_is_less порогового значения. Записи, в которых previous_is_less имеет значение true или null, могут быть выбраны.

with t as 
(
 select *, 
    lag(cum_length) over (order by cum_length) < 6 as previous_is_less
 from  
 (
  SELECT id, distance, length,
     SUM(length) OVER (order BY distance) AS cum_length
  from the_table
 ) as ordered_table
)
select id, distance, length, cum_length
from t 
where coalesce(previous_is_less, true)
order by cum_length;

DB-Fiddle демо

Ответ принят как подходящий

Попробуйте следующее:

WITH ordered_table AS 
(
  SELECT id, distance, length,
    SUM(length) OVER (ORDER BY distance) AS cum_length
  FROM table_name
)
SELECT id, distance, length, cum_length
FROM ordered_table
WHERE cum_length <= COALESCE((SELECT MIN(cum_length) FROM ordered_table WHERE cum_length > 6), 6)

Для ваших выборочных данных это эквивалентно WHERE cum_length <= 7.

Посмотреть демо

Может быть интересно сравнить с этим на большом наборе данных, только одна сортировка по расстоянию, и сканирование данных должно прекратиться, как только будет найдено пороговое значение.

with data(id, distance, length) as (
    select 1, 10, 1 FROM DUAL UNION ALL
    select 2, 5, 2 FROM DUAL UNION ALL
    select 3, 8, 1 FROM DUAL UNION ALL
    select 4, 1, 3 FROM DUAL UNION ALL
    select 5, 3, 2 FROM DUAL UNION ALL
    select 6, 9, 2 FROM DUAL -- UNION ALL
),
rdata(id, distance, length, rn) as (
    select id, distance, length, row_number() over(order by distance) as rn
    from data 
),
recdata(id, distance, length, rn, cumlength) as ( 
    select id, distance, length, rn, length
    from rdata d
    where rn = 1
    
    union all
    
    select d.id, d.distance, d.length, d.rn, d.length + r.cumlength
    from recdata r
    join rdata d on d.rn = r.rn + 1 and r.cumlength <= 6 
)
select id, distance, length, cumlength from recdata
;

Другие вопросы по теме