月报日：八张业务报表

月报第一件事是把口径钉死：同一个「GMV」，三种算法三个数--全部订单（含未支付、已取消）、只算已支付、再把 D01 埋的负金额压成 0。三列并排摆出来，差额来自哪一眼就看见。用 LEFT JOIN 是因为 city 可能为空，别让 INNER 把订单吃掉。选哪个口径都算对，错的是不把选择写进报表备注。

select coalesce(u.city, '未知') as 城市,
       sum(o.total_amount) as 全部口径,
       sum(o.total_amount) filter (where o.status = 2) as 已支付口径,
       sum(greatest(o.total_amount, 0)) filter (where o.status = 2) as 剔负口径
from orders o
left join users u on u.id = o.user_id
group by 1
order by 已支付口径 desc nulls last;

D06 的补零套路原样搬过来，只把「近 30 天」换成「本月 1 号到今天」：日历在左、订单在右，LEFT JOIN。两个坑都在右表没匹配上的那天--必须 count(o.id) 不能 count(*)（没订单的那天 LEFT JOIN 也给一行，count(*) 会数成 1），sum 对空集得 NULL 不是 0，要 coalesce 兜住。

select d.日期::date as 日期,
       count(o.id) as 单数,
       coalesce(sum(o.total_amount), 0) as gmv
from generate_series(date_trunc('month', current_date),
                     current_date,
                     interval '1 day') as d(日期)
left join orders o on o.created_at::date = d.日期::date
group by d.日期
order by d.日期;

口径题：分母选「购买该商品的用户数」（去重），不是订单数更不是件数--三种分母三个结果。选哪个都算对，错的是不把口径写进报表备注。

select p.name as 商品,
       sum(i.qty * i.unit_price) as gmv,
       count(distinct o.user_id) as 购买用户数,
       round(sum(i.qty * i.unit_price) / count(distinct o.user_id), 2) as 客单价
from order_items i
join orders o on o.id = i.order_id
join products p on p.id = i.product_id
group by p.id, p.name
order by 客单价 desc
limit 10;

「未支付」用 status = 1 圈定，别用 paid_at is null--已取消的单也没有 paid_at，口径会混进取消单。这里是在列明细不是聚合，一对多放大是应该的；千万别顺手 sum(o.total_amount)。

select o.id as 单号, o.created_at as 下单时间,
       p.name as 商品, i.qty as 数量, i.unit_price as 单价
from orders o
join order_items i on i.order_id = o.id
join products p on p.id = i.product_id
where o.status = 1
  and o.created_at < now() - interval '24 hours'
order by o.created_at, o.id;

「排名」在这里就是排序：ORDER BY 能引用 SELECT 里的别名 gmv，读到第几行就是第几名。city 为 NULL 的组照常成一组，展示时标「未知」。真要把名次落成一列、还要处理并列，那是 rank() 的活，D23 讲。

select u.city,
       sum(o.total_amount) as gmv
from orders o
join users u on o.user_id = u.id
group by u.city
order by gmv desc;

D09/D10/D11 三个点凑一块：LEFT JOIN 之后一个用户被放大成多行（下过 N 单就 N 行），所以分母必须 count(distinct u.id) 而不是 count(*)；o.id is null 就是 D10 的反连接，圈出没匹配上的那批；FILTER 让分子分母在同一条 SQL 里出来。自洽校验：从未下单 + 下过单 = 注册用户数。D12 用 EXCEPT 查的是同一批人，这里多要了一个占比。

select count(distinct u.id) as 注册用户数,
       count(distinct u.id) filter (where o.id is null) as 从未下单,
       round(100.0 * count(distinct u.id) filter (where o.id is null)
             / count(distinct u.id), 1) as 占比
from users u
left join orders o on o.user_id = u.id;

D12 的 DISTINCT ON 直接派上用场：第二排序键改成升序，留下的就是最早一单。窗口函数 row_number() 也能做，W4 展开。

select distinct on (o.user_id)
       o.user_id, u.name, o.created_at as 首单时间, o.total_amount as 首单金额
from orders o
join users u on u.id = o.user_id
order by o.user_id, o.created_at;

只有已支付单有 paid_at，其余状态 avg 直接得 NULL（聚合跳空）--「平均支付时长」这个指标只对 status = 2 有意义。另外那 20 单脏数据（支付早于下单）是负数，会把均值拉低一点，正式月报该剔除。

select coalesce(status::text, '未知') as 状态,
       count(*) as 单数,
       count(paid_at) as 有支付时间,
       round(avg(extract(epoch from (paid_at - created_at))) / 60, 1) as 平均支付时长_分钟
from orders
group by 1
order by 1;