How can I query subtraction of two values grouped by certain categories based on timestamp min max on big query

Question

Hi I have a table like this ( just an example)

Dashboarding Category	timestamp	value
PV ENERGY	11/03/2022	113.957348
BATTERY ENERGY	11/03/2022	140.5875153
HEAT ENERGY	11/03/2022	276.9997795
TOTAL ENERGY	11/03/2022	487.9871685
PV ENERGY	14/03/2022	534.6937951
BATTERY ENERGY	14/03/2022	625.9614076
HEAT ENERGY	14/03/2022	669.2673149
TOTAL ENERGY	14/03/2022	1175.157762
PV ENERGY	19/03/2022	1352.12033
BATTERY ENERGY	19/03/2022	1747.298151
HEAT ENERGY	19/03/2022	1891.235057
TOTAL ENERGY	19/03/2022	1909.890893
PV ENERGY	20/03/2022	2118.666904
BATTERY ENERGY	20/03/2022	2335.954084
HEAT ENERGY	20/03/2022	2542.706342
TOTAL ENERGY	20/03/2022	2675.744966
PV ENERGY	21/03/2022	3513.539046
BATTERY ENERGY	21/03/2022	4464.32658
HEAT ENERGY	21/03/2022	4469.372355
TOTAL ENERGY	21/03/2022	4650.514689

And this is the result I want based on the min and maximum timestamp in the entire table and extracting the corresponding values by dashboarding category.The query result expected is below

Dashboarding Category	value(t_min)	value(t_max)	max-min_value
PV ENERGY	113.957348	3513.539046	3399.581698
BATTERY ENERGY	140.5875153	4464.32658	4323.739064
HEAT ENERGY	276.9997795	4469.372355	4192.372575
TOTAL ENERGY	487.9871685	4650.514689	4162.52752

How can I achieve this in big query?

Thanks!

Answer 1

Consider below approach

select *, value_t_max - value_t_min as max_minus_min_value
from (
  select DashboardingCategory, 
    array_agg(value order by timestamp limit 1)[offset(0)] as value_t_min,
    array_agg(value order by timestamp desc limit 1)[offset(0)] as value_t_max,
  from  your_table
  group by DashboardingCategory
)                

if applied to sample data in your question - output is

Answer 2

I haven't used Bigquery, but I think it supports common table expressions like most other database types. The CTEs calculate and use a value referenced later in the query. This works, where you are using those CTEs to get the min and max values, and the bottom brings it altogether.

    with min_max_time as (
     select category, min(dt) as min_date, max(dt) as max_date
     from energy
     group by category
    ),
    min_value as (
     select e.category, e.evalue
     from energy e
     join min_max_time mmt
       on e.category = mmt.category
      and e.dt = mmt.min_date
    ), 
    max_value as (
     select e.category, e.evalue
     from energy e
     join min_max_time mmt
       on e.category = mmt.category
      and e.dt = mmt.max_date
    )
    select distinct e.category,
     minv.evalue as value_t_min, 
     maxv.evalue as value_t_max, 
     maxv.evalue - minv.evalue as max_minus_min_value
    from energy e
    join min_value minv
      on e.category = minv.category
    join max_value maxv
      on e.category = maxv.category

Db-fiddle found here.

EDIT Based on your comment, here's another solution that determines the min and max by Category and Building:

    with min_max_time as (
     select category, building, min(dt) as min_date, max(dt) as max_date
     from energy
     group by category, building
    ) ,
    min_value as (
    select e.category, e.building, e.evalue
    from energy e
    join min_max_time mmt
      on e.category = mmt.category
     and e.dt = mmt.min_date
     and e.building = mmt.building
    ), 
    max_value as (
    select e.category, e.building, e.evalue
    from energy e
    join min_max_time mmt
      on e.category = mmt.category
     and e.dt = mmt.max_date
     and e.building = mmt.building
    )
    select distinct e.category, 
    e.building, 
    minv.evalue as value_t_min, 
    maxv.evalue as value_t_max, 
    maxv.evalue - minv.evalue as max_minus_min_value
    from energy e
    join min_value minv
      on e.category = minv.category
     and e.building = minv.building
    join max_value maxv
      on e.category = maxv.category
     and e.building = maxv.building

The db-fiddle is found here, and the output looks like this:

    category        building    value_t_min     value_t_max     max_minus_min_value
    BATTERY ENERGY  house       140.5875153     4464.32658      4323.7390647
    BATTERY ENERGY  shed        40.5875153      464.32658       423.7390647
    HEAT ENERGY     house       276.9997795     4469.372355     4192.3725755
    HEAT ENERGY     shed        76.9997795      469.372355      392.3725755
    PV ENERGY       house       113.957348      3513.539046     3399.581698
    PV ENERGY       shed        13.957348       513.539046      499.581698
    TOTAL ENERGY    house       487.9871685     4650.514689     4162.5275205
    TOTAL ENERGY    shed        87.9871685      650.514689      562.5275205