oracle - 类似结果的 Oracle PARTITION BY 与 GROUP BY 的解释-6ren

oracle - 类似结果的 Oracle PARTITION BY 与 GROUP BY 的解释

转载作者：行者123 更新时间：2023-12-04 00:48:51

我有下表(标记):

firstname    lastname    Mark    
------------------------------
arun         prasanth    40      
ann          antony      45      
sruthy       abc         41      
new          abc         47      
arun         prasanth    45      
arun         prasanth    49      
ann          antony      49

并且想添加一个列，该列标记具有特定列的记录是否出现多次。这是结果:

firstname    lastname    Mark    MULTI_FLAG
----------------------------------------------
arun         prasanth    40      1
ann          antony      45      1
sruthy       abc         41      0
new          abc         47      0
arun         prasanth    45      1
arun         prasanth    49      1
ann          antony      49      1

我可以通过以下 GROUP BY 查询获得结果:

SELECT M1.firstname
      ,M1.lastname
      ,M1.Mark
      ,M2.MULTI_COUNT
FROM Marks  M1
JOIN (SELECT firstname, lastname, CASE WHEN COUNT (*) > 1 THEN 1 ELSE 0 END AS MULTI_COUNT
    FROM Marks
    GROUP BY firstname, lastname) M2
   ON M2.firstname = M1.firstname AND M2.lastname = M1.lastname;

或者通过这个更漂亮的 PARTITION BY 查询:

SELECT
  firstname,
  lastname,
  CASE WHEN COUNT(*) OVER (PARTITION BY
     firstname,
     lastname) > 1 THEN 1 ELSE 0 END AS MULTI_FLAG
FROM
  Marks

在返回的类似大表上运行 GROUP BY 查询:
34 米 56 秒 595 毫秒

在返回的类似大表上运行 PARTITION BY 查询:

第一次运行: 55 m 47 s 851 ms

第二次运行: 36 m 46 s 95 ms

我有兴趣知道:

实现我的结果的最佳方式

性能差异的原因。

编辑:如何阅读查询计划。

编辑:
甲骨文版本
Oracle 数据库 11g 企业版 11.2.0.3.0 版 - 64 位生产
PL/SQL 版本 11.2.0.3.0 - 生产
《核心 11.2.0.3.0 生产》
适用于 Linux 的 TNS:版本 11.2.0.3.0 - 生产
NLSRTL 版本 11.2.0.3.0 - 生产

按计划分区

PLAN_TABLE_OUTPUT
Plan hash value: 3822227444

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                            |   668K|    90M|       | 90429   (1)| 00:18:06 |
|   1 |  WINDOW SORT                   |                            |   668K|    90M|    98M| 90429   (1)| 00:18:06 |
|*  2 |   HASH JOIN RIGHT OUTER        |                            |   668K|    90M|       | 69340   (1)| 00:13:53 |
|   3 |    TABLE ACCESS FULL           | COUNTRY_REGION_MAPPINGS    |   177 |  4779 |       |     3   (0)| 00:00:01 |
|   4 |    NESTED LOOPS                |                            |       |       |       |            |          |
|   5 |     NESTED LOOPS               |                            |   377K|    41M|       | 69335   (1)| 00:13:53 |
|   6 |      MAT_VIEW ACCESS FULL      | PROJINFO_MAX_ITER_MVW      | 17713 |   328K|       |   782   (1)| 00:00:10 |
|*  7 |      INDEX RANGE SCAN          | Q_CLIN_ASSUM_BYCOUN_PK     |     1 |       |       |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID| Q_CLINICAL_ASSUM_BYCOUNTRY |    21 |  2016 |       |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("CRM"."COUNTRY"(+))=UPPER("QCAB"."TRIAL_COUNTRY"))
   7 - access("PMIM"."OPPORTUNITYNUM"="QCAB"."OPPORTUNITYNUM" AND "PMIM"."CONTRACTNUM"="QCAB"."CONTRACTNUM" 
              AND "PMIM"."ITERATION"="QCAB"."ITERATION")
       filter(UPPER("QCAB"."SHEET_LOC") LIKE '%COUNTRY ASSUMPTIONS%' OR UPPER("QCAB"."SHEET_LOC") LIKE 
              'INPUT%')

