选择价格未变化的行

发布于 2024-10-13 12:25:08 字数 515 浏览 6 评论 0原文

假设您有一个类似的表（正在使用 SQL Server 2008，没有审核日志 - 表很大）：

 SecID | Date    | Price
 1       1/1/11      10
 1       1/2/11      10
 1       1/3/11      5
 1       1/4/11      10
 1       1/5/11      10

假设这个表很大（不同 secID 和日期有数百万行） - 我想在价格更改时返回记录（寻找比使用光标和迭代更好的东西）：

我试图找出如何获取：

 SecID | StartDate | EndDate | Price
 1        1/1/11      1/2/11    10
 1        1/3/11      1/3/11    5
 1        1/4/11      1/5/11    10

即另一种看待它的方法是我正在寻找价格保持不变的一系列日期。

原文

Suppose you have a table like (am using SQL Server 2008, no audit log - table is HUGE):

 SecID | Date    | Price
 1       1/1/11      10
 1       1/2/11      10
 1       1/3/11      5
 1       1/4/11      10
 1       1/5/11      10

Suppose this table is HUGE (millions of rows for different secIDs and Date) - I would like to return the records when the price changed (looking for something better than using a cursor and iterating):

Am trying to figure out how to get:

 SecID | StartDate | EndDate | Price
 1        1/1/11      1/2/11    10
 1        1/3/11      1/3/11    5
 1        1/4/11      1/5/11    10

i.e. another way to look at it is that I am looking for a range of dates where the price has stayed the same.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凉城已无爱 2024-10-20 12:25:08

这是一个“孤岛”问题。

declare @Yourtable table
 (SecID int, Date Date, Price int)

 INSERT INTO @Yourtable
SELECT 1,GETDATE()-5,10 union all
SELECT 1,GETDATE()-4,10 union all
SELECT 1,GETDATE()-3,5 union all
SELECT 1,GETDATE()-2,10 union all
SELECT 1,GETDATE()-1, 10

;WITH cte AS
(
SELECT SecID,Date,Price,
       ROW_NUMBER() OVER (PARTITION BY SecID ORDER BY Date) -
       ROW_NUMBER() OVER (PARTITION BY Price, SecID ORDER BY Date) AS Grp
FROM @Yourtable
)
SELECT SecID,Price, MIN(Date) StartDate, MAX(Date) EndDate
FROM cte
GROUP BY SecID, Grp, Price
ORDER BY SecID,  MIN(Date)

This is an "islands" problem.

declare @Yourtable table
 (SecID int, Date Date, Price int)

 INSERT INTO @Yourtable
SELECT 1,GETDATE()-5,10 union all
SELECT 1,GETDATE()-4,10 union all
SELECT 1,GETDATE()-3,5 union all
SELECT 1,GETDATE()-2,10 union all
SELECT 1,GETDATE()-1, 10

;WITH cte AS
(
SELECT SecID,Date,Price,
       ROW_NUMBER() OVER (PARTITION BY SecID ORDER BY Date) -
       ROW_NUMBER() OVER (PARTITION BY Price, SecID ORDER BY Date) AS Grp
FROM @Yourtable
)
SELECT SecID,Price, MIN(Date) StartDate, MAX(Date) EndDate
FROM cte
GROUP BY SecID, Grp, Price
ORDER BY SecID,  MIN(Date)

回复收藏 0 原文

温柔少女心 2024-10-20 12:25:08

如果该值不变，标准偏差将为零

select secId
  from ...
 group by secId
having count(*) = 1  
    OR stdev(price) = 0

If the value does not change, the std deviation will be zero

select secId
  from ...
 group by secId
having count(*) = 1  
    OR stdev(price) = 0

回复收藏 0 原文

旧时浪漫 2024-10-20 12:25:08

我认为这应该有效

SELECT SecID, Min(Date) AS StartDate, Max(Date) AS EndDate, Price FROM BigTable GROUP BY SecID, EndDate Having Min(Date) != MAx(Date) And Date != NULL

I think this should work

SELECT SecID, Min(Date) AS StartDate, Max(Date) AS EndDate, Price FROM BigTable GROUP BY SecID, EndDate Having Min(Date) != MAx(Date) And Date != NULL

回复收藏 0 原文

~没有更多了~