DAY 05 W1 · 入职第一周：老板的第一张表

数据是脏的：函数与清洗

导数据时埋的雷陆续爆了：金额有负数、有的订单 paid_at 比 created_at 还早。老板：「能不能清洗成能看的样子？」

学 35 min
练 70 min
盘 15 min
共 120 分钟

学 · 35 min

  1. 01字符串函数：concat / substring / split_part / trim / lpad

    场景老板要「ORD-000001」格式的业务单号--拼接、截取、补零，全是字符串函数的活。

    一句话各记一个用途：concat / || 拼接、substring 按位置截取、split_part(串, 分隔符, 第几段) 按分隔符拆、trim 去首尾空白、lpad(文本, 总长, 补什么) 左侧补字符到指定长度。

    select 'ORD-' || lpad('123', 6, '0');        -- ORD-000123
select split_part('ORD-000123', '-', 2);     -- 000123
select substring('ORD-000123' from 5 for 3); -- 000

    易错lpad 的参数是文本，数字要先 ::text；数字直接塞进去会报类型错。

  2. 02类型转换 :: 与 cast()，转换失败怎么办

    场景字符串 '123' 要参与计算、'000123' 要变回数字--类型不转，SQL 寸步难行。

    :: 是 PG 的简写（cast(x as 类型) 是标准写法），'123'::int、123::text 随手转。要命的是转换失败直接报错：整条查询挂掉，一行脏数据毁一锅。清洗脏数据时先用 ~ 正则验格式，确认能转再转。

    select '123'::int + 1;                          -- 124
select 'ORD-000123' ~ '^ORD-\d{6}$';          -- true：格式合法才转
select split_part('ORD-000123', '-', 2)::int;  -- 123

    易错PG 没有内置的「转换失败返回 NULL」函数（别的库叫 try_cast），要么先验格式，要么自定义函数。

  3. 03COALESCE / NULLIF / GREATEST / LEAST

    场景负金额要抬到 0、除法分母可能为 0、展示时 NULL 要换文字--四个小工具各治一病。

    coalesce(a, b, ...) 取第一个非空（NULL 兜底）；nullif(a, b) 在 a=b 时返回 NULL（专治除零：x / nullif(y, 0) 分母为 0 得 NULL 而不是崩）；greatest / least 取多值中的最大 / 最小（负金额抬零用 greatest(金额, 0)）。

    select coalesce(paid_at, created_at) as 最后动作时间   -- NULL 兜底
from orders limit 5;

select greatest(total_amount, 0) as 修正展示     -- 负数抬 0
from orders where total_amount < 0 limit 5;

    易错除法永远配 nullif(分母, 0) 防御--报表崩在线上多半是没写它。

  4. 04round / abs 修数值：整数除法的坑

    场景算占比输出全是 0，数据明明没错。

    round(x, n) 四舍五入到 n 位，n 可以是负数（-1 舍到十位）；abs 绝对值。占比输出为 0 的元凶几乎总是整数除法：1 / 2 在 SQL 里得 0，写 100.0 * a / b 先把一边变浮点。

    select round(1234.56, -1);         -- 1230：舍到十位
select 1 / 2, 100.0 * 1 / 2;       -- 0 vs 50
select abs(-42.5);                 -- 42.5

    易错整数 / 整数 = 整数（向零截断），参与除法前先写 100.0 或 ::numeric。

  5. 05清洗的正确姿势：先「展示时修正」，别急着 UPDATE 原表

    场景老板说「洗成能看的样子」--你想直接把负金额 update 成 0。

    原表是事实记录：负金额是「导出工具出了问题」这个事实的证据，UPDATE 掉它，问题就永远查不到了。正确姿势是在查询层修正展示（greatest、coalesce、case），原表保持原样；什么时候真的要改表、怎么改才安全，W5 专门讲。

    -- 展示时修正：原表不动
select id,
       total_amount as 原始值,
       greatest(total_amount, 0) as 展示值
from orders
where total_amount < 0 limit 5;

    易错动手 UPDATE 原表 = 销毁证据。清洗的第一原则：能不动原表就不动。

练 · 70 min

  1. 找出负金额订单，用 greatest(total_amount, 0) 修正展示
    参考答案

    greatest(列, 0) 把负数抬到 0。注意没有 UPDATE 原表--原表是事实记录，修正只发生在展示层。

    select id, total_amount as 原金额,
       greatest(total_amount, 0) as 修正展示
from orders
where total_amount < 0;     -- ≈95 行
  2. 查出 paid_at 早于 created_at 的异常单（数据里埋了，找出几条）
    参考答案

    灌数时故意埋的 20 单；真实业务里这种行一般意味着时钟问题或补录数据，要上报而不是静默修掉。

    select id, created_at, paid_at
from orders
where paid_at < created_at;   -- 20 行
  3. 金额四舍五入到十位（round(x, -1)），统计各档订单数
    参考答案

    round 的第二个参数可以是负数：-1 舍到十位，-2 舍到百位。

    select round(total_amount, -1) as 金额档, count(*) as 单数
from orders
group by 1
order by 1;
  4. 用 row_number() 按时间顺序给订单编号，再拼出「ORD-000001」格式的业务单号（lpad）
    参考答案

    lpad(文本, 总长度, 补什么)：左补零到 6 位；数字要先 ::text 才能 lpad。

    select 'ORD-' || lpad(row_number() over (order by created_at)::text, 6, '0') as 业务单号
from orders
limit 5;
  5. 用 split_part 把第 4 题的单号反解回数字，验证与编号一致
    参考答案

    split_part(串, 分隔符, 第几段)。能成功 ::int 说明格式还原对了；真实验证时对第 4 题的输出列做反解再比对。

    select split_part('ORD-000123', '-', 2)::int;   -- 123
过关标准 负金额、时间倒挂、空状态三类脏数据各有处理办法，能说出为什么不直接改原表。