如何识别同一指令时段内的重叠用户？求Excel/VBA/SQL方案

我有导出为Excel格式以及存储在Hive/SQL表中的数百万条数据，需要标记出同一指令（Instruction）时段内同时使用的用户。要求保留所有原始记录，并在旁添加标记（比如当Adam和Eve同时使用指令111时，两条记录都要保留并标记）。每个指令最多有100个用户在不同时段使用，需识别任意用户在同一指令下的时间重叠情况。

示例数据 #

我的错误尝试 #

我试过自连接表的方法，但逻辑有误，附上尝试的SQL代码：

SELECT tlc.instruction, tlc.userfullname, tlc.logged_start_date, tlc2.logged_start_date, tlc.logged_end_date, tlc2.logged_end_date
FROM table1 as tlc, table1 as tlc2
WHERE tlc.instruction = tlc2.instruction
AND tlc.userfullname <> tlc2.userfullname
AND tlc.logged_start_date <= tlc2.logged_start_date
AND tlc.logged_end_date <= tlc2.logged_start_date

1. SQL/Hive 查询方案 #

百万级数据优先用SQL处理，核心是正确判断时间重叠逻辑：两个时间段[A_start, A_end)和[B_start, B_end)重叠的条件是A_start < B_end AND B_start < A_end（如果To Time是闭区间包含端点，可将<改为<=，需根据实际时间字段定义调整）。

正确的自连接查询 #

该查询会给每条记录标记是否存在重叠用户，同时保留原始记录：

SELECT 
    t1.Instruction,
    t1.Username,
    t1.From_Time,
    t1.To_Time,
    -- 标记是否有重叠的其他用户
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM table1 t2 
            WHERE t2.Instruction = t1.Instruction
              AND t2.Username <> t1.Username
              AND t1.From_Time < t2.To_Time
              AND t2.From_Time < t1.To_Time
        ) THEN '有同时使用用户'
        ELSE '无'
    END AS Overlap_Mark,
    -- 可选：列出所有重叠的用户名（Hive用concat_ws(',', collect_set(t2.Username))替代STRING_AGG）
    STRING_AGG(DISTINCT t2.Username, ', ') OVER (
        PARTITION BY t1.Instruction, t1.From_Time, t1.To_Time
    ) AS Overlap_Users
FROM table1 t1
ORDER BY t1.Instruction, t1.From_Time;

优化说明 #

• 用EXISTS替代笛卡尔积自连接，大幅减少计算量，适配百万级数据；
• 若使用Hive，需确保时间字段为timestamp类型，避免字符串比较出错；
• STRING_AGG/collect_set可直接展示所有重叠用户，便于排查。

2. Excel 函数方案（适合小批量数据） #

假设数据在A:D列，E列做标记，在E2单元格输入以下数组公式（按Ctrl+Shift+Enter确认）：

=IF(SUM(($A$2:$A$7=A2)*($B$2:$B$7<>B2)*($C$2:$C$7<D2)*($D$2:$D$7>C2))>0,"有同时使用用户","无")

原理：统计同一指令下、不同用户、时间重叠的记录数，大于0则标记。

3. Excel VBA 方案（适合中等数据量） #

如果Excel数据量较大（几万条），用VBA效率更高：

Sub MarkOverlappingUsers()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim instrID As String, userName As String
    Dim fromTime As Date, toTime As Date
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 添加表头
    ws.Range("E1").Value = "Overlap_Mark"
    
    For i = 2 To lastRow
        instrID = ws.Cells(i, "A").Value
        userName = ws.Cells(i, "B").Value
        fromTime = ws.Cells(i, "C").Value
        toTime = ws.Cells(i, "D").Value
        ws.Cells(i, "E").Value = "无"
        
        For j = 2 To lastRow
            If i <> j And ws.Cells(j, "A").Value = instrID And ws.Cells(j, "B").Value <> userName Then
                If ws.Cells(j, "C").Value < toTime And ws.Cells(j, "D").Value > fromTime Then
                    ws.Cells(i, "E").Value = "有同时使用用户"
                    Exit For ' 找到一个重叠用户即停止循环
                End If
            End If
        Next j
    Next i
End Sub

使用方法：按Alt+F11打开VBA编辑器，插入模块粘贴代码，执行即可。

内容的提问来源于stack exchange，提问作者Ben F.