Oracle 19c下Event表高效分组统计查询需求咨询

环境与需求说明 #

• 数据库：Oracle 19c
• 数据规模：EVENT表超200万行
• 核心需求：按CUSTOMER_ID和CONF_ID分组，统计每个ITEM_ID最新状态的事件数量，最终输出各状态的列化统计结果。

表结构 #

CREATE TABLE EVENT
(    
    "ID" NUMBER(19,0) NOT NULL ENABLE, 
    "CREATED" TIMESTAMP (6) NOT NULL, 
    "CUSTOMER_ID" VARCHAR2(255 CHAR) NOT NULL, 
    "CONF_ID" VARCHAR2(255 CHAR), 
    "STATE" VARCHAR2(255 CHAR) NOT NULL,
    "ITEM_ID" VARCHAR2(255 CHAR) NOT NULL
    -- 其他字段省略
);

CREATE TABLE ITEM
(   
    "ID" NUMBER(19,0) NOT NULL ENABLE,
    "NAME" VARCHAR2(255 CHAR) NOT NULL
    -- 其他字段省略
    PRIMARY KEY (ID)
);

ALTER TABLE EVENT
ADD CONSTRAINT EVENT_FK_ITEM_BID
    FOREIGN KEY (ITEM_ID) REFERENCES ITEM;

高效查询实现思路 #

1. 定位每个Item的最新事件：利用窗口函数ROW_NUMBER()按ITEM_ID分组，以CREATED时间倒序排序，标记每个Item的最新事件（排序值=1的记录）。
2. 透视统计结果：将最新事件的STATE转换为列，按CUSTOMER_ID和CONF_ID分组统计各状态的数量。
3. 索引优化：为EVENT表创建复合索引，覆盖分组、排序和筛选所需字段，避免全表扫描。

索引建议 #

创建以下复合索引，大幅提升查询效率：

CREATE INDEX IDX_EVENT_CUST_CONF_ITEM_CREATED ON EVENT (CUSTOMER_ID, CONF_ID, ITEM_ID, CREATED DESC, STATE);

完整查询语句 #

SELECT
    CUSTOMER_ID,
    CONF_ID,
    COUNT(CASE WHEN STATE = 'ACTIVATED' THEN 1 END) AS ACTIVATED,
    COUNT(CASE WHEN STATE = 'DEACTIVATED' THEN 1 END) AS DEACTIVATED,
    COUNT(CASE WHEN STATE = 'SUSPENDED' THEN 1 END) AS SUSPENDED
FROM (
    SELECT
        e.CUSTOMER_ID,
        e.CONF_ID,
        e.STATE,
        -- 标记每个ITEM_ID的最新事件
        ROW_NUMBER() OVER (PARTITION BY e.ITEM_ID ORDER BY e.CREATED DESC) AS rn
    FROM EVENT e
    -- 若需关联ITEM表过滤有效Item，可添加JOIN，否则可省略
    -- JOIN ITEM i ON e.ITEM_ID = i.ID
) latest_events
WHERE rn = 1 -- 仅保留每个Item的最新事件
GROUP BY CUSTOMER_ID, CONF_ID
ORDER BY CUSTOMER_ID, CONF_ID;

语句说明 #

• 子查询latest_events：通过ROW_NUMBER()窗口函数筛选出每个ITEM_ID的最新事件，确保只统计每个Item的当前状态。
• 外层查询：使用CASE表达式做列透视，按CUSTOMER_ID和CONF_ID分组统计各状态的数量，输出符合需求的报表格式。
• 若需仅统计存在于ITEM表的有效Item事件，可取消注释子查询中的JOIN ITEM语句。

结果示例 #

CUSTOMER_ID CONF_ID ACTIVATED DEACTIVATED SUSPENDED
----------  ------- --------- ----------- ---------
1           2      50000     20000       5000
1           1      70000     30000       2000
2           1      80000     10000       10000
2           2      50000     20000       5000

内容的提问来源于stack exchange，提问作者user3027786