sql - 计数状态变化

Question

我想知道是否可以计算记录中值之间的变化次数

例如我有以下数据

| radio | name | state | timestamp           |
|-------|------|-------|---------------------|
| 1     | AP1  | down  | 2013-03-07 10:00:00 |
| 1     | AP1  | down  | 2013-03-07 10:15:00 |
| 1     | AP1  | up    | 2013-03-07 10:30:00 |
| 1     | AP1  | down  | 2013-03-07 10:45:00 |
| 1     | AP1  | pend  | 2013-03-07 11:00:00 |
| 1     | AP1  | pend  | 2013-03-07 11:15:00 |
| 1     | AP1  | pend  | 2013-03-07 11:30:00 |
| 1     | AP1  | pend  | 2013-03-07 11:45:00 |

我需要执行以下技巧的查询

| name | S1     | S2     | S3       | S4         | (other time range) |
| AP1  | A      | A      | B        | A          | C                  |
                     \      /    \      /    \      /
                      DETECT      DETECT      DETECT    
                            \        \       / 
                             \------- \     /
                                       COUNT = 3 <-- this is the number i want

到目前为止，我还停留在基于计数的 SQL 中。

SELECT radio, name, state, COUNT(state) AS count FROM data
WHERE timestamp BETWEEN '2013-03-07 10:00:00' AND '2013-03-07 11:00:00'
GROUP BY radio, name, channel

结果会是

| radio | name | state | count |
|-------|------|-------|-------|
| 1     | AP1  | down  | 3     |
| 1     | AP1  | up    | 1     |
| 1     | AP1  | pend  | 1     |

所以现在的结果将是 3 行，也就是 3 次状态变化，但后来我意识到这不是真的，因为这个数字只会告诉我不同​​州名称的数量，在本例中为 3。

所以我的问题是这在 SQL 中是否可行，或者我是否需要将结果集拉入循环并通过外部脚本或 View 检测更改。

Answer 1

你可以使用类似的东西:

with Changes as
(
  select name
    , stateChange = case when prev.state is null then 0
      when sc.state <> prev.state then 1
      else 0
      end
  from data sc
    outer apply
    (
      select top 1 prev.state
      from data prev
      where sc.timestamp > prev.timestamp
        and sc.name = prev.name
      order by prev.timestamp desc
    ) prev
)
select name, stateChanges = sum(stateChange)
from Changes
group by name

SQL Fiddle with demo .

另外，在你的问题中，你的数据有点不一致；有时您会提到 AP1，有时会提到 AP2。如果上述查询/ fiddle 还不够，请提供更大的数据集。