-- 完整解决方案（使用CTE但不使用临时表和MERGE）
WITH
-- CTE1: 识别所有有效的在线时段
online_periods AS (
SELECT
d1.DEVICE_ID,
d1.HAPPEN_TIME AS ONLINE_TIME,
(
SELECT MIN(d2.HAPPEN_TIME)
FROM DEVICE_ONLINE d2
WHERE d2.DEVICE_ID = d1.DEVICE_ID
AND d2.HAPPEN_TIME > d1.HAPPEN_TIME
AND d2."ONLINE" = 0
) AS OFFLINE_TIME
FROM DEVICE_ONLINE d1
WHERE d1."ONLINE" = 1
AND (
-- 第一条记录
NOT EXISTS (
SELECT 1 FROM DEVICE_ONLINE prev
WHERE prev.DEVICE_ID = d1.DEVICE_ID
AND prev.HAPPEN_TIME < d1.HAPPEN_TIME
)
-- 或前一条是离线状态
OR EXISTS (
SELECT 1 FROM DEVICE_ONLINE prev
WHERE prev.DEVICE_ID = d1.DEVICE_ID
AND prev.HAPPEN_TIME < d1.HAPPEN_TIME
AND prev."ONLINE" = 0
)
)
),
-- CTE2: 生成数字序列（0-365）
day_offsets AS (
SELECT LEVEL-1 AS DAY_OFFSET
FROM DUAL
CONNECT BY LEVEL <= 366 -- 最多处理1年数据
),
-- CTE3: 展开为每日记录
daily_expanded AS (
SELECT
op.DEVICE_ID,
CAST(op.ONLINE_TIME AS DATE) + do.DAY_OFFSET AS RECORD_DATE,
op.ONLINE_TIME,
CASE
WHEN op.OFFLINE_TIME IS NULL THEN CURRENT_TIMESTAMP
ELSE op.OFFLINE_TIME
END AS OFFLINE_TIME
FROM online_periods op
JOIN day_offsets do ON
CAST(op.ONLINE_TIME AS DATE) + do.DAY_OFFSET <=
CASE
WHEN op.OFFLINE_TIME IS NULL THEN CURRENT_DATE
ELSE CAST(op.OFFLINE_TIME AS DATE)
END
),
-- CTE4: 计算每日运行时长
daily_runtime AS (
SELECT
de.DEVICE_ID,
de.RECORD_DATE,
SUM(
DATEDIFF(SECOND,
GREATEST(de.ONLINE_TIME, CAST(de.RECORD_DATE AS TIMESTAMP)),
LEAST(de.OFFLINE_TIME, CAST(de.RECORD_DATE + 1 AS TIMESTAMP))
)
) AS RUN_SECOND
FROM daily_expanded de
GROUP BY de.DEVICE_ID, de.RECORD_DATE
HAVING SUM(
DATEDIFF(SECOND,
GREATEST(de.ONLINE_TIME, CAST(de.RECORD_DATE AS TIMESTAMP)),
LEAST(de.OFFLINE_TIME, CAST(de.RECORD_DATE + 1 AS TIMESTAMP))
)
) > 0
),
-- CTE5: 为插入生成ID
results_with_ids AS (
SELECT
dr.*,
(SELECT COALESCE(MAX(ID), 0) FROM DEVICE_RUN_TIME) +
ROW_NUMBER() OVER (ORDER BY dr.DEVICE_ID, dr.RECORD_DATE) AS NEW_ID
FROM daily_runtime dr
)
-- 分两步更新目标表（达梦不支持MERGE）
-- 1. 先删除需要更新的记录
DELETE FROM DEVICE_RUN_TIME
WHERE (DEVICE_ID, RECORD_DATE) IN (
SELECT DEVICE_ID, RECORD_DATE FROM results_with_ids
);
-- 2. 再插入所有新记录
INSERT INTO DEVICE_RUN_TIME (ID, DEVICE_ID, RECORD_DATE, RUN_SECOND)
SELECT
NEW_ID,
DEVICE_ID,
RECORD_DATE,
RUN_SECOND
FROM results_with_ids; 以上语句为何报错no column specified
本文记录了使用PyCharm进行环境配置的过程，并分享了测试MNIST数据集的经验，包括测试准确率达到0.9193的具体方法。同时，文中还提到了后续的学习计划，包括GPU配置、MNIST原理深入研究及学习路线的规划。
扫一扫
448
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
