主页 / dba / 问题 / 283483
Accepted
H. Pauwelyn
Asked: 2021-01-19 05:02:09 +0800 CST

在 Oracle 中选择出现最多的 varchar2

  • 772

我们需要在 Oracle 数据库中对数据进行分组，并且必须得到 aProductNumber和 a Description。Description需要是表中出现次数最多的那个。两者都是一个varchar2。原始数据见下图：

产品编号 描述
美国广播公司 产品ABC（有折扣）
美国广播公司 产品ABC
美国广播公司 产品ABC
国防军 产品定义

预期结果：

产品编号 描述
美国广播公司 产品ABC
国防军 产品定义

我们已经尝试过了，但没有找到一种方法来包含出现Description次数最多的 。

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;

在这里你可以找到一个 fiddle。

你知道我们该怎么做吗？

oracle group-by

2 个回答

  1. Best Answer

    这是使用窗口函数的一种方法：

    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;

    编辑：添加了一组稍大的数据的计划，小提琴

    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)

    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")

    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. 我看了这个 - 这是一个有趣的问题（+1）。

    我还添加了示例数据 - 总是很难涵盖边缘情况，但值得一看（数据和整个分析在这里小提琴）。

    所以，我们有：

    SELECT * FROM inventory;

    结果：

    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

    我制定了以下 SQL（我已经展示了导致我回答的早期步骤 - 部分是为了您的理解，部分是为了我自己的理解！:-)）：

    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;

    结果：

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

    现在，请注意我的 SQL 选择了在两个（有和没有折扣）记录RST, Product RST without discount之间进行选择的记录。RST

    现在，您将从小提琴中看到 Lennart 的查询返回另一条 RST记录。

    您可能希望对此进行调查并确保您检索到在所有情况下都需要的记录。

    我还查看了查询计划。我从 Franck Pachot 的一篇文章中的小提琴和相关的 Oracle 小提琴在这里找到了如何使用小提琴来做到这一点。

    现在，我远不是 Oracle 计划方面的专家，但鉴于它们的相似性，我无法判断 Lennart 的查询还是我自己的查询在给定大型数据集的情况下效率更高。我会敦促您使用真实的数据集进行测试。

    从我的查询中获得的唯一“具体”好处是它可以在不支持的旧版本服务器上使用ROW_NUMBER()- 但即使是 MySQL 现在也有这些，因此不太可能获得显着收益！:-)

    ps 你会注意到我已经稍微修改了你的查询以使用 ANSI 连接，如下所示：

    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;

    我希望你觉得这很有帮助和/或更具可读性？我认为你的执行一个CROSS JOIN然后过滤器，这可能对大型数据集有问题？

    • 2

相关问题