admin管理员组

文章数量:1125590

I have written a query in oracle sql as follows. But the sql query brings the result for a long time. For example, when searching for a customer, the result comes in 4 minutes. Please help me to optimize this sql query. I tried several ways to solve the problem, but I couldn't get any result. First, I ran the sql script part by part. Some parts took a lot of time. However, I did not know how to optimize those parts. As a second way, I wanted to find out what made the sql script weak to bring the result, using the v$sql_monitor table, but I could not come to a conclusion. It is interesting to me that this logic can be written in a simpler and faster way while keeping the same logic. The time given to me by my boss is over. Please help me.

WITH d_table AS (
    SELECT *
    FROM (
        SELECT *
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY customer_id, product ORDER BY min_fx_date ASC) AS rnk,
                   customer_id,
                   min_fx_date,
                   product,
                   balance_column
            FROM table_def
        )
        WHERE rnk = 1
    )
    WHERE min_fx_date > '01/01/2017'
),

tc_lo_product AS (
    SELECT a.customer_id, 
           a.currency, 
           b.product, 
           a.rep_date
    FROM table_port a
    LEFT JOIN table_product_names b
        ON a.ln_aim = b.product_name
),

if_lo AS (
    SELECT DISTINCT b.customer_id,
                    total_amount,
                    a.i_date,
                    x.product,
                    a.document_n,
                    a.pro,
                    b.close_date
    FROM table_full a
    LEFT JOIN (
        SELECT customer_id, 
               rep_date,
               FIRST_VALUE(close_date) OVER (PARTITION BY document_n ORDER BY rep_date DESC) AS close_date,
               currency,
               document_n
        FROM table_port
    ) b
        ON a.document_n = b.document_n
        AND a.i_date = LAST_DAY(b.rep_date)
    LEFT JOIN table_product_names x
        ON a.ln_aim = x.product_name
),

part3 AS (
    SELECT DISTINCT k.customer_id,
                    k.currency,
                    FIRST_VALUE(k.currency) OVER (PARTITION BY k.customer_id, k.product ORDER BY t.min_fx_date) AS main_currency,
                    t.min_fx_date,
                    k.product,
                    t.balance_column
    FROM d_table t
    LEFT JOIN tc_lo_product k
        ON t.customer_id = k.customer_id
        AND t.product = k.product
        AND k.rep_date = t.min_fx_date
),

kurs_cedvel AS (
    SELECT *
    FROM (
        SELECT c1.cur_short, 
               c1.k_v, 
               LAST_DAY(c1.p_date) AS rep_date, 
               ROW_NUMBER() OVER (PARTITION BY c1.cu_code, TO_CHAR(c1.p_date, 'MM-YYYY') ORDER BY c1.p_date DESC) AS rn
        FROM table_currency c1
        WHERE c1.kurs_type = 'CBAR'
    )
    WHERE rn = 1
),

in_rate_tab AS (
    SELECT *
    FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY customer_id, TRUNC(rep_date, 'MM') ORDER BY rep_date DESC, date_give DESC) AS rnk,
               rep_date,
               customer_id,
               interest_rate,
               date_give
        FROM table_port
        WHERE rep_date = LAST_DAY(rep_date)
          AND document_n NOT LIKE '%KOP%'
    )
    WHERE rnk = 1
),

ana_cedvel AS (
    SELECT p.*, 
           o.sum_payed,
           CASE
               WHEN main_currency = 'AZN' THEN p.total_amount
               ELSE ROUND(p.total_amount / v.k_v, 2)
           END AS total_amount_c,
           CASE
               WHEN main_currency = 'AZN' THEN p.pro
               ELSE ROUND(p.pro / v.kurs_val, 2)
           END AS pro_c,
           CASE
               WHEN main_currency = o.valyuta THEN o.sum_payed
               ELSE ROUND(o.sum_payed / v.k_v, 2)
           END AS sum_payed_c
    FROM (
        SELECT k.customer_id,
               k.currency,
               k.main_currency,
               k.min_fx_date,
               k.product,
               k.balance_column,
               c.total_amount,
               c.i_date,
               c.document_n,
               c.pro,
               c.close_date
        FROM part3 k
        LEFT JOIN if_lo c
            ON k.customer_id = c.customer_id
            AND k.product = c.product
            AND c.i_date >= k.min_fx_date
    ) p
    LEFT JOIN kurs_cedvel v
        ON p.main_currency = v.cur_short 
        AND p.i_date = v.rep_date
    LEFT JOIN payments_table o
        ON p.document_n = o.document_n
        AND o.payment_date >= p.min_fx_date
        AND p.i_date = LAST_DAY(o.payment_date)
),

final_result AS (
    SELECT DISTINCT h.i_date,
                    h.customer_id,
                    h.main_currency,
                    h.product,
                    h.min_fx_date,
                    h.balance_column,
                    h.total_amount,
                    h.pro,
                    h.sum_payed,
                    h.interest_rate,
                    CASE
                        WHEN h.max_i_date = LAST_DAY(h.close_date)
                             AND h.i_date = h.max_i_date THEN h.close_date
                        ELSE NULL
                    END AS date_end
    FROM (
        SELECT DISTINCT i_date,
                        j.customer_id,
                        main_currency,
                        MAX(i_date) OVER (PARTITION BY j.customer_id, j.product) AS max_i_date,
                        j.document_n,
                        product,
                        min_fx_date,
                        balance_column,
                        p.interest_rate,
                        close_date,
                        SUM(total_amount_c) OVER (PARTITION BY i_date, j.customer_id, product) AS total_amount,
                        SUM(sum_payed_c) OVER (PARTITION BY i_date, j.customer_id, product) AS sum_payed,
                        SUM(pro) OVER (PARTITION BY i_date, j.customer_id, product) AS pro
        FROM ana_cedvel j
        LEFT JOIN in_rate_tab p
            ON j.customer_id = p.customer_id
            AND j.i_date = p.rep_date
    ) h
    WHERE h.customer_id = 85278945789
)

I have written a query in oracle sql as follows. But the sql query brings the result for a long time. For example, when searching for a customer, the result comes in 4 minutes. Please help me to optimize this sql query. I tried several ways to solve the problem, but I couldn't get any result. First, I ran the sql script part by part. Some parts took a lot of time. However, I did not know how to optimize those parts. As a second way, I wanted to find out what made the sql script weak to bring the result, using the v$sql_monitor table, but I could not come to a conclusion. It is interesting to me that this logic can be written in a simpler and faster way while keeping the same logic. The time given to me by my boss is over. Please help me.

WITH d_table AS (
    SELECT *
    FROM (
        SELECT *
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY customer_id, product ORDER BY min_fx_date ASC) AS rnk,
                   customer_id,
                   min_fx_date,
                   product,
                   balance_column
            FROM table_def
        )
        WHERE rnk = 1
    )
    WHERE min_fx_date > '01/01/2017'
),

tc_lo_product AS (
    SELECT a.customer_id, 
           a.currency, 
           b.product, 
           a.rep_date
    FROM table_port a
    LEFT JOIN table_product_names b
        ON a.ln_aim = b.product_name
),

if_lo AS (
    SELECT DISTINCT b.customer_id,
                    total_amount,
                    a.i_date,
                    x.product,
                    a.document_n,
                    a.pro,
                    b.close_date
    FROM table_full a
    LEFT JOIN (
        SELECT customer_id, 
               rep_date,
               FIRST_VALUE(close_date) OVER (PARTITION BY document_n ORDER BY rep_date DESC) AS close_date,
               currency,
               document_n
        FROM table_port
    ) b
        ON a.document_n = b.document_n
        AND a.i_date = LAST_DAY(b.rep_date)
    LEFT JOIN table_product_names x
        ON a.ln_aim = x.product_name
),

part3 AS (
    SELECT DISTINCT k.customer_id,
                    k.currency,
                    FIRST_VALUE(k.currency) OVER (PARTITION BY k.customer_id, k.product ORDER BY t.min_fx_date) AS main_currency,
                    t.min_fx_date,
                    k.product,
                    t.balance_column
    FROM d_table t
    LEFT JOIN tc_lo_product k
        ON t.customer_id = k.customer_id
        AND t.product = k.product
        AND k.rep_date = t.min_fx_date
),

kurs_cedvel AS (
    SELECT *
    FROM (
        SELECT c1.cur_short, 
               c1.k_v, 
               LAST_DAY(c1.p_date) AS rep_date, 
               ROW_NUMBER() OVER (PARTITION BY c1.cu_code, TO_CHAR(c1.p_date, 'MM-YYYY') ORDER BY c1.p_date DESC) AS rn
        FROM table_currency c1
        WHERE c1.kurs_type = 'CBAR'
    )
    WHERE rn = 1
),

