賬戶每天有存入，取出，余額一直在變動。以24小時為周期，取每天最后一條余額，來計算月或年平均怎么做？
cusid date time balance
001 20190801 10:05:00 10000
001 20190801 10:06:01 5000
001 20190801 15:15:00 250000
001 20190801 23:05:00 5000
001 20190802 09:10:00 100000
001 20190802 10:06:01 5000
001 20190802 15:15:00 250000
001 20190802 15:20:00 0
001 20190803 08:32:00 50000
……
uj5u.com熱心網友回復：
--測驗資料
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([cusid] nvarchar(23),[date] Date,[time] Time,[balance] int)
Insert #T
select N'001','20190801','10:05:00',10000 union all
select N'001','20190801','10:06:01',5000 union all
select N'001','20190801','15:15:00',250000 union all
select N'001','20190801','23:05:00',5000 union all
select N'001','20190802','09:10:00',100000 union all
select N'001','20190802','10:06:01',5000 union all
select N'001','20190802','15:15:00',250000 union all
select N'001','20190802','15:20:00',0 union all
select N'001','20190803','08:32:00',50000
Go
--測驗資料結束
;WITH cte AS (
Select *,ROW_NUMBER()OVER(PARTITION BY date ORDER BY time DESC) rn from #T
)
SELECT MONTH(cte.date) AS date,AVG(cte.balance) AS balance FROM cte WHERE rn=1 GROUP BY MONTH(cte.date)
uj5u.com熱心網友回復：
我可能沒有描述清楚，我想要每一天最后一筆余額的平均值，這個資料，應該是三天8/1最后一筆5000+8/2最后一筆0+8/3余額50000=5.5萬/3 求平均uj5u.com熱心網友回復：
1#悟空實作的就是你的需求，你認真看看，它的結果中date欄位是月份，也就是你的8月，18333就是5.5萬/3的平均值
uj5u.com熱心網友回復：
謝謝哦，新手小白給大家添麻煩了uj5u.com熱心網友回復：
--測驗資料if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([cusid] nvarchar(23),[date] Date,[time] Time,[balance] int)
Insert #T
select N'001','20190801','10:05:00',10000 union all
select N'001','20190801','10:06:01',5000 union all
select N'001','20190801','15:15:00',250000 union all
select N'001','20190801','23:05:00',5000 union all
select N'001','20190802','09:10:00',100000 union all
select N'001','20190802','10:06:01',5000 union all
select N'001','20190802','15:15:00',250000 union all
select N'001','20190802','15:20:00',0 union all
select N'001','20190803','08:32:00',50000
Go
--賬戶每天有存入，取出，余額一直在變動。以24小時為周期，取每天最后一條余額，來計算月或年平均怎么做？
--平均天數按記錄天數算
select cusid,SUM([balance])/COUNT(*) as m_balance from(
select * from #t a where not exists(
select * from #t
where a.cusid=cusid and a.date=date and a.time<time
))tt group by cusid
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/146841.html
標籤：疑難問題