题解 | SaaS产品租户核心功能模块用量及占比分析

哈哈学到了两点：①lateral join可以跟普通的join一起用②lateral内层的别名和外层别名可以一样

with module_agg as (
    select
    tenant_id,
    module_name,
    sum(call_count) module_count
    from usage_logs
    group by tenant_id, module_name
),

usage_agg as (
    select
    tenant_id,
    sum(module_count) total_count
    from module_agg
    group by tenant_id
)

select 
t.tenant_name,
t.plan_type,
m.module_name,
m.module_count total_calls,
round(m.module_count/u.total_count*100,2) usage_pct
from tenants t
join usage_agg u
on t.tenant_id = u.tenant_id
join lateral (
    select
    m.tenant_id,
    m.module_name,
    m.module_count
    from module_agg m
    where t.tenant_id = m.tenant_id
    order by m.tenant_id, m.module_count desc
    limit 2
) m
on true
order by t.tenant_id, total_calls desc, module_name