SQL Server 获取按时间排序的数据中的特定记录，但没有用于关联的主键

vaj7vani 于 2023-01-20 发布在其他

关注(0)|答案(1)|浏览(115)

好吧，标题不足以说明问题，我有下面的数据，它给出了铁棒的时间和长度，没有主键来标识每根铁棒，但是数据是按时间排序的，只要长度为零，我们就知道它后面的值属于同一根铁棒，时间是测量记录的时间。长度是度量单位。下面是创建数据的脚本：

DECLARE @data TABLE ([time] DATETIME, [length] FLOAT)

INSERT INTO @data
VALUES ('2023-01-15 01:00:10', 0), ('2023-01-15 02:23:15', 0), ('2023-01-15 02:23:16', 0.3334), ('2023-01-15 02:23:23', 1.11), ('2023-01-15 02:23:50', 2.225), ('2023-01-15 02:24:00', 5), ('2023-01-15 02:24:13', 5.334), ('2023-01-15 02:24:23', 5.9), ('2023-01-15 02:24:35', 6.125), ('2023-01-15 02:25:30', 6.334), ('2023-01-15 02:26:33', 7.996), ('2023-01-15 02:27:31', 10.3), ('2023-01-15 02:27:32', 0), ('2023-01-15 02:28:28', 0), ('2023-01-15 02:29:22', 0), ('2023-01-15 02:30:14', 0.66669), ('2023-01-15 02:30:15', 1.669), ('2023-01-15 02:30:25', 2.334), ('2023-01-15 02:30:30', 3), ('2023-01-15 02:30:35', 5.669), ('2023-01-15 02:30:56', 6.669), ('2023-01-15 02:31:15', 7.332), ('2023-01-15 02:31:30', 8), ('2023-01-15 02:31:40', 9.5), ('2023-01-15 02:31:44', 9.9), ('2023-01-15 02:31:55', 10), ('2023-01-15 02:32:15', 10.4), ('2023-01-15 02:32:20', 0), ('2023-01-15 02:32:30', 0.5), ('2023-01-15 02:32:44', 1), ('2023-01-15 02:32:54', 1.334)

我想从这个数据中得到的是长度为0、5、7和10时的记录。通常，长度不是整数，而是一个分数。在这种情况下，我将取接近整数的第一个长度。例如，如果我有以下长度：4.9、5.33、5.5、5.7，那么我将取值5.33，因为它是大于5的最小数。对于其余长度也是如此。
数字0表示一个新的铁条，但也可以为同一个铁条记录多次。下面是上面数据的屏幕截图：3 iron bars with lengths这是我想要得到的数据：enter image description here
我尝试了以下方法，但这会导致数据被打乱。只是添加了这个，因为stackoverflow请求它，但显然不是我想要的。

SELECT MIN([time]), FLOOR([length])
FROM @data
WHERE FLOOR([length]) IN (0, 5, 7, 10)
GROUP BY FLOOR([length])

谢谢。

sql-server

来源：https://stackoverflow.com/questions/75164079/get-specific-records-in-an-ordered-data-by-time-but-doesnt-have-primary-key-for

1条答案

按热度按时间

flvlnr441#

很酷的问题，似乎要复杂得多，然后它似乎是：）

;with candidates as (
    select 
        *
    from @data 
    where floor([length]) in (0, 5, 7, 10)
)
, candidatesWithPrevLength as (
    select 
        *
        , lag([length]) over (order by [time]) as lengthPrevious
    from candidates     
), candidatesWithGroupStarts as (
    select
        *
        , case when [length] < lengthPrevious or lengthPrevious is null then 1 else null end as 'groupStart'
    from candidatesWithPrevLength
), groups as (
    select
        [time]
        , lead([time]) over (order by [time]) as validUntil
        , row_number() over (order by [time]) as groupId
    from candidatesWithGroupStarts  
    where groupStart = 1
), candidatesInGroups as (
    select 
        c.* 
        , g.groupId
    from candidates c, groups g
    where c.[time] >= g.[time] and (c.[time] < g.validUntil or g.validUntil is null)
), candidatesInGroupsWithOrders as (
    select 
        * 
        , row_number() over (partition by groupId, floor([length]) order by [time]) as OrderNo
    from candidatesInGroups
)
select [time], [length] from candidatesInGroupsWithOrders where orderno = 1

令人惊讶的是，我不得不使用一堆SQL机制来实现这个目标：CTE、联接、窗口函数
将尝试描述的想法在简短：

查找候选条（与初始条件匹配的条）
计算候选项组（基于0到10个长度检查）
把所有候选人分成小组
从这些组中选择所需的钢筋

这只是一个可能的选择。当然，有点“脏”。不确定它将如何处理大量的数据。当然可以简化和优化。但它的工作，可以是一个很好的“起点”

赞(0）回复(0）举报 2023-01-20

我来回答

SQL Server 获取按时间排序的数据中的特定记录，但没有用于关联的主键

1条答案

相关问题

热门标签

最新问答