根据时间序列中的序列岛对表行进行交叉制表 [英] Crosstabbing rows based on sequence islands in a list of times

查看：49
本文介绍了根据时间序列中的序列岛对表行进行交叉制表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

只写问题标题就伤了我的头！

Just writing the question title hurt my head!

请忍受我！请参阅以下DDL&虚拟数据:

Please bear with me! Please see the following DDL & Dummy Data:

CREATE TABLE [dbo].[tbl_Example](
    [Date] [date] NULL,
    [Time] [time](0) NULL,
    [Branch] [varchar](3) NULL,
    [Count] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00907E0000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00CC7E0000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00087F0000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00447F0000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00807F0000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00BC7F0000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00F87F0000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0034800000000000 AS Time), N'001', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0070800000000000 AS Time), N'001', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00AC800000000000 AS Time), N'001', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00E8800000000000 AS Time), N'001', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0024810000000000 AS Time), N'001', 2)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0060810000000000 AS Time), N'001', 2)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x009C810000000000 AS Time), N'001', 2)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00D8810000000000 AS Time), N'001', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0014820000000000 AS Time), N'001', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0050820000000000 AS Time), N'001', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x008C820000000000 AS Time), N'001', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00C8820000000000 AS Time), N'001', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0004830000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0040830000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00C4860000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0000870000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x003C870000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0078870000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00B4870000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00907E0000000000 AS Time), N'002', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00CC7E0000000000 AS Time), N'002', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00087F0000000000 AS Time), N'002', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00447F0000000000 AS Time), N'002', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00807F0000000000 AS Time), N'002', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00BC7F0000000000 AS Time), N'002', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00F87F0000000000 AS Time), N'002', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0034800000000000 AS Time), N'002', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0070800000000000 AS Time), N'002', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00AC800000000000 AS Time), N'002', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00E8800000000000 AS Time), N'002', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0024810000000000 AS Time), N'002', 2)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0060810000000000 AS Time), N'002', 2)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x009C810000000000 AS Time), N'002', 2)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00D8810000000000 AS Time), N'002', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0014820000000000 AS Time), N'002', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0050820000000000 AS Time), N'002', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x008C820000000000 AS Time), N'002', 1)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00C8820000000000 AS Time), N'002', 3)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0004830000000000 AS Time), N'002', 3)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0040830000000000 AS Time), N'002', 3)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x007C830000000000 AS Time), N'002', 3)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00B8830000000000 AS Time), N'002', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00F4830000000000 AS Time), N'002', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0030840000000000 AS Time), N'002', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x006C840000000000 AS Time), N'002', 0)

现在，我需要按如下方式渲染虚拟数据:

Now, i need to render the dummy data as follows:

Branch  Date        Start Time  End Time    EntryCount
======  ====        ==========  ========    ========
001     12/12/2011  09:00:00    09:06:00    0
001     12/12/2011  09:07:00    09:10:00    1
001     12/12/2011  09:11:00    09:13:00    2
001     12/12/2011  09:14:00    09:18:00    1
001     12/12/2011  09:19:00    09:20:00    0
001     12/12/2011  09:35:00    09:39:00    0
002     13/12/2011  09:00:00    09:06:00    0
002     13/12/2011  09:07:00    09:10:00    1
002     13/12/2011  09:11:00    09:13:00    2
002     13/12/2011  09:14:00    09:17:00    1
002     13/12/2011  09:18:00    09:21:00    3
002     13/12/2011  09:22:00    09:25:00    0

如您所见，我需要将孤岛的开始和结束时间提取为单独的列，并根据分支"，日期"和日期"来谨慎地分割孤岛. EntryCount.当您处理一个简单的数字序列时，我见过聪明的解决方案，但是我无法让它们适合这种情况.我很想看看大家如何处理这个问题.

As you can see, i need to extract the island start and end time as seperate columns, making care to split islands based on the Branch, Date & EntryCount. Ive seen clever solutions to do this when your dealing with a simple numeric sequence - but i cant get them to fit in this scenario. I'm interested to see how you folks would approach this.

我正在使用SQL Server 2008 +

I'm using SQL Server 2008+

屏幕快照显示了岛屿边界，原因可能是分支发生了变化，日期或计数

A screenshot showing the Island boundres, either because the branch changed, the date, or the Count

推荐答案

;WITH T
     AS (SELECT *,
                DATEPART(HOUR, Time) * 60 + 
                DATEPART(MINUTE, Time) -
                ROW_NUMBER() OVER (PARTITION BY Branch, 
                                                Date, 
                                                Count 
                                        ORDER BY Time) AS Grp
         FROM   [dbo].[tbl_Example])
SELECT Branch,
       Date,
       MIN(Time) AS [Start Time],
       MAX(Time) AS [End Time],
       [Count]   AS EntryCount
FROM   T
GROUP  BY Branch,
          Date,
          [Count],
          Grp
ORDER  BY Branch,
          Date,
          [Start Time]

这篇关于根据时间序列中的序列岛对表行进行交叉制表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