如何获取所有日期的汇总值，即使在某些日子丢失数据时也是如此？

I have the data with users tracking time. The data is in segments and each row represent one segment. Here is the sample data

我有用户跟踪时间的数据。数据是分段的，每行代表一个分段。这是样本数据

http://sqlfiddle.com/#!6/2fa61

http://sqlfiddle.com/#!6/2fa61

How can I get the data on daily basis i.e. if a complete day is of 1440 minutes then I want to know how many minutes the user was tracked in a day. I also want to show 0 on the day when there is no data.

如何每天获取数据，即如果一整天是1440分钟，那么我想知道用户在一天内被跟踪了多少分钟。我还想在没有数据的那天显示0。

I am expecting the following output

我期待以下输出

5 个解决方案

#1

Use table of numbers. I personally have a permanent table Numbers with 100K numbers in it.

使用数字表。我个人有一个永久表格，里面有100K数字。

Once you have a set of numbers you can generate a set of dates for the range that you need. In this query I'll take MIN and MAX dates from your data, but since you may not have data for some dates, it is better to have explicit parameters defining the range.

获得一组数字后，您可以为所需的范围生成一组日期。在这个查询中，我将从您的数据中获取MIN和MAX日期，但由于您可能没有某些日期的数据，因此最好有明确的参数来定义范围。

For each date I have the beginning and ending of a day - our grouping interval.

对于每个日期，我有一天的开始和结束 - 我们的分组间隔。

For each date we are searching among track rows for those that intersect with this interval. Two intervals (DayStart, DayEnd) and (StartTime, EndTime) intersect if StartTime < DayEnd and EndTime > DayStart. This goes into WHERE.

对于每个日期，我们在轨道行中搜索与此间隔相交的日期。如果StartTime DayStart，则两个间隔（DayStart，DayEnd）和（StartTime，EndTime）相交。这进入了WHERE。 和endtime>

For each intersecting intervals we are calculating the range that belongs to both intervals: from MAX(DayStart, StartTime) to MIN(DayEnd, EndTime).

对于每个交叉间隔，我们计算属于两个间隔的范围：从MAX（DayStart，StartTime）到MIN（DayEnd，EndTime）。

Finally, we group by day and sum up durations of all ranges.

最后，我们按天分组并总结所有范围的持续时间。

I added a row to your sample data to test the case when interval covers the whole day. From 2015-02-14 20:50:43 to 2015-02-16 19:49:59. I chose this interval to be well before intervals in your sample, so that results for the dates in your example are not affected. Here is SQL Fiddle.

我在样本数据中添加了一行，以便在间隔覆盖整天的情况下测试案例。从2015-02-14 20:50:43到2015-02-16 19:49:59我在样本中的间隔之前选择了此间隔，因此示例中日期的结果不会受到影响。这是SQL小提琴。

DECLARE @track table
(
Email varchar(20),
StartTime datetime,
EndTime datetime,
DurationInSeconds int,
FirstDate datetime,
LastUpdate datetime
);

Insert into @track  values ( 'ABC', '2015-02-20 08:49:43.000', '2015-02-20 14:49:59.000', 21616, '2015-02-19 00:00:00.000', '2015-02-28 11:45:27.000')
Insert into @track  values ( 'ABC', '2015-02-20 14:49:59.000', '2015-02-20 22:12:07.000', 26528, '2015-02-19 00:00:00.000', '2015-02-28 11:45:27.000')
Insert into @track  values ( 'ABC', '2015-02-20 22:12:07.000', '2015-02-21 07:00:59.000', 31732, '2015-02-19 00:00:00.000', '2015-02-28 11:45:27.000')
Insert into @track  values ( 'ABC', '2015-02-21 09:49:43.000', '2015-02-21 16:30:10.000', 24027, '2015-02-19 00:00:00.000', '2015-02-28 11:45:27.000')
Insert into @track  values ( 'ABC', '2015-02-21 16:30:10.000', '2015-02-22 09:49:30.000', 62360, '2015-02-19 00:00:00.000', '2015-02-28 11:45:27.000')
Insert into @track  values ( 'ABC', '2015-02-22 09:55:43.000', '2015-02-22 11:49:59.000', 5856, '2015-02-19 00:00:00.000', '2015-02-28 11:45:27.000')
Insert into @track  values ( 'ABC', '2015-02-22 11:49:10.000', '2015-02-23 08:49:59.000', 75649, '2015-02-19 00:00:00.000', '2015-02-28 11:45:27.000')
Insert into @track  values ( 'ABC', '2015-02-23 10:59:43.000', '2015-02-23 12:49:59.000', 6616, '2015-02-19 00:00:00.000', '2015-02-28 11:45:27.000')
Insert into @track  values ( 'ABC', '2015-02-23 12:50:43.000', '2015-02-24 19:49:59.000', 111556, '2015-02-19 00:00:00.000', '2015-02-28 11:45:27.000')
Insert into @track  values ( 'ABC', '2015-02-28 08:49:43.000', '2015-02-28 14:49:59.000', 21616, '2015-02-19 00:00:00.000', '2015-02-28 11:45:27.000')

