甲骨文-从WebFocus到

Question

我有一个用WebFocus创建的ETL，并将其迁移到Oracle。

我想要一种替代以下情况的办法。在WebFocus中，他可以使用以前计算的列作为参数来计算另一个值。

示例：

SUM(CASE
   WHEN T1.M2143831 > T1.M2460254
     THEN
       (T1.M2143831 * 100 / T1.M2143833)
     ELSE
       (T1.M2460254 * 100 / T1.M2460256)
END) AS TAXA_L,
SUM(CASE
   WHEN T4.FABR_ID = 'TEST'
        AND TAX_L >= 80
     THEN  1
   ELSE NULL
END) OVER80

例如，它使用在前一步中计算的TAX_L作为第二种情况的参数，而Oracle不能这样做。我试了如下：

SUM(CASE
      WHEN T4.FABR_ID = 'TEST'
         AND SUM(CASE
                    WHEN T1.M2143831 > T1.M2460254
                    THEN
                        (T1.M2143831 * 100 / T1.M2143833)
                    ELSE
                        (T1.M2460254 * 100 / T1.M2460256)
                 END) >= 80
      THEN
         1
      ELSE
         NULL
END)AS OVER80

但我收到了以下错误：

ORA-00937:非单组群函数

除了这个案子还有别的选择吗？

-原始查询/ WebFocus的一部分

  SELECT   T4.FABR_ID,
           T3.REPORT_DATE_TIME,
           T3.DAY_OF_WEEK,
           T4.REGIONAL_ID,
           T4.UF_ID,
           T4.BSC_ID,
           T2.COD_IBGE,
           T4.BTS_ID,
           SUM (T1.M2251827) AS CAP_ALLOC,
           SUM(CASE
                  WHEN T1.M2143831 > T1.M2460254
                  THEN
                     (T1.M2143831 * 100 / T1.M2143833)
                  ELSE
                     (T1.M2460254 * 100 / T1.M2460256)
               END)
              AS TAX_L,
           SUM (100 * T1.M2489212 / T1.M2489213) AS IUB_FRAME_LOST,
           SUM (T1.M1979632 * T1.M1979630) AS NUM_ACE,
           SUM (T1.M1977785 * T1.M1973189) AS DEN_ACE,
           SUM (T1.M2225994) AS THROUGHPUT_IUB_DOWNLINK_E,
           SUM(  (T1.M2016166 + T1.M2016172 + T1.M1978319 + T1.M1978331)
               * 8
               / 3600)
              AS THROUGHPUT_DADOS_TOTAL_H,
           SUM(CASE
                  WHEN T4.FABR_ID = 'TEST'
                       AND TAX_L >= 80
                  THEN
                     1
                  ELSE
                     NULL
               END)
              AS OVER80,
           SUM(CASE
                  WHEN     T4.FABR_ID = 'TEST'
                       AND TAX_L < 80
                       AND TAX_L IS NOT NULL
                  THEN
                     1
                  ELSE
                     NULL
               END)
              AS OVER80
    FROM   DBN1.F_BTS T1,
           DBN1.DA_CADASTRO T2,
           DBN1.D_TIME T3,
           DBN1.D_ENTITY_BTS T4
   WHERE       (T2.AA_CADASTRO_KEY = T1.AA_CADASTRO_KEY)
           AND (T3.TIME_KEY = T1.TIME_KEY)
           AND (T4.ENTITY_KEY = T1.ENTITY_KEY)
           AND (T2.COD_IBGE <> -1)
           AND (T3.HOUR BETWEEN 08 AND 23)
           AND (T3.REPORT_DATE_TIME BETWEEN TO_DATE ('27-09-2016 08:00:00',
                                                     'DD-MM-YYYY HH24:MI:SS')
                                        AND  TO_DATE ('27-09-2016 23:59:59',
                                                      'DD-MM-YYYY HH24:MI:SS'))
           AND (T4.FABR_ID IN ('TEST1', 'TEST2'))
           AND (T4.BTS_ID NOT IN ('', '*, -1'))
GROUP BY   T4.FABR_ID,
           T3.REPORT_DATE_TIME,
           T3.DAY_OF_WEEK,
           T4.REGIONAL_ID,
           T4.UF_ID,
           T4.BSC_ID,
           T2.COD_IBGE,
           T4.BTS_ID
