威胁用户

SELECT userId targetId
FROM
(
            SELECT
        userIds AS userId, 1 AS rule_index
        FROM (select arrayJoin(userIds) as userIds from mt_insight_anomaly_base) miab
        INNER JOIN (
            SELECT DISTINCT userId
            FROM (
            SELECT arrayJoin(userIds) AS userId
                FROM mt_insight_anomaly_base mt_insight_anomaly_base0
                where mt_insight_anomaly_base.name = '异常4'
                AND mt_insight_anomaly_base.create_time >= toDateTime('2021-02-03 10:33:00')
                AND mt_insight_anomaly_base.create_time <= toDateTime('2021-02-03 10:44:00')
                GROUP BY arrayJoin(userIds)
                HAVING count(1) >= 1
            )
        ) filter_table
        ON filter_table.userId = miab.userIds
            UNION ALL
        SELECT
        userIds AS userId, 2 AS rule_index
        FROM (select arrayJoin(userIds) as userIds from mt_insight_anomaly_base) miab
        INNER JOIN (
            SELECT DISTINCT userId
            FROM (
            SELECT arrayJoin(userIds) AS userId
                FROM mt_insight_anomaly_base mt_insight_anomaly_base1
                where mt_insight_anomaly_base.name = '异常5'
                AND mt_insight_anomaly_base.create_time >= toDateTime('2021-02-03 10:33:00')
                AND mt_insight_anomaly_base.create_time <= toDateTime('2021-02-03 10:44:00')
                GROUP BY arrayJoin(userIds)
                HAVING count(1) >= 1
            )
        ) filter_table
        ON filter_table.userId = miab.userIds
)
GROUP BY userId
HAVING hasAll(groupUniqArray(rule_index), [1,2]) = 1