PostgreSQL数据统计SQL示例_pqsql-优快云博客

在 PostgreSQL 中进行复杂的数据分析时，通常会涉及多表联合查询、窗口函数、聚合函数、子查询等多种 SQL 特性。以下是一个包含多个复杂操作的 PostgreSQL 示例，用于统计不同维度的数据，比如按用户、日期和地区进行的销售统计。

假设有以下几个表：

sales — 销售数据表，记录每笔销售的详细信息。
products — 产品表，记录产品的详细信息。
users — 用户表，记录用户的详细信息。
regions — 地区表，记录不同地区的信息。

示例表结构

CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    product_id INT,
    user_id INT,
    region_id INT,
    sale_date DATE,
    sale_amount DECIMAL(10, 2)
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100)
);

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    user_name VARCHAR(100)
);

CREATE TABLE regions (
    region_id SERIAL PRIMARY KEY,
    region_name VARCHAR(100)
);

示例 SQL 查询：按用户、日期和地区统计销售额，包含复杂的窗口函数和子查询

这个查询统计每个用户在不同日期、不同地区的销售额，并计算每个用户的总销售额和排名。

WITH SalesSummary AS (
    SELECT
        s.user_id,
        s.sale_date,
        s.region_id,
        r.region_name,
        p.product_name,
        SUM(s.sale_amount) AS total_sales
    FROM sales s
    JOIN products p ON s.product_id = p.product_id
    JOIN regions r ON s.region_id = r.region_id
    GROUP BY s.user_id, s.sale_date, s.region_id, r.region_name, p.product_name
),
UserTotalSales AS (
    SELECT
        user_id,
        SUM(total_sales) AS user_total_sales
    FROM SalesSummary
    GROUP BY user_id
),
RankedSales AS (
    SELECT
        ss.user_id,
        ss.sale_date,
        ss.region_name,
        ss.product_name,
        ss.total_sales,
        uts.user_total_sales,
        RANK() OVER (PARTITION BY ss.sale_date ORDER BY ss.total_sales DESC) AS daily_sales_rank,
        RANK() OVER (PARTITION BY ss.region_name ORDER BY ss.total_sales DESC) AS region_sales_rank
    FROM SalesSummary ss
    JOIN UserTotalSales uts ON ss.user_id = uts.user_id
)
SELECT
    rs.user_id,
    u.user_name,
    rs.sale_date,
    rs.region_name,
    rs.product_name,
    rs.total_sales,
    rs.user_total_sales,
    rs.daily_sales_rank,
    rs.region_sales_rank
FROM RankedSales rs
JOIN users u ON rs.user_id = u.user_id
ORDER BY rs.sale_date, rs.region_name, rs.daily_sales_rank;

解释：

SalesSummary：首先，我们聚合销售数据，按用户、日期、地区和产品分组，计算每组的总销售额。
UserTotalSales：然后，我们计算每个用户的总销售额。这个结果用于后续排名计算。
RankedSales：使用窗口函数 RANK() 对每个日期和地区内的销售额进行排名。PARTITION BY 用于按日期和地区分别计算排名，ORDER BY 指定按销售额降序排列。
最终查询：最终将所有的销售数据与用户信息合并，按日期和地区排序，显示每个用户在每个日期、每个地区的销售额及排名。

复杂性说明：

多个表联合：涉及了四个表的联合查询。
聚合函数：使用了 SUM() 来计算总销售额。
窗口函数：使用了 RANK() 来进行排名，按销售额对用户进行排序。
子查询：通过 WITH 子查询来拆分复杂的计算逻辑。

扩展：

如果需要进一步扩展，可以增加更多维度的聚合，或者增加计算同比、环比等时间序列的分析。

详细注释说明的 SQL 查询：

-- 用于汇总销售数据的子查询
WITH SalesSummary AS (
    -- 从销售表中选择用户、日期、地区、产品及其销售额
    SELECT
        s.user_id,  -- 用户ID
        s.sale_date,  -- 销售日期
        s.region_id,  -- 地区ID
        r.region_name,  -- 地区名称（通过地区ID