2. Programming Q&A
  3. sql - Pair and transpose adjacent records within the group - Stack Overflow

sql - Pair and transpose adjacent records within the group - Stack Overflow

admin2025-04-25  2

The device switch table (table name DeviceSwitchLog) is as follows:

Building Device Action ActionTime
Building1 Device1 Open 2025-01-01 00:09:59
Building1 Device1 Close 2025-01-01 00:18:55
Building1 Device1 Open 2025-01-01 00:32:06
Building1 Device1 Close 2025-01-01 00:39:57
Building1 Device1 Open 2025-01-01 00:54:18
Building1 Device1 Close 2025-01-01 00:56:49
Building1 Device1 Open 2025-01-01 00:57:31
Building1 Device1 Close 2025-01-01 01:13:34
Building1 Device2 Open 2025-01-01 01:14:26
Building1 Device2 Close 2025-01-01 01:20:41
Building1 Device2 Open 2025-01-01 01:27:53
Building1 Device2 Open 2025-01-01 01:33:01
Building1 Device2 Close 2025-01-01 01:36:58
Building1 Device2 Open 2025-01-01 01:45:22
Building1 Device2 Close 2025-01-01 01:51:36
Building2 Device1 Open 2025-01-01 02:03:20
Building2 Device1 Close 2025-01-01 02:12:34
Building2 Device1 Open 2025-01-01 02:27:44
Building2 Device1 Close 2025-01-01 02:31:46
Building2 Device1 Open 2025-01-01 02:45:10
Building2 Device1 Close 2025-01-01 02:56:47
Building2 Device1 Open 2025-01-01 03:11:51
Building2 Device2 Close 2025-01-01 03:12:08
Building2 Device2 Open 2025-01-01 03:26:07
Building2 Device2 Close 2025-01-01 03:37:08
Building2 Device2 Close 2025-01-01 03:40:43
Building2 Device2 Open 2025-01-01 03:47:12
Building2 Device2 Close 2025-01-01 03:50:50

The device switch table (table name DeviceSwitchLog) is as follows:

Building Device Action ActionTime
Building1 Device1 Open 2025-01-01 00:09:59
Building1 Device1 Close 2025-01-01 00:18:55
Building1 Device1 Open 2025-01-01 00:32:06
Building1 Device1 Close 2025-01-01 00:39:57
Building1 Device1 Open 2025-01-01 00:54:18
Building1 Device1 Close 2025-01-01 00:56:49
Building1 Device1 Open 2025-01-01 00:57:31
Building1 Device1 Close 2025-01-01 01:13:34
Building1 Device2 Open 2025-01-01 01:14:26
Building1 Device2 Close 2025-01-01 01:20:41
Building1 Device2 Open 2025-01-01 01:27:53
Building1 Device2 Open 2025-01-01 01:33:01
Building1 Device2 Close 2025-01-01 01:36:58
Building1 Device2 Open 2025-01-01 01:45:22
Building1 Device2 Close 2025-01-01 01:51:36
Building2 Device1 Open 2025-01-01 02:03:20
Building2 Device1 Close 2025-01-01 02:12:34
Building2 Device1 Open 2025-01-01 02:27:44
Building2 Device1 Close 2025-01-01 02:31:46
Building2 Device1 Open 2025-01-01 02:45:10
Building2 Device1 Close 2025-01-01 02:56:47
Building2 Device1 Open 2025-01-01 03:11:51
Building2 Device2 Close 2025-01-01 03:12:08
Building2 Device2 Open 2025-01-01 03:26:07
Building2 Device2 Close 2025-01-01 03:37:08
Building2 Device2 Close 2025-01-01 03:40:43
Building2 Device2 Open 2025-01-01 03:47:12
Building2 Device2 Close 2025-01-01 03:50:50

In fact, there are about tens of thousands of devices distributed in more than ten buildings, and the device numbers between different buildings may be repeated. The device numbers within the same building are unique. In the above device switch table, the Action field has two values: Open/Close, and the ActionTime field has a value corresponding to the time of the Open/Close event.

We now need to pair a group of records with adjacent Actions of Open and Close, and transpose them into one record. The result is as follows:

Building Device Open Close
Building1 Device1 2025-01-01 00:09:59 2025-01-01 00:18:55
Building1 Device1 2025-01-01 00:32:06 2025-01-01 00:39:57
Building1 Device1 2025-01-01 00:54:18 2025-01-01 00:56:49
Building1 Device1 2025-01-01 00:57:31 2025-01-01 01:13:34
Building1 Device2 2025-01-01 01:14:26 2025-01-01 01:20:41
Building1 Device2 2025-01-01 01:27:53
Building1 Device2 2025-01-01 01:33:01 2025-01-01 01:36:58
Building1 Device2 2025-01-01 01:45:22 2025-01-01 01:51:36
Building2 Device1 2025-01-01 02:03:20 2025-01-01 02:12:34
Building2 Device1 2025-01-01 02:27:44 2025-01-01 02:31:46
Building2 Device1 2025-01-01 02:45:10 2025-01-01 02:56:47
Building2 Device1 2025-01-01 03:11:51
Building2 Device2 2025-01-01 03:12:08
Building2 Device2 2025-01-01 03:26:07 2025-01-01 03:37:08
Building2 Device2 2025-01-01 03:40:43
Building2 Device2 2025-01-01 03:47:12 2025-01-01 03:50:50

