如何计算组数据的日期差异

I would like to count the date-time difference, when I group the data.

当我对数据进行分组时,我想计算日期 - 时间差异。

Lets have a look on table content example :

让我们来看看表格内容示例:

id | session_id | created
1  | 101        | 1/10/2010 9:30:10
2  | 102        | 1/10/2010 9:31:10
3  | 101        | 1/10/2010 9:32:10
4  | 103        | 1/10/2010 9:35:10
5  | 102        | 1/10/2010 9:38:10
6  | 103        | 1/10/2010 9:39:10

Output should be as follow :

输出应如下:

session_id | time_count
101        | 2 (minutes)
102        | 7 (minutes)
103        | 4 (minutes)

So here i want to calculate time difference of 'created' field for data which is group by session_id.

所以这里我想计算按session_id分组的数据的'created'字段的时间差。

Any help would be great. Thanks in advance :)

任何帮助都会很棒。提前致谢 :)

My Situation :

我的情况 :

I have a table with following fields :

我有一个包含以下字段的表:

id, session_id, platform, event, created

there are 2 event (Start,End)

有2个事件(开始,结束)

Now if there are records of Start and End for same session_id then i want the time takes to complete this session.

现在,如果有相同session_id的Start和End记录,那么我想要完成此会话所需的时间。

But if i have 2 Start session, but only 1 End session then i do not want the time difference for 2nd Session because it did not end

但如果我有2个开始会话,但只有1个结束会话,那么我不希望第二个会话的时间差,因为它没有结束

3 个解决方案

#1

SELECT session_id,
DATEDIFF(MINUTE, MAX(created), MIN(created)) AS diff
FROM table
GROUP BY session_id

#2

Try this:

Table Schema:

CREATE TABLE A(id INT, session_id INT,Event VARCHAR(20), created DATETIME);

INSERT INTO A VALUES(1, 101,'Start','2010/10/01 9:30:10')
,(2, 102,'Start'  , '2010/10/01 9:31:10')
,(3, 101,'End'    , '2010/10/01 9:32:10')
,(4, 103,'Start'  , '2010/10/01 9:35:10')
,(5, 102,'End'    , '2010/10/01 9:38:10')
,(6, 103,'End'    , '2010/10/01 9:39:10')
,(7, 101,'Start'  , '2010/10/01 9:39:10')
,(8, 102,'Start'  , '2010/10/01 9:39:10')
,(9, 101,'End'    , '2010/10/01 9:55:10');

Query:

SELECT D.session_id
    ,TIMESTAMPDIFF(MINUTE,MIN(D.created), MAX(D.created)) time_count
FROM(
  SELECT a.id,a.session_id,a.created
    ,ROUND(count(*)/2,0) as RN 
    ,count(*) as row_number1 
  FROM a AS a
  JOIN a AS b ON a.session_id = b.session_id AND a.id >= b.id
  GROUP BY a.id,a.session_id,a.created
  ORDER BY 2,3
    )D
GROUP BY D.session_id,D.RN
HAVING COUNT(1)>1

Output:

session_id  time_count
101         2
102         7
103         4
101         16

Fiddle:

Check the Output in the #SQL Fiddle

检查#SQL小提琴中的输出

#3

-1

You can try it's sintax :

你可以尝试它的sintax:

    WITH cte AS (
    SELECT
        session_id,
        DATEDIFF(minute, created, LAG(created, 1)) AS diff,
        ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY created) rn
    FROM yourTable
)

SELECT

        session_id,
        diff AS time_count
    FROM cte
    WHERE rn % 2 = 0

;

#1

SELECT session_id,
DATEDIFF(MINUTE, MAX(created), MIN(created)) AS diff
FROM table
GROUP BY session_id

#2