代码之家  ›  专栏  ›  技术社区  ›  JohnMark Sill

通过一个条件将子集合从一行求和到另一行

sql-server-2012 sql-server sql
  •  0
  • JohnMark Sill  · 技术社区  · 6 年前

    我不太熟悉SQL的语法,正在努力理解如何聚合一组简单的数据。

    问题是:

    基于 'Open' 和 'Closed' 时间戳,我必须使用下面的登录历史记录表总结用户在每个工作站上花费的时间量。

    要求:

    • 工作站或用户名列为空的行应标记为无效
    • 按不以开头的时间戳排序的每一行的状态 '打开' 或以结束 '已关闭' 还应标记为无效
    • 倍数 '打开' a之前的状态 '已关闭' 仍然可以认为有效,但总和应从 '打开' 
    +-----------------+-------------+--------+------------+-------------------------+
|       KEY       | WORKSTATION | STATUS |  USERNAME  |        TIMESTAMP        |
+-----------------+-------------+--------+------------+-------------------------+
| 181861-0001-001 |             | Closed |            | 2015-07-01 18:19:48.527 |
| 181861-0001-001 |             | Closed |            | 2015-07-01 20:20:46.383 |
| 181861-0001-001 | 1AHVW       | Open   | ANDJOH0427 | 2015-07-01 13:18:46.547 |
| 181861-0001-001 | 1AHVW       | Closed | ANDJOH0427 | 2015-07-01 14:11:41.920 |
| 181861-0001-001 | 1ALVW       | Closed | DWYGRE0609 | 2015-07-01 18:29:39.127 |
| 181861-0001-001 | 1ALVW       | Closed | DWYGRE0609 | 2015-07-01 18:29:40.300 |
| 181861-0001-001 | 1AHVW       | Closed | HORDOU0521 | 2015-07-01 19:27:34.667 |
| 181861-0001-001 | 1AHVW       | Closed | HORDOU0521 | 2015-07-01 19:44:36.167 |
| 181861-0001-001 | 1AQCI       | Open   | POUJON702  | 2015-07-02 00:46:37.540 |
| 181861-0001-001 | 1ALVW       | Open   | PRIADA747  | 2015-07-01 14:51:02.937 |
| 181861-0001-001 | 1ALVW       | Open   | PRIADA747  | 2015-07-01 15:29:48.357 |
| 181861-0001-001 | 1ALVW       | Open   | PRIADA747  | 2015-07-01 16:13:20.953 |
| 181861-0001-001 | 1ALVW       | Open   | PRIADA747  | 2015-07-01 17:49:42.717 |
| 181861-0001-001 | 1ALVW       | Closed | PRIADA747  | 2015-07-01 17:53:28.217 |
| 181861-0001-001 | 1ALVW       | Open   | PRIADA747  | 2015-07-01 18:34:11.043 |
| 181861-0001-001 | 1ALVW       | Closed | PRIADA747  | 2015-07-01 19:20:11.540 |
+-----------------+-------------+--------+------------+-------------------------+

    我的(几乎)解决方案: 

    SELECT Project, username, Workstation, 
min(case when [Status] = 'Open' then [TimeStamp] end) AS [Started],
max(case when [Status] = 'Closed' then [TimeStamp] end) as [Ended],
DATEDIFF(second, min(case when [Status] = 'Open' then [TimeStamp] end), max(case when [Status] = 'Closed' then [TimeStamp] end)) AS ActualSeconds
FROM History
GROUP BY Project, username, Workstation

    遗憾的是,此查询无法解释用户登录、注销然后重新登录到同一工作站的情况。

    因此,我需要找到 MIN 在 '打开' 和 '已关闭' 地位

    预期结果: 

    +-----------------+-------------+------------+-------------------------+-------------------------+---------+
|       KEY       | WORKSTATION |  USERNAME  |        START TIME       |        END TIME         | SECONDS |
+-----------------+-------------+------------+-------------------------+-------------------------+---------+
| 181861-0001-001 |             |            | NULL                    | 2015-07-01 18:19:48.527 | NULL    |
| 181861-0001-001 | 1AHVW       | ANDJOH0427 | 2015-07-01 13:18:46.547 | 2015-07-01 14:11:41.920 | 3175    |
| 181861-0001-001 | 1ALVW       | DWYGRE0609 | NULL                    | 2015-07-01 18:29:39.127 | NULL    |
| 181861-0001-001 | 1AHVW       | HORDOU0521 | NULL                    | 2015-07-01 19:27:34.667 | NULL    |
| 181861-0001-001 | 1AQCI       | POUJON702  | 2015-07-02 00:46:37.540 | NULL                    | NULL    |
| 181861-0001-001 | 1ALVW       | PRIADA747  | 2015-07-01 14:51:02.937 | 2015-07-01 17:53:28.217 | 10945   |
| 181861-0001-001 | 1ALVW       | PRIADA747  | 2015-07-01 18:34:11.043 | 2015-07-01 19:20:11.540 | 2760    |
+-----------------+-------------+------------+-------------------------+-------------------------+---------+
    2 回复  |  直到 6 年前
        1
  •  0
  •   shawnt00    6 年前

    这应该很接近: 

    with data as (
    select *,
        row_number() over (partition by workstation order by timestamp) as tn,
        row_number() over (partition by workstation order by username, timestamp) as un,
        sum(case when status = 'Closed' then 1 end) over (
            partition by workstation, username order by timestamp desc) as sn
    from t
)
select workstation, username,
    min(case when status = 'Open' then timestamp end) as start_time,
    max(case when status = 'Closed' then timestamp end) as end_time,
    datediff(second,
        min(case when status = 'Open' then timestamp end),
        max(case when status = 'Closed' then timestamp end)) as diff,
    datediff(millisecond,
        min(case when status = 'Open' then timestamp end), 
        max(case when status = 'Closed' then timestamp end)) / 1000 as diff2,
