How to get the first wire_open and wire_close group in the data
P粉3176793422024-02-26 08:47:46
0
1
328
This is the data I want to output, it matches the blue line and the photo of the desired output I have attached, please take a look and use the SQL query to get the desired result
WITH
cte AS (
SELECT *, SUM(delay_ping_type = 'wire_close') OVER (ORDER BY last_updated_on DESC) group_no
FROM source_table
)
SELECT MIN(last_updated_on) wire_open,
MAX(last_updated_on) wire_close
FROM cte
-- WHERE group_no > 0 -- removes final open without close
GROUP BY group_no
-- HAVING wire_open
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
测试一下：
WITH cte AS ( SELECT *, SUM(delay_ping_type = 'wire_close') OVER (ORDER BY last_updated_on DESC) group_no FROM source_table ) SELECT MIN(last_updated_on) wire_open, MAX(last_updated_on) wire_close FROM cte -- WHERE group_no > 0 -- removes final open without close GROUP BY group_no -- HAVING wire_openhttps://www.db-fiddle.com/f/njuPYFYug87jTWKHHv6yRK/2 （感谢 @lemon 的小提琴）。