SQL中通过日期循环实现一次查询过去12个月末活跃案例平均金额
不需要使用PL/SQL循环,纯SQL即可实现一次跑出所有月份的结果,核心思路是先生成所有待统计的月末日期列表,再和业务表关联后分组聚合即可,示例代码如下:
-- 可按需调整统计的时间范围,这里示例统计2020年5月到2021年4月共12个月 DEFINE start_month = '01/05/20' -- 统计起始月首日,按DD/MM/YY格式填写 DEFINE end_month = '30/04/21' -- 统计结束月月末,按DD/MM/YY格式填写 WITH month_ends AS ( -- 生成时间范围内所有月份的月末日期 SELECT LAST_DAY(ADD_MONTHS(TO_DATE('&start_month', 'DD/MM/YY'), LEVEL - 1)) AS month_end FROM dual CONNECT BY LAST_DAY(ADD_MONTHS(TO_DATE('&start_month', 'DD/MM/YY'), LEVEL - 1)) <= TO_DATE('&end_month', 'DD/MM/YY') ), active_cases AS ( SELECT me.month_end, t.amount FROM month_ends me -- 注意替换your_table为实际业务表名(table是Oracle关键字,不建议直接作为表名使用) LEFT JOIN your_table t ON t.start_date <= me.month_end AND t.end_date > me.month_end ) SELECT EXTRACT(YEAR FROM month_end) AS Year, EXTRACT(MONTH FROM month_end) AS Month, AVG(amount) AS avg_amt FROM active_cases GROUP BY month_end ORDER BY Year, Month;
如果需要统计当前时间往前推12个月的动态数据,不需要手动修改起止时间,可以把month_ends部分替换为以下代码:
WITH month_ends AS ( SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -LEVEL)) AS month_end FROM dual CONNECT BY LEVEL <= 12 )
内容的提问来源于stack exchange,提问作者lynx.ts
相关产品推荐
相关产品推荐

