可以使用窗口函数。从现有的条件聚合查询开始，这将计算每套房子当前日期和上一日期之间的消费差异：

select
    date,
    house_a,
    coalesce(house_a - lag(house_a) over(order by date), 0) daily_a,
    house_b,
    coalesce(house_b - lag(house_b) over(order by date), 0) daily_b,
    house_c,
    coalesce(house_c - lag(house_c) over(order by date), 0)daily_c
from (
    select 
        date, 
        sum(case when houses = 'House_A' then kw_measure end) as house_a,
        sum(case when houses = 'House_B' then kw_measure end) as house_b,
        sum(case when houses = 'House_C' then kw_measure end) as house_c
    from table1
    group by date
) t
order by date

子查询在技术上不是必需的，但它避免了重复条件查询 sum() 两次。

如果你想让房子像结果显示的那样成为一个独立的柱子，你可以使用：

select house, date, kw_measure,
       (kw_measure -
        lag(kw_measure, 1, kw_measure) over (partition by house order by date) 
       ) as diff
from table1;

你似乎每个房子每个日期只有一行，所以聚合似乎没有必要。

你可以用 lag ，这是演示。

with cte as
(
  select
    distinct date,
    houses,
    kW_measure,
    lag(kW_measure) over (partition by houses order by date) as last_value
  from table1
)

select
  date,
  coalesce(sum(case when houses = 'House_A' then kW_measure - last_value end), 0) as House_A,
  coalesce(sum(case when houses = 'House_B' then kW_measure - last_value end), 0) as House_B,
  coalesce(sum(case when houses = 'House_C' then kW_measure - last_value end), 0) as House_C
from cte
group by
  date
order by
  date

输出：

| date       | house_a | house_b | house_c |
| -----------| ------- | ------- | ------- |
| 2020-01-01 | 0       | 0       | 0       |
| 2020-01-02 | 20      | 25      | 10      |
| 2020-01-03 | 10      | 25      | 10      |