根据布尔列对开始和停止日期进行分组

发布于 2025-01-15 12:12:08 字数 2511 浏览 1 评论 0原文

我在 SQL 服务器上有以下记录器表。该表记录电机启动和停止的时间。

日期	状态
2022-03-01 08:00:00.000	1
2022-03-01 08:30:00.000	0
2022-03-01 09:00:00.000	0
2022-03-01 09:30:00.000	1
2022-03- 01 10:00:00.000	1
2022-03-01 10:30:00.000	1
2022-03-01 11:00:00.000	0
2022-03-01 12:30:00.000	0

Where Status=1 is working and 0 is stopped

我想确定开始时间和停止时间在每个周期并计算经过的时间（工作时间分钟）。下表是想要的结果。

开始	停止	工作时间
2022-03-01 08:00:00.000	2022-03-01 08:30:00.000	30
2022-03-01 09:30:00.000	2022-03-01 11:00:00.000	90

此外，如果你放一些的想法和评论procedure，（作为 SQL 的初学者）这对我有很大帮助！

更新

正如我所提到的，我正在学习 SQL...以下来源为我提供了如何继续的观点，但在某一点之后我对如何适应我的情况感到困惑。更具体地说明如何检查与先前记录测试 lag() 和 DATEDIFF() 的日期时间差异。

https://community.looker.com/sql-10/sql-pattern-summarizing-entities-with-a-start-end-date-over-time-4868

按开始和结束时间分组的条目数

时间窗口定义的开始/停止事件中列的总和值

原文

I have the following logger table on a SQL server. This table logs when a motor starts and stops.

Date	Status
2022-03-01 08:00:00.000	1
2022-03-01 08:30:00.000	0
2022-03-01 09:00:00.000	0
2022-03-01 09:30:00.000	1
2022-03-01 10:00:00.000	1
2022-03-01 10:30:00.000	1
2022-03-01 11:00:00.000	0
2022-03-01 12:30:00.000	0

Where Status=1 is working and 0 is stopped

I would like to identify both the start and stop time on every period and calculate the elapsed time (working time in minutes). The next table is the wanted result.

Start	Stop	Working_Time
2022-03-01 08:00:00.000	2022-03-01 08:30:00.000	30
2022-03-01 09:30:00.000	2022-03-01 11:00:00.000	90

Moreover if you put some thoughts and comments on the procedure, (as a beginner on SQL) this will help me a lot!

UPDATE

As i have mentioned i'm developing my learning on SQL... the following sources gave me a perspective on how to proceed but i was confused after a point, on how to adapt to my case. More specific on how to check datetime difference with previous record testing lag() and DATEDIFF().

https://community.looker.com/sql-10/sql-pattern-summarizing-entities-with-a-start-end-date-over-time-4868

Count entries grouped by start and end time

Sum values of column in time window defined start/stop event

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情仇皆在手 2025-01-22 12:12:08

使用解析SQL找到工作块，然后按工作块分组

SELECT block, min(date) date_start, max(date_end) date_end  
FROM 
(SELECT date, 
        date_end, 
        status, 
        prev_status, 
        status*sum(case when prev_status=0 then status else 0 end) OVER (ORDER BY date) block  
 FROM   
    (SELECT date, 
            lead(date) OVER (ORDER BY date) date_end, 
            status, 
            COALESCE(lag(status) OVER (ORDER BY date),0) prev_status 
      FROM logger) as x ) as y 
WHERE block > 0
GROUP BY block

Find the working block using analytic SQL, and then group by working block

SELECT block, min(date) date_start, max(date_end) date_end  
FROM 
(SELECT date, 
        date_end, 
        status, 
        prev_status, 
        status*sum(case when prev_status=0 then status else 0 end) OVER (ORDER BY date) block  
 FROM   
    (SELECT date, 
            lead(date) OVER (ORDER BY date) date_end, 
            status, 
            COALESCE(lag(status) OVER (ORDER BY date),0) prev_status 
      FROM logger) as x ) as y 
WHERE block > 0
GROUP BY block

回复收藏 0 原文

~没有更多了~