按计划分组

PLAN_TABLE_OUTPUT
Plan hash value: 648231064

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                            |   912 |  2052K|       |   226K  (1)| 00:45:22 |
|*  1 |  HASH JOIN                        |                            |   912 |  2052K|       |   226K  (1)| 00:45:22 |
|   2 |   TABLE ACCESS FULL               | COUNTRY_REGION_MAPPINGS    |   177 |  4779 |       |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN                       |                            | 89667 |   194M|    45M|   226K  (1)| 00:45:22 |
|   4 |    NESTED LOOPS                   |                            |       |       |       |            |          |
|   5 |     NESTED LOOPS                  |                            |   377K|    41M|       | 69335   (1)| 00:13:53 |
|   6 |      MAT_VIEW ACCESS FULL         | PROJINFO_MAX_ITER_MVW      | 17713 |   328K|       |   782   (1)| 00:00:10 |
|*  7 |      INDEX RANGE SCAN             | Q_CLIN_ASSUM_BYCOUN_PK     |     1 |       |       |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID   | Q_CLINICAL_ASSUM_BYCOUNTRY |    21 |  2016 |       |     4   (0)| 00:00:01 |
|   9 |    VIEW                           |                            |   668K|  1377M|       | 86518   (1)| 00:17:19 |
|  10 |     HASH GROUP BY                 |                            |   668K|    72M|    80M| 86518   (1)| 00:17:19 |
|* 11 |      HASH JOIN RIGHT OUTER        |                            |   668K|    72M|       | 69340   (1)| 00:13:53 |
|  12 |       TABLE ACCESS FULL           | COUNTRY_REGION_MAPPINGS    |   177 |  2478 |       |     3   (0)| 00:00:01 |
|  13 |       NESTED LOOPS                |                            |       |       |       |            |          |
|  14 |        NESTED LOOPS               |                            |   377K|    35M|       | 69335   (1)| 00:13:53 |
|  15 |         MAT_VIEW ACCESS FULL      | PROJINFO_MAX_ITER_MVW      | 17713 |   328K|       |   782   (1)| 00:00:10 |
|* 16 |         INDEX RANGE SCAN          | Q_CLIN_ASSUM_BYCOUN_PK     |     1 |       |       |     3   (0)| 00:00:01 |
|  17 |        TABLE ACCESS BY INDEX ROWID| Q_CLINICAL_ASSUM_BYCOUNTRY |    21 |  1701 |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("R2"."TRIAL_COUNTRY_CD"="CRM"."COUNTRY_CD" AND 
              UPPER("CRM"."COUNTRY")=UPPER("QCAB"."TRIAL_COUNTRY"))
   3 - access("R2"."OPPORTUNITYNUM"="QCAB"."OPPORTUNITYNUM" AND "R2"."ITERATION"="QCAB"."ITERATION" AND 
              "R2"."CONTRACTNUM"="QCAB"."CONTRACTNUM" AND "R2"."ASSUMPTION"="QCAB"."ASSUMPTION")
   7 - access("PMIM"."OPPORTUNITYNUM"="QCAB"."OPPORTUNITYNUM" AND "PMIM"."CONTRACTNUM"="QCAB"."CONTRACTNUM" AND 
              "PMIM"."ITERATION"="QCAB"."ITERATION")
       filter(UPPER("QCAB"."SHEET_LOC") LIKE '%COUNTRY ASSUMPTIONS%' OR UPPER("QCAB"."SHEET_LOC") LIKE 'INPUT%')
  11 - access(UPPER("CRM"."COUNTRY"(+))=UPPER("QCAB"."TRIAL_COUNTRY"))
  16 - access("PMIM"."OPPORTUNITYNUM"="QCAB"."OPPORTUNITYNUM" AND "PMIM"."CONTRACTNUM"="QCAB"."CONTRACTNUM" AND 
              "PMIM"."ITERATION"="QCAB"."ITERATION")
       filter(UPPER("QCAB"."SHEET_LOC") LIKE '%COUNTRY ASSUMPTIONS%' OR UPPER("QCAB"."SHEET_LOC") LIKE 'INPUT%')

