SQL-包含基于多台计算机的职业信息的当前固件字符串的列 [英] SQL - Column containing current firmware string based on occational messages with several machines

问题描述

我有一个名为SessionEvents的表.

I have a table called SessionEvents.

我正在尝试创建一列，该列代表机器上的固件，以便以后仅能过滤某些软件的消息.

I'm trying to create a column that represents the firmware that is on the machine to later be able to filter messages for only certain software.

当前，我的SQL查询看起来像这样.(基于adamlamar在如何有效选择中的回答以前的非null值?，并稍作修改.)

Currently my SQL query looks like this. (Based on answer by adamlamar in How do I efficiently select the previous non-null value? with slight modifications.)

我正在使用JOIN，因为machine_id在一个名为Sessions的相关表中.

I'm using JOIN in since machine_id is in a related table called Sessions.

SELECT *,
    first_value(message_info) over (partition by value_partition order by event_time) as ColumnFirmware

FROM(        
SELECT SessionEvents.event_time, SessionEvents.message_type, SessionEvents.message_info, Sessions.machine_id,
    sum(case when message_type <> 1 then 0 else 1 end) over (partition by machine_id order by event_time) as value_partition

FROM SessionEvents
JOIN Sessions ON SessionEvents.SessionId=Sessions.Id) t
-- WHERE machine_id = 1

这适用于一台计算机，但是我找不到一种方法可以对多台计算机正确实施.我应该如何修改查询以适用于多台计算机?

This works for a single machine but i cant find a way to implement this properly for several machines. How should i modify the query to adopt if for several machines?

(从Azure SQL数据库中获取数据)

(Data is fetched from Azure SQL database)

推荐答案

这是一个孤岛问题.首先定义孤岛，然后传播固件:

This is a gaps-and-islands problem. First define the islands, then spread the firmware:

SELECT s.*,
       MAX(CASE WHEN message_type = 1 THEN message_info
           END) OVER (PARTITION BY machine_id, seqnum - sequm_m
                     ) as firmware
FROM (SELECT se.event_time, se.message_type, se.message_info, s.machine_id,
             ROW_NUMBER() OVER (PARTITION BY machine_id ORDER BY event_time) as seqnum_m,
             ROW_NUMBER() OVER (ORDER BY event_time) as seqnum
      FROM SessionEvents se JOIN
           Sessions s
           ON se.SessionId = s.Id
      ) s

这篇关于SQL-包含基于多台计算机的职业信息的当前固件字符串的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！