将每月成本填充到每日记录

Question

我有 3 列（日期、标志、成本）日期从年初开始，旗帜是每天或每月和费用。

对于每日值，这很好。对于每月值，我想将整个月标记的值相加，然后除以该月的天数。结果率，填充整个月份

Date      Flag      Cost
1/1/2014        
1/2/2014  DAILY     10
1/3/2014  DAILY     15
1/4/2014  DAILY     56
1/5/2014  DAILY     22
1/6/2014  DAILY     32
1/7/2014        
1/8/2014  MONTHLY   3500
1/9/2014        
1/10/2014

结果应该是

---

Date        Cost
1/1/2014    112.9032258 
1/2/2014    122.9032258 
1/3/2014    127.9032258 
1/4/2014    168.9032258 
1/5/2014    134.9032258 
1/6/2014    144.9032258 
1/7/2014    112.9032258 
1/8/2014    112.9032258 
1/9/2014    112.9032258 
1/10/2014   112.9032258 
          .
          .
          .
1/30/2014   112.9032258 
1/31/2014   112.9032258

Answer 1

如果我理解得很好，这应该给你"每月"值的平均值：

SELECT "Cost" / EXTRACT(DAY FROM LAST_DAY("Date")) "cost_per_day",
       LAST_DAY("Date") "month"
FROM T
WHERE "Flag" = 'MONTHLY'

一旦有了它，你的最终查询可以写成：

WITH monthly AS (
    SELECT "Cost" / EXTRACT(DAY FROM LAST_DAY("Date")) "cost_per_day",
           LAST_DAY("Date") "month"
    FROM T
    WHERE "Flag" = 'MONTHLY'
)
SELECT T."Date", NVL("Cost",0) + NVL("cost_per_day",0) "cost"
FROM T FULL JOIN monthly ON LAST_DAY(T."Date") = "month"
WHERE T."Flag" = 'DAILY'
ORDER BY T."Date";

请参阅 http://sqlfiddle.com/#!4/cea34/14

至于获取"每月全天"，这已经回答了好几次（oracle sql查询列出上个月的所有日期，使用SQL生成一系列日期）

Answer 2

这个解决方案是怎么回事？

SELECT THE_DATE, Flag, COST, 
    CASE Flag
    WHEN 'DAILY' THEN COST
    WHEN 'MONTHLY' THEN
        COST/EXTRACT(DAY FROM LAST_DAY(THE_DATE))
    ELSE NULL
    END AS AVG_COST
FROM THE_TABLE;

Answer 3

首先，让我们看一下行生成器，以获取该月所有日期的列表，包括每月每天平均分配的相同数量的 COST 存储桶。

我硬编码值"3500/3"只是为了便于演示。在原始查询中，只需与表联接并从表中获取 **MONTHLY" 值。

SQL> WITH DATA AS
  2    (SELECT to_date('01/01/2014', 'DD/MM/YYYY') date1,
  3      to_date('31/01/2014', 'DD/MM/YYYY') date2
  4    FROM dual
  5    )
  6  SELECT TO_CHAR(date1+level-1, 'DD/MM/YYYY') the_date,
  7    3500/31 AS "cost"
  8  FROM data
  9    CONNECT BY LEVEL <= date2-date1+1
 10  /
THE_DATE         cost
---------- ----------
01/01/2014 112.903226
02/01/2014 112.903226
03/01/2014 112.903226
04/01/2014 112.903226
05/01/2014 112.903226
06/01/2014 112.903226
07/01/2014 112.903226
08/01/2014 112.903226
09/01/2014 112.903226
10/01/2014 112.903226
11/01/2014 112.903226
12/01/2014 112.903226
13/01/2014 112.903226
14/01/2014 112.903226
15/01/2014 112.903226
16/01/2014 112.903226
17/01/2014 112.903226
18/01/2014 112.903226
19/01/2014 112.903226
20/01/2014 112.903226
21/01/2014 112.903226
22/01/2014 112.903226
23/01/2014 112.903226
24/01/2014 112.903226
25/01/2014 112.903226
26/01/2014 112.903226
27/01/2014 112.903226
28/01/2014 112.903226
29/01/2014 112.903226
30/01/2014 112.903226
31/01/2014 112.903226
31 rows selected.
SQL>

您需要做的就是将 nvl（"成本"， 0） 添加到上面的成本中。您可以对表进行外部连接，并为那些具有"cost"值的行添加 nvl（"cost"， 0）。

Answer 4

此请求将仅对表中已存在的行执行此操作

SELECT TO_CHAR(D.DAY, 'MM/DD/YYYY') "Date",
       NVL(D.COST, 0) + M.COST / EXTRACT(DAY FROM LAST_DAY(M.DAY)) "Cost"
    FROM SO28292226 D
    JOIN SO28292226 M ON EXTRACT(MONTH FROM M.DAY) = EXTRACT(MONTH FROM D.DAY)
    WHERE NVL(D.FLAG, 'DAILY') = 'DAILY'
        AND M.FLAG = 'MONTHLY';