iT邦幫忙

3

SQL 依時間排序 連續資料的分組

資料 DTime,No
排序依時間,連續的No分為同一組,
最後取得各組最大與最小時間。

資料表:
http://sqlfiddle.com/#!7/2e568


DTime        No    
2020/05/07 08:44:08 A     
2020/05/07 08:44:53 A     
2020/05/07 08:45:38 B     
2020/05/07 08:46:23 B     
2020/05/07 08:47:08 B     
2020/05/07 08:47:53 A     
2020/05/07 08:48:38 A     
2020/05/07 08:49:23 C    

分組 NO  minTime        MaxTime
1   A  2020/05/07 08:44:08 2020/05/07 08:44:53
2   B  2020/05/07 08:45:38 2020/05/07 08:47:08
3   A  2020/05/07 08:47:53 2020/05/07 08:48:38
4   C  2020/05/07 08:49:23 2020/05/07 08:49:23

你可以把你的問題用 http://sqlfiddle.com/ 把表格建立起來,這樣大家會比較不用那麼辛苦的建表回答你
hichu314 iT邦新手 5 級 ‧ 2021-06-07 16:59:40 檢舉
感謝提點,已更新。
5
石頭
iT邦研究生 1 級 ‧ 2021-06-07 20:00:31
最佳解答

這是一個islands and gaps problem 之前我有在一篇文章有分析此問題解法和特徵這裡我就不多敘述了

基本上運用一個簡單算法來分群取得你要的資料

;WITH CTE AS (
SELECT NCode,MIN(DTime) 'minTime',MAX(DTime) 'MaxTime'
FROM (
  SELECT *, ROW_NUMBER() OVER(ORDER BY DTime) - 
            ROW_NUMBER() OVER(PARTITION BY NCode ORDER BY DTime) rn
  FROM da
) t1
GROUP BY rn,NCode
)
SELECT ROW_NUMBER() OVER(ORDER BY minTime) no,*
FROM CTE 

Note:

只要你的dbms可以使用window function都可以使用上面解法

sqlfiddle

很棒的解法

石頭 iT邦研究生 1 級 ‧ 2021-06-08 11:14:14 檢舉

謝謝:)

0
paicheng0111
iT邦高手 1 級 ‧ 2021-06-07 17:37:18
select a.*, b.minimun, b.maximum
from
(select distinct [Ncode], [team] from da) a
left join 
(select max([dtime]) as maximum, min([dtime]) as minimun, [ncode], [team] FROM da GROUP BY [ncode], [team]) b
on a.[ncode]=b.[ncode] and a.[team]=b.[team]

http://sqlfiddle.com/#!7/2344d/5/0

hichu314 iT邦新手 5 級 ‧ 2021-06-07 17:54:57 檢舉

感謝您的熱心幫忙,不過原始資料沒有[team]欄位。
這個也是目前比較困擾的,
有稍微去看了Rank、Dense_Rank、Row_Number等,
不過目前還弄不出來。

1
一級屠豬士
iT邦大師 1 級 ‧ 2021-06-07 17:57:40
with t0(id, dtime, no) as (
select row_number() over(order by dtime, no) 
     , *
  from it210607b
), t1(id, dtime, no, cflag) as (
select *
     , coalesce(no <> lag(no) over(order by id), True)
  from t0
), t2(id, dtime, no, cflag, rn) as (
select *
     , row_number() over(order by id)
  from t1
 where t1.cflag = True
), t3 as (
select t1.*
     , t2.rn
  from t1
  left join t2
    on (t1.id = t2.id)
), t4(dtime, no, grp) as (
select dtime, no
     , max(rn) over(partition by no order by id, rn)
  from t3
)
select grp, no
     , min(dtime)
     , max(dtime)
  from t4
 group by grp, no
 order by grp, no;

 grp | no |         min         |         max
-----+----+---------------------+---------------------
   1 | A  | 2020-05-07 08:44:08 | 2020-05-07 08:44:08
   2 | B  | 2020-05-07 08:45:38 | 2020-05-07 08:47:08
   3 | A  | 2020-05-07 08:47:53 | 2020-05-07 08:48:38
   4 | C  | 2020-05-07 08:49:23 | 2020-05-07 08:49:23
   5 | A  | 2020-05-07 09:44:53 | 2020-05-07 09:44:53
(5 rows)
hichu314 iT邦新手 5 級 ‧ 2021-06-07 18:04:12 檢舉

感謝您的熱心回答,
先說個抱歉,建資料沒有建好,真的非常不好意思。

如果是那樣的話,資料輸出就會變成:
分組 NO  minTime        MaxTime
1   A  2020/05/07 08:44:08 2020/05/07 08:44:08
2   B  2020/05/07 08:45:38 2020/05/07 08:47:08
3   A  2020/05/07 08:47:53 2020/05/07 08:48:38
4   C  2020/05/07 08:49:23 2020/05/07 08:49:23
5   A  2020/05/07 09:44:53 2020/05/07 09:44:53

那也沒關係,但是一開始的排的順序,你要講清楚. 這個題目一點都不簡單.但是你自己不組織好.這裡的版面,是會縮小的.下面的補充會不夠寬.
之後可讀性會變差的. 這種方式也是一樣的.寫法不變.

修改版在上面

1
純真的人
iT邦高手 1 級 ‧ 2021-06-07 18:29:53

恩..弄得好複雜@@""

只會用T-SQL

declare @da table
    ([DTime] datetime, [NCode] varchar(10))
;

INSERT INTO @da
    ([DTime], [NCode])
VALUES
    ('2020/05/07 08:44:08', 'A'),
    ('2020/05/07 09:44:53', 'A'),
    ('2020/05/07 08:45:38', 'B'),
    ('2020/05/07 08:46:23', 'B'),
    ('2020/05/07 08:47:08', 'B'),
    ('2020/05/07 08:47:53', 'A'),
    ('2020/05/07 08:48:38', 'A'),
    ('2020/05/07 08:49:23', 'C')

declare @dk table
    ([DTime] datetime, [NCode] varchar(10),Sort int)
;

select no=Identity(int,1,1),* Into #temptable
from @da
declare @Sort int=0,@TmpStr1 nvarchar(50) = '',@TmpStr2 nvarchar(50) = '',@i int = 0,@TotalNum int = 0
select @TotalNum=Count(0) from #temptable
while(@i<@TotalNum)
begin
	set @i = @i + 1
	select @TmpStr1=NCode
	from #temptable
	where no=@i
	if(@TmpStr1<>@TmpStr2)
	begin
		set @TmpStr2 = @TmpStr1
		set @Sort = @Sort + 1
	end
	insert into @dk
	select [DTime]
	,[NCode]
	,@Sort
	from #temptable
	where no=@i
end
Drop Table #temptable

select Sort
,[NCode]
,Min(DTime) Min_DTime
,Max(DTime) Max_DTime
from @dk
group by Sort
,[NCode]
order by Sort

https://ithelp.ithome.com.tw/upload/images/20210607/20061369UM4D7baOZK.png

我要發表回答

立即登入回答