27,580
社区成员




use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([time] bigint,[power] bigint)
Insert #1
select 1451577600,1000 union all
select 1451577660,1002 union all
select 1451577720,1005
Go
Select [time],[power],[power]-LAG([power],1,0)OVER(ORDER BY time) AS 差值 from (Select ([time]-1)/360 AS time,SUM([power]) AS [power] from #1 GROUP BY ([time]-1)/360
) AS t
/*
time power 差值
4032159 1000 1000
4032160 2007 1007
*/
WITH
/* 测试数据
table1(time,power) AS (
SELECT 1451577600,1000 UNION ALL
SELECT 1451577660,1002 UNION ALL
SELECT 1451577720,1005
),
*/
a AS (
SELECT time,
power,
ROW_NUMBER() OVER(ORDER BY time) rn
FROM table1
)
SELECT a1.time, -- 你喜欢用前一个时点还是后一个时点自己调整
a1.power - a0.power AS used_power
FROM a a0
JOIN a a1
ON a0.rn + 1 = a1.rn
ORDER BY a1.time
time used_power
----------- -----------
1451577660 2
1451577720 3
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([time] bigint,[power] bigint)
Insert #1
select 1451577600,1000 union all
select 1451577660,1002 union all
select 1451577720,1005
Go
Select ([time]-1)/360 AS time,SUM([power]) AS [power] from #1 GROUP BY ([time]-1)/360
/*
time power
4032159 1000
4032160 2007*/