postgresql利用函数返回表的方式，实现视图内入参，提高查询效率

Mr_Air_Boy

已于 2023-05-30 13:48:58 修改

阅读量1.2k

点赞数 1

文章标签： postgresql 数据库 sql

于 2023-05-30 11:33:03 首次发布

本文链接：https://blog.csdn.net/Mr_Air_Boy/article/details/130943148

版权

文章讨论了一个用于计算证券收益排名的PostgreSQL视图，原始视图在处理大量数据时效率低下。为了解决这个问题，提出了通过创建函数来动态传入查询日期，只处理指定日期的数据，从而显著提高查询速度。这种方法将视图的逻辑封装到函数中，实现了按需计算，减少了不必要的聚合运算。

摘要由CSDN通过智能技术生成

一、背景：

创建一个计算某一行业的证券在某一个交易日收益排名的视图：

CREATE OR REPLACE VIEW v_ranking AS 
WITH RANKING_TAB AS (
SELECT
	code /*证券代码*/
	, trade_date/*日期*/
	, yield /*收益率*/
	, ROW_NUMBER() OVER(PARTITION BY trade_date ORDER BY yield DESC) AS ranking /*排名*/
FROM
	yield_table
WHERE
	code IS NOT NULL
)
SELECT
	rt.code/*证券代码*/
	, rt.trade_date/*日期*/
	, rt.ranking /*排名*/
	, TO_NUMBER( DECODE((SELECT count(*) FROM ranking_tab rt0 WHERE rt0.trade_date = rt.trade_date), 0, 0, rt.ranking / (SELECT count(*) FROM ranking_tab rt0 WHERE rt0.trade_date = rt.trade_date ) * 100)) AS fractile /*排名分位数*/
FROM
	ranking_tab rt;

视图使用：
查询’000001.SZ’股票在’20230528’这一天的涨跌幅排名：

select * from v_ranking v WHERE v.trade_date = '20230528' and v.code = '000001.SZ';

以上使用方式，首先会运行v_ranking视图的完整逻辑，将yield_table表中的所有日期的数据进行分组排序，参与聚合运算的数据量巨大：假设，每一天的股票数量是6000条，该表中存了100天的数据量，那么该表中就有600000条数据进行聚合运算；随着时间的增长，每天都会增加6000条数据，该视图的速度回越来越慢。

二、提高效率思路：

如果以上视图逻辑中能够准确查询并聚合运算指定一天的证券数据，那么每次运行的数据量是6000；
目标的运行逻辑是这样的：添加一个查询条件AND trade_date = '20230528' /*这里是目标的查询条件*/

WITH RANKING_TAB AS (
SELECT
	code /*证券代码*/
	, trade_date/*日期*/
	, yield /*收益率*/
	, ROW_NUMBER() OVER(PARTITION BY trade_date ORDER BY yield DESC) AS ranking /*排名*/
FROM
	yield_table
WHERE
	code IS NOT NULL
	AND trade_date = '20230528' /*这里是目标的查询条件*/
)
SELECT
	rt.code/*证券代码*/
	, rt.trade_date/*日期*/
	, rt.ranking /*排名*/
	, TO_NUMBER( DECODE((SELECT count(*) FROM ranking_tab rt0 WHERE rt0.trade_date = rt.trade_date), 0, 0, rt.ranking / (SELECT count(*) FROM ranking_tab rt0 WHERE rt0.trade_date = rt.trade_date ) * 100)) AS fractile /*排名分位数*/
FROM
	ranking_tab rt;

但是，pgsql的视图逻辑无法实现入参，只能运行固定的逻辑。

三、使用FUNCTION函数返回值类型为TABLE的方式实现：

函数返回聚合计算完成的结果集，并且结果集为table结构

CREATE OR REPLACE
FUNCTION func_get_ranking ( p_query_date VARCHAR ) /*入参为需要查询的日期*/
-- 定义返回值类型：table
RETURNS TABLE (
		code VARCHAR
,
		trade_date VARCHAR
,
		ranking bigint
,
		fractile NUMERIC
		) 
		LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$
BEGIN
		RETURN QUERY
-- 字段一一映射
WITH RANKING_TAB AS (
SELECT
	code /*证券代码*/
	, trade_date/*日期*/
	, yield /*收益率*/
	, ROW_NUMBER() OVER(PARTITION BY trade_date ORDER BY yield DESC) AS ranking /*排名*/
FROM
	yield_table
WHERE
	code IS NOT NULL
	AND trade_date = p_query_date /*指定查询某一天的数据，并且进行聚合运行*/
)
SELECT
	rt.code/*证券代码*/
	, rt.trade_date/*日期*/
	, rt.ranking /*排名*/
	, TO_NUMBER( DECODE((SELECT count(*) FROM ranking_tab rt0 WHERE rt0.trade_date = rt.trade_date), 0, 0, rt.ranking / (SELECT count(*) FROM ranking_tab rt0 WHERE rt0.trade_date = rt.trade_date ) * 100)) AS fractile /*排名分位数*/
FROM
	ranking_tab rt;
END;
$function$;

函数使用：

select * from func_get_ranking('20230510');

运行结构返回和表查询一样：

 code   | trade_date |ranking | fractile
 -------------------------------------
000001.SZ 20230510	1	3.22580645161290303
000003.SZ 20230510	2	6.45161290322580605
000005.SZ 20230510	3	9.67741935483870996
000007.SZ 20230510	4	12.9032258064516121
000002.SZ 20230510	5	16.129032258064516
000066.SZ 20230510	6	19.3548387096774199
000043.SZ 20230510	7	22.5806451612903203
000046.SZ 20230510	8	25.8064516129032242
000987.SZ 20230510	9	29.0322580645161317
002345.SZ 20230510	10	32.258064516129032
000056.SZ 20230510	11	35.4838709677419359
000088.SZ 20230510	12	38.7096774193548399