ORDER BY   T4.FABR_ID,
           T3.REPORT_DATE_TIME,
           T3.DAY_OF_WEEK,
           T4.REGIONAL_ID,
           T4.UF_ID,
           T4.BSC_ID,
           T2.COD_IBGE,
           T4.BTS_ID;

Answer 1

我完全不熟悉上面列出的构造，但无论如何我还是要尝试一下，因为您可以验证从一个系统到另一个系统的结果。我的猜测(只是猜测)是，您可以通过使用CTE来获得第一个和(TAX_L)，然后在主查询中使用该值来获得over80/以下的值，从而达到这个结果：

with yoda as (
  SELECT
    T4.FABR_ID, T3.REPORT_DATE_TIME, T3.DAY_OF_WEEK, T4.REGIONAL_ID,
    T4.UF_ID, T4.BSC_ID, T2.COD_IBGE, T4.BTS_ID,
    SUM (T1.M2251827) AS CAP_ALLOC,
    SUM(CASE
          WHEN T1.M2143831 > T1.M2460254
            THEN (T1.M2143831 * 100 / T1.M2143833)
          ELSE (T1.M2460254 * 100 / T1.M2460256)
        END) AS TAX_L,
    SUM (100 * T1.M2489212 / T1.M2489213) AS IUB_FRAME_LOST,
    SUM (T1.M1979632 * T1.M1979630) AS NUM_ACE,
    SUM (T1.M1977785 * T1.M1973189) AS DEN_ACE,
    SUM (T1.M2225994) AS THROUGHPUT_IUB_DOWNLINK_E,
    SUM ((T1.M2016166 + T1.M2016172 + T1.M1978319 + T1.M1978331)
          * 8 / 3600) AS THROUGHPUT_DADOS_TOTAL_H
  FROM
    DBN1.F_BTS T1,
    DBN1.DA_CADASTRO T2,
    DBN1.D_TIME T3,
    DBN1.D_ENTITY_BTS T4
  WHERE
    T2.AA_CADASTRO_KEY = T1.AA_CADASTRO_KEY
    AND T3.TIME_KEY = T1.TIME_KEY
    AND T4.ENTITY_KEY = T1.ENTITY_KEY
    AND T2.COD_IBGE <> -1
    AND T3.HOUR BETWEEN 08 AND 23
    AND T3.REPORT_DATE_TIME BETWEEN
      TO_DATE ('27-09-2016 08:00:00', 'DD-MM-YYYY HH24:MI:SS') and
      TO_DATE ('27-09-2016 23:59:59', 'DD-MM-YYYY HH24:MI:SS')
    AND T4.FABR_ID IN ('TEST1', 'TEST2')
    AND T4.BTS_ID NOT IN ('', '*, -1')
  GROUP BY
    T4.FABR_ID, T3.REPORT_DATE_TIME, T3.DAY_OF_WEEK, T4.REGIONAL_ID,
    T4.UF_ID, T4.BSC_ID, T2.COD_IBGE, T4.BTS_ID
)
select
  fabr_id, report_date_time, day_of_week, regional_id, uf_id, bsc_id,
  cod_ibge, bts_id, cap_alloc, tax_l, iub_frame_lost, num_ace,
  den_ace, throughput_uib_downlink_e, throughput_dados_total_h,
  SUM(CASE
        WHEN FABR_ID = 'TEST' AND TAX_L >= 80 THEN 1
        ELSE NULL
      END) AS OVER80,
  SUM(CASE
        WHEN FABR_ID = 'TEST' AND TAX_L < 80
          -- AND TAX_L IS NOT NULL  -- < 80 automatically means not null
          THEN 1
        ELSE NULL
      END) AS OVER80 -- you mean under 80?
from yoda
group by
  fabr_id, report_date_time, day_of_week, regional_id, uf_id, bsc_id,
  cod_ibge, bts_id, cap_alloc, tax_l, iub_frame_lost, num_ace,
  den_ace, throughput_uib_downlink_e, throughput_dados_total_h

一些杂项意见。

1. “on”通常没有必要，但是既然我们正在讨论这个话题，您的意思是“Other0”吗？相反?1+1+1+1+空+1=空
2. 你有两列别名为“超过80”。我以为其中一个是“在”
3. 字段> x，字段< x，字段!= x都意味着非空值。null不是> 80，< 80，也不是80 double not intended。

同样，由于我从未见过在查询的同一部分中引用聚合，所以上面的SQL是一个有根据的猜测。