选择时间组和枢轴语句中的最新和最早时间。

Question

我有包含用户名、时间和状态(IN或OUT)的辅助数据。我想要显示出勤数据，其中包含一个名称，以及签入/退出时间。我希望一个人一天不超过两次进出。数据如下：

​

​

正如您所看到的，我的问题是，在相同的登录尝试中，一个人可以在不同的秒内拥有多个数据条目。这是因为我从指纹助理扫描仪获得数据，在某些情况下，机器会输入多个条目，有时就在5-10秒之内。我想选择这样的数据：

​

​

如何确定登录尝试的适当时间，然后使用支点选择数据？

Answer 1

首先，您需要通过删除重复条目来规范您的数据。在您的情况下，这是一个挑战，因为复制的数据不易识别为重复。不过，你可以做一些假设。下面，我假设没有人会在两分钟内多次尝试登录。您可以首先使用公共表表达式(CTE，使用WITH子句)来做到这一点。

在CTE中，您可以使用LAG函数。本质上，这段代码的意思是“对于用户和条目类型的每个分区，如果前面的值在此值的2分钟内，则输入一个数字，否则为null。”我选择null作为保留值的标志，因为第一个条目的LAG将为null。因此，您的CTE将返回一个条目事件表(ID)，这是不同的尝试。

现在，您准备了另一个PIVOT将从中提取的CTE，它包含表中的所有内容，但只用于您所关心的条目ID。PIVOT将查看您的输入/输出时间的最小/最大值。

WITH UNIQUE_LOGINS AS (
    SELECT ID FROM LOGIN_TABLE 
    WHERE CASE WHEN LAG(TIME, 1, 0) OVER (PARTITION BY USERNAME, STATUS ORDER BY TIME) 
              + (2/60/24) < TIME THEN NULL ELSE 1 END IS NULL ),   -- Times within 2 minutes
TEMP_FOR_PIVOT AS (
    SELECT USERNAME, TIME, STATUS FROM LOGIN_TABLE WHERE ID IN (SELECT ID FROM UNIQUE_LOGINS)
)
SELECT * FROM TEMP_FOR_PIVOT
PIVOT (
    MIN(TIME), MAX(TIME) FOR STATUS IN ('IN', 'OUT')
)

在那里，如果您需要重新排列或重命名您的列，那么您只需将最后一个SELECT放入另一个CTE中，然后从其中选择您的值。这里还有关于PIVOT的更多信息：Rotate/pivot table with aggregation in Oracle