You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Teradata资产状态与时间计算SQL问题求助

avatar
安全阿岳
2026-7-20
资产状态统计SQL优化问题

需求规则

  • START TIME：取该资产下所有流程的最早起始时间；若所有流程均未启动则为NULL
  • FINAL_REFRESH_TIME：仅当该资产下所有3个流程均完成时，取最后完成时间；否则为NULL
  • STATUS：每个资产对应唯一状态，按以下优先级判断：
    • 存在'R'（Running）流程 → Running
    • 全为'C'（Completed） → Completed
    • 部分完成部分未启动（'N'） → Waiting
    • 全未启动 → Not Started

问题描述

针对资产ID 10080编写的SQL查询返回多行结果，不符合单资产单行的预期输出要求。

原SQL代码 

SELECT
    MODULE_BASED.APPLICATION,
    MODULE_BASED.ASSET_TYP,
    MODULE_BASED.ASSET_NAME,
    MODULE_BASED.MODULE_NAME,
    MODULE_BASED.DATA_DATE,
    CASE 
        WHEN MODULE_BASED.STATUS IN ('Completed','In Progress','Not Started') AND MAX_STATUS = 'In Progress' THEN 'In Progress'
        WHEN MODULE_BASED.STATUS IN ('Completed','In Progress','Not Started') AND MAX_STATUS = 'Not Started' THEN 'In Progress'
        WHEN MODULE_BASED.STATUS IN ('Completed') AND MAX_STATUS = 'Completed' THEN 'Completed'
        WHEN MODULE_BASED.STATUS IN ('Not Started') AND MAX_STATUS = 'Not Started' THEN 'Not Started'
        WHEN MODULE_BASED.STATUS IN ('In Progress') AND MAX_STATUS = 'In Progress' THEN 'In Progress'
        WHEN MODULE_BASED.STATUS IN ('Completed','Not Started') AND MAX_STATUS = 'Not Started' THEN 'Waiting'
        WHEN MODULE_BASED.STATUS IN ('Failed','Not Started','In Progress','Completed') AND MAX_STATUS = 'Failed' THEN 'Failed'
    END AS STATUS, 
    MIN(MODULE_BASED.START_TIME) AS START_TIME,
    MAX(MODULE_BASED.END_TIME) AS END_TIME,
    SUBSTRING((TRIM((CAST(((CAST(MAX(MODULE_BASED.END_TIME) AS TIMESTAMP(0)) - CAST(MIN(MODULE_BASED.START_TIME) AS TIMESTAMP(0))) DAY(4) TO SECOND) AS VARCHAR(50))))),2,9) AS DURATION
