用SQL计算电费表

Question

我有以下模式的表“阅读”：

----------------------------------------
|  Id  |        DateTime     |  value  |
---------------------------------------
|  16  |  01/01/19  22:50:59 |    90   | 
---------------------------------------
|  16  |  01/02/19  8:53:11  |    200  | 
---------------------------------------
|  16  |  04/01/19  22:50:59 |    400  |  
---------------------------------------
|  16  |  03/01/19  1:20:00  |    100  |
---------------------------------------

另一个表“关税”的模式如下：

-------------------------------------------------
|  Id  |   start_value  | end_value |    rate   |
-------------------------------------------------
|  16  |        0       |    100    |    0.2    | 
-------------------------------------------------
|  16  |       100      |    500    |    0.25   | 
------------------------------------------------
|  16  |       500      |    1000   |    0.3    |   
-------------------------------------------------

我想以总成本为例:根据关税表，数值之和为= 90 + 200 + 400 + 100 = 790，前100的总和乘以0.2，其余的400乘以0.25……

Then the cost = (100 * 0.2) + (400 * 0.25) + (290 * 0.3) = 207

Answer 1

我认为这是一个join和聚合：

select sum(value * rate)
from readings r join
     tariffs t
     on r.id = t.id and
        r.value >= t.start_value and
        r.value < t.end_value;

编辑：

哦，我明白了。您需要根据总金额计算这个值：

select sum(t.rate *
           greatest(least(sum_value, t.end_value) - greatest(sum_value, t.start_value), 0)
          )
from (select r.id, sum(value) as sum_value
      from readings r
     ) r join
     tariffs t
     on r.id = t.id 

Answer 2

最后，我通过以下查询来解决这个问题

select sum(case when sum_value - end_value >= 0 then (end_value - start_value) * rate when sum_value - start_value >= 0 then (sum_value - start_value) * rate  else 0 end)
from (
        select sum(value) as sum_value, tariffs.rate as rate, tariffs.end_value as 
        end_value, tariffs.start_value as start_value
        from readings
        join
        tariffs
        on readings.id = tariffs.id
        group by rate, end_value, start_value
     )