Oracle SQL：基于现有表创建月度状态统计新表

问题背景 #

现有Oracle表结构如下：

NAME VARCHAR2(255 BYTE) , 
TYPE VARCHAR2(255 BYTE) , 
DT_CHECKPOINT VARCHAR2(255 BYTE),  
STATUS VARCHAR2(255 BYTE) 

现有数据包含两种日期字符串格式（dd/mm/yyyy和dd-MON-yy），需要按NAME、TYPE、月份分组，统计每月OK和KO状态的数量，生成新表。

解决方案 #

通过CREATE TABLE AS SELECT语句可一次性完成新表创建和统计数据插入，核心是统一处理两种日期格式，提取月份标识后分组统计。

完整SQL语句 #

-- 创建月度状态统计新表并插入统计数据
CREATE TABLE monthly_status_stats (
    NAME VARCHAR2(255 BYTE),
    TYPE VARCHAR2(255 BYTE),
    MONTH_PERIOD VARCHAR2(10 BYTE), -- 存储如OTT-23的月份标识
    OK_COUNT NUMBER,
    KO_COUNT NUMBER
) AS
SELECT
    NAME,
    TYPE,
    -- 统一转换为MON-YY格式的月份标识，适配两种日期字符串格式
    CASE
        -- 处理dd/mm/yyyy格式，转换为MON-YY（适配意大利语月份缩写）
        WHEN DT_CHECKPOINT LIKE '%/%' THEN
            TO_CHAR(TO_DATE(DT_CHECKPOINT, 'dd/mm/yyyy', 'NLS_DATE_LANGUAGE=ITALIAN'), 'MON-YY', 'NLS_DATE_LANGUAGE=ITALIAN')
        -- 处理dd-MON-yy格式，直接提取月份+年份部分
        WHEN DT_CHECKPOINT LIKE '%-%' THEN
            SUBSTR(DT_CHECKPOINT, INSTR(DT_CHECKPOINT, '-') + 1)
        -- 异常格式统一标记为UNKNOWN，可根据实际情况调整
        ELSE 'UNKNOWN'
    END AS MONTH_PERIOD,
    -- 统计OK状态的记录数
    COUNT(CASE WHEN STATUS = 'OK' THEN 1 END) AS OK_COUNT,
    -- 统计KO状态的记录数
    COUNT(CASE WHEN STATUS = 'KO' THEN 1 END) AS KO_COUNT
FROM
    source_table -- 替换为你的实际现有表名
GROUP BY
    NAME,
    TYPE,
    CASE
        WHEN DT_CHECKPOINT LIKE '%/%' THEN
            TO_CHAR(TO_DATE(DT_CHECKPOINT, 'dd/mm/yyyy', 'NLS_DATE_LANGUAGE=ITALIAN'), 'MON-YY', 'NLS_DATE_LANGUAGE=ITALIAN')
        WHEN DT_CHECKPOINT LIKE '%-%' THEN
            SUBSTR(DT_CHECKPOINT, INSTR(DT_CHECKPOINT, '-') + 1)
        ELSE 'UNKNOWN'
    END;

注意事项 #

• 请将语句中的source_table替换为实际的现有表名称
• NLS_DATE_LANGUAGE=ITALIAN用于适配示例中的意大利语月份缩写（如OTT），若数据包含其他语言的月份标识，需修改该参数
• 若存在其他日期字符串格式，需在CASE语句中补充对应的转换逻辑
• 新表字段长度可根据实际数据量调整，例如MONTH_PERIOD字段长度10字节足够覆盖需求

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