in_rate_tab AS (
    SELECT *
    FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY customer_id, TRUNC(rep_date, 'MM') ORDER BY rep_date DESC, date_give DESC) AS rnk,
               rep_date,
               customer_id,
               interest_rate,
               date_give
        FROM table_port
        WHERE rep_date = LAST_DAY(rep_date)
          AND document_n NOT LIKE '%KOP%'
    )
    WHERE rnk = 1
),

ana_cedvel AS (
    SELECT p.*, 
           o.sum_payed,
           CASE
               WHEN main_currency = 'AZN' THEN p.total_amount
               ELSE ROUND(p.total_amount / v.k_v, 2)
           END AS total_amount_c,
           CASE
               WHEN main_currency = 'AZN' THEN p.pro
               ELSE ROUND(p.pro / v.kurs_val, 2)
           END AS pro_c,
           CASE
               WHEN main_currency = o.valyuta THEN o.sum_payed
               ELSE ROUND(o.sum_payed / v.k_v, 2)
           END AS sum_payed_c
    FROM (
        SELECT k.customer_id,
               k.currency,
               k.main_currency,
               k.min_fx_date,
               k.product,
               k.balance_column,
               c.total_amount,
               c.i_date,
               c.document_n,
               c.pro,
               c.close_date
        FROM part3 k
        LEFT JOIN if_lo c
            ON k.customer_id = c.customer_id
            AND k.product = c.product
            AND c.i_date >= k.min_fx_date
    ) p
    LEFT JOIN kurs_cedvel v
        ON p.main_currency = v.cur_short 
        AND p.i_date = v.rep_date
    LEFT JOIN payments_table o
        ON p.document_n = o.document_n
        AND o.payment_date >= p.min_fx_date
        AND p.i_date = LAST_DAY(o.payment_date)
),

final_result AS (
    SELECT DISTINCT h.i_date,
                    h.customer_id,
                    h.main_currency,
                    h.product,
                    h.min_fx_date,
                    h.balance_column,
                    h.total_amount,
                    h.pro,
                    h.sum_payed,
                    h.interest_rate,
                    CASE
                        WHEN h.max_i_date = LAST_DAY(h.close_date)
                             AND h.i_date = h.max_i_date THEN h.close_date
                        ELSE NULL
                    END AS date_end
    FROM (
        SELECT DISTINCT i_date,
                        j.customer_id,
                        main_currency,
                        MAX(i_date) OVER (PARTITION BY j.customer_id, j.product) AS max_i_date,
                        j.document_n,
                        product,
                        min_fx_date,
                        balance_column,
                        p.interest_rate,
                        close_date,
                        SUM(total_amount_c) OVER (PARTITION BY i_date, j.customer_id, product) AS total_amount,
                        SUM(sum_payed_c) OVER (PARTITION BY i_date, j.customer_id, product) AS sum_payed,
                        SUM(pro) OVER (PARTITION BY i_date, j.customer_id, product) AS pro
        FROM ana_cedvel j
        LEFT JOIN in_rate_tab p
            ON j.customer_id = p.customer_id
            AND j.i_date = p.rep_date
    ) h
    WHERE h.customer_id = 85278945789
)
  • sql
  • oracle-database
  • oracle-sqldeveloper
  • sql-execution-plan
  • execution-time
Share Improve this question edited 2 days ago Abdul Alim Shakir 1,20714 silver badges29 bronze badges asked Jan 9 at 6:38 Narin Narin 196 bronze badges 6
  • 3 format your code to be readable and provide the DDLs and the explain plan. – p3consulting Commented Jan 9 at 7:14
  • Please tell us what the query is supposed to do. – Thorsten Kettner Commented yesterday
  • 1 On a side note: This: min_fx_date > '01/01/2017' looks bad. I suppose that the column is a date column. You are comparing with a string that the DBMS will try to convert into a date, too, but it will have to guess which is the month, day and year. Depending on session settings this will or will not work. Use a date literal instead: min_fx_date > DATE '2017-01-01', so as to get to a stable query. And aliases are supposed to enhance readabilty. Generic aliases like a, b, c don't. Instead use mnemonic aliases like p for the ports table and pn for the product names table for instance. – Thorsten Kettner Commented yesterday
  • SELECT DISTINCT is very, very often an indicator for an inappropriate query or even a bad database design. Ask yourself: Why are there duplicates that must be removed? Generally, a database should not contain duplicates and a query should not produce them, so what makes it necessary to remove duplicates in your query? This answer along with the amended query may already solve the performance issues. – Thorsten Kettner Commented yesterday
  • As @ThorstenKettner says, SELECT DISTINCT typically indicates a misunderstanding of the data or poor database design. You use it 4 times. Also, with 14 subqueries, I wouldn't expect the query to be fast. Some sense of row counts in each source table, granularity, indexing, etc. may help. So, basically, DDL and row counts. – dougp Commented yesterday
 |  Show 1 more comment

