月报日：八张业务报表

sum(...) over () 在分组之后、LIMIT 之前算出全局总 GMV，正好当分母。自洽校验：去掉 LIMIT，全部商品的占比之和 = 100%；TOP10 之和当然小于 100%。

select p.name as 商品,
       sum(i.qty * i.unit_price) as gmv,
       round(100.0 * sum(i.qty * i.unit_price)
             / sum(sum(i.qty * i.unit_price)) over (), 1) as 占比
from order_items i
join products p on p.id = i.product_id
group by p.id, p.name
order by gmv desc
limit 10;

两个数字来自不同的表和时间列，先各自按月聚合成「月份表」再对齐。用户注册横跨两年、订单只有近 90 天--必须 FULL JOIN，INNER JOIN 会把没订单的月份整行吃掉。

with new_u as (
  select date_trunc('month', created_at) as 月份, count(*) as 新增用户数
  from users
  group by 1
),
act as (
  select date_trunc('month', created_at) as 月份,
         count(distinct user_id) as 当月下单用户数
  from orders
  group by 1
)
select coalesce(n.月份, a.月份) as 月份,
       coalesce(n.新增用户数, 0) as 新增用户数,
       coalesce(a.当月下单用户数, 0) as 当月下单用户数
from new_u n
full join act a on a.月份 = n.月份
order by 1;

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

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;

窗口函数在 GROUP BY 之后求值，所以 over () 里能直接引用 sum(...)--排序键就是聚合结果。city 为 NULL 的组照常参与排名，展示时标「未知」。

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

三要素翻译：粒度 = 用户（先压成每人一行）、过滤 = 下过单的用户、度量 = 单数 ≥ 2 的占比。分母是「下过单的用户」而不是全部注册用户--两个口径差一截，月报里必须写明用的哪个。

select count(*) filter (where 单数 >= 2) as 复购用户数,
       count(*) as 下单用户数,
       round(100.0 * count(*) filter (where 单数 >= 2) / count(*), 1) as 复购率
from (
  select user_id, count(*) as 单数
  from orders
  group by user_id
) t;

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

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;