Início / coding / Perguntas / 79216699
Accepted
Christian Bongiorno
Asked: 2024-11-23 06:01:51 +0800 CST

No Oracle: Como obter apenas os registros cuja soma constitui > x % do total

  • 772

Tenho estes dados:

select * from (
    select 'A' as JOB, 15 as errors from dual union all
    select 'B' as JOB, 17 as errors from dual union all
    select 'C' as JOB, 29 as errors from dual union all
    select 'D' as JOB, 27 as errors from dual union all
    select 'E' as JOB, 35 as errors from dual union all
    select 'F' as JOB, 32 as errors from dual union all
    select 'G' as JOB, 75 as errors from dual union all
    select 'H' as JOB, 31 as errors from dual union all
    select 'I' as JOB, 12 as errors from dual union all
    select 'J' as JOB, 10 as errors from dual
)

E, em palavras, eu preciso:The jobs constituting the (top) 60% of errors

Então, neste caso, seria (113):

select sum(errors) * .4 as cut_off from ...

Os resultados finais seriam estes, pois sua soma < 113:

TRABALHO ERROS
G 75
E 35

Basicamente, preciso de um filtro que mantenha algum tipo de soma corrente e, depois, descarte tudo quando atingir esse valor.

Tenho essa consulta, que não funciona muito bem e eu preferiria não usar a withdeclaração

with data as (
    select 'A' as JOB, 15 as errors from dual union all
        select 'B' as JOB, 17 as errors from dual union all
        select 'C' as JOB, 29 as errors from dual union all
        select 'D' as JOB, 27 as errors from dual union all
        select 'E' as JOB, 35 as errors from dual union all
        select 'F' as JOB, 32 as errors from dual union all
        select 'G' as JOB, 75 as errors from dual union all
        select 'H' as JOB, 31 as errors from dual union all
        select 'I' as JOB, 12 as errors from dual union all
        select 'J' as JOB, 10 as errors from dual
)
select k.*
from (
    select t.*,
           errors + LAG(errors, 1, 0) OVER (order by errors desc ) previous
    from data t
) k where previous >= (select sum(errors) *.4 from data) order by errors desc

E eu tentei a soma em janela:

select k.*
from (
    select t.*,
           SUM(errors) OVER (
               partition by JOB
               order by errors desc
               RANGE BETWEEN UNBOUNDED PRECEDING
                AND CURRENT ROW
          ) as limit
    from (
        select 'A' as JOB, 15 as errors from dual union all
        select 'B' as JOB, 17 as errors from dual union all
        select 'C' as JOB, 29 as errors from dual union all
        select 'D' as JOB, 27 as errors from dual union all
        select 'E' as JOB, 35 as errors from dual union all
        select 'F' as JOB, 32 as errors from dual union all
        select 'G' as JOB, 75 as errors from dual union all
        select 'H' as JOB, 31 as errors from dual union all
        select 'I' as JOB, 12 as errors from dual union all
        select 'J' as JOB, 10 as errors from dual
    ) t
) k order by errors desc
sql

2 respostas

  1. Best Answer
    • SUM(errors) OVER (ORDER BY errors DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)calcula o total de erros em ordem decrescente.
    • WHERE cum_errors <= cut_offfiltra trabalhos com erros cumulativos abaixo de 40% do total.
    SELECT job, errors
FROM (
    SELECT job, errors,
        SUM(errors) OVER (ORDER BY errors DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_errors,
        SUM(errors) OVER () * 0.4 AS cut_off
    FROM (
        SELECT 'A' AS job, 15 AS errors FROM DUAL UNION ALL
        SELECT 'B' AS job, 17 AS errors FROM DUAL UNION ALL
        SELECT 'C' AS job, 29 AS errors FROM DUAL UNION ALL
        SELECT 'D' AS job, 27 AS errors FROM DUAL UNION ALL
        SELECT 'E' AS job, 35 AS errors FROM DUAL UNION ALL
        SELECT 'F' AS job, 32 AS errors FROM DUAL UNION ALL
        SELECT 'G' AS job, 75 AS errors FROM DUAL UNION ALL
        SELECT 'H' AS job, 31 AS errors FROM DUAL UNION ALL
        SELECT 'I' AS job, 12 AS errors FROM DUAL UNION ALL
        SELECT 'J' AS job, 10 AS errors FROM DUAL
    ) t
)
WHERE cum_errors <= cut_off
ORDER BY errors DESC;

    Saída:

    TRABALHO ERROS
    G 75
    E 35

    violino

    • 2

  2. Outra lógica usando subconsulta e autojunção.

    total_erros_cte:

    Este CTE calcula o número total de erros da tabela jobs_errors. Ele simplesmente soma todos os erros na tabela.

    soma_corrente_cte:

    Este CTE calcula a soma cumulativa de erros em ordem decrescente. Para cada trabalho, ele soma os erros de todos os trabalhos que têm erros maiores ou iguais à contagem de erros do trabalho atual (ONDE x.errors >= t.errors). Ele também recupera o total_errors do total_errors_cte para comparar a soma corrente com 40% do total de erros.

    WITH total_errors_cte AS (
    -- Calculate the total errors for all jobs
    SELECT SUM(errors) AS total_errors
    FROM jobs_errors
),
running_sum_cte AS (
    -- Calculate the running sum of errors, ordered by errors DESC
    SELECT JOB, errors, 
           (SELECT total_errors FROM total_errors_cte) AS total_errors,
           (
               SELECT SUM(errors)
               FROM jobs_errors x
               WHERE x.errors >= t.errors
           ) AS running_sum
    FROM jobs_errors t
)
-- Filter jobs whose running sum is <= 40% of total errors
SELECT JOB, errors
FROM running_sum_cte
WHERE running_sum <= total_errors * 0.4
ORDER BY errors DESC;

    Saída :

    Violino

    insira a descrição da imagem aqui

    • 0

relate perguntas