SQL 中更高效的累加器？

我正在編寫一個分類帳系統，其中每筆交易都可以有多個分類。例如，如果有人以 50 美元購買了一個小元件，我可以將該交易歸類為具有“收入”帳戶和 SKU 為“SKU1”的帳戶。

然後使用者可以選擇他們希望報告的維度，我可以生成聚合。

當我的數據庫有 10M+ 事務時，以下查詢速度非常慢。 Memory limit exceeded大約 10 秒後，我在 8GB 筆記型電腦上收到錯誤消息。

因此問題是：我實際上並不關心各個行，我只關心這些值的累積。在我的測試中，我只期望聚合後返回大約 10 行。

這是一個小提琴：http ://sqlfiddle.com/#!17/4a7d8/10/0

select
  year,
  sum(amount),
  t1.value as account,
  t2.value as sku
from 
   transactions 
left join
   tags t1 on transactions.id = t1.transaction_id and t1.name ='account'
left join
   tags t2 on transactions.id = t2.transaction_id and t2.name = 'sku'
group by
   year,
   t1.value,
   t2.value;

這是查詢計劃：

Expression ((Projection + Before ORDER BY))
 Aggregating
   Expression (Before GROUP BY)
     Join (JOIN)
       Expression ((Before JOIN + (Projection + Before ORDER BY)))
         Join (JOIN)
           Expression (Before JOIN)
             ReadFromMergeTree (default.transactions)
           Expression ((Joined actions + (Rename joined columns + (Projection + Before ORDER BY))))
             ReadFromMergeTree (default.tags)
       Expression ((Joined actions + (Rename joined columns + (Projection + Before ORDER BY))))
         ReadFromMergeTree (default.tags)

最後，這是架構：

CREATE TABLE default.transactions
(
   `id` Int32,
   `date` Date,
   `amount` Float32
)
ENGINE = MergeTree
PRIMARY KEY id
ORDER BY id
SETTINGS index_granularity = 8192

CREATE TABLE default.tags
(
   `transaction_id` Int32,
   `name` String,
   `value` String,
   INDEX idx_tag_value value TYPE set(0) GRANULARITY 4,
   INDEX idx_tag_name name TYPE set(0) GRANULARITY 4
)
ENGINE = MergeTree
PRIMARY KEY (transaction_id, name)
ORDER BY (transaction_id, name)
SETTINGS index_granularity = 8192

我的問題是：

• 我可能會使用不同的架構或不同的 Clickhouse 功能集嗎？
• 我應該改為預先計算聚合嗎？
• 是否有不同的數據庫可以更有效地執行這種計算？

Clickhouse 是出了名的記憶體消耗，並且不適合在只有 8 Gi 記憶體的單個節點上執行。我建議您選擇更適合您的“基礎設施”的東西。例如，Postgres 聚合 1000 萬行不會有任何問題。

此外，您的模型看起來很像 EAV，這對於分析查詢來說是一件非常糟糕的事情。如果您堅持使用 Clickhouse，請將您的模型非規範化。

我找到了一個適用於 Clickhouse 的解決方案。transactions與其擁有兩個表（和），不如為具有Map類型tags的標籤創建一列。

新結構如下所示：

CREATE TABLE default.transactions
(
   `id` Int32,
   `date` Date,
   `amount` Float32,
   `tags` Map(String, String),
)
ENGINE = MergeTree
PRIMARY KEY id

從那裡，您可以查詢：

SELECT
 year,
 SUM(amount),
 tags['account'],
 tags['sku']
FROM
 transactions
GROUP BY
 year,
 tags['account'],
 tags['sku']

當我對一個有 280M 行的表執行它時，它能夠在 7 秒內返回。

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