SQL查询需求：筛选高于所属公司平均租金的房产及注册时长统计

1. 查询租金高于所属公司平均租金的房产 #

假设三张表关联关系为：Properties 通过 branch_id 关联 Branches，Branches 通过 company_id 关联 Companies。采用窗口函数实现高效查询，避免重复表扫描：

SELECT p.*
FROM (
    SELECT 
        p.*,
        AVG(p.rent) OVER (PARTITION BY c.company_id) AS company_avg_rent
    FROM Properties p
    JOIN Branches b ON p.branch_id = b.branch_id
    JOIN Companies c ON b.company_id = c.company_id
) AS sub
WHERE sub.rent > sub.company_avg_rent;

关键点 #

• 窗口函数 AVG(p.rent) OVER (PARTITION BY c.company_id) 在一次关联扫描中完成各公司平均租金计算，性能优于传统子查询关联方式。
• 外层仅需对比单条房产租金与对应公司平均租金即可完成筛选。

2. 按注册时长区间统计房产数量 #

假设 Properties 表包含房产注册时间字段 register_date，通过 CASE WHEN 划分区间并分组统计：

-- PostgreSQL 版本
SELECT
    CASE
        WHEN DATE_PART('month', AGE(CURRENT_DATE, register_date)) <= 3 THEN '0-3个月'
        WHEN DATE_PART('month', AGE(CURRENT_DATE, register_date)) <= 6 THEN '4-6个月'
        WHEN DATE_PART('month', AGE(CURRENT_DATE, register_date)) <= 12 THEN '6-12个月'
        WHEN DATE_PART('year', AGE(CURRENT_DATE, register_date)) <= 2 THEN '1-2年'
        ELSE '2年以上'
    END AS register_duration,
    COUNT(*) AS property_count
FROM Properties
GROUP BY register_duration
ORDER BY 
    CASE register_duration
        WHEN '0-3个月' THEN 1
        WHEN '4-6个月' THEN 2
        WHEN '6-12个月' THEN 3
        WHEN '1-2年' THEN 4
        WHEN '2年以上' THEN 5
    END;

-- MySQL 版本
SELECT
    CASE
        WHEN TIMESTAMPDIFF(MONTH, register_date, NOW()) <= 3 THEN '0-3个月'
        WHEN TIMESTAMPDIFF(MONTH, register_date, NOW()) <= 6 THEN '4-6个月'
        WHEN TIMESTAMPDIFF(MONTH, register_date, NOW()) <= 12 THEN '6-12个月'
        WHEN TIMESTAMPDIFF(YEAR, register_date, NOW()) <= 2 THEN '1-2年'
        ELSE '2年以上'
    END AS register_duration,
    COUNT(*) AS property_count
FROM Properties
GROUP BY register_duration
ORDER BY 
    CASE register_duration
        WHEN '0-3个月' THEN 1
        WHEN '4-6个月' THEN 2
        WHEN '6-12个月' THEN 3
        WHEN '1-2年' THEN 4
        WHEN '2年以上' THEN 5
    END;

关键点 #

• 根据不同SQL方言替换时间计算函数，保证兼容性。
• ORDER BY 中的CASE语句确保统计结果按区间逻辑顺序展示，避免字符串排序导致的混乱。

内容的提问来源于stack exchange，提问作者scw