Shulou(Shulou.com)06/01 Report--

This article will explain in detail where POSTGRESQL is better than ORACLE. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

First of all, introduce the situation of this table to avoid gossip that the structure of the table is special. There are these fields in the picture. The region field is a digital self-increasing column of BIGINT, the random calculated value of the country column MD5, the system time when year is inserted into the data, region_class is a column classified according to the numerical range of region, for example, region in 1000 is 1, the others are 23, and so on. There is also NULL, which, by the way, automatically inserts 10 million data into this 8-gigabyte broken machine in 3.6 seconds.

Application grouping set

Objective: to group by region_class and take the maximum number of region in each group.

Select region_class,max (region) as max_in from kill_O where region

< 100000 and region_class is not null group by ROLLUP (region_class); 耗时 181ms 没有任何索引 加大难度：本次查询其实没有任何意义，仅仅是为了加大查询的难度，依然没有任何索引。在多字段分组，并且进行排序，时间1009ms select region_class,sum(region) as sum_in from kill_O where region < 100000 and region_class is not null group by ROLLUP (region_class,region) order by region_class 经过查看了执行计划，POSTGRESQL 启用了并行扫描 如果在计算中，我们在进行多个字段GROUP BY 时 希望的是举例 GROUP BY 1 2 的数据 一次性获取更多的统计数据 希望以此能获得 group by 1 2 + group by 1 + group 2 的数据，POSTGRESQL 可以做吗？YES select region_class,sum(region) as sum_in from kill_O where region < 100000 and region_class is not null group by cube (region_class,region) 继续不使用索引，3892ms 完成了整体的计算 继续提高要求，目前需要 GROUP (1 2) ROLLUP+ GROUP 1 ROLLUP + GROUP 2 ROLLUP POSTGRESQL 可以做吗，YES select region_class,sum(region) as sum_in from kill_O where region < 100000 and region_class is not null GROUP BY GROUPING SETS (region_class,region) 不添加索引的情况下，2113ms 完成 如此就完事了，没有，难度还的继续。 我们在进行分组的时候，select 后面的 字段需要进行分割显示，例如 我统计 一车水果，通过水果的 好 或 坏来进行分类，但显示的时候，要对苹果，香蕉，芒果来分别显示他们的好 和坏的 总数量。 POSTGRESQL 能做这样的事情吗 YES select region_class, sum(region) filter(where year >

'2019-04-19 10 4315 33' and year

< '2019-04-19 10:44:33' ) from kill_O where region < 100000 and region_class is not null group by (region_class); 191ms 完成 依然是没有加索引。 这没有完，继续，在统计分析中有一个概念叫中位数概念，这个概念是抛弃所有数据的50%后，在做统计，当然也可以调整，抛弃更多的数据或留下更多的数据。 具体还是看下面的语句把，都完成了。

It can be done in seconds.

Window function, this POSTGRESQL can not be let go, see what he can do

1 OVER

2 Partition by

The partition by plus condition is also OK. And there's nothing wrong with partition by order by.

In the end, we can still carry out

And the most common thing we use is to re-sort the records we query.

Of course, the functions of POSTGRESQL in database processing are also a large number of simple lists

LEAD ()

LAG ()

The data can be compared and used differently to facilitate the use of some commonly used data comparison reports.

First_value ()

Nth_value ()

Last_value ()

Row_number ()

Functions, etc.

At the same time, you can also create your own aggregation to facilitate customization.

Finally, why there is no index in the overall operation is mainly due to the large number of index categories of POSTGRESQL, many of which are functions that ORACLE does not have. The use of advanced indexing functions is suspected of being unable to use force.

This is the end of the article on "where is POSTGRESQL better than ORACLE?". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.