oracle - Oracle查询从多个表中获取计数并并排显示

Question

我正在尝试编写一个查询以从 11 个不同的表中获取设备计数，即 11 个表中将有 11 个单独的计数。我需要根据键列加入它们，并根据键列并排显示它们。设备是关键列。所以输出应该是设备 A，后跟来自 11 个不同表的 11 个不同计数。

Device A Count 1 count 2 count 3 .....
Device B Count 1 Count 2 Count 3....
Device C Count 1 Count 2 Count 3...

所以这是我到目前为止所做的。我创建了一个合并所有 11 个表的视图。并使用它作为源表来导出我的计数。我在 select 子句中编写了子查询。它可以工作，但问题是它非常慢，因为数据量很大，所以需要很长时间。我确信这不是最好的方法，但我的想法已经不多了......想知道是否有一种有效且更简单的方法来解决这个问题。

 select
B.MODEL_ID,
B.REC_CREATE_DT_KEY,
B.DEVICE_ID,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'STAGE_CTDI' and test_result = 'PASSED' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) stg_pass_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'STAGE_CTDI' and test_result = 'FAILED' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) stg_fail_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'TEST_CTDI' and test_result = 'PASSED' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) test_pass_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'TEST_CTDI' and test_result = 'FAILED' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) test_fail_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REFURB_CTDI' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) refurb_total_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REFURB_CTDI' and TEST_RESULT in ('FS001','FS005')
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) refurb_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REFURB_CTDI' and TEST_RESULT in ('FS001','FS005')
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) refurb_wlbl_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REFURB_CTDI' and TEST_RESULT = 'FS003'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) refurb_ber_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REPAIR_CTDI'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) repair_sent_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REPAIR_CTDI' and TEST_RESULT = 'FS002'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) repaired_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REPAIR_CTDI' and TEST_RESULT = 'FS001'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) repair_ntf_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REPAIR_CTDI' and TEST_RESULT = 'FS003'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) repair_ber_count
from V_CPE_BAU_METRICS_SRC b
group by
B.MODEL_ID,
B.REC_CREATE_DT_KEY,
B.DEVICE_ID;

标签： oracleperformance

Answer 1

使用公用表表达式消除子查询：

WITH cteCounts AS (SELECT MODEL_ID,
                          NVL(REC_CREATE_DT_KEY, 2) AS REC_CREATE_DT_KEY,
                          NVL(DEVICE_ID, 1) AS DEVICE_ID,
                          FILE_TYPE,
                          TEST_RESULT,
                          COUNT(DISTINCT(SERIAL_NBR)) AS COUNT_DISTINCT_SERIAL_NBR
                     FROM V_CPE_BAU_METRICS_SRC
                     GROUP BY MODEL_ID,
                              NVL(REC_CREATE_DT_KEY, 2),
                              NVL(DEVICE_ID, 1),
                              FILE_TYPE,
                              TEST_RESULT)
SELECT b.MODEL_ID,
       b.REC_CREATE_DT_KEY,
       b.DEVICE_ID,
       SUM(CASE
             WHEN c.FILE_TYPE = 'STAGE_CTDI' AND c.TEST_RESULT = 'PASSED' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS STG_PASS_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'STAGE_CTDI' AND c.TEST_RESULT = 'FAILED' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS STG_FAIL_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'TEST_CTDI' AND c.TEST_RESULT = 'PASSED' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS TEST_PASS_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'TEST_CTDI' AND c.TEST_RESULT = 'FAILED' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS TEST_FAIL_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REFURB_CTDI' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REFURB_TOTAL_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REFURB_CTDI' AND c.TEST_RESULT IN ('FS001','FS005') THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REFURB_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REFURB_CTDI' AND c.TEST_RESULT IN ('FS001','FS005') THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REFURB_WLBL_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REFURB_CTDI' AND c.TEST_RESULT = 'FS003' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REFURB_BER_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REPAIR_CTDI' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REPAIR_SENT_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REPAIR_CTDI' AND c.TEST_RESULT = 'FS002' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REPAIRED_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REPAIR_CTDI' AND c.TEST_RESULT = 'FS001' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REPAIR_NTF_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REPAIR_CTDI' AND c.TEST_RESULT = 'FS003' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REPAIR_BER_COUNT
  FROM V_CPE_BAU_METRICS_SRC b
  INNER JOIN cteCounts c
    ON c.MODEL_ID = b.MODEL_ID AND
       c.REC_CREATE_DT_KEY = NVL(b.REC_CREATE_DT_KEY, 2) AND
       c.DEVICE_ID = NVL(b.DEVICE_ID, 1)
  GROUP BY b.MODEL_ID,
           b.REC_CREATE_DT_KEY,
           b.DEVICE_ID