最佳答案

通常，您从解析函数 count(*) 开始这导致了一个紧凑的 SQL。

这种方法的缺点是必须对数据进行排序(请参阅 WINDOW SORT 操作)。 GROUP BY方法避免
排序为 HASH GROUP BY可以使用，这可以带来更好的性能。

您的示例涉及更多内容，因为您不使用表，而是使用连接三个表的 View - 对于 GROUP BY，此连接执行两次以及详细数据；哪一个
当然不是最优的。

所以我将从查询的分析函数版本开始(可能带有 PARALLEL 选项)。

如果您想尝试 GROUP BY轻轨版本是可能的:

1) 只对重复的键进行分组

2) 制作 OUTER JOIN分配 MULTI_FLAG
下面带有执行计划的示例 - 对您的数据进行简单测试

with dups as (
select firstname,lastname  from tmp
group by firstname,lastname
having count(*) > 1)
select tmp.FIRSTNAME, tmp.LASTNAME, tmp.MARK,
case when dups.firstname is not NULL then 1 else 0 end as MULTI_FLAG
from tmp
left outer join dups on tmp.firstname = dups.firstname and tmp.lastname = dups.lastname;

您仍然需要访问您的 View 两次，但最终加入会更快(特别是如果您只有少量重复键)。

--------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |   105K|    26M|       |  1673   (1)| 00:00:21 |
|*  1 |  HASH JOIN RIGHT OUTER|      |   105K|    26M|    11M|  1673   (1)| 00:00:21 |
|   2 |   VIEW                |      |   105K|    10M|       |   128   (4)| 00:00:02 |
|*  3 |    FILTER             |      |       |       |       |            |          |
|   4 |     HASH GROUP BY     |      |   105K|    10M|       |   128   (4)| 00:00:02 |
|   5 |      TABLE ACCESS FULL| TMP  |   105K|    10M|       |   125   (1)| 00:00:02 |
|   6 |   TABLE ACCESS FULL   | TMP  |   105K|    15M|       |   125   (1)| 00:00:02 |
--------------------------------------------------------------------------------------

关于oracle - 类似结果的 Oracle PARTITION BY 与 GROUP BY 的解释，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/55357476/

文章推荐： unit-testing - 使用 JMS (ActiveMQ) 进行单元测试

文章推荐： ssis - 将退出代码从 SSIS 包返回到外部调度程序

文章推荐： visual-studio - Visual Studio 2010 的 Smart Paster 插件