1 Answer 1

Reset to default 2

Reformatting your query so thta every subquery is represented as a common table expression:

WITH 
tbl_def as (
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY customer_id, product ORDER BY min_fx_date ASC) AS rnk
  , customer_id
  , min_fx_date
  , product
  , balance_column
  FROM table_def
),
tbl_defb as (
  SELECT *
  FROM tbl_def
  WHERE rnk = 1
),
d_table AS (
  SELECT *
  FROM tbl_defb
  WHERE min_fx_date > '01/01/2017'
),
tc_lo_product AS (
  SELECT 
    a.customer_id
  , a.currency
  , b.product
  , a.rep_date
  FROM        table_port a
    LEFT JOIN table_product_names b ON a.ln_aim = b.product_name
),
tbl_port as (
  SELECT 
    customer_id
  , rep_date
  , FIRST_VALUE(close_date) OVER (PARTITION BY document_n ORDER BY rep_date DESC) AS close_date
  , currency
  , document_n
  FROM table_port
),
if_lo AS (
  SELECT DISTINCT 
    b.customer_id
  , total_amount
  , a.i_date
  , x.product
  , a.document_n
  , a.pro
  , b.close_date
  FROM        table_full a
    LEFT JOIN tbl_port b ON a.document_n = b.document_n
                        AND a.i_date = LAST_DAY(b.rep_date)
    LEFT JOIN table_product_names x ON a.ln_aim = x.product_name
),
part3 AS (
  SELECT DISTINCT 
    k.customer_id
  , k.currency
  , FIRST_VALUE(k.currency) OVER (PARTITION BY k.customer_id, k.product ORDER BY t.min_fx_date) AS main_currency
  , t.min_fx_date
  , k.product
  , t.balance_column
  FROM        d_table t
    LEFT JOIN tc_lo_product k ON t.customer_id = k.customer_id
                             AND t.product = k.product
                             AND k.rep_date = t.min_fx_date
),
cbar as (
  SELECT 
    c1.cur_short
  , c1.k_v
  , LAST_DAY(c1.p_date) AS rep_date
  , ROW_NUMBER() OVER (PARTITION BY c1.cu_code, TO_CHAR(c1.p_date, 'MM-YYYY') ORDER BY c1.p_date DESC) AS rn
  FROM table_currency c1
  WHERE c1.kurs_type = 'CBAR'
),
kurs_cedvel AS (
  SELECT *
  FROM cbar
  WHERE rn = 1
),
kop as (
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY customer_id, TRUNC(rep_date, 'MM') ORDER BY rep_date DESC, date_give DESC) AS rnk
  , rep_date
  , customer_id
  , interest_rate
  , date_give
  FROM table_port
  WHERE rep_date = LAST_DAY(rep_date)
    AND document_n NOT LIKE '%KOP%'
),
in_rate_tab AS (
  SELECT *
  FROM kop
  WHERE rnk = 1
),
part3b as (
  SELECT 
    k.customer_id
  , k.currency
  , k.main_currency
  , k.min_fx_date
  , k.product
  , k.balance_column
  , c.total_amount
  , c.i_date
  , c.document_n
  , c.pro
  , c.close_date
  FROM        part3 k
    LEFT JOIN if_lo c ON k.customer_id = c.customer_id
                     AND k.product = c.product
                     AND c.i_date >= k.min_fx_date
),
ana_cedvel AS (
  SELECT 
    p.*
  , o.sum_payed
  , CASE
      WHEN main_currency = 'AZN' THEN p.total_amount
      ELSE ROUND(p.total_amount / v.k_v, 2)
    END AS total_amount_c
  , CASE
      WHEN main_currency = 'AZN' THEN p.pro
      ELSE ROUND(p.pro / v.kurs_val, 2)
    END AS pro_c
  , CASE
      WHEN main_currency = o.valyuta THEN o.sum_payed
      ELSE ROUND(o.sum_payed / v.k_v, 2)
    END AS sum_payed_c
  FROM        part3b p
    LEFT JOIN kurs_cedvel v ON p.main_currency = v.cur_short 
                           AND p.i_date = v.rep_date
    LEFT JOIN payments_table o ON p.document_n = o.document_n
                              AND o.payment_date >= p.min_fx_date
                              AND p.i_date = LAST_DAY(o.payment_date)
),
almost_there as (
  SELECT DISTINCT 
    i_date
  , j.customer_id
  , main_currency
  , MAX(i_date) OVER (PARTITION BY j.customer_id, j.product) AS max_i_date
  , j.document_n
  , product
  , min_fx_date
  , balance_column
  , p.interest_rate
  , close_date
  , SUM(total_amount_c) OVER (PARTITION BY i_date, j.customer_id, product) AS total_amount
  , SUM(sum_payed_c) OVER (PARTITION BY i_date, j.customer_id, product) AS sum_payed
  , SUM(pro) OVER (PARTITION BY i_date, j.customer_id, product) AS pro
  FROM        ana_cedvel j
    LEFT JOIN in_rate_tab p ON j.customer_id = p.customer_id
                           AND j.i_date = p.rep_date
),
final_result AS (
  SELECT DISTINCT 
    h.i_date
  , h.customer_id
  , h.main_currency
  , h.product
  , h.min_fx_date
  , h.balance_column
  , h.total_amount
  , h.pro
  , h.sum_payed
  , h.interest_rate
  , CASE
      WHEN h.max_i_date = LAST_DAY(h.close_date)
       AND h.i_date = h.max_i_date 
        THEN h.close_date
      ELSE NULL
    END AS date_end
  FROM almost_there h
  WHERE h.customer_id = 85278945789
)

