I have two tables:
Meter (condensed)
id Meter_Id Date Time Consumption 1 1 2016-01-01 0.5 0.0769 2 1 2016-01-01 1.0 0.0649 3 1 2016-01-01 1.5 0.0379 4 1 2016-01-01 2.0 0.0439 5 1 2016-01-01 2.5 0.045 6 1 2016-01-01 3.0 0.034 7 1 2016-01-01 3.5 0.0419 8 1 2016-01-01 4.0 0.0399 9 1 2016-01-01 4.5 0.0329 10 1 2016-01-01 5.0 0.045 11 1 2016-01-01 5.5 0.035 12 1 2016-01-01 6.0 0.035 13 1 2016-01-01 6.5 0.0439 14 1 2016-01-01 7.0 0.0309 15 1 2016-01-01 7.5 0.039 16 1 2016-01-01 8.0 0.041 17 1 2016-01-01 8.5 0.032 18 1 2016-01-01 9.0 0.0399 19 1 2016-01-01 9.5 0.039 20 1 2016-01-01 10.0 0.032 21 1 2016-01-01 10.5 0.039 22 1 2016-01-01 11.0 0.0399 23 1 2016-01-01 11.5 0.032 24 1 2016-01-01 12.0 0.041 25 1 2016-01-01 12.5 0.039 26 1 2016-01-01 13.0 0.032 27 1 2016-01-01 13.5 0.676 28 1 2016-01-01 14.0 0.0379 29 1 2016-01-01 14.5 0.0329 30 1 2016-01-01 15.0 0.0419 31 1 2016-01-01 15.5 0.0379 32 1 2016-01-01 16.0 0.032 33 1 2016-01-01 16.5 0.046 34 1 2016-01-01 17.0 0.035 35 1 2016-01-01 17.5 0.037 36 1 2016-01-01 18.0 0.0469 37 1 2016-01-01 18.5 0.032 38 1 2016-01-01 19.0 0.0399 39 1 2016-01-01 19.5 0.041 40 1 2016-01-01 20.0 0.0309 41 1 2016-01-01 20.5 0.043 42 1 2016-01-01 21.0 0.037 43 1 2016-01-01 21.5 0.0329 44 1 2016-01-01 22.0 0.0439 45 1 2016-01-01 22.5 0.0329 46 1 2016-01-01 23.0 0.034 47 1 2016-01-01 23.5 0.043 48 1 2016-01-01 24.0 0.032 49 1 2016-01-02 0.5 0.032 50 1 2016-01-02 1.0 0.0439 51 1 2016-01-02 1.5 0.0309 52 1 2016-01-02 2.0 0.034 53 1 2016-01-02 2.5 0.0419 54 1 2016-01-02 3.0 0.0309 55 1 2016-01-02 3.5 0.035 56 1 2016-01-02 4.0 0.041 57 1 2016-01-02 4.5 0.0299 58 1 2016-01-02 5.0 0.3049 59 1 2016-01-02 5.5 0.446 60 1 2016-01-02 6.0 0.0299 61 1 2016-01-02 6.5 0.0299 62 1 2016-01-02 7.0 0.0419 63 1 2016-01-02 7.5 0.0329 64 1 2016-01-02 8.0 0.0299 65 1 2016-01-02 8.5 0.037 66 1 2016-01-02 9.0 0.037 67 1 2016-01-02 9.5 0.0309 68 1 2016-01-02 10.0 0.0299 69 1 2016-01-02 10.5 0.0399 70 1 2016-01-02 11.0 0.035 71 1 2016-01-02 11.5 0.0299 72 1 2016-01-02 12.0 0.037 73 1 2016-01-02 12.5 0.039 74 1 2016-01-02 13.0 0.0309 75 1 2016-01-02 13.5 0.0309 76 1 2016-01-02 14.0 0.0419 77 1 2016-01-02 14.5 0.0359 78 1 2016-01-02 15.0 0.0309 79 1 2016-01-02 15.5 0.0399 80 1 2016-01-02 16.0 0.037 81 1 2016-01-02 16.5 0.032 82 1 2016-01-02 17.0 0.0379 83 1 2016-01-02 17.5 0.041 84 1 2016-01-02 18.0 0.032 85 1 2016-01-02 18.5 0.037 86 1 2016-01-02 19.0 0.041 87 1 2016-01-02 19.5 0.0309 88 1 2016-01-02 20.0 0.039 89 1 2016-01-02 20.5 0.048 90 1 2016-01-02 21.0 0.074 91 1 2016-01-02 21.5 0.7059 92 1 2016-01-02 22.0 0.046 93 1 2016-01-02 22.5 0.0329 94 1 2016-01-02 23.0 0.035 95 1 2016-01-02 23.5 0.0399 96 1 2016-01-02 24.0 0.0329 97 1 2016-01-03 0.5 0.0299 ...
Tariff
ID CHARGE 1 13.38
I’m trying to create a query on the consumption with some conditions:
- It must only look at consumption values between “peak hours” (15, 15.5, 16, …, 19.5, 20, 20.5).
- For each day, find the highest (MAX) value and multiply by the tariff
CHARGE. Call thisPeak_Charge. - SUM the
Peak_Chargevalues for each day into aTotal_Peak_Chargevalue.
e.g. In the above tables, the MAX of day 2016-01-01 in peak hours is 0.0469 and day 2016-01-02 is 0.048. With these, I need to return a Total_Peak_Charge value that is equal to (0.0469 * 13.38) + (0.048 * 13.38) = 1.269762.
Advertisement
Answer
In your question you don’t clarify how Meter is related to Tariff.
If the columns Meter.meter_id and Tariff.id are related then join the tables, aggregate and then use window function sum():
select distinct t.charge * sum(max(m.Consumption)) over () Total_Peak_Charge from Meter m inner join Tariff t on t.id = m.meter_id where m.Time between 15.0 and 20.5 group by m.Date
If the 2 tables are unrelated and Tariff contains only 1 row, then:
select distinct (select charge from Tariff) * sum(max(Consumption)) over () Total_Peak_Charge from Meter where Time between 15.0 and 20.5 group by Date;
See the demo.
Results:
| Total_Peak_Charge | | ----------------- | | 1.269762 |