时间戳

首页 > 解决方案 > 如何根据条件增加计数

sql - 如何根据条件增加计数

问题描述

我正在尝试根据条件提取新列，因此新列将充当计数器，并且它只会计算 if (MessageDate1- MessageDate2).TotalMinutes > 10。

例如，如果我有以下输入：

╔════════════════╦═══════════════════════╦═══════════════════════╗
║ ConversationId ║     MessageDate1      ║     MessageDate2      ║
╠════════════════╬═══════════════════════╬═══════════════════════╣
║              1 ║ 2/15/2012 11:53:16 AM ║ 1/1/0001 12:00:00 AM  ║
║              1 ║ 2/16/2012 11:53:20 AM ║ 2/15/2012 11:53:16 AM ║
║              1 ║ 2/16/2012 11:54:01 AM ║ 2/16/2012 11:53:20 AM ║
║              1 ║ 2/16/2012 11:05:03 PM ║ 2/16/2012 11:54:01 AM ║
║              1 ║ 2/16/2012 11:09:03 PM ║ 2/16/2012 11:05:03 PM ║
║              1 ║ 2/16/2012 11:11:03 PM ║ 2/16/2012 11:09:03 PM ║
║              1 ║ 2/16/2012 11:11:04 PM ║ 2/16/2012 11:11:03 PM ║
║              1 ║ 2/16/2012 11:11:05 PM ║ 2/16/2012 11:11:04 PM ║
║              1 ║ 2/16/2012 11:22:06 PM ║ 2/16/2012 11:11:05 PM ║
║              1 ║ 2/17/2012 11:05:03 PM ║ 2/16/2012 11:22:06 PM ║
║              1 ║ 2/17/2012 11:09:03 PM ║ 2/17/2012 11:05:03 PM ║
║              1 ║ 2/17/2012 11:11:03 PM ║ 2/17/2012 11:09:03 PM ║
║              2 ║ 2/15/2012 11:53:18 AM ║ 1/1/0001 12:00:00 AM  ║
║              2 ║ 2/16/2012 11:54:01 AM ║ 2/15/2012 11:53:18 AM ║
║              2 ║ 2/16/2012 11:54:02 AM ║ 2/16/2012 11:54:01 AM ║
║              3 ║ 2/16/2012 11:53:50 AM ║ 1/1/0001 12:00:00 AM  ║
║              3 ║ 2/16/2012 11:54:50 AM ║ 2/16/2012 11:53:50 AM ║
║              4 ║ 2/16/2012 11:54:04 AM ║ 1/1/0001 12:00:00 AM  ║
╚════════════════╩═══════════════════════╩═══════════════════════╝

所以输出将是：

╔════════════════╦═══════════════════════╦═══════════════════════╦═════════╗
║ ConversationID ║     MessageDate1      ║     MessageDate2      ║ Counter ║
╠════════════════╬═══════════════════════╬═══════════════════════╬═════════╣
║              1 ║ 2/15/2012 11:53:16 AM ║ 1/1/0001 12:00:00 AM  ║       1 ║
║              1 ║ 2/16/2012 11:53:20 AM ║ 2/15/2012 11:53:16 AM ║       2 ║
║              1 ║ 2/16/2012 11:54:01 AM ║ 2/16/2012 11:53:20 AM ║       2 ║
║              1 ║ 2/16/2012 11:05:03 PM ║ 2/16/2012 11:54:01 AM ║       3 ║
║              1 ║ 2/16/2012 11:09:03 PM ║ 2/16/2012 11:05:03 PM ║       3 ║
║              1 ║ 2/16/2012 11:11:03 PM ║ 2/16/2012 11:09:03 PM ║       3 ║
║              1 ║ 2/16/2012 11:11:04 PM ║ 2/16/2012 11:11:03 PM ║       3 ║
║              1 ║ 2/16/2012 11:11:05 PM ║ 2/16/2012 11:11:04 PM ║       3 ║
║              1 ║ 2/16/2012 11:22:06 PM ║ 2/16/2012 11:11:05 PM ║       4 ║
║              1 ║ 2/17/2012 11:05:03 PM ║ 2/16/2012 11:22:06 PM ║       5 ║
║              1 ║ 2/17/2012 11:09:03 PM ║ 2/17/2012 11:05:03 PM ║       5 ║
║              1 ║ 2/17/2012 11:11:03 PM ║ 2/17/2012 11:09:03 PM ║       5 ║
║              2 ║ 2/15/2012 11:53:18 AM ║ 1/1/0001 12:00:00 AM  ║       6 ║
║              2 ║ 2/16/2012 11:54:01 AM ║ 2/15/2012 11:53:18 AM ║       7 ║
║              2 ║ 2/16/2012 11:54:02 AM ║ 2/16/2012 11:54:01 AM ║       7 ║
║              3 ║ 2/16/2012 11:53:50 AM ║ 1/1/0001 12:00:00 AM  ║       8 ║
║              3 ║ 2/16/2012 11:54:50 AM ║ 2/16/2012 11:53:50 AM ║       8 ║
║              4 ║ 2/16/2012 11:54:04 AM ║ 1/1/0001 12:00:00 AM  ║       9 ║
╚════════════════╩═══════════════════════╩═══════════════════════╩═════════╝