select *
from final_result

The most obvious performance improvement will come from reducing the number of rows being processed at each step. Applying the customer_id filter as early as possible in the process will do that. It turns out, you can do that all the way up in the tc_lo_product query.

But there's more...

You only want one specific customer_id, so you are not interested in any customer_id that would be NULL at that point in the process. But when I track that back, customer_id filter tracks through part3 as k.customer_id. So effectively, in part3 you want is...

FROM        t
  LEFT JOIN k on t.customer_id = k.customer_id
WHERE k.customer_id

That's what I refer to as a LEFT INNER JOIN (a fictional construct). In other words, you have stated you want a LEFT OUTER JOIN (which will return NULLs from the right side if no matching row exists) but then include something from the right side in the 'WHERE' clause, forcing the join to be an INNER JOIN.

There may be more blunders in the query, but after spending maybe 10 minutes on this... The following may provide some performance improvement. But you may also want to consider other factors in your logic. For example, both table_port and table_product_names are called at least twice each. If the logic is as it must be, using some temporary tables along the way may help.

  • more blunders example:
    part3 and if_lo both include left joins that could result in product being NULL. But product is used downstream as part of a join. I don't understand what requirement would lead to that query logic.
WITH 
tbl_def as (
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY customer_id, product ORDER BY min_fx_date ASC) AS rnk
  , customer_id
  , min_fx_date
  , product
  , balance_column
  FROM table_def
),
tbl_defb as (
  SELECT *
  FROM tbl_def
  WHERE rnk = 1
),
d_table AS (
  SELECT *
  FROM tbl_defb
  WHERE min_fx_date > '01/01/2017'
),
tc_lo_product AS (
  SELECT 
    a.customer_id
  , a.currency
  , b.product
  , a.rep_date
  FROM        table_port a
    LEFT JOIN table_product_names b ON a.ln_aim = b.product_name
  WHERE a.customer_id = 85278945789
),
tbl_port as (
  SELECT 
    customer_id
  , rep_date
  , FIRST_VALUE(close_date) OVER (PARTITION BY document_n ORDER BY rep_date DESC) AS close_date
  , currency
  , document_n
  FROM table_port
),
if_lo AS (
  SELECT DISTINCT 
    b.customer_id
  , total_amount
  , a.i_date
  , x.product
  , a.document_n
  , a.pro
  , b.close_date
  FROM        table_full a
    LEFT JOIN tbl_port b ON a.document_n = b.document_n
                        AND a.i_date = LAST_DAY(b.rep_date)
    LEFT JOIN table_product_names x ON a.ln_aim = x.product_name
),
part3 AS (
  SELECT DISTINCT 
    k.customer_id
  , k.currency
  , FIRST_VALUE(k.currency) OVER (PARTITION BY k.customer_id, k.product ORDER BY t.min_fx_date) AS main_currency
  , t.min_fx_date
  , k.product
  , t.balance_column
  FROM        d_table t
    --LEFT JOIN 
    INNER JOIN tc_lo_product k ON t.customer_id = k.customer_id
                              AND t.product = k.product
                              AND k.rep_date = t.min_fx_date
  --WHERE k.customer_id = 85278945789
),
cbar as (
  SELECT 
    c1.cur_short
  , c1.k_v
  , LAST_DAY(c1.p_date) AS rep_date
  , ROW_NUMBER() OVER (PARTITION BY c1.cu_code, TO_CHAR(c1.p_date, 'MM-YYYY') ORDER BY c1.p_date DESC) AS rn
  FROM table_currency c1
  WHERE c1.kurs_type = 'CBAR'
),
kurs_cedvel AS (
  SELECT *
  FROM cbar
  WHERE rn = 1
),
kop as (
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY customer_id, TRUNC(rep_date, 'MM') ORDER BY rep_date DESC, date_give DESC) AS rnk
  , rep_date
  , customer_id
  , interest_rate
  , date_give
  FROM table_port
  WHERE rep_date = LAST_DAY(rep_date)
    AND document_n NOT LIKE '%KOP%'
),
in_rate_tab AS (
  SELECT *
  FROM kop
  WHERE rnk = 1
),
part3b as (
  SELECT 
    k.customer_id
  , k.currency
  , k.main_currency
  , k.min_fx_date
  , k.product
  , k.balance_column
  , c.total_amount
  , c.i_date
  , c.document_n
  , c.pro
  , c.close_date
  FROM        part3 k
    LEFT JOIN if_lo c ON k.customer_id = c.customer_id
                     AND k.product = c.product
                     AND c.i_date >= k.min_fx_date
  --WHERE k.customer_id = 85278945789
),
ana_cedvel AS (
  SELECT 
    p.*
  , o.sum_payed
  , CASE
      WHEN main_currency = 'AZN' THEN p.total_amount
      ELSE ROUND(p.total_amount / v.k_v, 2)
    END AS total_amount_c
  , CASE
      WHEN main_currency = 'AZN' THEN p.pro
      ELSE ROUND(p.pro / v.kurs_val, 2)
    END AS pro_c
  , CASE
      WHEN main_currency = o.valyuta THEN o.sum_payed
      ELSE ROUND(o.sum_payed / v.k_v, 2)
    END AS sum_payed_c
  FROM        part3b p
    LEFT JOIN kurs_cedvel v ON p.main_currency = v.cur_short 
                           AND p.i_date = v.rep_date
    LEFT JOIN payments_table o ON p.document_n = o.document_n
                              AND o.payment_date >= p.min_fx_date
                              AND p.i_date = LAST_DAY(o.payment_date)
  --WHERE p.customer_id = 85278945789
),
almost_there as (
  SELECT DISTINCT 
    i_date
  , j.customer_id
  , main_currency
  , MAX(i_date) OVER (PARTITION BY j.customer_id, j.product) AS max_i_date
  , j.document_n
  , product
  , min_fx_date
  , balance_column
  , p.interest_rate
  , close_date
  , SUM(total_amount_c) OVER (PARTITION BY i_date, j.customer_id, product) AS total_amount
  , SUM(sum_payed_c) OVER (PARTITION BY i_date, j.customer_id, product) AS sum_payed
  , SUM(pro) OVER (PARTITION BY i_date, j.customer_id, product) AS pro
  FROM        ana_cedvel j
    LEFT JOIN in_rate_tab p ON j.customer_id = p.customer_id
                           AND j.i_date = p.rep_date
  --WHERE j.customer_id = 85278945789
),
final_result AS (
  SELECT DISTINCT 
    h.i_date          -- can be NULL
  , h.customer_id     -- can be NULL
  , h.main_currency   -- can be NULL
  , h.product         -- can be NULL
  , h.min_fx_date
  , h.balance_column
  , h.total_amount    -- can be NULL
  , h.pro             -- can be NULL
  , h.sum_payed       -- can be NULL
  , h.interest_rate   -- can be NULL
  , CASE
      WHEN h.max_i_date = LAST_DAY(h.close_date)
       AND h.i_date = h.max_i_date 
        THEN h.close_date
      ELSE NULL
    END AS date_end   -- can be NULL
  FROM almost_there h
  --WHERE h.customer_id = 85278945789
)

select *
from final_result

本文标签： oracle sql query brings the result for a long timeStack Overflow

版权声明：本文标题：oracle sql query brings the result for a long time - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736668256a1946779.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。