有沒有更好的方法來按標識符匯總包含不同類型事件的表？

我有感測器事件的這個表定義：

                     Table "public.sen_sensor_event"
  Column    |            Type             | Collation | Nullable | Default 
-------------+-----------------------------+-----------+----------+---------
dtype       | character varying(31)       |           | not null | 
id          | bigint                      |           | not null | 
created     | timestamp without time zone |           |          | 
type        | character varying(255)      |           | not null | 
temperature | double precision            |           |          | 
sensor_id   | bigint                      |           | not null | 
updated     | timestamp without time zone |           |          | 

我正在尋找每種事件類型的每個感測器的摘要。目前我正在通過這個物化視圖來做這件事，它給出了每種類型的最近事件的創建時間戳（映射到程式碼中的列舉）和總事件計數。

CREATE MATERIALIZED VIEW public.sen_sensor_summary AS
SELECT x.belongs_to,
   x.sensor_id,
   x.name,
   y.event_count,
   y.temperature,
   y.temperature_created,
   y.human_motion_created,
   y.presence_created,
   y.heartbeat_created,
   y.startup_created
  FROM (public.sen_sensor x
    FULL JOIN ( SELECT COALESCE(z.sensor_id, a.sensor_id, b.sensor_id, c.sensor_id, d.sensor_id, e.sensor_id) AS sensor_id,
           z.event_count,
           a.temperature,
           a.created AS temperature_created,
           b.created AS human_motion_created,
           c.created AS presence_created,
           d.created AS heartbeat_created,
           e.created AS startup_created
          FROM (((((( SELECT DISTINCT ON (z_1.sensor_id) z_1.sensor_id,
                   count(z_1.sensor_id) AS event_count
                  FROM public.sen_sensor_event z_1
                 GROUP BY z_1.sensor_id
                 ORDER BY z_1.sensor_id) z
            FULL JOIN ( SELECT DISTINCT ON (a_1.sensor_id) a_1.sensor_id,
                   a_1.created,
                   a_1.temperature
                  FROM public.sen_sensor_event a_1
                 WHERE ((a_1.type)::text = 'TEMPERATURE'::text)
                 ORDER BY a_1.sensor_id, a_1.created DESC) a ON ((z.sensor_id = a.sensor_id)))
            FULL JOIN ( SELECT DISTINCT ON (b_1.sensor_id) b_1.sensor_id,
                   b_1.created
                  FROM public.sen_sensor_event b_1
                 WHERE ((b_1.type)::text = 'HUMAN_MOTION'::text)
                 ORDER BY b_1.sensor_id, b_1.created DESC) b ON ((z.sensor_id = b.sensor_id)))
            FULL JOIN ( SELECT DISTINCT ON (c_1.sensor_id) c_1.sensor_id,
                   c_1.created
                  FROM public.sen_sensor_event c_1
                 WHERE ((c_1.type)::text = 'PRESENCE'::text)
                 ORDER BY c_1.sensor_id, c_1.created DESC) c ON ((z.sensor_id = c.sensor_id)))
            FULL JOIN ( SELECT DISTINCT ON (d_1.sensor_id) d_1.sensor_id,
                   d_1.created
                  FROM public.sen_sensor_event d_1
                 WHERE ((d_1.type)::text = 'HEARTBEAT'::text)
                 ORDER BY d_1.sensor_id, d_1.created DESC) d ON ((z.sensor_id = d.sensor_id)))
            FULL JOIN ( SELECT DISTINCT ON (e_1.sensor_id) e_1.sensor_id,
                   e_1.created
                  FROM public.sen_sensor_event e_1
                 WHERE ((e_1.type)::text = 'STARTUP'::text)
                 ORDER BY e_1.sensor_id, e_1.created DESC) e ON ((z.sensor_id = e.sensor_id)))
         ORDER BY COALESCE(z.sensor_id, a.sensor_id, b.sensor_id, c.sensor_id, d.sensor_id, e.sensor_id)) y ON ((x.id = y.sensor_id)))
 ORDER BY x.name
 WITH NO DATA;

我喜歡結果視圖，我通過 Hibernate 將其映射到 Java 中的 SensorSummary 對象。但是，對於我添加的每個新事件類型，我需要在視圖定義中添加一個新的 FULL JOIN 部分。

是否有更動態的方式來定義物化視圖，以便在出現新類型值時自動將其添加到視圖中？

當引入新類型時，您沒有辦法不必觸及查詢，因為在實際執行查詢之前，數據庫必須知道查詢的列的數量和類型（它們在解析時進行評估） ）。

但是您可以通過使用過濾聚合來簡化查詢，以在輸出中獲取不同的創建列。sen_sensor在製作駕駛表時，您也不需要完全連接。在這種情況下，左連接就足夠了（假設偶數表中沒有感測器表中不存在的感測器）。

SELECT x.belongs_to,
      x.sensor_id,
      x.name,
      y.event_count,
      y.temperature[1] as temperature,
      y.temperature_created,
      y.human_motion_created,
      y.presence_created,
      y.heartbeat_created,
      y.startup_created
FROM sen_sensor x
 left join (
     select sensor_id, 
            count(*) as event_count,
            array_agg(temperature order by created desc) filter (where type = 'TEMPERATURE') temperature,
            max(created) filter (where type = 'TEMPERATURE') as temperature_created,
            max(created) filter (where type = 'HUMAN_MOTION') as human_motion_created,
            max(created) filter (where type = 'PRESENCE') as presence_created,
            max(created) filter (where type = 'HEARTBEAT') as heartbeat_created,
            max(created) filter (where type = 'STARTUP') as startup_created
     from sen_sensor_event
     group by sensor_id
 ) as y on y.sensor_id = x.sensor_id      

獲得“最新溫度”array_agg(temperature ...)是必要的，因為 Postgres 沒有“last_value”作為有序集聚合函式（僅作為視窗函式）。將這些值聚合到一個排序的數組中，然後選擇第一個值基本上是相同的目的。

添加新類型時，您只需添加一個新max(...) filter (...)表達式，仍然需要手動更改，但要輸入的內容要少得多。事實上，您可以有一個函式（或過程），根據可用類型動態生成必要的 SQL。

這也應該比您的解決方案快得多。

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