如何優化我的事務級別執行餘額笛卡爾連接？

這是此處發現的問題的延續：

使用 GROUP BY day 生成多個執行總計

這是上一個問題的延續，其中可以找到表定義和範例數據。（非常感謝@Erwin Brandstetter 的幫助）。

所有這些都是在PostgreSQL 11.5 DB 上完成的。

我正在嘗試找出可以優化以下查詢中似乎必要的巨型笛卡爾連接的方法：

SELECT c.customer_id, d.the_day
, sum(t.tsla_amount) OVER w AS tsla_running_amount
, sum(t.goog_amount) OVER w AS goog_running_amount
FROM   (
  SELECT the_day::date
  FROM   generate_series(timestamp '2019-01-01'
                   , date_trunc('day', localtimestamp)
                   , interval '1 day') the_day
  ) d 
CROSS  JOIN (SELECT DISTINCT customer_id FROM transactions) c  -- !
LEFT   JOIN (
SELECT customer_id
   , created_at::date AS the_day
   , sum(t.amount) FILTER (WHERE stock_ticker = 'tsla') AS tsla_amount
   , sum(t.amount) FILTER (WHERE stock_ticker = 'goog') AS goog_amount
FROM   transactions t
WHERE  created_at >= timestamp '2019-01-01'
GROUP  BY customer_id, created_at::date
) t USING (customer_id, the_day)
WINDOW w AS (PARTITION BY customer_id ORDER BY the_day)
ORDER  BY customer_id, the_day;

雖然此查詢有效，但我要實現的最終目標是，我希望僅將當天完成交易的客戶包含在報告中，而不是每個客戶每天都有一個條目，然後一旦“新”客戶進行交易，他們就會被包括在內。

（目前，此查詢為每個客戶創建行，即使在過去他們從未有過交易的日子裡，預設值為 0）

CUSTOMER DDL:

CREATE TABLE customers (
customer_id varchar(255) NOT NULL,
city varchar(255) NULL,
state varchar(255) NULL,
postal_code varchar(255) NULL,
inserted_at timestamp NOT NULL,
updated_at timestamp NOT NULL,
CONSTRAINT customers_pkey PRIMARY KEY (customer_id)
);


CREATE TRANSACTION DDL:

CREATE TABLE public.transactions (
transaction_id varchar(255) NOT NULL,
amount float8 NOT NULL,
stock_ticker varchar(255) NOT NULL,
transaction_type varchar(255) NOT NULL,
customer_id varchar NOT NULL,
inserted_at timestamp NOT NULL,
created_at timestamp NOT NULL,
CONSTRAINT transactions_pkey PRIMARY KEY (transaction_id)
);

範例：

老客戶從 01-01-20 向前進行股票交易

新客戶在 01-03-20 進行第一次股票交易（購買 2 goog）

在這種情況下，為這些客戶生成的報告將每天都有一個條目，每個客戶顯示其股票交易的執行餘額：

01-01-2020 - old_123_cust_id - 5 - tsla

01-01-2020 - old_234_cust_id - 10 - goog

01-02-2020 - old_123_cust_id - 5 - tsla 01-02-2020

• old_234_cust_id - 10 - goog

01-03-2020 - old_123_cust_id - 5 - tsla

01-03-2020 - old_234_cust_id - 10 - goog

01-03-2020 - NEW_567_cust_id - 2 - goog

01-04-2020 - old_123_cust_id - 5 - tsla

01-04-2020 - old_234_cust_id - 10 - goog

01-04-2020 - NEW_567_cust_id - 2 - goog

在此範例中，NEW_567_cust_id 不會出現在報告中，直到他們的第一次交易，然後繼續前進，該客戶繼續出現在報告中，即使他們沒有進行任何其他交易。

附加資訊：

估計的交易行數：300,000

估計的客戶行數：45,000

最早的交易：01-01-2019

範圍：01-01-2019 -> Today()（當查詢執行時）

客戶第一次交易的日期永遠不會改變

很高興提供任何其他資訊！

您可以動態獲取每個客戶的第一筆交易日期。通過適當的索引，這只是痛苦的一半。正在進行的工作是在 Postgres 的下一個版本（寫作時為 13 個）中包含“索引跳過掃描”，以減少痛苦。看：

• 在每個 GROUP BY 組中選擇第一行？
• 優化 GROUP BY 查詢以檢索每個使用者的最新行

但保存這些資訊會更簡單、更便宜。似乎每個客戶的第一筆交易日期不應該在以後更改，例如生日？（並且您在更新中也確認了這一點。）讓我們稱它為**first_transaction_at**適合您目前的模式。

對於尚未進行第一次交易的客戶 ( first_transaction_at IS NULL)，您仍然可以動態檢查，或者在每次執行查詢之前嘗試更新。

或者，如果您不能或不會更改表定義，您可能會MATERIALIZED VIEW為此添加一個：跟踪first_transaction_at每個客戶。

更新後的表可能如下所示：

CREATE TABLE customers (
  customer_id varchar(255) PRIMARY KEY,
  city varchar(255) NULL,
  state varchar(255) NULL,
  postal_code varchar(255) NULL,
  first_transaction_at timestamp,   -- can be NULL !
  inserted_at timestamp NOT NULL,
  updated_at timestamp NOT NULL
);

然後你可以使用這樣的查詢：

SELECT c.customer_id, d.the_day
    , sum(t.tsla_amount) OVER w AS tsla_running_amount
    , sum(t.goog_amount) OVER w AS goog_running_amount
FROM   customers c  -- !
CROSS  JOIN LATERAL (
  SELECT the_day::date
  FROM   generate_series(first_transaction_at  -- !
                       , date_trunc('day', localtimestamp)
                       , interval '1 day') the_day
  ) d 
LEFT   JOIN (
  SELECT customer_id
       , created_at::date AS the_day
       , sum(t.amount) FILTER (WHERE stock_ticker = 'tsla') AS tsla_amount
       , sum(t.amount) FILTER (WHERE stock_ticker = 'goog') AS goog_amount
  FROM   transactions t
  WHERE  created_at >= timestamp '2020-01-01'
  GROUP  BY customer_id, created_at::date
  ) t USING (customer_id, the_day)
WINDOW w AS (PARTITION BY customer_id ORDER BY the_day)
ORDER  BY customer_id, the_day;

db<>在這裡擺弄

關鍵是您不使用每個客戶的完整天數範圍，而是在LATERAL子查詢中生成的自定義範圍。

從查詢中CROSS JOIN刪除客戶，first_transaction_at IS NULL因為子查詢中沒有生成任何行。

旁白 1：數據類型varchar(255)通常是 Postgres 中的一種誤解——對於PRIMARY KEY. 看：

• 將外鍵重構為欄位

旁白2：考慮timestamptz代替timestamp. 看：

• 在 Rails 和 PostgreSQL 中完全忽略時區

引用自：https://dba.stackexchange.com/questions/266018