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

select
left_flame.tenant_name
,left_flame.plan_type
,right_flame.module_name
,right_flame.total_calls
,round( total_calls / tenant_calls * 100, 2 ) as usage_pct
from (
    select  
    tenant_id
    ,tenant_name
    ,plan_type
    ,sum( call_count ) as tenant_calls
    from usage_logs left join tenants using(tenant_id)
    group by tenant_id, tenant_name, plan_type
) as left_flame
left join lateral(
    select
    tenant_name
    ,plan_type
    ,module_name
    ,sum( call_count ) as total_calls
    from usage_logs  left join tenants using(tenant_id)
    where tenant_name = left_flame.tenant_name
    group by tenant_name, plan_type, module_name
    order by tenant_name, total_calls desc
    limit 2
) as right_flame on true
order by left_flame.tenant_id, total_calls desc, module_name;