將執行/累計總數與靜態目標進行比較

我有一些每日數據和每月數據。

我想將這兩個數據集與 Hive 進行比較，並在actual一個月的總和超過相應月份的目標時執行支付。在上面的數據集中，id每個月的目標是 6000。支出應該為 0，除非id從每日數據中相應月份累積超過 6000。

範例：Comp1 第 1 個月的目標是 6000。當我們查看每日數據時，我們可以看到 Comp12020-01-06通過累積actual6600 達到了這個目標。此時，支出應該是：(6600-6000)/100*1。此外，Comp1 應該會在之後的每一天看到支付，直到月底，因為他們已經超過了每月目標。

這是我嘗試過的：

select
   d.yyyy_mm_dd,
   d.name,
   d.id,
   d.actual,
   case when d.actual > t.target
       then (d.actual - t.target) / 100.0
       else 0
   end payout
from monthly_targets t
inner join (
   select yyyy_mm_dd, name, id, sum(cast(actual as int)) actual
   from daily_data
   group by yyyy_mm_dd, name, id
) d
   on month(d.yyyy_mm_dd) = t.month
   and d.name = t.name
where
   d.yyyy_mm_dd >= '2020-01-01' 
   and d.name in ('Comp1', 'Comp2')

但是，這只是將每日actual價值與每月目標進行比較。這種方式永遠不會有支出，因為每月目標永遠不會在一天內達到。我該如何修改上述內容，以便計算actual當月的累積值並進行比較？

我期待這樣的輸出：

+------------+----+-------+--------+----------------+--------+
| yyyy_mm_dd | id | name  | actual | actual_to_date | payout |
+------------+----+-------+--------+----------------+--------+
| 2020-01-01 |  1 | Comp1 |   1100 |           1100 |      0 |
| 2020-01-02 |  1 | Comp1 |   1100 |           2200 |      0 |
| 2020-01-03 |  1 | Comp1 |   1100 |           3300 |      0 |
| 2020-01-04 |  1 | Comp1 |   1100 |           4400 |      0 |
| 2020-01-05 |  1 | Comp1 |   1100 |           5500 |      0 |
| 2020-01-06 |  1 | Comp1 |   1100 |           6600 |      6 |
| 2020-01-07 |  1 | Comp1 |   1100 |           7700 |     17 |
| 2020-01-08 |  1 | Comp1 |   1100 |           8800 |     28 |
| ...        |    |       |        |                |        |
| ...        |    |       |        |                |        |
| ...        |    |       |        |                |        |
| 2020-01-31 |  1 | Comp1 |   1100 |          34100 |    281 |
| 2020-02-01 |  1 | Comp1 |   1100 |           1100 |      0 |
| 2020-02-02 |  1 | Comp1 |   1100 |           2200 |      0 |
| 2020-02-03 |  1 | Comp1 |   1100 |           3300 |      0 |
+------------+----+-------+--------+----------------+--------+

我想我現在有了一個可行的解決方案。下面給出了預期的輸出。它可能會被優化一點，因為它不是最快的（只有兩家公司需要幾分鐘）。在我的真實數據集中，我有 65 家公司，所以如果可以改進，我很樂意看到它！

SELECT
   x.yyyy_mm_dd,
   x.id,
   x.name,
   x.actual,
   x.target,
   x.actual_to_date,
   CASE WHEN x.actual_to_date > x.target THEN ((x.actual_to_date - x.target) /100) * 1 ELSE 0 END AS payout
FROM(
   SELECT
       daily.yyyy_mm_dd,
       daily.id,
       daily.name,
       daily.actual,
       t.target,
       SUM(daily.actual) OVER (PARTITION BY MONTH(daily.yyyy_mm_dd), daily.id ORDER BY daily.yyyy_mm_dd RANGE UNBOUNDED PRECEDING) AS actual_to_date
   FROM(
       SELECT
           yyyy_mm_dd,
           id,
           name,
           sum(cast(actual as int)) as actual
       FROM
           daily_data_table
       WHERE
           yyyy_mm_dd >= '2020-01-01'
       GROUP BY
           1,2,3
   ) daily
   INNER JOIN
       monthly_target_table t
       ON t.id = daily.id AND t.month = month(daily.yyyy_mm_dd)
   WHERE
       daily.name = 'Comp1'
) x

引用自：https://dba.stackexchange.com/questions/258863