Я использую PostgreSQL, и у меня есть table
со следующими столбцами: id
, distance
и length
. Я хочу упорядочить таблицу по distance
и создать новый столбец с именем cum_length
, используя оконную функцию. Я также хочу отфильтровать строки, чтобы в окончательный результат включались только строки до тех пор, пока значение cum_length
не превысит определенный порог.
Пример ввода table
:
Желаемый результат для порога 6
:
Это 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
Но это опускает последнюю строку желаемого результата.
@a_horse_with_no_name порог минимальный. Мне нужно cum_length
, чтобы пересечь порог, но после этого мне нужен только первый ряд.
Ну, а если это минимум, то не стоит ли использовать >= 6
?
Очень сложно это четко донести... 🤔 Я хочу все ряды, пока у меня не будет хотя бы порога, не меньше.
Вот он, основанный на вашем запросе 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
;
Почему вы ожидаете, что cum_length = 7 в желаемом выводе, если порог равен 6?