类似 SQL 的搜索字符串开头为
学习SQL。有一个简单的带有字段标题的桌面游戏。我想根据标题进行搜索。如果我有一款名为 Age of Empires III: Dynasties 的游戏，并且我使用 LIKE 和参数 Age of
haskell - 为列表创建一个镜头(类似)
我正在尝试为以下数据结构创建镜头。我正在使用lens-family . data Tree = Tree { _text :: String, _subtrees ::
类似 Clojure 的方式在循环中使用数据结构
我发现很难理解这一点。比如说，在 Python 中，如果我想要一个根据用户输入在循环中修改的列表，我会有这样的内容: def do_something(): x = [] while(
类似 mysql 的部分匹配问题
我有一个像这样的 mysql 查询 SELECT group_name FROM t_groups WHERE group_name LIKE '%PCB%'; 结果是 group_name ----
MySQL - 类似 - 正则表达式的替代品？
我的数据库表中有超过一百万条记录。当我使用like时非常慢，当我使用match against时他们丢失了一些记录。我创建帮助表: 标签列表 tag_id tag_name tag_rel_me
javascript - JXBrowser 类似
我在我的一个 Java 项目中使用 JXBrowser 来简单显示 googlemaps 网页，以便我可以在那里跟踪路线，但最近我想改进该项目，但我的问题是 JXBrowser 的许可证过期(只有一个
类似 Mysql 的语法
小问题:如何将 mysql_escape_string 变量包含在 like 子句中？ "SELECT * FROM table WHERE name LIKE '%". %s . "%'" 或
类似 Javascript 的确认框
我尝试使用几个jquery消息插件，例如alertify . 但我注意到的主要事情是系统消息框会停止后台功能，直到用户响应。其他插件没有此功能。有没有办法将此功能添加到 jquery 插件中？可以扩
类似 ruby 猫壳
我是 Ruby 新手。我过去使用过 shell。我正在将 shell 程序转换为 ruby。我有以下命令 cmd="cat -n " + infile + " | grep '127.0.0.1
类似 iOS 的网页界面
关闭。这个问题不符合Stack Overflow guidelines .它目前不接受答案。要求我们推荐或查找工具、库或最喜欢的场外资源的问题对于 Stack Overflow 来说是偏离主题的，
string - 类似 str 的迭代器
当我研究 Rust 时，我试图编写一个 Rust 函数来查看任何可迭代的字符串。我最初的尝试是 fn example_1(iter: impl Iterator); fn example_2(ite
GWT 代码拆分下载进度条 - 类似 gmail
我必须在我的项目中使用代码拆分。但无论如何，第一次初始下载有一些代码。现在我想向最终用户展示代码下载(.cache.html - 或其他代码拆分)的进度，例如 gmail 启动进度。请你帮帮我。
Clojure::类似 Lint 的提示
我今天找到了一个错误，它最终是由我代码中的以下片段引起的(我试图在列表中仅过滤“PRIMARY KEY”约束): (filter #(= (% :constraint_type "PRIMARY KE
SQL Server - 类似/模式匹配
我正在尝试在关键字段上实现检查约束。关键字段由 3 个字符的前缀组成，然后附加数字字符(可以手动提供，但默认是从序列中获取整数值，然后将其转换为 nvarchar)。关键字段定义为 nvarhcar(
java - 类似 ArrayList 声明之间的类型差异
我正在尝试使用以下方式创建 List 实例: List listOne = new ArrayList(); List listTwo = new ArrayList(){}; List listTh
macos - UITableView 类似 NSTableView
我过去曾为 iOS 开发过，最近转向了 mac 开发。我开始了一个“感受”事物的项目，但遇到了一个问题。我试图创建一个 NSTableView 来显示多个项目，包括一个标签、一个 2 UIImageV
类似 "not having"的 SQL 查询？
我正在尝试编写一个查询，该查询将返回哪些主机缺少某个软件: Host Software A Title1 A
c - 类似 malloc 函数的严格别名的原因
AFAIK，在三种情况下别名是可以的仅限定符或符号不同的类型可以互为别名。 struct 或 union 类型可以为包含在其中的类型设置别名。将 T* 转换为 char* 是可以的。 (不允许相反
regex - 类似 Perl 的速记字符类在括号表达式中不起作用
\s 似乎不适用于 sed 's/[\s]\+//' tempfile 当它为工作时 sed 's/[ ]\+//' tempfile 我正在尝试删除由于命令而出现在每行开头的空格: nl -s ')
F# 图形库(类似 ocamlgraph)
我正在使用 ocamlgraph 在 ocaml 中编写程序，并想知道是否要将其移植到 F# 我有哪些选择？谢谢。最佳答案 QuickGraph .Net 最完整的图形库之一关于F# 图形库(类似

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

oracle - 类似结果的 Oracle PARTITION BY 与 GROUP BY 的解释