Nest new column into a Select query

Question

I have the following table that I cannot edit called connector_meter_value:

+----------------+---------------------+-------------+------+
| transaction_pk | value_timestamp     | value       | unit |
+----------------+---------------------+-------------+------+
|              1 | 2020-01-24 11:50:19 | 1690.100    | kWh  |
+----------------+---------------------+-------------+------+
|              1 | 2020-01-24 12:00:01 | 1691260.000 | Wh   |
+----------------+---------------------+-------------+------+
|              1 | 2020-01-24 12:00:01 | 7382.430    | W    |
+----------------+---------------------+-------------+------+
|              1 | 2020-01-24 12:30:00 | 1694940.000 | Wh   |
+----------------+---------------------+-------------+------+
|              1 | 2020-01-24 12:30:00 | 7385.140    | W    |
+----------------+---------------------+-------------+------+
|              2 | 2020-01-30 20:18:29 | 1701.680    | kWh  |
+----------------+---------------------+-------------+------+
|              2 | 2020-01-30 20:30:01 | 1703070.000 | Wh   |
+----------------+---------------------+-------------+------+
|              2 | 2020-01-30 20:30:01 | 7424.300    | W    |
+----------------+---------------------+-------------+------+
|              2 | 2020-01-30 21:00:00 | 1706660.000 | Wh   |
+----------------+---------------------+-------------+------+

I would like to create the following view that gives me the difference between value per transaction_pk (cum_kWh), but first of all creates a new column that edits the value based on the unit (kWh). I also need to remove rows, such as unit = 'W'

+----------------+---------------------+-------------+------+----------+---------+
| transaction_pk | value_timestamp     | value       | unit | kWh      | cum_kWh |
+----------------+---------------------+-------------+------+----------+---------+
|              1 | 2020-01-24 11:50:19 | 1690.100    | kWh  | 1690.100 | 0       |
+----------------+---------------------+-------------+------+----------+---------+
|              1 | 2020-01-24 12:00:01 | 1691260.000 | Wh   | 1691.26  | 1.16    |
+----------------+---------------------+-------------+------+----------+---------+
|              1 | 2020-01-24 12:30:00 | 1694940.000 | Wh   | 1694.94  | 3.68    |
+----------------+---------------------+-------------+------+----------+---------+
|              2 | 2020-01-30 20:18:29 | 1701.680    | kWh  | 1701.680 | 0       |
+----------------+---------------------+-------------+------+----------+---------+
|              2 | 2020-01-30 20:30:01 | 1703070.000 | Wh   | 1703.07  | 2.39    |
+----------------+---------------------+-------------+------+----------+---------+
|              2 | 2020-01-30 21:00:00 | 1706660.000 | Wh   | 1706.66  | 3.59    |
+----------------+---------------------+-------------+------+----------+---------+

the kWh column takes the Wh value and divides by 1,000 to create a kWh value that can then be used in the cum_kWh column calculation.

I used the following code when i could edit the connector_meter_value table (i actually duplicated it and edited the duplicated table (which i called meter_value_kWh))

CREATE VIEW kWhcharging AS
select t.*,
   COALESCE(kWh - (select kWh
              from meter_value_kWh t2
              where t2.value_timestamp < t.value_timestamp AND t2.transaction_pk = t.transaction_pk
              order by t2.value_timestamp DESC
              limit 1
             ),0
   ) as cum_kWh
from meter_value_kWh t
WHERE value <>0 and t.transaction_pk <>'NULL' and unit<>"W"

This worked very well, but because the duplicated table doesn't automatically refresh when the original table does i've tried to create a view of the original table.

I'm now trying to insert a 'kWh' column into the query that is then used to calculate the cum_kWh column, but i can't figure out where to put the line. The starred section below is what I have added

select t.*,
   COALESCE(**select IF(unit = 'Wh', value/1000, value) as kWh
            From stevedb.connector_meter_value k1** - 
                   (select k1.kWh
                   FROM stevedb.connector_meter_value t2
                   where t2.value_timestamp < t.value_timestamp AND t2.transaction_pk = 
t.transaction_pk
                   order by t2.value_timestamp DESC
                   limit 1
                ),0
   ) as cum_kWh
from connector_meter_value t
WHERE t.value <>0 and t.transaction_pk <>'NULL' and t.unit<>"W"

Answer 1

Not sure what the problem is on how you add a new column to your view, as you already have added the new column cum_kWh to your view. When you want to have the value IF(unit = 'Wh', value/1000, value) as a new column you simply add it to your view the same way:

CREATE VIEW kWhcharging AS
SELECT
    t.transaction_pk,  -- |
    t.value_timestamp, -- | The columns from the original table
    t.value,           -- |
    t.unit,            -- |
    IF(unit = 'Wh', value/1000, value) as kWh, -- The new normalized column 'kWh'
    COALESCE(IF(unit = 'Wh', value/1000, value) - (select
                IF(unit = 'Wh', value/1000, value)
            from connector_meter_value t2
            where
                t2.value_timestamp < t.value_timestamp AND
                t2.transaction_pk = t.transaction_pk AND
                t2.unit != 'W'
            order by
                t2.value_timestamp desc
            limit 1
           )
       , 0) as cum_kWh -- The cumulative calculation
FROM
    connector_meter_value t
WHERE
    unit != 'W'

You can also create two views. One view with the additional normalized column kWh and the second view, which uses the first view, to calculate the cum_kWh value, but has access to the new kWh column from the first view.

This will generate the following result table:

+----------------+---------------------+---------+------+--------------------+--------------------+
| transaction_pk | value_timestamp     | value   | unit | kWh                | cum_kWh            |
+----------------+---------------------+---------+------+--------------------+--------------------+
|              1 | 2020-01-24 11:50:19 |  1690.1 | kWh  | 1690.0999755859375 |                  0 |
|              1 | 2020-01-24 12:00:01 | 1691260 | Wh   |            1691.26 |  1.160024414062491 |
|              1 | 2020-01-24 12:30:00 | 1694940 | Wh   |            1694.94 | 3.6800000000000637 |
|              2 | 2020-01-30 20:18:29 | 1701.68 | kWh  | 1701.6800537109375 |                  0 |
|              2 | 2020-01-30 20:30:01 | 1703070 | Wh   |            1703.07 | 1.3899462890624363 |
|              2 | 2020-01-30 21:00:00 | 1706660 | Wh   |            1706.66 | 3.5900000000001455 |
+----------------+---------------------+---------+------+--------------------+--------------------+

Answer 2

As already provided you the Reference. But for your problem you can use two user defined variable.

• First set both variable to zero.
• Now if transaction_pk is same for current and old record then find the diff between value column, if not set it to zero.
• Set both variable to current row data, so can be used in next row.
• It works on order by clause that is must to make sure it works fine.

Note: this solution is only for mysql lower version than 8. If mysql 8 use window function LAG for the same.

SELECT
    transaction_pk,
    value_timestamp,
    `value`,
    unit,
    ROUND(IF( transaction_pk = @old_tran, IF(unit = 'kWh', `value`, `value` / 1000) - @old_value, 0), 3) AS cum_kwh,
   @old_value:= ROUND(IF(unit = 'kWh' && @old_tran:= transaction_pk, `value`, `value` / 1000), 3)  AS Kwh
FROM connector_meter_value,
(SELECT @old_tran:= 0 AS tran, @old_value:= 0 AS o_val) AS b
WHERE unit <> 'W'
ORDER BY transaction_pk, value_timestamp

See the working Fiddle for the same.