Oracle计数和分组依据优化报表查询

ffscu2ro 于 2022-11-28 发布在 Oracle

关注(0)|答案(1)|浏览(212)

甲骨文19c
我们需要从Event表提供一些报告数据，Event和Item表具有如下模式

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;

其中，事件根据实时发生的不同状态创建。事件绑定到具有Item_id的项目表。
我们希望实现的是选择事件状态的计数（仅考虑每个item_id的最新状态），按CUSTOMER_ID和CONF_ID分组。
事件表可以有超过200万行。
结果应如下所示

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

您能否指导我们构建一个高效的查询？

oracle

来源：https://stackoverflow.com/questions/74530932/oracle-count-and-group-by-optimized-report-query

1条答案

按热度按时间

neekobn81#

可以使用RANK分析函数（如果每个customer_id, conf_id, item_id元组只有一个最新行，则使用ROW_NUMBER分析函数）查找最新行，然后使用PIVOT：

SELECT *
FROM   (
  SELECT customer_id,
         conf_id,
         state
  FROM   (
    SELECT customer_id,
           conf_id,
           state,
           RANK() OVER (PARTITION BY customer_id, conf_id, item_id ORDER BY created DESC) AS rnk
    FROM   event
    WHERE  state IN ('ACTIVATED', 'SUSPENDED', 'DEACTIVATED')
  )
  WHERE  rnk = 1
)
PIVOT (
  COUNT(*)
  FOR state IN (
    'ACTIVATED' AS activated,
    'SUSPENDED' AS suspended,
    'DEACTIVATED' AS deactivated
  )
);

fiddle

赞(0）回复(0）举报 2022-11-28

我来回答

Oracle计数和分组依据优化报表查询

1条答案

相关问题

热门标签

最新问答