Oracle 计算同比环比

需求：写一个存储过程，实现存储日期，销售渠道，当前订单金额，上年同期订单金额，上月同期订单金额，按日期和销售渠道分组，并记录日志

前提：日期字段类型均为INTEGER，格式为 YYYYMMDD

CREATE OR REPLACE PROCEDURE DMR_sales_report_0102 AS
  --定义全局变量，当前日期，上年同期，上月同期
  cur_date INTEGER := TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD'));
  last_year_date INTEGER := TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE, -12),'YYYYMMDD'));
  last_month_date INTEGER := TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE, -1),'YYYYMMDD'));
BEGIN
  -- 插入查询结果到报告表中
  INSERT INTO sales_report_0102 (
    report_date, --日期
    sales_channel,  --销售渠道
    cur_order_amount,  --当前订单金额
    last_year_order_amount,  --上年同期订单金额
    last_month_order_amount  --上月同期订单金额
    )
    --当期
  SELECT
    order_date AS report_date,
    sales_channel,
    SUM(order_amount) AS cur_order_amount,
    0 AS last_year_order_amount,
    0 AS last_month_order_amount
  FROM
    orders
  WHERE order_date = cur_date
  GROUP BY
    order_date,
    sales_channel
 UNION ALL
 --上年同期
 SELECT
    order_date AS report_date,
    sales_channel,
    0 AS cur_order_amount,
    SUM(order_amount) AS last_year_order_amount,
    0 AS last_month_order_amount
  FROM
    orders
  WHERE order_date = last_year_date
  GROUP BY
    order_date,
    sales_channel
 UNION ALL
 --上月同期
 SELECT
    order_date AS report_date,
    sales_channel,
    0 AS cur_order_amount,
    0 AS last_year_order_amount,
    SUM(order_amount) AS last_month_order_amount
  FROM
    orders
  WHERE order_date = last_month_date
  GROUP BY
    order_date,
    sales_channel;

COMMIT;
    
  -- 记录日志
  INSERT INTO log_table (log_date, operation) VALUES (SYSDATE, 'Sales report generated successfully.');
  
  --结束存储过程
  DBMS_OUTPUT.PUT_LINE('Sales report generated successfully.');
EXCEPTION
  WHEN OTHERS THEN
    -- 记录日志并向用户返回错误消息
    INSERT INTO log_table (log_date, operation) VALUES (SYSDATE, 'Sales report failed: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Sales report failed: ' || SQLERRM);
END;

使用以下语句来运行此存储过程： 

--执行存储过程
EXECUTE DMR_sales_report_0102;

 由存过加工出当期，上年同期，上月同期的订单金额后，可以拿来计算同比和环比指标了

• 同比：（当期/上年同期 -1）*100%
• 环比：（当期/上月同期 -1）*100%

SELECT
    cur_order_amount,  --当期
    CONCAT(ROUND((cur_order_amount/last_year_order_amount - 1) *100, 2), '%') AS LY_ORDER_RATIO,  --同比
    CONCAT(ROUND((cur_order_amount/last_month_order_amount - 1) *100, 2), '%') AS LM_ORDER_RATIO  --环比
FROM sales_report_0102
WHERE report_date = 20231203

 如果不写存储过程，并且日期类型为DATE的话，可以参考下面的SQL加工得到当期，上年同期和上月同期的值

SELECT
  TO_CHAR(order_date, 'YYYY-MM-DD') AS order_date,
  sales_channel,
  SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM SYSDATE) THEN order_amount ELSE 0 END) AS cur_order_amount,
  SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -12)) THEN order_amount ELSE 0 END) AS last_year_order_amount,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = EXTRACT(MONTH FROM ADD_MONTHS(SYSDATE,-1)) AND EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM SYSDATE) THEN order_amount ELSE 0 END) AS last_month_order_amount
FROM
  orders
GROUP BY
  TO_CHAR(order_date, 'YYYY-MM-DD'),
  sales_channel;

然后再套用同比环比计算公式求取指标。