SQL窗口函数的高级应用 SQL数据分析的强大工具

-- 基础示例：计算每个部门的平均工资，同时保留每个员工的详细信息
SELECT
    employee_id,
    employee_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_department_salary
FROM
    employees;

-- 另一个例子：按销售额对每个地区的商店进行排名
SELECT
    store_id,
    region,
    sales,
    RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rank_in_region
FROM
    sales_data;

OVER (PARTITION BY ... ORDER BY ...)

SELECT
    region,
    salesperson,
    sales_amount,
    RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
FROM
    sales_performance
WHERE
    RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) <= 3;

WITH RankedSales AS (
    SELECT
        region,
        salesperson,
        sales_amount,
        RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
    FROM
        sales_performance
)
SELECT
    region,
    salesperson,
    sales_amount,
    sales_rank
FROM
    RankedSales
WHERE
    sales_rank <= 3;

LEAD()

ROWS BETWEEN

RANGE BETWEEN

daily_sales

sale_date

amount

WITH DailySalesWithLag AS (
    SELECT
        sale_date,
        amount,
        LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS previous_day_amount
    FROM
        daily_sales
)
SELECT
    sale_date,
    amount,
    previous_day_amount,
    (amount - previous_day_amount) * 100.0 / previous_day_amount AS daily_growth_rate_percent
FROM
    DailySalesWithLag
WHERE
    previous_day_amount > 0; -- 避免除以零

SELECT
    sale_date,
    amount,
    AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS seven_day_moving_avg
FROM
    daily_sales;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

AVG()

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sales
FROM
    daily_sales;

OVER

WITH UserFirstPurchase AS (
    SELECT
        user_id,
        MIN(order_date) OVER (PARTITION BY user_id) AS first_purchase_date,
        order_date,
        order_amount
    FROM
        orders
)
SELECT
    user_id,
    first_purchase_date,
    order_date,
    order_amount,
    (order_date - first_purchase_date) AS days_since_first_purchase -- 假设日期可以直接相减得到天数
FROM
    UserFirstPurchase
WHERE
    (order_date - first_purchase_date) BETWEEN 0 AND 30; -- 分析首购后30天内的行为

SELECT
    employee_id,
    employee_name,
    department,
    sales_target_completion,
    AVG(sales_target_completion) OVER (PARTITION BY department) AS dept_avg_completion,
    sales_target_completion - AVG(sales_target_completion) OVER (PARTITION BY department) AS deviation_from_avg
FROM
    employee_performance;

deviation_from_avg

SELECT
    product_id,
    sale_date,
    daily_sales_volume,
    AVG(daily_sales_volume) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS thirty_day_moving_avg_sales
FROM
    product_daily_sales;