Insert into @track  values ( 'ABC', '2015-02-14 20:50:43.000', '2015-02-16 19:49:59.000', 0, '2015-02-19 00:00:00.000', '2015-02-28 11:45:27.000')

.

。

;WITH
CTE_Dates
AS
(
    SELECT
        Email
        ,CAST(MIN(StartTime) AS date) AS StartDate
        ,CAST(MAX(EndTime) AS date) AS EndDate
    FROM @track
    GROUP BY Email
)
SELECT
    CTE_Dates.Email
    ,DayStart AS xDate
    ,ISNULL(SUM(DATEDIFF(second, RangeStart, RangeEnd)) / 60, 0) AS TrackMinutes
FROM
    Numbers
    CROSS JOIN CTE_Dates -- this generates list of dates without gaps
    CROSS APPLY
    (
        SELECT
            DATEADD(day, Numbers.Number-1, CTE_Dates.StartDate) AS DayStart
            ,DATEADD(day, Numbers.Number, CTE_Dates.StartDate) AS DayEnd
    ) AS A_Date -- this is midnight of each current and next day
    OUTER APPLY
    (
        SELECT
          -- MAX(DayStart, StartTime)
          CASE WHEN DayStart > StartTime THEN DayStart ELSE StartTime END AS RangeStart

          -- MIN(DayEnd, EndTime)
          ,CASE WHEN DayEnd < EndTime THEN DayEnd ELSE EndTime END AS RangeEnd
        FROM @track AS T
        WHERE
            T.Email = CTE_Dates.Email
            AND T.StartTime < DayEnd
            AND T.EndTime > DayStart
    ) AS A_Track -- this is all tracks that intersect with the current day
WHERE
    Numbers.Number <= DATEDIFF(day, CTE_Dates.StartDate, CTE_Dates.EndDate)+1
GROUP BY DayStart, CTE_Dates.Email
ORDER BY DayStart;

Result

结果

Email    xDate         TrackMinutes
ABC      2015-02-14    189
ABC      2015-02-15    1440
ABC      2015-02-16    1189
ABC      2015-02-17    0
ABC      2015-02-18    0
ABC      2015-02-19    0
ABC      2015-02-20    910
ABC      2015-02-21    1271
ABC      2015-02-22    1434
ABC      2015-02-23    1309
ABC      2015-02-24    1189
ABC      2015-02-25    0
ABC      2015-02-26    0
ABC      2015-02-27    0
ABC      2015-02-28    360

You can still get TrackMinutes more than 1440, if two or more intervals in your data overlap.

如果数据中的两个或多个间隔重叠，您仍可以使TrackMinutes超过1440。

update

更新

You said in the comments that you have few rows in your data, where intervals do overlap and result has values more than 1440. You can wrap SUM into CASE to hide these errors in the data, but ultimately it is better to find these rows with problems and fix the data. You saw only few rows with values more than 1440, but there could be many more other rows with the same problem, which is not so visible. So, it is better to write a query that finds such overlapping rows and check how many there are and then decide what to do with them. The danger here is that at the moment you think that there are only few, but there could be a lot. This is beyond the scope of this question.

您在评论中说过，您的数据中有几行，其中间隔重叠，结果的值大于1440.您可以将SUM包装到CASE中以隐藏数据中的这些错误，但最终找到这些行最好问题并修复数据。您只看到几行的值超过1440，但可能会有更多其他行具有相同的问题，这是不可见的。因此，最好编写一个查找此类重叠行的查询，并检查有多少行，然后决定如何处理它们。这里的危险是，目前你认为只有少数，但可能会有很多。这超出了这个问题的范围。

To hide the problem replace this line in the query above:

要隐藏问题，请在上面的查询中替换此行：

,ISNULL(SUM(DATEDIFF(second, RangeStart, RangeEnd)) / 60, 0) AS TrackMinutes

with this:

有了这个：

,CASE 
WHEN ISNULL(SUM(DATEDIFF(second, RangeStart, RangeEnd)) / 60, 0) > 1440
THEN 1440
ELSE ISNULL(SUM(DATEDIFF(second, RangeStart, RangeEnd)) / 60, 0) 
END AS TrackMinutes

#2

I am making some guesses on the date ranges but this should be pretty close.

我对日期范围做了一些猜测，但这应该非常接近。

On my system I keep a view named cteTally which is my version of a tally table. Here is the code to create it.

在我的系统上，我保留了一个名为cteTally的视图，这是我的计数表的版本。这是创建它的代码。

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

Now we can utilize this to build your results. We just need to put in a couple other CTEs to get the date ranges established.

现在我们可以利用它来建立你的结果。我们只需要安排其他几个CTE来建立日期范围。

with DateRange as
(
    select MIN(FirstDate) as StartDate
        , MAX(LastUpdate) as EndDate 
    from track
)
, AllDates as
(
    select DateAdd(DAY, t.N - 1, StartDate) BaseDate
    from DateRange dr
    cross join cteTally t
    where t.N <= DATEDIFF(day, StartDate, EndDate) + 1
)

