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

WITH t1 AS (
    SELECT u.tenant_id,
           module_name,
           SUM(call_count) AS  total_calls,
           round(SUM(call_count)/avg(total_cnt)*100,2) as usage_pct
    FROM usage_logs u
    LEFT JOIN (
        SELECT sum(call_count) as total_cnt,tenant_id
        from usage_logs
        GROUP BY tenant_id
    ) a ON u.tenant_id=a.tenant_id
    GROUP BY tenant_id,module_name
)
SELECT tenant_name,
       plan_type,
       module_name,
       total_calls,
       usage_pct
FROM tenants t 
inner join lateral (
    SELECT *
    from t1 
    where t1.tenant_id=t.tenant_id
    order by t.tenant_id ASC, total_calls  DESC,module_name ASC
    limit 2
) c on true