用AI写SQL1——连续违规通报

/* 提示词： 有一张表 CREATE TABLE zx_risk_summary ( acct_year VARCHAR(32), week VARCHAR(32), week_duration VARCHAR(32), area_name VARCHAR(32), office_name VARCHAR(32), district_name VARCHAR(32), no_id VARCHAR(32), agent_name VARCHAR(32), non_collect_cnt VARCHAR(32), -- 假设这个字段存储的是非收集数量的字符串表示 rank_no VARCHAR(32), penalty_amount varchar(32), create_time TIMESTAMP -- 这个字段用于存储时间戳 ); week表示周, 根据以上规则写一个sql 查询最大1周zx_risk_summary 每个no_id对应的penalty_amount, 提示：可以先查出表中最大1周数据，然后用最大1周no_id分别关联前面2周的是否出现，如连续3周出现则penalty_amount=500， 连续2周出现则penalty_amount=200， 如果匹配不到，也就是第一次出现则penalty_amount=100。 最新一周就是week最大的一期。 前面2周=最大1周-2，前面1周=最大1周-1。 */

-- 更新最新一期罚款 写法1 百度
update zx_risk_summary t1,
(
-- 查询最新一周的week值
WITH LatestWeek AS (
    SELECT MAX(cast(week as decimal )) AS latest_week FROM zx_risk_summary
),

-- 查询最新一周的数据
CurrentWeekData AS (
    SELECT * FROM zx_risk_summary
    WHERE week = (SELECT latest_week FROM LatestWeek)
),

-- 查询前一周的数据
PreviousWeekData AS (
    SELECT * FROM zx_risk_summary
    WHERE week = (SELECT latest_week - 1 FROM LatestWeek)
),

-- 查询前两周的数据
TwoWeeksAgoData AS (
    SELECT * FROM zx_risk_summary
    WHERE week = (SELECT latest_week -2  FROM LatestWeek)
),

-- 关联最新一周、前一周和前两周的数据，计算penalty_amount
PenaltyCalculation AS (
    SELECT
        cwd.no_id,
    cwd.week,
        CASE
            WHEN pwd.no_id IS NOT NULL AND twd.no_id IS NOT NULL THEN 500
            WHEN pwd.no_id IS NOT NULL THEN 200
            ELSE 100
        END AS penalty_amount
    FROM
        CurrentWeekData cwd
    LEFT JOIN PreviousWeekData pwd ON cwd.no_id = pwd.no_id
    LEFT JOIN TwoWeeksAgoData twd ON cwd.no_id = twd.no_id
)

-- 选择最终结果
SELECT
    no_id,
    week,
    penalty_amount
FROM
    PenaltyCalculation

    ) t2 set t1.penalty_amount = t2.penalty_amount where t1.no_id = t2.no_id and t1.week = t2.week;

-- 更新最新一期罚款，写法2 deepseek 实现
-- 更新最新一期罚款
update zx_risk_summary t1,
(
WITH max_week_cte AS (
    SELECT
        MAX(CAST(week AS decimal)) AS max_week
    FROM
        zx_risk_summary
)
SELECT
    z.no_id,
    CASE
        WHEN
            EXISTS (
                SELECT 1
                FROM zx_risk_summary
                WHERE week = (mw.max_week - 2) AND no_id = z.no_id
            )
            AND EXISTS (
                SELECT 1
                FROM zx_risk_summary
                WHERE week = (mw.max_week - 1) AND no_id = z.no_id
            )
        THEN 500
        WHEN
            EXISTS (
                SELECT 1
                FROM zx_risk_summary
                WHERE week = (mw.max_week - 1) AND no_id = z.no_id
            )
        THEN 200
        ELSE 100
    END AS penalty_amount,
    week
FROM
    zx_risk_summary z
    INNER JOIN max_week_cte mw
WHERE
    z.week = mw.max_week
GROUP BY
    z.no_id, mw.max_week

    ) t2 set t1.penalty_amount = t2.penalty_amount where t1.no_id = t2.no_id and t1.week = t2.week