select t.Email
    , ad.BaseDate as xDate
    , t.DurationInSeconds as TrackMinutes
from AllDates ad
left join track t on cast(t.StartTime as date) = ad.BaseDate

#3

1. Create a table variable for the dates
2. 为日期创建表变量
3. Populate table in a WHILE loop
4. 在WHILE循环中填充表
5. Cross join to tracker data with the dates table variable
6. 使用日期表变量交叉连接到跟踪器数据
7. Convert values in column [DurationInSeconds] into minutes
8. 将[DurationInSeconds]列中的值转换为分钟
9. Replace nulls with zero
10. 将零替换为零

Code:

码：

DECLARE @dates TABLE ( ReportDates DATE )  
DECLARE @BeginDate AS DATE
  , @EndDate AS DATE
  , @RunDate AS DATE

SELECT @BeginDate = MIN(starttime) FROM dbo.track
SELECT @EndDate = MAX(starttime) FROM dbo.track

SET @RunDate = @BeginDate
WHILE @RunDate <= @EndDate
    BEGIN
        SET @RunDate = DATEADD(DAY, 1, @RunDate)
        INSERT  INTO @dates
        VALUES  ( @RunDate )
    END;

SELECT e.Email 
     , e.ReportDates
     , ISNULL(SUM(DurationInSeconds / 60), 0) AS TotDurationInMinutes
FROM (  SELECT  d.ReportDates
               ,t.email
        FROM    @dates AS d
        cross JOIN track AS t  
        GROUP BY d.ReportDates, t.Email ) AS e
LEFT JOIN track AS t ON e.ReportDates = CAST(t.StartTime AS DATE)
GROUP BY e.ReportDates, e.Email

Results:

结果：

Email ReportDates TotDurationInMinutes
----- ----------- ----------------------
ABC   2015-02-21  1439
ABC   2015-02-22  1357
ABC   2015-02-23  1969
ABC   2015-02-24  0
ABC   2015-02-25  0
ABC   2015-02-26  0
ABC   2015-02-27  0
ABC   2015-02-28  360
ABC   2015-03-01  0

#4

you should group by the day value. you could get the day with the function DATEPART as in : DATEPART(d,[StartTime])

你应该按日值分组。您可以使用DATEPART函数获取当天：DATEPART（d，[StartTime]）

SELECT cast([StartTime] as date) as date ,sum(datediff(n,[StartTime],[EndTime])) as "min" 
FROM [test].[dbo].[track] 
group by DATEPART(d,[StartTime]),cast([StartTime]as date)

#5

hope it helps

希望能帮助到你

SET NOCOUNT ON;

DROP TABLE #temp_table

CREATE TABLE #temp_table (
    Email VARCHAR(20)
    ,StartTime DATETIME
    ,DurationInSeconds INT
    ,
    )

DECLARE @Nextday DATETIME
    ,@Email VARCHAR(20)
    ,@StartTime DATETIME
    ,@DurationInSeconds INT
    ,@lastduration INT
    ,@currentduration INT
    ,@FirstDate DATETIME

SET @FirstDate = (
        SELECT TOP 1 LEFT(StartTime, 11)
        FROM track
        )

DECLARE vendor_cursor CURSOR
FOR
SELECT Email
    ,StartTime
    ,DurationInSeconds
FROM track

OPEN vendor_cursor

FETCH NEXT
FROM vendor_cursor
INTO @Email
    ,@StartTime
    ,@DurationInSeconds

WHILE @@FETCH_STATUS = 0
BEGIN
    IF EXISTS (
            SELECT 1
            FROM #temp_table
            WHERE LEFT(StartTime, 11) = LEFT(@StartTime, 11)
            )
    BEGIN
        SELECT @lastduration = DurationInSeconds
        FROM #temp_table
        WHERE LEFT(StartTime, 11) = LEFT(@StartTime, 11)

        SET @currentduration = @lastduration + @DurationInSeconds

        UPDATE #temp_table
        SET DurationInSeconds = @currentduration
        WHERE LEFT(StartTime, 11) = LEFT(@StartTime, 11)
    END
    ELSE
    BEGIN
        INSERT INTO #temp_table
        SELECT @Email
            ,@StartTime
            ,@DurationInSeconds

        SET @FirstDate = DATEADD(day, 1, @FirstDate)
    END

    IF NOT EXISTS (
            SELECT 1
            FROM track
            WHERE LEFT(StartTime, 11) = @FirstDate
            )
    BEGIN
        INSERT INTO #temp_table
        SELECT @Email
            ,@FirstDate
            ,0

        SET @FirstDate = DATEADD(day, 1, @FirstDate)
    END

    -- Get the next vendor.
    FETCH NEXT
    FROM vendor_cursor
    INTO @Email
        ,@StartTime
        ,@DurationInSeconds
END

CLOSE vendor_cursor;

DEALLOCATE vendor_cursor;

SELECT *
FROM #temp_table
ORDER BY StartTime