SQL查询，查找每个月中每一天的第一次记录和最后一次记录之间的小时差异，并将每个差异相加以找到总数。
SQL查询，查找每个月中每一天的第一次记录和最后一次记录之间的小时差异，并将每个差异相加以找到总数。
提问于 2015-12-04 13:08:49
这不是用一句话来描述的最简单的查询，这就是为什么标题可能不是最好的。
基本上我有一份登录记录。这个日志看起来像：
ContactId, LocationId, TimeStamp这些代表一个联系人(注册人)在给定的时间内在一个地点被看到。
我想要做的是选择一个约会时间窗口，对于该窗口中的每一天，我都想获取第一个登录和最后一个登录，并计算出以小时为单位的差异。这一小时的差异，然后应有助于总的总数，给出了总时间的联系在该地点度过了一个月。
没有看到接触的日子显然应该被忽略，而只看到接触一次的日子也不应该被使用，因为没有差别可以计算。
我知道如何在几个小时内找出两次约会的区别：
select DATEDIFF(HOUR, datetime1, datetime2) as hoursestimate但我不知道如何：
- 让datetime1和datetime2成为一天中的第一次也是最后一次记录。
- 循环数据，增加日数，以查找一个月的累计总时数(TotalHours)。
一旦根据第一次和最后一次看到的差异获得了该时间段的累计小时数，我将使用联系人表执行一个连接，以便通过这个TotalHours列获得它们的名称和顺序，因此希望从查询中返回的最后一个表如下：
Id FirstName LastName TotalHours
35 Bob Bobberson 65
40 Jim Jimmerson 63
2 Harry Harrison 54然而，步骤1和步骤2让我有点不知所措。有什么想法吗？
回答 2
Stack Overflow用户
回答已采纳
发布于 2015-12-04 13:11:50
我想你想要两个集合：
select contactid, sum(hoursestimate) as total
from (select contactid, cast(TimeStamp as date) as dte,
DATEDIFF(HOUR, min(TimeStamp), max(TimeStamp)) as hoursestimate
from t
where TimeStamp >= @start and TimeStamp < @end
group by contactid, cast(TimeStamp as date)
) t
group by contactid;Stack Overflow用户
发布于 2015-12-04 13:28:13
这里有几种不同的技术可以帮助你。
投射将允许您从彼此之间删除日期时间戳的日期和时间部分。这对于将同一天、但不同时间的多个记录组合在一起确实很方便。
有可用于过滤出仅在一天内记录的联系人。这是通过计算贡献记录的数量和删除低于阈值的记录来实现的。
这个例子结合了这些技术：
WITH SampleDate AS
(
/* Lets make some records to experiment with.
*/
SELECT
r.*
FROM
(
VALUES
(1, 1, '2015-01-01 09:00:00.000'),
(1, 1, '2015-01-01 12:00:00.000'),
(1, 1, '2015-01-01 17:00:00.000'),
(2, 1, '2015-01-01 09:00:00.000')
) AS r(ContactId, LocationId, [TimeStamp])
)
SELECT
ContactId,
LocationId,
CAST([TimeStamp] AS DATE) AS [Day],
MIN(CAST([TimeStamp] AS TIME)) AS FirstSeenTime,
MAX(CAST([TimeStamp] AS TIME)) AS LastSeenTime,
DATEDIFF(
HOUR,
MIN(CAST([TimeStamp] AS TIME)),
MAX(CAST([TimeStamp] AS TIME))
) AS HoursEstimate
FROM
SampleDate
GROUP BY
ContactId,
LocationId,
CAST([TimeStamp] AS DATE) -- Removing the time allows us to create 1 record per day.
HAVING
COUNT(*) > 1 -- Make sure we've seen the contact at least twice.
;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/34089139
复制相关文章
相似问题
腾讯云开发者
