ITKeyword,专注技术干货聚合推荐

注册 | 登录

解决sql - Select rows where column value has changed

itPublisher 分享于

2020腾讯云双十一活动,全年最低!!!(领取3500元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1073

【阿里云】双十一活动,全年抄底价,限时3天!(老用户也有),
入口地址https://www.aliyun.com/1111/home

推荐:如何开启SQL Server 2008的远程联机

    需要开启SQL Server 2008 远程联机,需按如下操作步骤执行:       1、首先需要在{程序}-{Microsoft SQL Server 2008}-{配置工具}-{SQL Server 配置

Let's say I have the following table:

Value    Time
0        15/06/2012 8:03:43 PM
1        15/06/2012 8:03:43 PM     *
1        15/06/2012 8:03:48 PM 
1        15/06/2012 8:03:53 PM
1        15/06/2012 8:03:58 PM     
2        15/06/2012 8:04:03 PM     *
2        15/06/2012 8:04:08 PM
3        15/06/2012 8:04:13 PM     *
3        15/06/2012 8:04:18 PM
3        15/06/2012 8:04:23 PM
2        15/06/2012 8:04:28 PM     *
2        15/06/2012 8:04:33 PM     

How do I select the starred rows, that is, where Value has changed? Basically I'm trying to find the time when Value has changed so I can do other queries based on those time intervals. The solution shouldn't depend on knowing Value or Time in advance.

It seems to me that this shouldn't be very hard (but it's hard enough for me apparently!).

I'm currently using SQL Server 2008 although I have access to 2012 if the new window/analytic functions are helpful.

I tried adapting the solutions here http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/ but my query didn't complete after an hour! I think the joins explode the row size to something unmanageable (or I screwed it up).

I can solve this problem with C# code and multiple db calls, but it seems like something that could be done in a table-valued function or SP which would be much nicer.

Also, a solution that only works with increasing Value is OK if that is easier.

sql sql-server-2008 tsql select
edited Jun 20 '12 at 21:59 Aaron Bertrand 174k 21 290 343 asked Jun 20 '12 at 20:14 agentnega 2,023 1 12 27

 | 

2 Answers
2

解决方法

I think this is what you're after:

;WITH x AS
(
  SELECT value, time, rn = ROW_NUMBER() OVER 
  (PARTITION BY Value ORDER BY Time)
  FROM dbo.table
)
SELECT * FROM x WHERE rn = 1;

This may be slow if the resultset is large and there isn't a good supporting index...

EDIT

Ah, wait a second, the values go up and down, not just up... if that is the case you can try this much slower approach:

DECLARE @x TABLE(value INT, [time] DATETIME)

INSERT @x VALUES
(0,'20120615 8:03:43 PM'),--
(1,'20120615 8:03:43 PM'),--*
(1,'20120615 8:03:48 PM'),--
(1,'20120615 8:03:53 PM'),--
(1,'20120615 8:03:58 PM'),--
(2,'20120615 8:04:03 PM'),--*
(2,'20120615 8:04:08 PM'),--
(3,'20120615 8:04:13 PM'),--*
(3,'20120615 8:04:18 PM'),--
(3,'20120615 8:04:23 PM'),--
(2,'20120615 8:04:28 PM'),--*
(2,'20120615 8:04:33 PM');

;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER (ORDER BY time)
  FROM @x
)
SELECT x.value, x.[time]
FROM x LEFT OUTER JOIN x AS y
ON x.rn = y.rn + 1
AND x.value <> y.value
WHERE y.value IS NOT NULL;

Results:

value  time
-----  -----------------------
1      2012-06-15 20:03:43.000
2      2012-06-15 20:04:03.000
3      2012-06-15 20:04:13.000
2      2012-06-15 20:04:28.000

edited Jun 20 '12 at 20:25 answered Jun 20 '12 at 20:18 Aaron Bertrand 174k 21 290 343      Wow that was fast. Value increasing is OK. I'll try it. –  agentnega Jun 20 '12 at 20:23      Fantastic, thanks @Aaron Bertrand. On my data the second version took less than double the time of the first version, so the performance difference isn't too bad. –  agentnega Jun 20 '12 at 20:40      How to convert this query to also include an ID (new column) in the resulting output? –  ThinkCode Sep 10 '15 at 21:20      @ThinkCode you'll need to be more specific about what you want in that column. Right now I could put just about anything in a new column, but that wouldn't be very useful. –  Aaron Bertrand Sep 10 '15 at 21:39      @Aaron : pastebin.com/LT0ygatC Instead of treating all rows for one ID, we now have 2 different IDs. Hope I made sense! Thank you –  ThinkCode Sep 10 '15 at 21:48  |  show more comments
DECLARE @x TABLE(value INT, [time] DATETIME)

INSERT @x VALUES
(0,'20120615 8:03:43 PM'),--
(1,'20120615 8:03:43 PM'),--*
(1,'20120615 8:03:48 PM'),--
(1,'20120615 8:03:53 PM'),--
(1,'20120615 8:03:58 PM'),--
(2,'20120615 8:04:03 PM'),--*
(2,'20120615 8:04:08 PM'),--
(3,'20120615 8:04:13 PM'),--*
(3,'20120615 8:04:18 PM'),--
(3,'20120615 8:04:23 PM'),--
(2,'20120615 8:04:28 PM'),--*
(2,'20120615 8:04:33 PM');


; with temp as
(
SELECT 
    value, [time],  lag(value,1,-1) over (order by [time] ) as lastValue
FROM    @x
) 
SELECT 
    [value],[time] 
FROM 
    temp 
WHERE value <> lastValue

Results:

value   time
---------------------------
0   2012-06-15 20:03:43.000
1   2012-06-15 20:03:43.000
2   2012-06-15 20:04:03.000
3   2012-06-15 20:04:13.000
2   2012-06-15 20:04:28.000

answered May 29 '14 at 15:12 valo 71 1 1 3   Neat, but worth mentioning this is only supported from SQL 2012 onward. –  Steve Pettifer May 29 '14 at 15:19      This was super fast! The query for previous versions worked 11 minutes and after that I had to cancel it. –  Vojtěch Dohnal Sep 2 '15 at 14:15

 | 


相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。