case when count(*) > 1 then 'Valid' else 'Invalid' end as flag
from data
group by workstation, username, tn - un, sn;

    http://rextester.com/JXS10553

    我注意到您预期输出中的时差不太匹配。问题是因为 datediff() 计算时间边界,而不是测量整个单位(在这种情况下为秒)我添加了第二种计算秒数的方法,它可以产生您所期望的结果。

        2
  •  0
  •   Thom A    6 年前

    这实际上是猜测,因为示例数据和OP提供的预期输出似乎不相关。这为用户名提供了正确的结果 NULL ,则, 'ANDJOH0427' ,则, 'HORDOU0521' 和 'DWYGRE0609' 但是,返回的结果 'PRIADA747' (从预期结果集中排除)并给出了非常不同的答案 'POUJON702' : 

    USE Sandbox;
GO

CREATE TABLE #Sample ([KEY] varchar(15),
                      WORKSTATION varchar(5),
                      [STATUS] varchar(6),
                      USERNAME varchar(10),
                      [TIMESTAMP] datetime);

INSERT INTO #Sample
VALUES ('181861-0001-001',NULL,'Closed',NULL,'2015-07-01T18:19:48.527'),
       ('181861-0001-001',NULL,'Closed',NULL,'2015-07-01T20:20:46.383'),
       ('181861-0001-001','1AHVW','Open','ANDJOH0427','2015-07-01T13:18:46.547'),
       ('181861-0001-001','1AHVW','Closed','ANDJOH0427','2015-07-01T14:11:41.920'),
       ('181861-0001-001','1ALVW','Closed','DWYGRE0609','2015-07-01T18:29:39.127'),
       ('181861-0001-001','1ALVW','Closed','DWYGRE0609','2015-07-01T18:29:40.300'),
       ('181861-0001-001','1AHVW','Closed','HORDOU0521','2015-07-01T19:27:34.667'),
       ('181861-0001-001','1AHVW','Closed','HORDOU0521','2015-07-01T19:44:36.167'),
       ('181861-0001-001','1AQCI','Open','POUJON702','2015-07-02T00:46:37.540'),
       ('181861-0001-001','1ALVW','Open','PRIADA747','2015-07-01T14:51:02.937'),
       ('181861-0001-001','1ALVW','Open','PRIADA747','2015-07-01T15:29:48.357'),
       ('181861-0001-001','1ALVW','Open','PRIADA747','2015-07-01T16:13:20.953'),
       ('181861-0001-001','1ALVW','Open','PRIADA747','2015-07-01T17:49:42.717'),
       ('181861-0001-001','1ALVW','Closed','PRIADA747','2015-07-01T17:53:28.217'),
       ('181861-0001-001','1ALVW','Open','PRIADA747','2015-07-01T18:34:11.043'),
       ('181861-0001-001','1ALVW','Closed','PRIADA747','2015-07-01T19:20:11.540');
GO
SELECT *
FROM #Sample;
GO

WITH Starts AS(
    SELECT [KEY],
           WORKSTATION,
           USERNAME,
           [TIMESTAMP],
           NULLIF(MIN(ISNULL(CASE STATUS WHEN 'Open' THEN [TIMESTAMP] END,'20550101')) OVER (PARTITION BY [KEY], WORKSTATION, USERNAME),'20550101') AS StartTime
    FROM #Sample S)
SELECT [KEY],
       WORKSTATION,
       USERNAME,
       StartTime,
       MAX([TIMESTAMP]) AS EndTime,
       DATEDIFF(SECOND, StartTime, MAX([TIMESTAMP])) AS Seconds
FROM Starts
GROUP BY [KEY],
         WORKSTATION,
         USERNAME,
         StartTime;
GO      
DROP TABLE #Sample;
    推荐文章
    K.Z  ·  无法使用ADO获取Insert命令的记录Id。NET 4.0中的NET
    2 年前
    Duvan  ·  将逗号(,)替换为点(.)[副本]
    2 年前
    Yara1994  ·  如果另一列具有特定值-SQL Server,则删除具有唯一Id的所有行
    2 年前
    Simon Worth  ·  是否可以使用SqlClient自动获取查询字符串中定义的参数列表
    2 年前
    Mateen Bagheri  ·  选择表的计数并选择其自身
    2 年前
    SoT  ·  SQL Server中求和函数的工作方式
    2 年前
    rapha123  ·  如何使用REPLACE函数使用SQL更改这两个单词?
    2 年前
    NKAT  ·  将列值聚合到列表中会产生错误
    2 年前
    deanpillow  ·  返回两列中有一个匹配值的记录
    2 年前
    snowflakes74  ·  在Dapper中异步查询多个结果
    2 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号