时间戳

首页 > 解决方案 > Oracle SQL 显示将一个变量与另一个变量相除的结果

oracle - Oracle SQL 显示将一个变量与另一个变量相除的结果

问题描述

我需要计算GROSS_AMOUNT的 YTD每周平均值

在下面的脚本中，代码的第一部分获取周数。代码的第二部分获取同一时间段的 YTD 总和

如何编写 SQL以便检索 YtdTotal/TotalWeeks 的实际值？

从 DUAL 中选择 YtdTotal/TotalWeeks；不工作

-- STORES NUMBER OF WEEKS SINCE BEGINING OF YEAR INTO THE VARIABLE, TotalWeeks
DECLARE
    TotalWeeks NUMBER;
BEGIN
    SELECT to_number(to_char(sysdate, 'WW')) - to_number(to_char(trunc(sysdate, 'year'),'WW'))
    INTO TotalWeeks
    FROM DUAL
    -- (retrieves number of weeks since beginning of year)
END

-- STORES THE SUM OF GROSS)AMOUNT FOR THE WEEKS CALCULATED ABOVE - INTO THE 
DECLARE
    VARIABLE, YtdTotal
BEGIN
    SELECT SUM(GROSS_AMOUNT) 
    INTO YtdTotal
    FROM PARENTS
    WHERE process_date BETWEEN
    (next_day(TRUNC(sysdate, 'year'),'SUN'))
    AND
    (next_day(TRUNC(sysdate),'SAT')-7);
END;

标签： oraclevariablesdivision

解决方案

不要使用两个单独的匿名 PL/SQL 块 - 将它们组合为一个！

如果您想返回结果，那么 - 而不是匿名 PL/SQL 块 - 创建一个函数。

CREATE OR REPLACE FUNCTION f_test
  return NUMBER
IS
   totalweeks  NUMBER;
   ytdtotal    NUMBER;
   result      NUMBER;
BEGIN
   -- STORES NUMBER OF WEEKS SINCE BEGINING OF YEAR INTO THE VARIABLE, TotalWeeks
   SELECT   TO_NUMBER (TO_CHAR (SYSDATE, 'WW'))
          - TO_NUMBER (TO_CHAR (TRUNC (SYSDATE, 'year'), 'WW'))
     INTO totalweeks
     FROM DUAL;

   -- (retrieves number of weeks since beginning of year)

   -- STORES THE SUM OF GROSS)AMOUNT FOR THE WEEKS CALCULATED ABOVE - INTO THE
   SELECT SUM (gross_amount)
     INTO ytdtotal
     FROM parents
    WHERE process_date BETWEEN (NEXT_DAY (TRUNC (SYSDATE, 'year'), 'SUN'))
                           AND (NEXT_DAY (TRUNC (SYSDATE), 'SAT') - 7);

   -- the final result
   result := ytdtogal / totalweeks;
   RETURN result;
END;
/

用它作为

select f_test from dual;

推荐阅读