Netezza中INSERT INTO SELECT时COUNT返回null 单独SELECT正常

INSERT INTO ... SELECT 语句做列映射时完全按位置顺序匹配，不会识别SELECT子句里的列别名，你的SQL存在列数缺失、顺序错位问题：

• 插入目标列共13个，顺序为TIME_FRAME,VIDEO,DATA,VOICE,PREV_VIDEO,PREV_DATA,PREV_VOICE,VIDEO_REVENUE,INTERNET_REVENUE,PHONE_REVENUE,OCC_REVENUE,TOTAL_REVENUE,ACCOUNTS
• SELECT子句仅返回12列，漏掉了PHONE_REVENUE对应的聚合计算，从第10列开始全部错位：
  • SELECT输出的SUM(D.OCC_REVENUE)被映射到目标列PHONE_REVENUE
  • SELECT输出的SUM(D.TOTAL_REVENUE)被映射到目标列OCC_REVENUE
  • SELECT输出的COUNT(D.ACCOUNT_NUMBER)被映射到目标列TOTAL_REVENUE
  • 最后一个目标列ACCOUNTS无对应SELECT输出值，在非严格SQL模式下会直接被赋值为NULL，这就是COUNT列返回null的根本原因。
    单独执行SELECT时是按列别名识别结果，不存在错位问题，自然能得到正确的COUNT计算值。

在SELECT子句中补上缺失的PHONE_REVENUE聚合逻辑，严格和INSERT声明的目标列顺序、数量、数据类型一一对应，修正后的SQL如下：

INSERT INTO DAILY_TOTALS(TIME_FRAME,
    VIDEO,
    DATA,
    VOICE,
    PREV_VIDEO,
    PREV_DATA,
    PREV_VOICE,
    VIDEO_REVENUE,
    INTERNET_REVENUE,
    PHONE_REVENUE,
    OCC_REVENUE,
    TOTAL_REVENUE,
    ACCOUNTS)
SELECT
D.TIME_FRAME,
D.VIDEO,
D.DATA,
CASE WHEN D.VOICE ='N' THEN 'N' ELSE 'Y' END AS VOICE,
D.PREV_VIDEO,
D.PREV_DATA,
CASE WHEN D.PREV_VOICE = 'N' THEN 'N' ELSE 'Y' END AS PREV_VOICE,
SUM(D.VIDEO_REVENUE) AS VIDEO_REVENUE,
SUM(D.INTERNET_REVENUE) AS INTERNET_REVENUE,
SUM(D.PHONE_REVENUE) AS PHONE_REVENUE, -- 补全缺失的匹配列
SUM(D.OCC_REVENUE) AS OCC_REVENUE,
SUM(D.TOTAL_REVENUE) AS TOTAL_REVENUE,
COUNT(D.ACCOUNT_NUMBER) AS ACCOUNTS
FROM DAILY D
WHERE  D.TIME_FRAME = CURRENT_DATE -1
GROUP BY 
D.TIME_FRAME,
D.VIDEO,
D.DATA,
D.VOICE,
D.PREV_VIDEO,
D.PREV_DATA,
D.PREV_VOICE

• 编写INSERT INTO ... SELECT语句时，永远不要依赖列别名做匹配，必须逐列核对顺序和数量。
• 建议开启数据库严格SQL模式，这类列数不匹配的问题会直接抛出错误，不会静默插入NULL值产生脏数据。

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