基于称重的周分割

Question

我在一张桌子上定义了一个与下面类似的重量。

DayNum | Day | Weight | Cumulative Weight
1      | MON | 0.3    | 0.3
2      | TUE | 0.15   | 0.45 (Sum of Mon and Tues)
3      | WED | 0.1    | 0.55 (Sum of Mon and Tues and Wed)
4      | THU | 0.1    | 0.65
5      | FRI | 0.15   | 0.8
6      | SAT | 0.2    | 1

我在另一个表格中定义了每周一次的金额(星期一-太阳)，如下所示。

Item | Date       | Amount
A    | 30-May-16  | 10  ---- Week in May and June
A    | 6-Jun-16   | 20
A    | 13-Jun-16  | 30  and so on
A    | 27-Jun-16  | 60  ---- Week in Jun and July

现在，我想插入到另一个表格的每日水平，这周是重叠的两个不同的月份(在上面的例子-5月30日至6月5日)。有人能解释我如何在Oracle中实现这一点吗？

输出应如下所示。

Item | Date       | Amount
A    | 30-May-16  | 4.5     (2 days from May which are Mon and Tues - so calculation is 10 * 0.45)
A    | 1-Jun-16   | 5.5     (5 days from May which is the rest of the week - 10 minus 4.5)
A    | 6-Jun-16   | 20  and so on
A    | 27-Jun-16  | 39      (4 days from June which are Mon till Thurs - so calculation is 60 * 0.65)
A    | 1-Jul-16   | 21      (3 days from July which is the rest of the week - 60 minus 39)

Answer 1

尝试：

WITH some_data AS(
    select a.*,
           trunc( trunc( add_months( "DATE", 1 ), 'MM' ) - "DATE" ) 
                        As days_to_end_of_month,
           trunc( add_months( "DATE", 1 ), 'MM' ) 
                        As start_of_next_month
    from amounts a
), some_other_data AS (
      SELECT some_data.*,
             CASE WHEN days_to_end_of_month >= 6 THEN Amount
                  ELSE ( SELECT some_data.amount * "Cumulative Weight" FROM Weights w
                         WHERE some_data.days_to_end_of_month = DayNum )
             END as new_Amount
      FROM some_data
)
SELECT Item, "DATE", New_Amount as amount
FROM some_other_data
UNION ALL
SELECT Item, start_of_next_month, amount-new_amount
FROM some_other_data
WHERE days_to_end_of_month < 6
ORDER BY "DATE"
;

Answer 2

Oracle安装

CREATE TABLE Weights ( DayNum, Day, Weight ) AS
SELECT 1, 'MON', 0.3  FROM DUAL UNION ALL
SELECT 2, 'TUE', 0.15 FROM DUAL UNION ALL
SELECT 3, 'WED', 0.1  FROM DUAL UNION ALL
SELECT 4, 'THU', 0.1  FROM DUAL UNION ALL
SELECT 5, 'FRI', 0.15 FROM DUAL UNION ALL
SELECT 6, 'SAT', 0.2  FROM DUAL;

CREATE TABLE weekly_levels ( Item, "Date", Amount ) AS
SELECT 'A', DATE '2016-05-30', 10 FROM DUAL UNION ALL
SELECT 'A', DATE '2016-06-06', 20 FROM DUAL UNION ALL
SELECT 'A', DATE '2016-06-13', 30 FROM DUAL UNION ALL
SELECT 'A', DATE '2016-06-27', 60 FROM DUAL;

查询

SELECT  item,
        start_date,
        SUM( amount * weight ) AS amount
FROM    (
          SELECT item,
                 "Date" AS start_date,
                 LEAST( "Date" + INTERVAL '6' DAY, LAST_DAY( "Date" ) ) AS end_date,
                 amount
          FROM   weekly_levels
          UNION
          SELECT item,
                 GREATEST( "Date", TRUNC( "Date" + INTERVAL '6' DAY, 'MM' ) ) AS start_date,
                 "Date" + INTERVAL '6' DAY AS end_date,
                 amount
          FROM   weekly_levels
        ) d
        INNER JOIN
        Weights w
        ON ( w.DayNum BETWEEN TO_CHAR( start_date, 'D' )
                          AND TO_CHAR( end_date,   'D' ) )
GROUP BY item, start_date
ORDER BY item, start_date;

输出

ITEM START_DATE              AMOUNT
---- ------------------- ----------
A    2016-05-30 00:00:00        4.5 
A    2016-06-01 00:00:00        5.5 
A    2016-06-06 00:00:00         20 
A    2016-06-13 00:00:00         30 
A    2016-06-27 00:00:00         39 
A    2016-07-01 00:00:00         21