如何在大查询上根据时间戳最小值最大值查询按某些类别分组的两个值的减法

Question

您好，我有一个像这样的表（只是一个例子）

仪表板类别	时间戳	值
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
光伏能源	14/03/2022	534.6937951
电池能源	14/03/2022	625.9614076
热能	14/03/2022	669.2673149
总能源	14/03/2022	1175.157762
光伏能源	19/03/2022	1352.12033
电池能源	19/03/2022	1747.298151
热能	19/03/2022	1891.235057
总能源	19/03/2022	1909.890893
光伏能源	20/03/2022	2118.666904
电池能源	20/03/2022	2335.954084
热能	20/03/2022	2542.706342
总能源	20/03/2022	2675.744966
光伏能源	21/03/2022	3513.539046
电池能源	21/03/2022	4464.32658
热能	21/03/2022	4469.372355
总能源	21/03/2022	4650.514689

这是我想要的结果，基于整个表中的最小和最大时间戳，并通过仪表板类别提取相应的值。预期的查询结果低于

仪表板类别	value(t_min)	value(t_max)	max -min_value
光伏能源	113.957348	3513.539046	3399.581698
电池能量	140.5875153	4464.32658	4323.739064
热能	276.9997795	4469.372355	4192.372575
总能量	487.9871685	4650.514689	4162.52752

如何在大查询中实现此目的？

谢谢！

Answer 1

请考虑以下方法

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
)                

如果应用于问题中的示例数据，

- 输出为

Answer 2

我没有使用过 Bigquery，但我认为它像大多数其他数据库类型一样支持通用表表达式。 CTE 计算并使用稍后在查询中引用的值。这是有效的，您可以使用这些 CTE 来获取最小值和最大值，并且底部将其全部呈现出来。

    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 在此处找到。

编辑
根据您的评论，这是另一个按类别和建筑物确定最小值和最大值的解决方案：

    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

找到 db-fiddle 此处，输出如下所示：

    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