Início / dba / Perguntas / 283483
Accepted
H. Pauwelyn
Asked: 2021-01-19 05:02:09 +0800 CST

Selecione a maioria das ocorrências de um varchar2 no Oracle

  • 772

Precisamos agrupar dados em um banco de dados Oracle e devemos obter a ProductNumbere a Description. O Descriptionprecisa ser aquele com mais ocorrências dentro da tabela. Ambos são um varchar2. Veja abaixo os dados brutos:

Número de produto Descrição
abc Produto ABC (com desconto)
abc Produto ABC
abc Produto ABC
DEF DEF do produto

Resultado esperado:

Número de produto Descrição
abc Produto ABC
DEF DEF do produto

Tentamos isso, mas não encontramos uma maneira de incluir o Descriptioncom a maioria das ocorrências.

select distinct cnt1.ProductNumber
from (select COUNT(*) as total, ProductNumber
      from Inventory
      group by ProductNumber) cnt1,
     (select MAX(total) as maxtotal, ProductNumber
      from (select COUNT(*) as total, ProductNumber 
            from Inventory 
            group by ProductNumber)
      group by ProductNumber) cnt2
where cnt1.total = cnt2.maxtotal;

Aqui você pode encontrar um violino .

Você sabe como poderíamos fazer isso?

oracle group-by

2 respostas

  1. Best Answer

    Aqui está uma maneira de fazer isso usando funções de janela:

    select ProductNumber, Description
from (
  select ProductNumber, Description
       , row_number() over (partition by productnumber order by cnt1, cnt2 desc) as rn
  from (
    select ProductNumber, Description
         , count(1) over (partition by ProductNumber) as cnt1
         , count(1) over (partition by ProductNumber, Description) as cnt2
    from inventory
  ) t
) u 
where rn = 1;

    EDIT: Adicionado planos para um conjunto de dados um pouco maior, Fiddle

    Consulta Verace

    -----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |      1 |        |    676 |00:00:00.01 |     122 |       |       |          |
|   1 |  SORT UNIQUE               |           |      1 |  17576 |    676 |00:00:00.01 |     122 | 46080 | 46080 |40960  (0)|
|*  2 |   HASH JOIN SEMI           |           |      1 |  17576 |  17576 |00:00:00.01 |     122 |  2178K|  2050K| 2666K (0)|
|   3 |    TABLE ACCESS FULL       | INVENTORY |      1 |  17576 |  17576 |00:00:00.01 |      61 |       |       |          |
|*  4 |    VIEW                    |           |      1 |  17576 |    676 |00:00:00.01 |      61 |       |       |          |
|*  5 |     WINDOW SORT PUSHED RANK|           |      1 |  17576 |    676 |00:00:00.01 |      61 | 48128 | 48128 |43008  (0)|
|   6 |      HASH GROUP BY         |           |      1 |  17576 |    676 |00:00:00.01 |      61 |  1214K|  1214K| 1854K (0)|
|   7 |       TABLE ACCESS FULL    | INVENTORY |      1 |  17576 |  17576 |00:00:00.01 |      61 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("T1"."DESCRIPTION"="I2"."DESCRIPTION")
   4 - filter("T1"."RN"=1)
   5 - filter(ROW_NUMBER() OVER ( PARTITION BY "I1"."PRODUCTNUMBER" ORDER BY COUNT(*) DESC )<=1)

    Consulta modificada de H. Pauwelyn

    ------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |    676 |00:00:00.01 |     122 |       |       |          |
|   1 |  HASH UNIQUE          |           |      1 |    176 |    676 |00:00:00.01 |     122 |  2170K|  2170K| 1427K (0)|
|*  2 |   HASH JOIN RIGHT SEMI|           |      1 |    176 |    676 |00:00:00.01 |     122 |  2546K|  2546K|  654K (0)|
|   3 |    VIEW               |           |      1 |  17576 |    676 |00:00:00.01 |      61 |       |       |          |
|   4 |     HASH GROUP BY     |           |      1 |  17576 |    676 |00:00:00.01 |      61 |  1520K|  1520K| 1882K (0)|
|   5 |      TABLE ACCESS FULL| INVENTORY |      1 |  17576 |  17576 |00:00:00.01 |      61 |       |       |          |
|   6 |    VIEW               |           |      1 |  17576 |    676 |00:00:00.01 |      61 |       |       |          |
|   7 |     HASH GROUP BY     |           |      1 |  17576 |    676 |00:00:00.01 |      61 |  1520K|  1520K| 1882K (0)|
|   8 |      TABLE ACCESS FULL| INVENTORY |      1 |  17576 |  17576 |00:00:00.01 |      61 |       |       |          |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CNT1"."TOTAL"="CNT2"."MAXTOTAL")

    A pergunta de Lennart

    ----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |    676 |00:00:00.03 |     208 |       |       |          |
