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!
CodePudding user response：
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 