聚合視窗函式和外連接

我試圖以面向性能的方式解決以下問題。我當前的實作涉及丑陋的回圈并且非常緩慢。

具體來說，我有一個表（交易）每個客戶的各種專案的時間戳訂單：

此外，我還有第二張表（估價）顯示這些物品的價格：

現在，我想跟蹤根據估值表中的時間戳采樣的每個客戶的股票（累計數量）的價值（價格*股票）：

從本質上講，這將是某種形式的（正確的）聯合交易和估值。但是，這里的問題是我必須對每個（客戶、專案）組合進行一次正確的連接。換句話說，對于每個（客戶、專案），我都必須加入完整的時間戳集。

我當前（可能效率很低）的解決方案在客戶之間回圈。對于每個客戶，它會創建來自交易表的累積交易量、右連接估值和前向填充（使用最后一個函式）列：

CREATE OR REPLACE FUNCTION public.last_func(anyelement, anyelement)
 RETURNS anyelement
 LANGUAGE sql
 IMMUTABLE STRICT
AS $function$
select $2;
$function$
;

   create or replace function last_func(anyelement, anyelement)
returns anyelement language sql immutable strict
as $$
    select $2;
$$;
    
select 
    valuations.timestamp,
    last(t.customer) over (partition by valuations.item order by valuations.timestamp) as customer,
    valuations.item,
    last(t.stock) over (partition by valuations.item order by valuations.timestamp) as stock,
    last(t.stock) over (partition by valuations.item order by valuations.timestamp) * valuations.price as value
from (select 
    timestamp,
    customer,
    item,
    volume as order_volume,
    sum(volume) over (partition by item order by item, timestamp) as stock
from 
    transactions
where customer = 'Joe') t
right join 
    valuations on t.timestamp = valuations.timestamp and t.item = valuations.item

這似乎相當低效，并且對于大量客戶來說變得非常緩慢。有誰知道如何一次性做到這一點？如果你能在這里幫助我就好了。

提前致謝并致以最誠摯的問候

uj5u.com熱心網友回復：

只是一個建議，因為我無法在大量資料上進行測驗。

但是，如果您使用包含客戶和驗證的所有預期組合的臨時表會怎樣。

然后留給客戶來計算滾動總和。

例如：

create temporary table tmp_customer_valuations (
 timestamp int not null, 
 item varchar(30) not null, 
 customer varchar(30) not null, 
 price decimal(10,1) not null
);

insert into tmp_customer_valuations
(timestamp, item, price, customer)
select v.timestamp, v.item, v.price, c.customer
from valuations v
join (
  select item, customer, min(timestamp) as min_timestamp
  from transactions
  group by item, customer 
) c
  on c.item = v.item
 and c.min_timestamp <= v.timestamp

create index idx_tmp_customer_valuations
on tmp_customer_valuations (timestamp, item)

select 
  tmp.timestamp
, tmp.customer
, tmp.item
--, tr.volume as order_volume,
, sum(coalesce(tr.volume, 0)) 
     over (partition by tmp.item, tmp.customer 
           order by tmp.timestamp) as stock
, tmp.price * sum(coalesce(tr.volume, 0)) 
     over (partition by tmp.item, tmp.customer 
           order by tmp.timestamp) as value
from tmp_customer_valuations tmp
left join transactions tr
  on tr.timestamp = tmp.timestamp 
 and tr.item = tmp.item
 and tr.customer = tmp.customer
order by
 tmp.customer desc,
 tmp.item,
 tmp.timestamp;

db<>在這里擺弄

（順便說一句，還要驗證表是否可以使用額外的索引）

uj5u.com熱心網友回復：

看起來是橫向連接的好例子。這并不假設時間戳將相同。我猜測一般情況下估值之間可能沒有甚至多次交易。（我什至不確定您是否需要外部聯接。）

select v.*, stock * price
from valuations v left join lateral (
    select distinct on (customer) customer,
        sum(volume) over (partition by customer, item order by timestamp) as stock
    from transactions t
    where t.item = v.item and t.timestamp <= v.timestamp
    order by customer, timestamp desc
) t on true
order by customer, timestamp

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=af82f52655dfc55029e430b7933cd899

標籤：sql PostgreSQL的

上一篇：transaction.atomic回滾是否遞增到pk序列

下一篇：我想在psql中做一些查詢。但我遇到了這個問題。我想稍微優化一下。有誰能幫幫我嗎？