题解 | 贷款情况

select city
,total_loan_amount
,average_loan_amount
,total_customers
,loan_type_name as most_applied_loan_type
from (
    select city
    ,round(sum(loan_amount),2) as total_loan_amount
    ,round(sum(loan_amount) / count(distinct customer_name),2) as average_loan_amount
    ,round(count(distinct customer_id),2) as total_customers
    from loan_applications join customers using(customer_id)
    group by city
) as t1 
left join (
    select city
    ,loan_type_id
    ,loan_type_name
    ,row_number()over(partition by city order by count(loan_type_name) DESC,loan_type_id ASC ) as rk
    from loan_types join loan_application_types using(loan_type_id)
    join loan_applications using(application_id)
    join customers using(customer_id)
    group by city,loan_type_id,loan_type_name
) as t2
using(city)
where rk = 1;

row_number()over(partition by city order by count(loan_type_name) DESC,loan_type_id ASC )

子查询给出分组排名以及值，主查询不用再分组，只对排名进行筛选即可

group by city,loan_type_id,loan_type_name 这一行是必要的，核心原因是 SQL 分组逻辑与聚合函数的依赖关系，以下是详细拆解：

1. 为何需要 GROUP BY？

在子查询 t2 中：

sql

select 
  city,
  loan_type_id,
  loan_type_name,
  row_number()over(...) as rk
from loan_types 
join loan_application_types using(loan_type_id)
join loan_applications using(application_id)
join customers using(customer_id)
group by city, loan_type_id, loan_type_name  -- 关键分组

• 聚合逻辑：需统计每个 city 下，每个 loan_type_id（及对应的 loan_type_name） 的申请数量（用于 row_number() 排序）。
• SQL 规则：若使用聚合函数（如 count(loan_type_name)）或窗口函数，且 SELECT 包含非聚合字段（city、loan_type_id、loan_type_name），则这些字段必须出现在 GROUP BY 中（否则违反 ONLY_FULL_GROUP_BY 规则）。