121 lines
3.5 KiB
Plaintext
121 lines
3.5 KiB
Plaintext
WITH RECURSIVE seq AS (
|
||
SELECT
|
||
SN,
|
||
MIN(itemCurrentCycles) AS current_cycle,
|
||
MAX(itemCurrentCycles) AS max_cycle
|
||
FROM TestReq
|
||
WHERE SN = '543009862' -- 加入 SN 条件
|
||
AND startTime > '2025-02-18 18:00:00' -- 加入 startTime 条件
|
||
GROUP BY SN
|
||
|
||
UNION ALL
|
||
|
||
SELECT
|
||
SN,
|
||
current_cycle + 1,
|
||
max_cycle
|
||
FROM seq
|
||
WHERE current_cycle < max_cycle
|
||
)
|
||
|
||
SELECT
|
||
s.SN,
|
||
s.current_cycle AS missing_cycle
|
||
FROM seq s
|
||
LEFT JOIN TestReq t
|
||
ON s.SN = t.SN
|
||
AND s.current_cycle = t.itemCurrentCycles
|
||
AND t.startTime > '2025-02-18 18:00:00' -- 在 JOIN 中加入 startTime 条件
|
||
WHERE t.itemCurrentCycles IS NULL
|
||
AND s.SN = '543009862'; -- 在最终结果中过滤 SN
|
||
|
||
获取指定SN的各个字段的最小值和最大值
|
||
SELECT
|
||
MIN(stationDropCycles) as min_stationDropCycles,
|
||
MAX(stationDropCycles) as max_stationDropCycles,
|
||
MIN(itemCurrentCycles) as min_itemCurrentCycles,
|
||
MAX(itemCurrentCycles) as max_itemCurrentCycles,
|
||
MIN(dropCycles) as min_dropCycles,
|
||
MAX(dropCycles) as max_dropCycles
|
||
FROM TestReq
|
||
WHERE SN = '指定SN值';
|
||
|
||
|
||
获取指定SN在特定时间范围内的各个字段的最小值和最大值
|
||
SELECT
|
||
MIN(stationDropCycles) as min_stationDropCycles,
|
||
MAX(stationDropCycles) as max_stationDropCycles,
|
||
MIN(itemCurrentCycles) as min_itemCurrentCycles,
|
||
MAX(itemCurrentCycles) as max_itemCurrentCycles,
|
||
MIN(dropCycles) as min_dropCycles,
|
||
MAX(dropCycles) as max_dropCycles
|
||
FROM TestReq
|
||
WHERE SN = '指定SN值'
|
||
AND startTime >= '2025-02-26 19:29:45'
|
||
AND endTime <= '2025-02-26 19:29:47';
|
||
|
||
|
||
|
||
检查 itemCurrentCycles stationDropCycles dropCycles 字段的连续性并找出缺失值
|
||
WITH RECURSIVE params AS (
|
||
SELECT
|
||
'202401032245180707' AS target_sn,
|
||
'2025-02-26 19:29:45' AS range_start,
|
||
'2026-02-26 19:29:47' AS range_end,
|
||
'itemCurrentCycles' AS cycle_field_name -- 可选的字段名：dropCycles, itemCurrentCycles, stationDropCycles
|
||
),
|
||
min_max AS (
|
||
SELECT
|
||
MIN(
|
||
CASE params.cycle_field_name
|
||
WHEN 'dropCycles' THEN TestReq.dropCycles
|
||
WHEN 'itemCurrentCycles' THEN TestReq.itemCurrentCycles
|
||
WHEN 'stationDropCycles' THEN TestReq.stationDropCycles
|
||
ELSE NULL
|
||
END
|
||
) AS min_cycle,
|
||
MAX(
|
||
CASE params.cycle_field_name
|
||
WHEN 'dropCycles' THEN TestReq.dropCycles
|
||
WHEN 'itemCurrentCycles' THEN TestReq.itemCurrentCycles
|
||
WHEN 'stationDropCycles' THEN TestReq.stationDropCycles
|
||
ELSE NULL
|
||
END
|
||
) AS max_cycle
|
||
FROM TestReq, params
|
||
WHERE SN = params.target_sn
|
||
AND startTime >= params.range_start
|
||
AND endTime <= params.range_end
|
||
),
|
||
numbers(n) AS (
|
||
SELECT min_cycle FROM min_max
|
||
UNION ALL
|
||
SELECT n + 1 FROM numbers, min_max
|
||
WHERE n < min_max.max_cycle
|
||
)
|
||
SELECT
|
||
n AS missing_value,
|
||
(SELECT min_cycle FROM min_max) AS min_cycle,
|
||
(SELECT max_cycle FROM min_max) AS max_cycle,
|
||
(SELECT cycle_field_name FROM params) AS field_name
|
||
FROM numbers
|
||
WHERE n NOT IN (
|
||
SELECT
|
||
CASE params.cycle_field_name
|
||
WHEN 'dropCycles' THEN TestReq.dropCycles
|
||
WHEN 'itemCurrentCycles' THEN TestReq.itemCurrentCycles
|
||
WHEN 'stationDropCycles' THEN TestReq.stationDropCycles
|
||
ELSE NULL
|
||
END
|
||
FROM TestReq, params
|
||
WHERE SN = params.target_sn
|
||
AND startTime >= params.range_start
|
||
AND endTime <= params.range_end
|
||
)
|
||
ORDER BY n;
|
||
|
||
|
||
|
||
|
||
)
|
||
ORDER BY n; |