按日期或时间顺序查看分组记录.可能是按级别分组的日志表

问题描述

假设您的 SQL Server(目前是 SQL Server 2008)数据库中有一个如下所示的表:

Say you had a table in your SQL Server ( prensently SQL Server 2008) database that look like this:

Date                    Id State
=================================
2013-09-12 15:02:41,844 1 OK
2013-09-12 15:02:41,844 2 OK
2013-09-12 15:02:41,844 3 ERROR
2013-09-12 15:02:41,844 4 ERROR
2013-09-12 15:02:41,844 5 ERROR
2013-09-13 15:02:41,844 1 ERROR
2013-09-14 15:02:41,844 1 OK
2013-09-15 15:02:41,844 1 ERROR
2013-09-15 15:02:41,844 2 ERROR

表的目的是保存记录状态.我写了这张表，但现在我不知道如何查询它以获得时间序列中不同状态的概览.

The purpouse of the table is to save records state. I wrote this table but now i cannot figure out how to query it for an overview of the different states during a time sequence.

我正在寻找的结果是:

2013-09-12 16:00:00 OK 2
2013-09-12 16:00:00 ERROR 3
2013-09-13 16:00:00 OK 1
2013-09-13 16:00:00 ERROR 4
2013-09-14 16:00:00 OK 2
2013-09-14 16:00:00 ERROR 3
2013-09-15 16:00:00 OK 0
2013-09-15 16:00:00 ERROR 5

编辑:我想要实现的是我的对象状态的日常视图.哪些对象有错误，哪些没问题.也许车库是一个更好的例子，但我会坚持这个.

EDIT: What i am trying to achieve is a day-by-day view of the state of my objects. which objects has an error and which ones are ok. Maybe a garage would be a better example, but i will stick to this.

12 日，3 个对象出现错误，2 个对象正常.
在 13 日，一个对象从 OK 变为 Error.我现在有 4 个对象处于 ERROR 状态，一个正常.
在 14 日，我的 ID 为 1 的对象已修复，现在我又回到了两个正常的对象和三个具有错误状态的对象

我已经想出了(找到)如何像这样生成日期序列(不使用变量声明):

I have figured out (found) how to generate a sequence of dates (without using variable declarations) like this:

;WITH dates
AS (
    SELECT CAST('2013-12-17 16:00:00' AS DATETIME) 'date'

    UNION ALL

    SELECT DATEADD(dd, 1, t.DATE)
    FROM dates t
    WHERE DATEADD(dd, 1, t.DATE) <= GETDATE()
    )
SELECT dates.DATE   
FROM dates

而且我有一个查询，它执行相关分组(我认为)以提供所需的输出(和点):

And i have a query that does the relevant grouping (i think) that delivers a desired output (and point):

Date State Count(state)
=======================    
2013-09-12 16:00:00 OK 2
        2013-09-12 16:00:00 ERROR 3
        2013-09-13 16:00:00 ERROR 1
        2013-09-14 16:00:00 OK 1
        2013-09-15 16:00:00 ERROR 1

那么问题是，你如何将日期序列与我的分组结果结合起来以达到预期的结果.

So the question is, how do you marry the sequence of dates to my grouping result to achieve the desired result.

推荐答案

通过 SQL Server 2012 提供的窗口函数，您可以使用以下查询:

With SQL Server 2012 providing window functions you can use the following query:

SELECT date,
       sum(ok_mod) over (order by date) as ok
FROM (
  SELECT date,
         sum(case when state = 'OK' then 1
                  when state = 'ERROR' and prev_state is not null then -1
                  else 0
             end) as ok_mod
  FROM (
    SELECT date, id, state,
           lag(state) over (partition by id order by date) prev_state
    FROM data
  ) AS data
  GROUP BY date
) AS data
ORDER BY date;

这仅给您 OK 部分，但您也可以轻松地将 ERROR 部分的相应查询联合起来.SQL Fiddle 此处.

This gives you only OK part, but you can easily union corresponding query for ERROR part also. SQL Fiddle here.

使用 2008 版本恕我直言，如果没有过程或非常复杂的查询，您将无法实现.

With 2008 version imho you won't make it without a procedure or really complex query.