时间戳

首页 > 解决方案 > SQL 根据列的审计跟踪中的值返回 1 或 0

sql - SQL 根据列的审计跟踪中的值返回 1 或 0

问题描述

如果我有一张如下表：

ID_ last_updated_by
1 机器人
1 人类
1 机器人
2 机器人
3 机器人
3 人类

使用 SQL，我如何按 ID 分组并创建一个新列来指示人类是否曾经更新过这样的记录：

ID_ last_updated_by 更新人
1 机器人 1
2 机器人 0
3 机器人 1

更新

我目前正在执行以下操作，尽管我不确定这有多有效。选择最新记录，然后通过子选择将其与我的计算列合并。

SELECT  MAIN.TRANSACTION_ID,
            MAIN.CREATED_DATE
            MAIN.CREATED_BY_USER_ID,
            MAIN.OWNER_USER_ID,
            STP.TOUCHED_BY_HUMAN
    FROM (
            SELECT  TRANSACTION_ID,
                    CREATED_DATE
                    CREATED_BY_USER_ID_
                    OWNER_USER_ID_
            FROM    TABLE_NAME
            WHERE   CREATED_DATE >= CAST('{start_date} 00:00:00' AS TIMESTAMP)
            AND     CREATED_DATE <= CAST('{end_date} 23:59:59' AS TIMESTAMP)
            QUALIFY row_number() OVER (partition by TRANSACTION_ID order by End_Dt desc) = 1
) MAIN

LEFT JOIN (
        SELECT  TRANSACTION_ID,
                CASE
                WHEN   CREATED_BY_USER_ID IN ('ROBOT', 'MACHINE')  OR
                       CREATED_BY_USER_ID LIKE 'N%' OR
                       CREATED_BY_USER_ID IS NULL
                THEN 0
                ELSE 1 END AS CREATED_BY_HUMAN,
                CASE
                WHEN   OWNER_USER_ID IN ('ROBOT', 'MACHINE')  OR
                       OWNER_USER_ID LIKE 'N%' OR
                       OWNER_USER_ID IS NULL
                THEN 0
                ELSE 1 END AS OWNED_BY_HUMAN,
                CASE
                WHEN   CREATED_BY_HUMAN = 0 AND
                       OWNED_BY_HUMAN = 0
                THEN 0
                ELSE 1 END AS TOUCHED_BY_HUMAN_
        FROM    TABLE_NAME
        WHERE   CREATED_DATE >= CAST('{start_date} 00:00:00' AS TIMESTAMP)
        AND     CREATED_DATE <= CAST('{end_date} 23:59:59' AS TIMESTAMP)
        QUALIFY row_number() OVER (partition by TRANSACTION_ID order by TOUCHED_BY_HUMAN_ desc) = 1
) STP
ON MAIN.TRANSACTION_ID = STP.TRANSACTION_ID

标签： sqlteradata

解决方案

这是您的查询到条件聚合的 1:1 转换：

SELECT  TRANSACTION_ID,
        CREATED_DATE,
        CREATED_BY_USER_ID,
        OWNER_USER_ID,
        Max(CASE
              WHEN  CREATED_BY_USER_ID IN ('ROBOT', 'MACHINE')  OR
                    CREATED_BY_USER_ID LIKE 'N%' OR
                    CREATED_BY_USER_ID IS NULL
              THEN 0
              ELSE 1
            END) Over (PARTITION BY TRANSACTION_ID) AS CREATED_BY_HUMAN
FROM    Table_Name
WHERE   CREATED_DATE >= Cast('{start_date} 00:00:00' AS TIMESTAMP)
AND     CREATED_DATE <= Cast('{end_date} 23:59:59' AS TIMESTAMP)
QUALIFY Row_Number() Over (PARTITION BY TRANSACTION_ID ORDER BY End_Dt DESC) = 1

推荐阅读