FROM
(
    SELECT 
        QRY3.APPLICATION,
        QRY3.ASSET_TYP,
        QRY3.ASSET_NAME,
        QRY3.MODULE_NAME,
        QRY3.COMPLETING_PROCESS_ID,
        QRY3.DATA_DATE,
        QRY3.START_TIME,
        QRY3.END_TIME,
        SUBSTRING((TRIM((CAST(((CAST(QRY3.END_TIME AS TIMESTAMP(0)) - CAST(QRY3.START_TIME AS TIMESTAMP(0))) DAY(4) TO SECOND)AS VARCHAR(50))))),2,9) AS DURATION,
        (TRIM((CAST(((CAST(QRY3.END_TIME AS TIMESTAMP(0)) - CAST(QRY3.START_TIME AS TIMESTAMP(0))) DAY(4) TO SECOND)AS VARCHAR(50))))) AS DURATION_TIMESTAMP,
        ((CAST(QRY3.END_TIME AS TIMESTAMP(0)) - QRY3.START_TIME) DAY(4) to SECOND(4)) AS t1,
        (EXTRACT(DAY from t1)*(24*60*60) +
            EXTRACT(HOUR from t1)*(60*60) + 
            EXTRACT(MINUTE from t1)*60 +
            EXTRACT(SECOND from t1)
            ) AS Required_Output,
        CASE WHEN ETL1.PROCESS_RUN_STATUS_CD = 'S' THEN 'Completed'
            WHEN ETL1.PROCESS_RUN_STATUS_CD = 'R' THEN 'In Progress'
            WHEN ETL1.PROCESS_RUN_STATUS_CD = 'N' THEN 'Not Started'
            WHEN ETL1.PROCESS_RUN_STATUS_CD = 'F' THEN 'Failed'
        END AS STATUS
    FROM
    (
        SELECT 
            QRY2.APPLICATION,
            QRY2.ASSET_TYP,
            QRY2.ASSET_NAME,
            QRY2.MODULE_NAME,
            QRY2.COMPLETING_PROCESS_ID,
            QRY2.DATA_DATE,
            MIN(QRY2.START_TIME) OVER(PARTITION BY QRY2.APPLICATION,QRY2.ASSET_TYP,QRY2.ASSET_NAME,QRY2.MODULE_NAME,QRY2.COMPLETING_PROCESS_ID,QRY2.DATA_DATE)AS START_TIME,
            MAX(QRY2.END_TIME) OVER(PARTITION BY QRY2.APPLICATION,QRY2.ASSET_TYP,QRY2.ASSET_NAME,QRY2.MODULE_NAME,QRY2.COMPLETING_PROCESS_ID,QRY2.DATA_DATE)AS END_TIME,
            MAX(QRY2.RUN_ID) OVER(PARTITION BY QRY2.APPLICATION,QRY2.ASSET_TYP,QRY2.ASSET_NAME,QRY2.MODULE_NAME,QRY2.COMPLETING_PROCESS_ID,QRY2.DATA_DATE)AS RUN_ID
        FROM
        (
            SELECT 
                QRY1.APPLICATION,
                QRY1.ASSET_TYP,
                QRY1.ASSET_NAME,
                QRY1.MODULE_NAME,
                QRY1.COMPLETING_PROCESS_ID,
                QRY1.DATA_DATE,
                ETL.PROCESS_RUN_STATUS_CD,
                ETL.PROCESS_START_TS AS START_TIME,
                ETL.PROCESS_END_TS AS END_TIME,
                ETL.RUN_ID
            FROM
            (
                select MASTER.ASSET_ID,
                    MASTER.APPLICATION,
                    MASTER.ASSET_TYP,
                    MASTER.ASSET_NAME, 
                    MASTER.MODULE_NAME,
                    DEPEND.COMPLETING_PROCESS_ID,
                    CAL.CALENDAR_DAY_DT AS RUN_DATE,
                    (cast(CAL.CALENDAR_DAY_DT as format 'YYYY-MM-DD')+ cast(DEPEND.DATA_DELAY as interval DAY)) AS DATA_DATE,
                    DEPEND.COMPL_SESSION_NAME
                from 
                    NDW_EBI_DMR_DEV_TABLES.ASSET_CONFIG MASTER
                    INNER JOIN NDW_EBI_DMR_DEV_TABLES.ASSET_DEPENDENCY_CONFIG DEPEND
                        ON MASTER.ASSET_ID = DEPEND.ASSET_ID
                    inner join ndw_base_views.fiscal_calendar cal
                        on 1=1
                        and CALENDAR_DAY_DT BETWEEN '2023-06-02' AND '2023-06-02' 
                where
                    DEPEND.PRCS_CNTRL_STORE_TYP = 'NDW_EBI_DMR_SHARED_VIEWS.PROCESS_CONTROL'
                    AND MASTER.ASSET_ID = 10080
                GROUP BY 1,2,3,4,5,6,7,8,9
            )QRY1
            LEFT JOIN NDW_EBI_DMR_SHARED_VIEWS.PROCESS_CONTROL ETL
                ON ETL.PROCESS_ID = QRY1.COMPLETING_PROCESS_ID
                AND ETL.LOAD_START_TS = QRY1.DATA_DATE
        )QRY2
    )QRY3
    INNER JOIN NDW_EBI_DMR_SHARED_VIEWS.PROCESS_CONTROL ETL1
        ON ETL1.PROCESS_ID = QRY3.COMPLETING_PROCESS_ID
        AND ETL1.RUN_ID = QRY3.RUN_ID
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13
) MODULE_BASED
GROUP BY 1,2,3,4,5,7;

当前输出 

Enterprise,Care Data Assets (Xfinity 2.0),NSD,Business Critical,2023-06-01,2023-06-01 06:00:34,2023-06-02 06:23:33, 00:22:59, Completed
Enterprise,Care Data Assets (Xfinity 2.0),NSD,Business Critical,2023-06-01,2023-06-02 07:36:20,2023-06-02 18:00:34, 08:11:16, Running

期望输出 

Enterprise,Care Data Assets (Xfinity 2.0),NSD,Business Critical,2023-06-01,2023-06-01 06:00:34,2023-06-02 18:00:34, 18:00:00, Running

解决方案

原SQL问题出在最外层GROUP BY包含了MODULE_BASED.STATUS，导致不同状态的流程被单独分组，产生多行结果。需要重新设计聚合逻辑，先按资产维度聚合所有流程数据，再根据规则计算最终状态和时间。

优化后的SQL代码 

