Блог

Вот в чем вопрос: можно ли просмотреть выходные данные, которые были выбраны, из предыдущей строки выполняемого SQL-запроса в Postgres?

Я знаю, что lag это существует для просмотра входных данных, «из» запроса. Я также знаю, что CTE, подзапрос или боковое соединение могут решить большинство проблем такого рода. Но я думаю, что проблема, с которой я сталкиваюсь, действительно требует взглянуть на выходные данные предыдущей строки. Почему? Поскольку выходные данные текущей строки зависят от константы из справочной таблицы, и используемое значение также просматривается, эта константа представляет собой совокупность всех предыдущих строк. И если этот поиск возвращает неправильную константу, все последующие строки будут все больше отклоняться от ожидаемого значения.

Весь остальной текст представляет собой упрощенный пример, основанный на проблеме, с которой я столкнулся. Должна быть возможность ввести его в PostgreSQL 12 и выше и поиграть. Мне ужасно жаль, что это так сложно, как есть, но я думаю, что это самое простое, что я могу сделать, сохраняя при этом основную проблему: поиск в таблице поиска на основе совокупности из всех предыдущих строк, а также тот факт, что отслеживаемый «инвентарь» моделируетсякак серию транзакций двух дискретных типов.

Сама база данных существует для отслеживания нескольких рыбных ферм или клеток, полных рыбы. Рыба может быть перемещена / передана между этими фермами, и фермы кормятся примерно ежедневно. Почему бы просто не перенести агрегат в виде поля в таблицу? Потому что должна быть возможность отключить таблицу поиска после окончания сезона, чтобы настроить ее так, чтобы она лучше соответствовала реальности.

 -- A listing of all groups of fish ever grown.
create table farms (
    id             bigserial primary key,
    start          timestamp not null,
    stop           timestamp
);

insert into farms
    (id, start)
values (
    1, '2021-02-01T13:37'
);

-- A transfer of fish from one odling to another.
-- If the source is null the fish is transferred from another fishery outside our system.
-- If the destination is null the fish is being slaughtered, removed from the system.
create table transfers (
    source           bigint    references farms(id),
    destination      bigint    references farms(id),
    timestamp        timestamp not null default current_timestamp,
    total_weight_g   bigint    not null constraint positive_nonzero_total_weight_g check (total_weight_g > 0),
    average_weight_g bigint    not null constraint positive_nonzero_average_weight_g check (average_weight_g > 0),
    number_fish      bigint    generated always as (total_weight_g / average_weight_g) stored
);

insert into transfers
    (source, destination, timestamp, total_weight_g, average_weight_g)
values
    (null, 1, '2021-02-01T16:38',  5,   5),
    (null, 1, '2021-02-15T16:38',  500, 500);


-- Transactions of fish feed into a farm.
create table feedings (
    id             bigserial primary key,
    growth_table   bigint    not null,
    farm           bigint    not null references farms(id),
    amount_g       bigint    not null constraint positive_nonzero_amunt_g check (amount_g > 0),
    timestamp      timestamp not null
);

insert into feedings
    (farm, growth_table, amount_g, timestamp)
values
    (1, 1, 1,  '2021-02-02T13:37'),
    (1, 1, 1,  '2021-02-03T13:37'),
    (1, 1, 1,  '2021-02-04T13:37'),
    (1, 1, 1,  '2021-02-05T13:37'),
    (1, 1, 1,  '2021-02-06T13:37'),
    (1, 1, 1,  '2021-02-07T13:37');


create view combined_feed_and_transfer_history as
with transfer_history as (
  select timestamp, destination as farm, total_weight_g, average_weight_g, number_fish
    from transfers as deposits
   where deposits.destination = 1 -- TODO: This view only works for one farm, fix that.
  union all
  select timestamp, source as farm, -total_weight_g, -average_weight_g, -number_fish
    from transfers as withdrawals
   where withdrawals.source = 1
)
select timestamp, farm, total_weight_g, number_fish, average_weight_g, null as growth_table
from transfer_history
union all
select timestamp, farm, amount_g, 0 as number_fish, 0 as average_weight_g, growth_table
from feedings
order by timestamp;