The original data may be missing, meaning that the Open and Close records were supposed to form a pair, but in reality they may be missing either the Open or the Close, making it impossible to pair. In this case, fill in the blanks at the corresponding positions.

I hope to implement the above functions using SQL supported by Microsoft SQL Server 2012.

I tried it, my idea was to group the data by Building and Device first, then sort the data by time within the group, and then pair Open and Close within the group. The code I tried is as follows:

SELECT
    Building,
    Device,
    Action,
    ActionTime
FROM DeviceSwitchLog
GROUP BY Building, Device
ORDER BY ActionTime

But writing SQL in this way will result in an error. SQL requires grouping and performing aggregation at the same time. I can't find any solution now and don't know how to write it. Can any expert help me? Thank you!

  • sql
  • sql-server
Share Improve this question asked Jan 15 at 7:43 sonmasonma 11 bronze badge
Add a comment  | 

1 Answer 1

Reset to default 0

Sharing another approach by breaking down the logic into steps

  1. ORD_ACTION CTE gives the sequential number of all the actions for each building and device.

  2. Then we just filter the actions based on OPEN and CLOSE in OPENS and CLOSES CTE.

  3. The third part idea is to create pairs by first taking all opens into consideration (regardless of whether or not there is a corresponding close event) and then combining them with taking all closes into consideration(regardless of whether or not there is a corresponding open event).

Sample query

WITH ORD_ACTION AS (
    SELECT 
        BUILDING,DEVICE,ACTION,ACTIONTIME,
        ROW_NUMBER() OVER (PARTITION BY BUILDING, DEVICE ORDER BY ACTIONTIME) AS RN
    FROM TEST
),
OPENS AS (
    SELECT 
        BUILDING, DEVICE,ACTIONTIME AS OPENTIME,RN
    FROM ORD_ACTION
    WHERE ACTION = 'Open'
),
CLOSES AS (
    SELECT 
        BUILDING,DEVICE,ACTIONTIME AS CLOSETIME, RN
    FROM ORD_ACTION
    WHERE ACTION = 'Close'
)
SELECT 
    O.BUILDING, O.DEVICE,O.OPENTIME ,C.CLOSETIME 
FROM OPENS O
LEFT JOIN CLOSES C
    ON O.BUILDING = C.BUILDING
    AND O.DEVICE = C.DEVICE
    AND O.RN = C.RN - 1
UNION ALL
SELECT 
    C.BUILDING, C.DEVICE, O.OPENTIME ,C.CLOSETIME
FROM OPENS O
RIGHT JOIN CLOSES C
    ON O.BUILDING = C.BUILDING
    AND O.DEVICE = C.DEVICE
    AND O.RN = C.RN - 1
WHERE O.RN IS NULL
ORDER BY BUILDING, DEVICE,OPENTIME ;

Output

DEMO Fiddle

BUILDING DEVICE OPENTIME CLOSETIME
Building1 Device1 2025-01-01 00:09:59.000 2025-01-01 00:18:55.000
Building1 Device1 2025-01-01 00:32:06.000 2025-01-01 00:39:57.000
Building1 Device1 2025-01-01 00:54:18.000 2025-01-01 00:56:49.000
Building1 Device1 2025-01-01 00:57:31.000 2025-01-01 01:13:34.000
Building1 Device2 2025-01-01 01:14:26.000 2025-01-01 01:20:41.000
Building1 Device2 2025-01-01 01:27:53.000 null
Building1 Device2 2025-01-01 01:33:01.000 2025-01-01 01:36:58.000
Building1 Device2 2025-01-01 01:45:22.000 2025-01-01 01:51:36.000
Building2 Device1 2025-01-01 02:03:20.000 2025-01-01 02:12:34.000
Building2 Device1 2025-01-01 02:27:44.000 2025-01-01 02:31:46.000
Building2 Device1 2025-01-01 02:45:10.000 2025-01-01 02:56:47.000
Building2 Device1 2025-01-01 03:11:51.000 null
Building2 Device2 null 2025-01-01 03:12:08.000
Building2 Device2 null 2025-01-01 03:40:43.000
Building2 Device2 2025-01-01 03:26:07.000 2025-01-01 03:37:08.000
Building2 Device2 2025-01-01 03:47:12.000 2025-01-01 03:50:50.000
转载请注明原文地址:http://anycun.com/QandA/1745594730a90932.html

sqlPair and transpose adjacent records within the groupStack Overflow