我查询特定日期时间之间的这些值，并且这些值以日期时间排序的任何顺序出现。有时Weight可能会遗漏，Count和Number可能会出现。
Name Value Datetime
Weight 48.8 10/24/2021 8:34:52:053 PM
Count 18 10/24/2021 8:34:52:460 PM
Number 40015516 10/24/2021 8:34:52:553 PM
Weight 48.82 10/24/2021 8:34:59:063 PM
Number 40015518 10/24/2021 8:34:59:563 PM
Count 19 10/24/2021 8:35:00:470 PM
我希望结果以以下方式显示，日期是值集中的最大日期。
Datetime Number Count Weight
10/24/2021 8:34:52:553 PM 40015516 18 48.8
10/24/2021 8:35:00:470 PM 40015518 19 48.82
到目前为止，我尝试使用Ntile函数将数据分成桶，并使用桶循环数据并读取值作为列。问题是，有时源数据中缺少Number或count或weight，这会导致问题。而且数据并不总是按照与源相同的顺序。
我正在从源中执行count('*)并将其除以3并使用Ntile函数
select @bucketCount = count(*)/3
from #SourceTable
insert into #Temptable
select
Name,
value,
Date_time,
NTILE (@bucketCount) OVER (
ORDER BY id
) buckets
from #Sourcetable
如果源数据可以按精确的时间分组，这将容易得多。
对于时间上的细微差异，您可以使用间隙和岛屿方法在小误差范围内按时间顺序分组，例如:2秒…
create table #SourceTable (
Name nvarchar(10),
[Value] float, --<<-- WARNING: floats are approximate
[Datetime] datetime
);
insert #SourceTable (Name, [Value], [Datetime]) values
(N'Weight', 48.8, '2021-10-24T20:34:52.053'),
(N'Count', 18, '2021-10-24T20:34:52.460'),
(N'Number', 40015516, '2021-10-24T20:34:52.553'),
(N'Weight', 48.82, '2021-10-24T20:34:59.063'),
(N'Number', 40015518, '2021-10-24T20:34:59.563'),
(N'Count', 19, '2021-10-24T20:35:00.470');
with LagsAndLeads as (
select
[Datetime],
lag([DateTime]) over (order by [Datetime]) as Previous,
lead([DateTime]) over (order by [Datetime]) as Next
from #SourceTable
), IslandStarts as (
select
row_number() over (order by [Datetime]) as IslandNumber,
[Datetime] as IslandStart
from LagsAndLeads
where datediff(second, Previous, [Datetime]) >= 2
or Previous is null
), IslandEnds as (
select
row_number() over (order by [Datetime]) as IslandNumber,
[DateTime] as IslandEnd
from LagsAndLeads
where datediff(second, [Datetime], Next) >= 2
or Next is null
), Chronology as (
select
S.Name, S.[Value], S.[Datetime], Starts.IslandNumber, IslandStart, IslandEnd
from IslandStarts Starts
join IslandEnds Ends on Ends.IslandNumber=Starts.IslandNumber
join #SourceTable S on S.[Datetime] >= IslandStart and S.[Datetime] <= IslandEnd
)
select [IslandEnd] as [Datetime], [Number], [Count], [Weight]
from (
select [IslandEnd], [Name], [Value]
from Chronology
) Src
pivot (max([Value]) for Name in ([Number], [Count], [Weight])) as Pvt;
生成结果: