北京昆仑数智-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

即

'MC' AS DATA_REGION

表示：每一行数据都固定是 MC

6.29

SELECT 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 T

6.29.1

MD5(CONCAT(IFNULL( NULLIF(TRIM(T.OIL_ASSAY_ID), '') , 'UNKNOWN'), '_ANLS')) AS PRODUCT_OIL_ANLS_ID

TRIM(T.OIL_ASSAY_ID)

去掉前后空格

NULLIF(TRIM(...), '')

如果结果是空字符串''，转成NULL

IFNULL(..., 'UNKNOWN')

如果是 NULL，则替换为'UNKNOWN'

CONCAT(处理后的ID, '_ANLS')

在ID后面加上固定字符串_ANLS

MD5(...)

对拼接后的字符串做 MD5加密（哈希化）

作用：

（1）生成固定长度ID

• 输出 32位字符串
• 统一格式，适合数仓主键

（2）不可逆

• 无法从 MD5 反推原始 ID
• 适合脱敏/中间层建模

（3）避免重复/冲突风险（工程习惯）

• 统一主键风格

6.29.2

CAST(... AS DECIMAL(38,4))

DECIMAL(38,4)表示一个定点数类型：

• 总长度：38 位数字（精度 precision）
• 小数位：4 位（scale）

6.29.3

STR_TO_DATE(NULLIF(TRIM(T.CHECK_DATE), ''), '%Y-%m-%dT%H:%i:%s') AS CHECK_DATE

STR_TO_DATE(字符串, 格式)

作用：

按指定格式，把字符串解析成 DATETIME

6.29.4 NULLIF和IFNULL有区别吗

• NULLIF：把某个值“变成 NULL”
• IFNULL：把 NULL “替换成某个值”