SQL Group By 用于排序时间序列中的值

Question

下面是我正在努力实现的一个例子:

表A:

|    type    |  message  |          createdAt         | 
|:----------:|:---------:|:--------------------------:|
| create     |  awesome  | 2017-07-21 11:20:35.147629 |     
| create     |  hello    | 2017-07-21 11:20:35.147629 |    
| create     |  good     | 2017-07-22 10:20:34.123331 |     
| upload     |  nope     | 2017-07-22 11:28:08.815828 |      
| create     |  test     | 2017-07-22 11:29:35.147629 |    
| create     |  hello    | 2017-07-22 12:20:35.147629 | 

期望的输出:

|  type  |  new_message |       new_createdAt        |
|:------:|:------------:|:--------------------------:|
| create |      3       | 2017-07-22 10:20:34.123331 |
| upload |     nope     | 2017-07-22 11:28:08.815828 |
| create |      2       | 2017-07-22 12:20:35.147629 |

只有在createdAt序列中，SQL语句才应该组合相似的type。如果序列中相似 type 值的数量大于 1，则 new_message 是计数，否则 new_message 与 message(这个 if-else 子句不是最重要的功能，只给出计数的语句也可以)。

谢谢。

更新

是否可以在时间序列中添加另一个因素，仅当最低和最高 createdAt 之间的差异为 X 时才分组。

例如如果我选择 X=24 小时，表 A 的输出将更改为:

|  type  |  new_message |       new_createdAt        |
|:------:|:------------:|:--------------------------:|
| create |      2       | 2017-07-21 11:20:35.147629 |
| create |     good     | 2017-07-22 10:20:34.123331 |
| upload |     nope     | 2017-07-22 11:28:08.815828 |
| create |      2       | 2017-07-22 12:20:35.147629 |

没有 JOIN 有没有办法做到这一点。

Answer 1

您可以使用 ROW_NUMBER 的差异:

WITH CteRn AS(
    SELECT *,
        ROW_NUMBER() OVER(ORDER BY createdAt)
          - ROW_NUMBER() OVER(PARTITION BY type ORDER BY createdAt) AS rn
    FROM Tbl
)
SELECT
    type,
    CASE 
        WHEN COUNT(*) > 1 THEN CAST(COUNT(*) AS VARCHAR(30)) 
        ELSE MAX(cte.message)
    END AS message,
    MAX(cte.createdAt) AS createdAt
FROM CteRn cte
GROUP BY cte.type, cte.rn
ORDER BY MAX(cte.createdAt);

ONLINE DEMO