在Oracle中独立高效地从多个列中查找前N个值
在Oracle中独立高效地从多个列中查找前N个值
提问于 2011-09-02 11:16:40
假设我有300亿行和多列，我想高效地找到每个列的前N个最频繁的值，并且尽可能使用最优雅的SQL。例如，如果我有
FirstName LastName FavoriteAnimal FavoriteBook
--------- -------- -------------- ------------
Ferris Freemont Possum Ubik
Nancy Freemont Lemur Housekeeping
Nancy Drew Penguin Ubik
Bill Ribbits Lemur Dhalgren我想要前1名，那么结果将是：
FirstName LastName FavoriteAnimal FavoriteBook
--------- -------- -------------- ------------
Nancy Freemont Lemur Ubik我可能会想出一些方法来做到这一点，但不确定它们是否是最优的，当有300亿行时，这一点很重要；SQL可能又大又丑，可能会占用太多的临时空间。
使用Oracle。
回答 6
Stack Overflow用户
回答已采纳
发布于 2011-09-02 17:02:04
这应该只在表上传递一次。您可以使用count()的分析版本来独立获取每个值的频率：
select firstname, count(*) over (partition by firstname) as c_fn,
lastname, count(*) over (partition by lastname) as c_ln,
favoriteanimal, count(*) over (partition by favoriteanimal) as c_fa,
favoritebook, count(*) over (partition by favoritebook) as c_fb
from my_table;
FIRSTN C_FN LASTNAME C_LN FAVORIT C_FA FAVORITEBOOK C_FB
------ ---- -------- ---- ------- ---- ------------ ----
Bill 1 Ribbits 1 Lemur 2 Dhalgren 1
Ferris 1 Freemont 2 Possum 1 Ubik 2
Nancy 2 Freemont 2 Lemur 2 Housekeeping 1
Nancy 2 Drew 1 Penguin 1 Ubik 2然后，您可以将其用作CTE (或子查询分解，我认为在oracle术语中)，并仅从每一列中提取频率最高的值：
with tmp_tab as (
select /*+ MATERIALIZE */
firstname, count(*) over (partition by firstname) as c_fn,
lastname, count(*) over (partition by lastname) as c_ln,
favoriteanimal, count(*) over (partition by favoriteanimal) as c_fa,
favoritebook, count(*) over (partition by favoritebook) as c_fb
from my_table)
select (select firstname from (
select firstname,
row_number() over (partition by null order by c_fn desc) as r_fn
from tmp_tab
) where r_fn = 1) as firstname,
(select lastname from (
select lastname,
row_number() over (partition by null order by c_ln desc) as r_ln
from tmp_tab
) where r_ln = 1) as lastname,
(select favoriteanimal from (
select favoriteanimal,
row_number() over (partition by null order by c_fa desc) as r_fa
from tmp_tab
) where r_fa = 1) as favoriteanimal,
(select favoritebook from (
select favoritebook,
row_number() over (partition by null order by c_fb desc) as r_fb
from tmp_tab
) where r_fb = 1) as favoritebook
from dual;
FIRSTN LASTNAME FAVORIT FAVORITEBOOK
------ -------- ------- ------------
Nancy Freemont Lemur Ubik您正在为每一列执行一次CTE，但这仍然应该只命中实际的表一次(这要归功于materialize提示)。您可能想要添加到order by子句中，以调整在存在关联的情况下该如何处理。
这在概念上类似于Thilo、ysth和其他人的建议，只是您让Oracle跟踪所有的计数。
编辑：嗯，explain plan显示它正在进行四次全表扫描；可能需要考虑一下这一点……CTEEdite2：将(未记录的) MATERIALIZE提示添加到似乎可以解决这个问题；它创建了一个临时的临时表来保存结果，并且只执行一次全表扫描。不过，解释计划的成本更高--至少在这个时间样本数据集上是这样。对这样做的任何缺点的任何评论都是有兴趣的。
Stack Overflow用户
发布于 2011-09-02 20:18:58
到目前为止，我提出的最好的纯Oracle SQL是类似于@AlexPoole所做的。我使用count(A)而不是count(*)将空值推到底部。
with
NUM_ROWS_RETURNED as (
select 4 as NUM from dual
),
SAMPLE_DATA as (
select /*+ materialize */
A,B,C,D,E
from (
select 1 as A, 1 as B, 4 as C, 1 as D, 4 as E from dual
union all select 1 , -2 , 3 , 2 , 3 from dual
union all select 1 , -2 , 2 , 2 , 3 from dual
union all select null , 1 , 1 , 3 , 2 from dual
union all select null , 2 , 4 , null , 2 from dual
union all select null , 1 , 3 , null , 2 from dual
union all select null , 1 , 2 , null , 1 from dual
union all select null , 1 , 4 , null , 1 from dual
union all select null , 1 , 3 , 3 , 1 from dual
union all select null , 1 , 4 , 3 , 1 from dual
)
),
RANKS as (
select /*+ materialize */
rownum as RANKED
from
SAMPLE_DATA
where
rownum <= (select min(NUM) from NUM_ROWS_RETURNED)
)
select
r.RANKED,
max(case when A_RANK = r.RANKED then A else null end) as A,
max(case when B_RANK = r.RANKED then B else null end) as B,
max(case when C_RANK = r.RANKED then C else null end) as C,
max(case when D_RANK = r.RANKED then D else null end) as D,
max(case when E_RANK = r.RANKED then E else null end) as E
from (
select
A, dense_rank() over (order by A_COUNTS desc) as A_RANK,
B, dense_rank() over (order by B_COUNTS desc) as B_RANK,
C, dense_rank() over (order by C_COUNTS desc) as C_RANK,
D, dense_rank() over (order by D_COUNTS desc) as D_RANK,
E, dense_rank() over (order by E_COUNTS desc) as E_RANK
from (
select
A, count(A) over (partition by A) as A_COUNTS,
B, count(B) over (partition by B) as B_COUNTS,
C, count(C) over (partition by C) as C_COUNTS,
D, count(D) over (partition by D) as D_COUNTS,
E, count(E) over (partition by E) as E_COUNTS
from
SAMPLE_DATA
)
)
cross join
RANKS r
group by
r.RANKED
order by
r.RANKED
/提供：
RANKED| A| B| C| D| E
------|----|----|----|----|----
1| 1| 1| 4| 3| 1
2|null| -2| 3| 2| 2
3|null| 2| 2| 1| 3
4|null|null| 1|null| 4使用plan：
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 57 (20)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | VIEW | | 10 | 150 | 20 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 12 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 13 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 14 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 15 | LOAD AS SELECT | | | | | |
|* 16 | COUNT STOPKEY | | | | | |
| 17 | VIEW | | 10 | | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9| 10 | 150 | 2 (0)| 00:00:01 |
| 19 | SORT AGGREGATE | | 1 | | | |
| 20 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 21 | SORT GROUP BY | | 1 | 93 | 33 (34)| 00:00:01 |
| 22 | MERGE JOIN CARTESIAN | | 100 | 9300 | 32 (32)| 00:00:01 |
| 23 | VIEW | | 10 | 800 | 12 (84)| 00:00:01 |
| 24 | WINDOW SORT | | 10 | 800 | 12 (84)| 00:00:01 |
| 25 | WINDOW SORT | | 10 | 800 | 12 (84)| 00:00:01 |
| 26 | WINDOW SORT | | 10 | 800 | 12 (84)| 00:00:01 |
| 27 | WINDOW SORT | | 10 | 800 | 12 (84)| 00:00:01 |
| 28 | WINDOW SORT | | 10 | 800 | 12 (84)| 00:00:01 |
| 29 | VIEW | | 10 | 800 | 7 (72)| 00:00:01 |
| 30 | WINDOW SORT | | 10 | 150 | 7 (72)| 00:00:01 |
| 31 | WINDOW SORT | | 10 | 150 | 7 (72)| 00:00:01 |
| 32 | WINDOW SORT | | 10 | 150 | 7 (72)| 00:00:01 |
| 33 | WINDOW SORT | | 10 | 150 | 7 (72)| 00:00:01 |
| 34 | WINDOW SORT | | 10 | 150 | 7 (72)| 00:00:01 |
| 35 | VIEW | | 10 | 150 | 2 (0)| 00:00:01 |
| 36 | TABLE ACCESS FULL| SYS_TEMP_0FD9| 10 | 150 | 2 (0)| 00:00:01 |
| 37 | BUFFER SORT | | 10 | 130 | 33 (34)| 00:00:01 |
| 38 | VIEW | | 10 | 130 | 2 (0)| 00:00:01 |
| 39 | TABLE ACCESS FULL | SYS_TEMP_0FD9| 10 | 130 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
16 - filter( (SELECT MIN(4) FROM "SYS"."DUAL" "DUAL")>=ROWNUM)但是对于一个真实的表，它看起来像(对于稍微修改过的查询)：
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 422 | | 6026M (1)|999:59:59 | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | | |
| 2 | LOAD AS SELECT | | | | | | | | | | | |
|* 3 | COUNT STOPKEY | | | | | | | | | | | |
| 4 | PX COORDINATOR | | | | | | | | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ10000 | 10 | | | 2 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
|* 6 | COUNT STOPKEY | | | | | | | | | Q1,00 | PCWC | |
| 7 | PX BLOCK ITERATOR | | 10 | | | 2 (0)| 00:00:01 | 1 | 115 | Q1,00 | PCWC | |
| 8 | INDEX FAST FULL SCAN | IDX | 10 | | | 2 (0)| 00:00:01 | 1 | 115 | Q1,00 | PCWP | |
| 9 | SORT GROUP BY | | 1 | 422 | | 6026M (1)|999:59:59 | | | | | |
| 10 | MERGE JOIN CARTESIAN | | 22G| 8997G| | 6024M (1)|999:59:59 | | | | | |
| 11 | VIEW | | 2289M| 872G| | 1443M (1)|999:59:59 | | | | | |
| 12 | WINDOW SORT | | 2289M| 872G| 970G| 1443M (1)|999:59:59 | | | | | |
| 13 | WINDOW SORT | | 2289M| 872G| 970G| 1443M (1)|999:59:59 | | | | | |
| 14 | WINDOW SORT | | 2289M| 872G| 970G| 1443M (1)|999:59:59 | | | | | |
| 15 | WINDOW SORT | | 2289M| 872G| 970G| 1443M (1)|999:59:59 | | | | | |
| 16 | WINDOW SORT | | 2289M| 872G| 970G| 1443M (1)|999:59:59 | | | | | |
| 17 | WINDOW SORT | | 2289M| 872G| 970G| 1443M (1)|999:59:59 | | | | | |
| 18 | VIEW | | 2289M| 872G| | 248M (1)|829:16:06 | | | | | |
| 19 | WINDOW SORT | | 2289M| 162G| 198G| 248M (1)|829:16:06 | | | | | |
| 20 | WINDOW SORT | | 2289M| 162G| 198G| 248M (1)|829:16:06 | | | | | |
| 21 | WINDOW SORT | | 2289M| 162G| 198G| 248M (1)|829:16:06 | | | | | |
| 22 | WINDOW SORT | | 2289M| 162G| 198G| 248M (1)|829:16:06 | | | | | |
| 23 | WINDOW SORT | | 2289M| 162G| 198G| 248M (1)|829:16:06 | | | | | |
| 24 | WINDOW SORT | | 2289M| 162G| 198G| 248M (1)|829:16:06 | | | | | |
| 25 | PARTITION RANGE ALL| | 2289M| 162G| | 3587K (4)| 11:57:36 | 1 | 115 | | | |
| 26 | TABLE ACCESS FULL | LARGE_TABLE | 2289M| 162G| | 3587K (4)| 11:57:36 | 1 | 115 | | | |
| 27 | BUFFER SORT | | 10 | 130 | | 6026M (1)|999:59:59 | | | | | |
| 28 | VIEW | | 10 | 130 | | 2 (0)| 00:00:01 | | | | | |
| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9| 10 | 130 | | 2 (0)| 00:00:01 | | | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=10)
6 - filter(ROWNUM<=10)不过，我可以使用from LARGE_TABLE sample (0.01)来加快速度，但可能会得到失真的图片。这在53分钟内为一个有20亿行的表返回了一个答案。
Stack Overflow用户
发布于 2011-09-02 12:02:15
你不能这么做。
这里没有什么技巧，只是原始的工作。
简单地说，您必须遍历表中的每一行，计算您感兴趣的每一列的出现次数，然后对这些结果进行排序，以找到具有最高值的结果。
对于单个列，它很简单：
SELECT col, count(*) FROM table GROUP BY col ORDER BY count(*) DESC并获取第一行。
N列等于N个表扫描。
如果您编写逻辑并遍历一次表，那么您将计算每个列的每个值的每个实例。
如果你有300亿行，有300亿个值，你可以存储它们，它们的计数都是1，你可以对你关心的每一列都这样做。
如果这些信息对你来说很重要，你最好随着你的数据的到来独立地、增量地跟踪它。但这是一个不同的问题。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/7278905
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号