英文:
PostgreSQL calculate difference between multi rows total second
问题
要计算在m30等于1且door等于1时的时间差，以及在cutting等于1且door等于1时的时间差，你可以使用PostgreSQL中的窗口函数。以下是一个示例查询，可以帮助你执行这些计算：
SELECT
rid,
m_id,
prosessdate,
m30,
door,
cutting,
CASE
WHEN m30 = 1 AND door = 1 THEN
prosessdate - LAG(prosessdate) OVER (ORDER BY prosessdate)
WHEN cutting = 1 AND door = 1 THEN
prosessdate - LAG(prosessdate) OVER (ORDER BY prosessdate)
END AS time_difference
FROM your_table_name;
请将 "your_table_name" 替换为你的表名。这个查询会为每一行计算时间差，但只有在m30或cutting等于1且door等于1时才会计算时间差。
英文:
rid m_id prosessdate m30 door cutting
1 536477698 05-07-2023 11:05:12 1 0 0
2 536477698 05-07-2023 11:05:13 1 0 0
3 536477698 05-07-2023 11:05:14 1 0 0
4 536477698 05-07-2023 11:05:15 1 0 0
5 536477698 05-07-2023 11:05:16 1 0 0
6 536477698 05-07-2023 11:05:17 1 0 0
7 536477698 05-07-2023 11:05:18 1 0 0
8 536477698 05-07-2023 11:05:19 1 0 0
9 536477698 05-07-2023 11:05:20 1 0 0
10 536477698 05-07-2023 11:05:21 1 1 0
11 536477698 05-07-2023 11:05:22 1 1 0
12 536477698 05-07-2023 11:05:23 1 1 0
13 536477698 05-07-2023 11:05:24 1 1 0
14 536477698 05-07-2023 11:05:25 1 1 0
15 536477698 05-07-2023 11:05:26 1 1 0
16 536477698 05-07-2023 11:05:27 1 1 0
17 536477698 05-07-2023 11:05:28 1 1 0
18 536477698 05-07-2023 11:05:29 0 0 0
19 536477698 05-07-2023 11:05:30 0 0 0
20 536477698 05-07-2023 11:05:31 0 0 0
21 536477698 05-07-2023 11:05:32 0 0 0
22 536477698 05-07-2023 11:05:33 0 0 1
23 536477698 05-07-2023 11:05:34 0 0 1
i am using PosgreSQL
I tried to calculate difference between rows in a field using a query:
I Need when m30 = 1 to door =1 calculate different second
05-07-2023 11:05:21 - 05-07-2023 11:05:12
cutting = 1 to door = 1 calculate different second
05-07-2023 11:05:33 - 05-07-2023 11:05:21
how can i do this.
答案1
得分: 1
你可以使用 ROW_NUMBER() 来查找你想要的行。
例如：
select m.m_id,
d.prosessdate - m.prosessdate as diff1,
c.prosessdate - d.prosessdate as diff2
from (
select t.*, row_number() over(partition by m_id order by prosessdate) as rn
from t where m30 = 1
) m
join (
select t.*, row_number() over(partition by m_id order by prosessdate) as rn
from t where door = 1
) d on d.m_id = m.m_id and d.rn = 1
join (
select t.*, row_number() over(partition by m_id order by prosessdate) as rn
from t where cutting = 1
) c on c.m_id = m.m_id and c.rn = 1
where m.rn = 1
结果：
m_id diff1 diff2
--------- -------- --------
536477698 00:00:09 00:00:12
查看fiddle链接。
英文:
You can use ROW_NUMBER() to find the rows you want.
For example:
select m.m_id,
d.prosessdate - m.prosessdate as diff1,
c.prosessdate - d.prosessdate as diff2
from (
select t.*, row_number() over(partition by m_id order by prosessdate) as rn
from t where m30 = 1
) m
join (
select t.*, row_number() over(partition by m_id order by prosessdate) as rn
from t where door = 1
) d on d.m_id = m.m_id and d.rn = 1
join (
select t.*, row_number() over(partition by m_id order by prosessdate) as rn
from t where cutting = 1
) c on c.m_id = m.m_id and c.rn = 1
where m.rn = 1
Result:
m_id diff1 diff2
--------- -------- --------
536477698 00:00:09 00:00:12
See fiddle.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论