我有一個這樣的資料集：
Date Value type
2020-01-01 0 A
2020-01-04 102 A
2020-02-01 111 B
2020-02-03 10 A
2020-01-11 133 B
2020-04-01 1433 A
2020-04-23 102 A
2020-05-01 1045 A
2020-06-01 103 B
2020-06-01 100 A
2020-06-01 133 A
2020-06-11 150 A
2020-07-01 1000 A
2020-07-21 104 A
2020-07-25 140 A
2020-07-28 1600 A
2020-08-01 100 A
僅當從可用的最大日期開始的過去 7 天內“值”的總和超過 1000 時，我才嘗試獲取行。像這樣：
Type ISHIGH
A 1
B 0
這是我試過的查詢，
select type, case when sum(usage) > 1000 then 1 else 0 end as total_usage from tableA
where (select sum(usage) as usage from tableA where date = max(date)-7)
group by type, date
這顯然是不對的。有什么簡單的方法可以做到這一點？
uj5u.com熱心網友回復：
這很簡單，group by除了您需要能夠在分組之前訪問最大日期：
select type
, max(date) as last_usage_date
, sum(value) as total_usage
, case when sum(case when date >= cutoff_date then value end) >= 1000 then 'y' end as [is high!]
from t
cross apply (
select dateadd(day, -6, max(date))
from t as x
where x.type = t.type
) as ca(cutoff_date)
group by type, cutoff_date
如果您只想要這兩列，那么更簡單的方法是：
select t.type, case when sum(value) >= 1000 then 'y' end as [is high!]
from t
left join (
select type, dateadd(day, -6, max(date)) as cutoff_date
from t
group by type
) as a on t.type = a.type and t.date >= a.cutoff_date
group by t.type
uj5u.com熱心網友回復：
通過 查找最大日期type。然后用它來查找最后7幾天和sum()價值。
with
cte as
(
select [type], max([Date]) as MaxDate
from tableA
group by [type]
)
select c.[type], sum(a.Value),
case when SUM(a.Value) > 1000 then 1 else 0 end as ISHIGH
from cte c
inner join tableA a on a.[type] = c.[type]
and a.[Date] >= DATEADD(DAY, -7, c.MaxDate)
group by c.[type]
uj5u.com熱心網友回復：
這可以通過累積總計來完成，如下所示：
;With CTE As (
Select [type], [date],
SUM([value]) Over (Partition by [type] Order by [date] Desc) As Total,
Row_Number() Over (Partition by [type] Order by [date] Desc) As Row_Num
From Tbl)
Select Distinct CTE.[type], Case When C.[type] Is Not Null Then 1 Else 0 End As ISHIGH
From CTE Left Join CTE As C On (CTE.[type]=C.[type]
And DateDiff(dd,CTE.[date],C.[date])<=7
And C.Total>1000)
Where CTE.Row_Num=1
uj5u.com熱心網友回復：
我認為您與解決此問題的最初嘗試非常接近。只是一個小小的編輯：
select type, case when sum(value) > 1000 then 1 else 0 end as total_usage
from tableA
where date > (select max(date)-7 from tableA)
group by type
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/379311.html
標籤：sql sql-server