我有一張表，上面有數百只股票的歷史股票價格。我只需要提取那些第一次達到10 美元或更高的股票。
| 庫存 | 價錢 | 日期 |
|---|---|---|
| AAA | 9 | 2021-10-01 |
| AAA | 10 | 2021-10-02 |
| AAA | 8 | 2021-10-03 |
| AAA | 10 | 2021-10-04 |
| BBB | 9 | 2021-10-01 |
| BBB | 11 | 2021-10-02 |
| BBB | 12 | 2021-10-03 |
有沒有辦法計算每只股票擊中 >= 10 的次數，以便只提取那些計數 = 1 的股票（在這種情況下，考慮到它過去從未達到 10，它將是股票 BBB）？
由于我不知道如何創建計數，因此我嘗試了以下對最小/最大日期的操作，但這看起來有點尷尬。任何更簡單的解決方案的想法？
with query1 as (
select Stock, min(date) as min_greater10_dt
from t
where Price >= 10
group by Stock
), query2 as (
select Stock, max(date) as max_greater10_dt
from t
where Price >= 10
group by Stock
)
select Stock
from t a
join query1 b on b.Stock = a.Stock
join query2 c on c.Stock = a.Stock
where not(a.Price < 10 and a.Date between b.min_greater10_dt and c.max_greater10_dt)
uj5u.com熱心網友回復：
這是一種間隙和孤島問題，可以按如下方式解決：
- 使用滯后價格檢測從 < 10 到 >= 10 的變化
- 計算此類更改的數量
- 僅過濾發生過這種情況的庫存
- 并取第一行，因為您只需要股票（您可以在此處分組，但行號允許您根據需要選擇整行）。
declare @Table table (Stock varchar(3), Price money, [Date] date);
insert into @Table (Stock, Price, [Date])
values
('AAA', 9, '2021-10-01'),
('AAA', 10, '2021-10-02'),
('AAA', 8, '2021-10-03'),
('AAA', 10, '2021-10-04'),
('BBB', 9, '2021-10-01'),
('BBB', 11, '2021-10-02'),
('BBB', 12, '2021-10-03');
with cte1 as (
select Stock, Price, [Date]
, row_number() over (partition by Stock, case when Price >= 10 then 1 else 0 end order by [Date] asc) rn
, lag(Price,1,0) over (partition by Stock order by [Date] asc) LaggedStock
from @Table
), cte2 as (
select Stock, Price, [Date], rn, LaggedStock
, sum(case when Price >= 10 and LaggedStock < 10 then 1 else 0 end) over (partition by Stock) StockOver10
from cte1
)
select Stock
--, Price, [Date], rn, LaggedStock, StockOver10 -- debug
from cte2
where Price >= 10
and StockOver10 = 1 and rn = 1;
回傳：
| 庫存 |
|---|
| BBB |
注意：如上所示提供 DDL DML 使人們更容易提供幫助。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/311539.html
標籤：sql sql-server 查询语句