WITH asset_processes AS (
    SELECT 
        MASTER.APPLICATION,
        MASTER.ASSET_TYP,
        MASTER.ASSET_NAME,
        MASTER.MODULE_NAME,
        (CAST(CAL.CALENDAR_DAY_DT AS FORMAT 'YYYY-MM-DD') + CAST(DEPEND.DATA_DELAY AS INTERVAL DAY)) AS DATA_DATE,
        CASE 
            WHEN ETL.PROCESS_RUN_STATUS_CD = 'S' THEN 'Completed'
            WHEN ETL.PROCESS_RUN_STATUS_CD = 'R' THEN 'Running'
            WHEN ETL.PROCESS_RUN_STATUS_CD = 'N' THEN 'Not Started'
            WHEN ETL.PROCESS_RUN_STATUS_CD = 'F' THEN 'Failed'
            ELSE 'Unknown'
        END AS PROCESS_STATUS,
        ETL.PROCESS_START_TS AS START_TIME,
        ETL.PROCESS_END_TS AS END_TIME
    FROM NDW_EBI_DMR_DEV_TABLES.ASSET_CONFIG MASTER
    INNER JOIN NDW_EBI_DMR_DEV_TABLES.ASSET_DEPENDENCY_CONFIG DEPEND
        ON MASTER.ASSET_ID = DEPEND.ASSET_ID
    INNER JOIN ndw_base_views.fiscal_calendar cal
        ON CAL.CALENDAR_DAY_DT BETWEEN '2023-06-02' AND '2023-06-02'
    LEFT JOIN NDW_EBI_DMR_SHARED_VIEWS.PROCESS_CONTROL ETL
        ON ETL.PROCESS_ID = DEPEND.COMPLETING_PROCESS_ID
        AND ETL.LOAD_START_TS = (CAST(CAL.CALENDAR_DAY_DT AS FORMAT 'YYYY-MM-DD') + CAST(DEPEND.DATA_DELAY AS INTERVAL DAY))
    WHERE 
        DEPEND.PRCS_CNTRL_STORE_TYP = 'NDW_EBI_DMR_SHARED_VIEWS.PROCESS_CONTROL'
        AND MASTER.ASSET_ID = 10080
),
asset_aggregates AS (
    SELECT
        APPLICATION,
        ASSET_TYP,
        ASSET_NAME,
        MODULE_NAME,
        DATA_DATE,
        MIN(START_TIME) AS START_TIME,
        MAX(END_TIME) AS MAX_END_TIME,
        -- 统计未完成的流程数量
        COUNT(CASE WHEN PROCESS_STATUS != 'Completed' THEN 1 END) AS INCOMPLETE_COUNT,
        -- 收集所有流程状态
        ARRAY_AGG(DISTINCT PROCESS_STATUS) AS ALL_STATUSES
    FROM asset_processes
    GROUP BY APPLICATION, ASSET_TYP, ASSET_NAME, MODULE_NAME, DATA_DATE
)
SELECT
    APPLICATION,
    ASSET_TYP,
    ASSET_NAME,
    MODULE_NAME,
    DATA_DATE,
    -- 按规则判断最终状态
    CASE
        WHEN 'Running' IN (UNNEST(ALL_STATUSES)) THEN 'Running'
        WHEN 'Failed' IN (UNNEST(ALL_STATUSES)) THEN 'Failed'
        WHEN INCOMPLETE_COUNT = 0 THEN 'Completed'
        WHEN 'Completed' IN (UNNEST(ALL_STATUSES)) AND 'Not Started' IN (UNNEST(ALL_STATUSES)) THEN 'Waiting'
        ELSE 'Not Started'
    END AS STATUS,
    START_TIME,
    -- 全完成时取最晚结束时间，否则为NULL
    CASE WHEN INCOMPLETE_COUNT = 0 THEN MAX_END_TIME ELSE NULL END AS FINAL_REFRESH_TIME,
    -- 计算总时长
    CASE 
        WHEN START_TIME IS NOT NULL AND MAX_END_TIME IS NOT NULL THEN
            SUBSTRING(TRIM(CAST((CAST(MAX_END_TIME AS TIMESTAMP(0)) - CAST(START_TIME AS TIMESTAMP(0)) AS VARCHAR(50)))), 2, 9)
        ELSE NULL
    END AS DURATION
FROM asset_aggregates;

优化说明

  1. CTE分层简化逻辑：用asset_processes获取每个流程的基础状态和时间数据，避免多层嵌套子查询的混乱。
  2. 资产维度聚合：asset_aggregates按资产维度聚合，计算最早起始时间、最晚结束时间、未完成流程数，同时收集所有流程状态。
  3. 规则化状态判断：通过状态集合和未完成计数，严格按照需求规则判断最终资产状态，确保单资产单行输出。

内容的提问来源于stack exchange，提问作者Debasis

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.07.20 05:08:09