收集年度汇总数据以便以后快速访问 [英] collecting annual aggregated data for later quick access

问题描述

我有许多以 year 作为参数并生成给定年份的年度报告的SQL查询。

I have a number of sql queries which take year as a parameter and generate various annual reports for the given year.

这些查询非常麻烦，并且需要花费大量时间（20分钟-40分钟）执行。

Those queries are quite cumbersome and take a considerable amount of time to execute (20 min - 40 min).

为了使我的用户能够在需要时查看年度报告，我正在考虑预先执行这些查询并存储结果以供以后使用。

In order to give my users the ability to view annual report whenever they need to, I am considering to pre-execute these queries and store the results for later use.

一种解决方案是安排执行这些查询并将结果插入到某些临时表中。

One solution would be to schedule execution of these queries and insert the results in some temp tables.

但是我正在寻找一种更聪明的方法，该方法不涉及为所有这些查询编写数十个立即执行语句或自定义插入。

But I am looking for more clever approach one that would not involve writing dozens of execute immediate statements or custom inserts for all these queries.

任何想法都可以赞赏。我也不知道是否可以将物化视图用于此目的。

Any idea would be appreciated. Also I don't know if materialized views can be used to that end.

预期结果将是带有年份列的表或视图，以便用户可以对任何对象执行快速搜索

expected result would be a table or a view with a year column so that a user could execute quick search for any year.

例如

product_id |annual_sales|max_price|min_price|year
124|1200,56|80|50|2019
124|1400,00|85|55|2020

推荐答案

物化视图将是您要执行的操作的理想选择。这样，您可以一次为视图编写查询，然后根据需要多次刷新实例化视图中的数据。您可以有一个工作，每晚，周末或您选择的频率刷新一次数据。

A materialized view would be a great option for what you are looking to do. This way you can write the query once for the view, then have the data in the materialized view refresh as often as you'd like. You can have a job that refreshes the data once per night, on the weekends, or whatever frequency you choose.

创建实例化视图后，还可以在索引上方添加索引。

After the materialized view is created, you can also add indexes on top of the materialized view to assist with query performance if you so choose.

下面是一个有关如何创建实例化视图的快速示例。

A quick example on how to create a materialized view can be seen below.

CREATE TABLE sale
(
    product_id     NUMBER,
    sale_date      DATE,
    sale_amount    NUMBER
);

INSERT INTO sale (product_id, sale_date, sale_amount)
     VALUES (124, DATE '2019-02-01', 40.25);

INSERT INTO sale (product_id, sale_date, sale_amount)
     VALUES (124, DATE '2019-02-01', 80.99);

INSERT INTO sale (product_id, sale_date, sale_amount)
     VALUES (124, DATE '2020-02-01', 30.50);

INSERT INTO sale (product_id, sale_date, sale_amount)
     VALUES (124, DATE '2020-02-01', 46.75);

CREATE MATERIALIZED VIEW sales_summary
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
AS
      SELECT product_id,
             SUM (sale_amount)                 AS annual_sales,
             MAX (sale_amount)                 AS max_price,
             MIN (sale_amount)                 AS min_price,
             EXTRACT (YEAR FROM sale_date)     AS year
        FROM sale
    GROUP BY product_id, EXTRACT (YEAR FROM sale_date);

结果

select * from sales_summary;

   PRODUCT_ID    ANNUAL_SALES    MAX_PRICE    MIN_PRICE    YEAR
_____________ _______________ ____________ ____________ _______
          124          121.24        80.99        40.25    2019
          124           77.25        46.75         30.5    2020

这篇关于收集年度汇总数据以便以后快速访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！