|*  1 |  VIEW                    |      |      1 |  17576 |    676 |00:00:00.03 |     208 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK|      |      1 |  17576 |    676 |00:00:00.03 |     208 |  1116K|   557K|  991K (0)|
|   3 |    VIEW                  |      |      1 |  17576 |  17576 |00:00:00.02 |     208 |       |       |          |
|   4 |     WINDOW BUFFER        |      |      1 |  17576 |  17576 |00:00:00.02 |     208 |  1116K|   557K|  991K (0)|
|   5 |      INDEX FULL SCAN     | IX1  |      1 |  17576 |  17576 |00:00:00.01 |     208 |       |       |          |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PRODUCTNUMBER" ORDER BY "CNT1",INTERNAL_FUNCTION("CNT2") DESC 
              )<=1)
    • 2

  2. Eu olhei para isso - é uma pergunta interessante (+1).

    Eu também adicionei dados de amostra - é sempre difícil cobrir casos extremos, mas vale a pena olhar (dados e análise inteira no violino aqui ).

    Então nós temos:

    SELECT * FROM inventory;

    Resultado:

    PRODUCTNUMBER   DESCRIPTION
          ABC   Product ABC (with discount)
          ABC   Product ABC
          ABC   Product ABC
          DEF   Product DEF
          XYZ   Product XYZ   --  <<< extra data from here.
          XYZ   Product XYZ
          XYZ   Product XYZ
          XYZ   Product XYZ (with discount 1)
          XYZ   Product XYZ (with discount 2)
          XYZ   Product XYZ (with discount 3)
          RST   Product RST with discount       -- the interesting two records!
          RST   Product RST without discount

    Formulei o seguinte SQL (mostrei os primeiros passos que me levaram à minha resposta - em parte para sua compreensão, em parte para mim! :-) ):

    SELECT 
  DISTINCT 
  t2.ProductNumber AS "Product Number", 
  t1.Description   AS "Product Desc." 
FROM
(
  SELECT i1.Description, COUNT(i1.Description) AS cnt,
  ROW_NUMBER() OVER (PARTITION BY i1.ProductNumber ORDER BY COUNT(i1.Description) DESC) AS rn
  FROM inventory i1
  GROUP BY i1.Description, i1.ProductNumber
) t1
INNER JOIN
(
  SELECT i2.ProductNumber, i2.Description
  FROM inventory i2
) t2
ON t1.Description = t2.Description
WHERE t1.rn = 1
ORDER BY 1;

    Resultado:

    Product Number  Product Desc.
           ABC  Product ABC
           DEF  Product DEF
           RST  Product RST without discount
           XYZ  Product XYZ

    Agora, observe que meu SQL escolheu o RST, Product RST without discountregistro onde havia uma escolha entre os dois RSTregistros (com e sem desconto).

    Agora, você verá pelo violino que a consulta de Lennart retorna o outro RST registro.

    Você pode querer investigar isso e garantir que você recupere os registros necessários em todas as circunstâncias.

    Eu também olhei para os planos para as consultas. Eu descobri como fazer isso usando o violino de um artigo de Franck Pachot aqui e o violino Oracle associado aqui .

    Agora, estou longe de ser um especialista em planos da Oracle, mas dada sua semelhança, não posso dizer se a consulta de Lennart ou a minha seria a mais eficiente com um grande conjunto de dados. Eu recomendo que você teste com conjuntos de dados realistas.

    O único benefício "concreto" que resulta da minha consulta é que ele pode ser usado em versões antigas de servidores que não suportam ROW_NUMBER()- mas até o MySQL os tem agora, então é improvável que tenha um ganho significativo! :-)

    ps, você notará que modifiquei ligeiramente sua consulta para usar junções ANSI da seguinte maneira:

    select /*+ gather_plan_statistics */ distinct cnt1.ProductNumber
from 
(
  select COUNT(*) as total, ProductNumber
  from Inventory
  group by ProductNumber
) cnt1
JOIN
(
  select MAX(total) as maxtotal, ProductNumber
  from 
  (
    select COUNT(*) as total, ProductNumber 
    from Inventory 
    group by ProductNumber
  ) t
  group by ProductNumber
) cnt2
ON cnt1.total = cnt2.maxtotal;

    Espero que você ache isso útil e/ou mais legível? Eu acho que o seu executa um CROSS JOINe depois filtra o que pode ser problemático com grandes conjuntos de dados?

    • 2

relate perguntas