-- Conversion tables from feed to gained weight.
create table growth_coefficients (
    growth_table                   bigserial        not null,
    average_weight_g               bigint           not null constraint positive_nonzero_weight                 check (average_weight_g     >  0),
    feed_conversion_rate           double precision not null constraint positive_foderkonverteringsfaktor       check (feed_conversion_rate >= 0),
    primary key(growth_table, average_weight_g)
);

insert into growth_coefficients
    (average_weight_g, feed_conversion_rate, growth_table)
values
    (5.00,0.10,1),
    (10.00,10.00,1),
    (20.00,1.30,1),
    (50.00,1.31,1),
    (100.00,1.32,1),
    (300.00,1.36,1),
    (600.00,1.42,1),
    (1000.00,1.50,1),
    (1500.00,1.60,1),
    (2000.00,1.70,1),
    (2500.00,1.80,1),
    (3000.00,1.90,1),
    (4000.00,2.10,1),
    (5000.00,2.30,1);


-- My current solution is a bad one. It does a CTE that sums over all events but does not account
-- for the feed conversion rate. That means that the average weight used too look up the feed
-- conversion rate will diverge more and more from reality the further into the season time goes.
-- This is why it is important to look at the output, the average weight, of the previous row.

-- We start by summing up all the transfer and feed events to get a rough average_weight_g.
with estimate as (
    select
        timestamp,
        farm,
        total_weight_g as transaction_size_g,
        growth_table,
        sum(total_weight_g) over (order by timestamp)                                              as sum_weight_g,
        sum(number_fish)    over (order by timestamp)                                              as sum_number_fish,
        sum(total_weight_g) over (order by timestamp) / sum(number_fish) over (order by timestamp) as average_weight_g
    from
        combined_feed_and_transfer_history
)
select
    timestamp,
    sum_number_fish,
    transaction_size_g as trans_g,
    sum_weight_g,
    closest_lookup_table_weight.average_weight_g as lookup_g,
    converted_weight_g as conv_g,
    sum(converted_weight_g) over (order by timestamp)                   as sum_conv_g,
    sum(converted_weight_g) over (order by timestamp) / sum_number_fish as sum_average_g
from
    estimate
    join lateral ( -- We then use this estimated_average_weight to look up the closest constant in the growth coefficient table.
      (select gc.average_weight_g - estimate.average_weight_g as diff, gc.average_weight_g from growth_coefficients gc where gc.average_weight_g >= estimate.average_weight_g order by gc.average_weight_g asc  limit 1)
        union all
        (select estimate.average_weight_g - gc.average_weight_g as diff, gc.average_weight_g from growth_coefficients gc where gc.average_weight_g <= estimate.average_weight_g order by gc.average_weight_g desc limit 1)
        order by diff
        limit 1
    ) as closest_lookup_table_weight
    on true
    join lateral ( -- If the historical event is a feeding we need to lookup the feed conversion rate.
        select case when growth_table is null then 1
                  else (select feed_conversion_rate
                        from   growth_coefficients gc
                        where  gc.growth_table     = growth_table
                        and    gc.average_weight_g = closest_lookup_table_weight.average_weight_g)
             end
    ) as growth_coefficient
    on true
    join lateral (
        select feed_conversion_rate * transaction_size_g as converted_weight_g
    ) as converted_weight_g
    on true;
 

В самом низу находится мое текущее «решение». С приведенными выше примерами данных sum_conv_g должно быть 5.6, но из-за того, что агрегат используется в качестве подстановки, не учитывающей коэффициент конверсии sum_conv_g , вместо этого получается 45.2.

Одна из моих идей заключалась в том, что, возможно, что-то вроде локальных переменных запроса, которые можно было бы использовать для хранения sum_average_g между строками? Всегда есть выходная дверь, чтобы просто запросить транзакции на моем общем языке программирования Clojure и решить ее там, но было бы здорово, если бы это можно было решить полностью в базе данных.

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