高效使用窗口函数计算自定义规则数据

最新推荐文章于 2024-10-04 22:00:00 发布

baidu_37721054

最新推荐文章于 2024-10-04 22:00:00 发布

阅读量272

点赞数 4

文章标签： sql

本文链接：https://blog.csdn.net/baidu_37721054/article/details/137239693

版权

项目实战

--当期
sum(COALESCE($字段名称$::NUMERIC(17,10),0.0)) filter(where DATADATE::INTEGER=replace($日期$||'','-','')::integer) 当日数据,
sum(COALESCE($字段名称$::NUMERIC(17,10),0.0)) filter(where DATADATE::INTEGER/100=replace($日期$||'','-','')::integer/100 and DATADATE::INTEGER<=replace($日期$||'','-','')::integer) 当月数据,
sum(COALESCE($字段名称$::NUMERIC(17,10),0.0)) filter(where DATADATE::INTEGER/10000=replace($日期$||'','-','')::integer/10000 and DATADATE::INTEGER<=replace($日期$||'','-','')::integer) 当年数据,

--同期
sum(COALESCE($字段名称$::NUMERIC(17,10),0.0)) filter(where DATADATE::INTEGER=replace($日期$||'','-','')::integer-10000) 日同期,
sum(COALESCE($字段名称$::NUMERIC(17,10),0.0)) filter(where (DATADATE::INTEGER)/100=(replace($日期$||'','-','')::integer-10000)/100 and (DATADATE::INTEGER)<=(replace($日期$||'','-','')::integer-10000)) 月同期,
sum(COALESCE($字段名称$::NUMERIC(17,10),0.0)) filter(where (DATADATE::INTEGER)/10000=(replace($日期$||'','-','')::integer-10000)/10000 and (DATADATE::INTEGER)<=(replace($日期$||'','-','')::integer-10000)) 年同期,

--自定义同期
sum(COALESCE($字段名称$::NUMERIC(17,10),0.0)) filter(where DATADATE::INTEGER=(20220000 + mod(replace(replace(''||$日期$,'-','')||'','-','')::integer,10000))) 22年日同期,
sum(COALESCE($字段名称$::NUMERIC(17,10),0.0)) filter(where (DATADATE::INTEGER)/100=(20220000 + mod(replace(replace(''||$日期$,'-','')||'','-','')::integer,10000))/100 and (DATADATE::INTEGER)<=(20220000 + mod(replace(replace(''||$日期$,'-','')||'','-','')::integer,10000))) 22年月同期,
sum(COALESCE($字段名称$::NUMERIC(17,10),0.0)) filter(where (DATADATE::INTEGER)/10000=(20220000 + mod(replace(replace(''||$日期$,'-','')||'','-','')::integer,10000))/10000 and (DATADATE::INTEGER)<=(20220000 + mod(replace(replace(''||$日期$,'-','')||'','-','')::integer,10000))) 22年年同期,

窗口函数

SELECT column1,
column2,
SUM(column3) OVER (PARTITION BY column1 ORDER BY column2 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_column
FROM table_name
WHERE condition

FILTER子句
SELECT column1,
column2,
SUM(column3) FILTER (WHERE condition) OVER (PARTITION BY column1 ORDER BY column2 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_column
FROM table_name