SQL查询以获取周期计数（如果它与连续行中的列值匹配） [英] SQL Query to get count of cycles if it matches value of column in consecutive rows
问题描述
我有一个名为 vehicledata 的表，该表包含3列： id ， Veh No 和 Veh模式。
I have table named vehicledata which consists 3 columns: id, Veh No, and Veh Mode.
我的数据如下：
id Veh No Veh Mode
1 KA03-003 IDLE
2 KA03-003 IDLE
3 KA03-003 IDLE
4 KA03-003 DRIVE
5 KA03-003 DRIVE
6 KA03-003 DRIVE
7 KA03-003 DRIVE
8 KA03-003 DRIVE
9 KA03-003 IDLE
10 KA03-003 IDLE
11 KA03-003 IDLE
12 KA03-003 DRIVE
13 KA03-003 DRIVE
14 KA03-003 DRIVE
15 KA03-003 DRIVE
16 KA05-005 IDLE
17 KA05-005 IDLE
18 KA05-005 IDLE
19 KA05-005 DRIVE
20 KA05-005 DRIVE
21 KA05-005 DRIVE
22 KA05-005 DRIVE
23 KA05-005 DRIVE
24 KA05-005 IDLE
25 KA05-005 IDLE
26 KA05-005 IDLE
27 KA05-005 DRIVE
28 KA05-005 DRIVE
29 KA05-005 DRIVE
30 KA05-005 DRIVE
上表中有2个Veh No，即KA03- 003& KA05-005。
从ID 4到8&在图12至15中，KAO3-003有两个驱动周期，从id 19到23& 27日至30日，KAO5-005
有两个DRIVE循环。对于这些车辆，我需要在表&结果应该如下所示：
In the above table there are 2 Veh No i.e KA03-003 & KA05-005. From id 4 to 8 & 12 to 15 there are two DRIVE cycles for KAO3-003 and From id 19 to 23 & 27 to 30 there are two DRIVE cycles for KAO5-005 For these Vehicles i need to get count of 'DRIVE' cycles occured in a table & result should look as below:
Veh No No.of.Drive Cycles
KA03-003 2
KA05-005 2
我无法编写查询。请帮助我解决此问题。
I am not able to write query.Kindly help me in solving this. Thanks in advance.
推荐答案
@Pradeep-构建此查询的逻辑是仅检查车辆当前在驱动器中的行模式，在那行之前他是空闲的。您不必担心IDLE模式和连续DRIVE模式。您只需要检查车辆从IDLE转换为DRIVE的行。我使用了一个case语句和一个LAG Windows函数将这些记录仅标记为1，将所有其他标记都标记为0。然后，我只是为每辆车取了该列的总和。
@Pradeep - The logic to build this query will be to check only rows where vehicle is currently in drive mode and he was IDLE before that ROW. You don't have to worry about IDLE modes and also the continuous DRIVE modes. You only need to check the rows where a vehicle has transitioned from IDLE to DRIVE. I have used a case statement and a LAG windows function to mark only these records as 1 and everything else as 0. And then I just took a sum of this column for each vehicle.
------------------------
WITH vehicles(id , Veh_No, Veh_Mode) AS
(
SELECT 1 , 'KA03-003', 'IDLE' UNION
SELECT 2 , 'KA03-003', 'IDLE' UNION
SELECT 3 , 'KA03-003', 'IDLE' UNION
SELECT 4 , 'KA03-003', 'DRIVE' UNION
SELECT 5 , 'KA03-003', 'DRIVE' UNION
SELECT 6 , 'KA03-003', 'DRIVE' UNION
SELECT 7 , 'KA03-003', 'DRIVE' UNION
SELECT 8 , 'KA03-003', 'DRIVE' UNION
SELECT 9 , 'KA03-003', 'IDLE' UNION
SELECT 10, 'KA03-003', 'IDLE' UNION
SELECT 11, 'KA03-003', 'IDLE' UNION
SELECT 12, 'KA03-003', 'DRIVE' UNION
SELECT 13, 'KA03-003', 'DRIVE' UNION
SELECT 14, 'KA03-003', 'DRIVE' UNION
SELECT 15, 'KA03-003', 'DRIVE' UNION
SELECT 16, 'KA05-005', 'IDLE' UNION
SELECT 17, 'KA05-005', 'IDLE' UNION
SELECT 18, 'KA05-005', 'IDLE' UNION
SELECT 19, 'KA05-005', 'DRIVE' UNION
SELECT 20, 'KA05-005', 'DRIVE' UNION
SELECT 21, 'KA05-005', 'DRIVE' UNION
SELECT 22, 'KA05-005', 'DRIVE' UNION
SELECT 23, 'KA05-005', 'DRIVE' UNION
SELECT 24, 'KA05-005', 'IDLE' UNION
SELECT 25, 'KA05-005', 'IDLE' UNION
SELECT 26, 'KA05-005', 'IDLE' UNION
SELECT 27, 'KA05-005', 'DRIVE' UNION
SELECT 28, 'KA05-005', 'DRIVE' UNION
SELECT 29, 'KA05-005', 'DRIVE' UNION
SELECT 30, 'KA05-005', 'DRIVE'
)
SELECT veh_no, SUM(count_drive_cycle) AS drive_cycles
FROM (SELECT *,
CASE WHEN Veh_Mode = 'DRIVE'
AND LAG(veh_mode,1,'DRIVE') OVER (PARTITION BY Veh_No ORDER BY id) = 'IDLE' -- check if prev mode is IDLE
THEN 1
ELSE 0 END AS count_drive_cycle
FROM vehicles
) A
GROUP BY veh_no
这篇关于SQL查询以获取周期计数（如果它与连续行中的列值匹配）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
