ClickHouse MV 無法按我的需要完美執行

我是 ClickHouse 的新手，對 MV 有疑問。我有一個記錄表，它是數據源。我在這裡插入所有數據。然後使用 mv_adv_general_report 物化視圖創建了另一個名為 adv_general_report 的表。

這是我的架構。

記錄表數據。

奇怪的是，在將數據插入記錄表之後，展示次數的總和完美地添加到了 adv_general_report 和 mv_adv_general_report 物化視圖中，但瀏覽次數和點擊次數始終顯示為零。

您可以看到執行此查詢。顯示觀看量

SELECT sum(views) as views from records;

但是如果你執行這個

select sum(views) as views from adv_general_report;

它是 0 。用於物化視圖的選擇查詢也完美地顯示了視圖的總和。知道為什麼嗎？

• 它不需要對排序鍵中的列應用任何聚合函式*-campaign_id、adgroup_id、ads_id、MINUTE*

CREATE TABLE IF NOT EXISTS adv_general_report (
   campaign_id Int32, /* <<--- */
   adgroup_id Int32,
   ads_id Int32,
   MINUTE Int32,

   TIME SimpleAggregateFunction(any,DateTime) DEFAULT now(),
   /* .. */
   advertiser_cost SimpleAggregateFunction(SUM,Float64) DEFAULT 0.00
)
engine = AggregatingMergeTree
   PARTITION BY toYYYYMM(TIME)
   ORDER BY (MINUTE,ads_id,campaign_id,adgroup_id)
   PRIMARY KEY (MINUTE,ads_id,campaign_id,adgroup_id)
   SETTINGS index_granularity = 8192;

CREATE MATERIALIZED VIEW mv_adv_general_report TO adv_general_report
   AS SELECT
         campaign_id, /* <<--- */
         adgroup_id,
         ads_id,
         MINUTE,

         any(TIME) AS TIME,
         /* .. */
         SUM(advertiser_cost) AS advertiser_cost
       FROM records
       WHERE isNotNull(records.campaign_id)
       GROUP BY campaign_id, adgroup_id, ads_id, MINUTE;

• 需要通過GROUP BY子句來選擇數據

select campaign_id, sum(views) as views 
from adv_general_report
group by campaign_id;

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