北京昆仑数智-sql学习笔记

CAST(a.ANAL_COY AS VARCHAR(1000))原来a.ANAL_COY可能是数字NUMBER / INT或较短字符串或 CLOB / TEXT现在统一变成VARCHAR(1000)最多1000字符的字符串AS ANAL_COY是什么只是改回原字段名保证输出字段名不变a.TEST_WATER_ID AS SOURCE_DATA_ID即原字段新字段TEST_WATER_IDSOURCE_DATA_IDMC AS DATA_REGION表示每一行数据都固定是 MC6.29SELECT MD5(CONCAT(IFNULL(NULLIF(TRIM(T.OIL_ASSAY_ID), ), UNKNOWN), _ANLS)) AS PRODUCT_OIL_ANLS_ID, MD5(CONCAT(IFNULL(NULLIF(TRIM(T.OIL_ASSAY_ID), ), UNKNOWN))) AS SAMPLE_SERIAL_ID, MD5(CONCAT(IFNULL(NULLIF(TRIM(T.OIL_ASSAY_ID), ), UNKNOWN), _PROJ)) AS ANALYSIS_PROJ_ID, IFNULL(NULLIF(TRIM(T.STATION_ID), ), UNKNOWN) AS SAMPLE_SOURCE_ID, 玉门油田 AS SAMPLE_SOURCE_TYPE, NULL AS SULPHATED_ASH, CAST(T.WATER_CONTENT AS DECIMAL(38,4)) AS WATER_CONTENT, NULL AS KINEMATIC_VISCOSITY_40, NULL AS KINEMATIC_VISCOSITY_100, NULL AS RESIDUAL_CARBON, NULL AS OPEN_FLASH_POINT, NULL AS POUR_POINT, CAST(T.MECHANICAL_IMPURITY AS VARCHAR) AS MECHANICAL_IMPURITY, CAST(T.ACID_VALUE AS DECIMAL(38,4)) AS ACID_VALUE, NULL AS VISCOSITY_INDEX, NULL AS APPEARANCE, NULL AS MECHANICAL_IMPURITY_RESULTS, NULL AS CLOSED_FLASH_POINT, NULLIF(TRIM(T.REMARKS), ) AS REMARKS, NULLIF(TRIM(T.CREATE_APP_ID), ) AS CREATE_APP_ID, og_app_oil_assay AS CREATE_USER_ID, IFNULL(STR_TO_DATE(NULLIF(TRIM(T.CREATE_DATE), ), %Y-%m-%dT%H:%i:%s), NOW()) AS CREATE_DATE, EPN_YM AS UPDATE_USER_ID, NOW() AS UPDATE_DATE, STR_TO_DATE(NULLIF(TRIM(T.CHECK_DATE), ), %Y-%m-%dT%H:%i:%s) AS CHECK_DATE, NULLIF(TRIM(T.CHECK_USER_ID), ) AS CHECK_USER_ID, NULL AS UNIFY_TASK_ID, YM AS DATA_SOURCE, NULLIF(TRIM(T.OIL_ASSAY_ID), ) AS SOURCE_DATA_ID, YM AS DATA_REGION, 1 AS BSFLAG, 0 AS GOV_QC_STATUS, NULL AS GOV_QC_DATE, 0 AS GOV_PASS_STATUS, NULL AS GOV_PASS_DATE, 0 AS GOV_PUSH_STATUS, NULL AS GOV_QC_BATCH_ID, NULL AS SHARE_PUSH_DATE, IFNULL(STR_TO_DATE(NULLIF(TRIM(T.CREATE_DATE), ), %Y-%m-%dT%H:%i:%s), NOW()) AS SOURCE_CREATE_DATE, NULLIF(TRIM(T.SOURCE_LOCATION), ) AS SOURCE_LOCATION, NULLIF(TRIM(T.IS_ABNORMAL), ) AS IS_ABNORMAL, NULLIF(TRIM(T.CHLORIDE_CONTENT), ) AS CHLORIDE_CONTENT, CAST(T.VAPOR_PRESSURE AS DECIMAL(20,4)) AS VAPOR_PRESSURE, CAST(T.DENSITY AS DECIMAL(20,4)) AS DENSITY, CAST(T.SULFUR_CONTENT AS DECIMAL(20,6)) AS SULFUR_CONTENT, CAST(T.SALT_CONTENT AS DECIMAL(20,4)) AS SALT_CONTENT FROM og_app_oil_assay T6.29.1MD5(CONCAT(IFNULL( NULLIF(TRIM(T.OIL_ASSAY_ID), ) , UNKNOWN), _ANLS)) AS PRODUCT_OIL_ANLS_IDTRIM(T.OIL_ASSAY_ID)去掉前后空格NULLIF(TRIM(...), )如果结果是空字符串转成NULLIFNULL(..., UNKNOWN)如果是 NULL则替换为UNKNOWNCONCAT(处理后的ID, _ANLS)在ID后面加上固定字符串_ANLSMD5(...)对拼接后的字符串做 MD5加密哈希化作用1生成固定长度ID输出 32位字符串统一格式适合数仓主键2不可逆无法从 MD5 反推原始 ID适合脱敏/中间层建模3避免重复/冲突风险工程习惯统一主键风格6.29.2CAST(... AS DECIMAL(38,4))DECIMAL(38,4)表示一个定点数类型总长度38 位数字精度 precision小数位4 位scale6.29.3STR_TO_DATE(NULLIF(TRIM(T.CHECK_DATE), ), %Y-%m-%dT%H:%i:%s) AS CHECK_DATESTR_TO_DATE(字符串, 格式)作用按指定格式把字符串解析成 DATETIME6.29.4 NULLIF和IFNULL有区别吗NULLIF把某个值“变成 NULL”IFNULL把 NULL “替换成某个值”