标签： sqlsql-server

解决方案

设置一些虚拟数据来反映您提出的问题：

create table some_data
(
    id int,
    MessageDate1 datetime,
    MessageDate2 datetime
)

insert into some_data values
(1,'2012-02-15 11:53:16', '2012-02-15 00:00:00'),
(2,'2012-02-16 11:53:20', '2012-02-15 11:53:16'),
(3,'2012-02-16 11:54:01', '2012-02-15 11:53:20'),
(4,'2012-02-16 23:05:03', '2012-02-16 11:54:01'),
(5,'2012-02-16 23:09:03', '2012-02-16 23:05:03'),
(6,'2012-02-16 23:11:03', '2012-02-16 23:09:03'),
(7,'2012-02-16 23:11:04', '2012-02-16 23:11:03'),
(8,'2012-02-16 23:11:05', '2012-02-16 23:11:04'),
(9,'2012-02-16 23:22:06', '2012-02-16 23:11:05'),
(10,'2012-02-17 23:05:03', '2012-02-16 23:22:06'),
(11,'2012-02-17 23:09:03', '2012-02-17 23:05:03'),
(12,'2012-02-17 23:11:03', '2012-02-17 23:09:03'),
(13,'2012-02-15 11:53:18', '2012-02-15 00:00:00'),
(14,'2012-02-16 11:54:01', '2012-02-15 11:53:18'),
(15,'2012-02-16 11:54:02', '2012-02-16 11:54:01'),
(16,'2012-02-16 11:53:50', '2012-02-16 12:00:00'),
(17,'2012-02-16 11:54:50', '2012-02-16 11:53:50'),
(18,'2012-02-16 11:54:04', '2012-02-16 00:00:00')

我已经包含一个id字段以允许我对记录进行排序，因为我看不到任何特定逻辑来说明您的示例数据是如何排序的，并且计数的工作方式取决于记录的顺序。我还替换了您当年的日期值0001。

此查询给出了我认为所需的输出，Count每次遇到记录时MessageDate1和MessageDate2字段之间的差异超过 10 分钟时都会递增：

select
    MessageDate1,
    MessageDate2,
    sum(GT_10M) over (order by id) as [Count]
from
(
    select
        id,
        MessageDate1,
        MessageDate2,
        case when abs(datediff(minute,MessageDate2,MessageDate1)) > 10 then 1 else 0 end as GT_10M
    from some_data
) r

结果：

/-----------------------------------------------------------\
|      MessageDate1       |      MessageDate2       | Count |
|-------------------------|-------------------------|-------|
| 2012-02-15 11:53:16.000 | 2012-02-15 00:00:00.000 |   1   |
| 2012-02-16 11:53:20.000 | 2012-02-15 11:53:16.000 |   2   |
| 2012-02-16 11:54:01.000 | 2012-02-15 11:53:20.000 |   3   |
| 2012-02-16 23:05:03.000 | 2012-02-16 11:54:01.000 |   4   |
| 2012-02-16 23:09:03.000 | 2012-02-16 23:05:03.000 |   4   |
| 2012-02-16 23:11:03.000 | 2012-02-16 23:09:03.000 |   4   |
| 2012-02-16 23:11:04.000 | 2012-02-16 23:11:03.000 |   4   |
| 2012-02-16 23:11:05.000 | 2012-02-16 23:11:04.000 |   4   |
| 2012-02-16 23:22:06.000 | 2012-02-16 23:11:05.000 |   5   |
| 2012-02-17 23:05:03.000 | 2012-02-16 23:22:06.000 |   6   |
| 2012-02-17 23:09:03.000 | 2012-02-17 23:05:03.000 |   6   |
| 2012-02-17 23:11:03.000 | 2012-02-17 23:09:03.000 |   6   |
| 2012-02-15 11:53:18.000 | 2012-02-15 00:00:00.000 |   7   |
| 2012-02-16 11:54:01.000 | 2012-02-15 11:53:18.000 |   8   |
| 2012-02-16 11:54:02.000 | 2012-02-16 11:54:01.000 |   8   |
| 2012-02-16 11:53:50.000 | 2012-02-16 12:00:00.000 |   8   |
| 2012-02-16 11:54:50.000 | 2012-02-16 11:53:50.000 |   8   |
| 2012-02-16 11:54:04.000 | 2012-02-16 00:00:00.000 |   9   |
\-----------------------------------------------------------/

推荐阅读