在 PostgreSQL 中按日期汇总数据

由 John Doe 十一月 6, 2024

摘要：在本文中，我们会介绍一些用于日期查询和汇总的关键函数。

目录

介绍

例如，当您的销售数据日期以时间戳格式存储，但现在您需要按天、周、季度和月获取报告。您需要将销售数据汇总到易于查看的报告中。您需要 BI 工具吗？实际上还没有必要。您的 Postgres 数据库中具有数百个函数，可让您按日期查询数据分析。通过使用一些好的传统 SQL，您可以对任何数据集的日期详情，获得强大的分析和商业智能。

在本文中，我们会介绍一些用于日期查询和汇总的关键函数。有关在 PostgreSQL 中存储日期和时间的最佳方法的总结，请参阅在 PostgreSQL 中处理时间。

日期间隔

interval是一种数据类型，可以修改其他时间相关数据，并可用于时间和日期查询。间隔类型非常方便，是您可以按日期快速汇总数据的第一选择。

下面是一个示例查询，它将计算过去 90 天内的订单总数。

SELECT SUM(total_amount) 
FROM orders
WHERE order_date >= NOW() - INTERVAL '90 days';

间隔值可以有两种写法，也可以用强制转换。这本质上是完全相同的查询。

SELECT SUM(total_amount) 
FROM orders
WHERE order_date >= NOW() - '90 days'::interval;

您还可以对更复杂的问题使用间隔。您可以选择时间范围，并且可以对多个间隔进行比较。

在此示例查询中，您可以使用 CASE 语句创建多个间隔范围。SQL 中的CASE语句用于在查询中执行条件处理，类似于编程中的if-else结构。在下面的查询中，它用于将订单分类为不同的时间范围（例如，“30-60 天前”、“60-90 天前”）。

SELECT
    CASE
        WHEN order_date BETWEEN (NOW() - INTERVAL '60 days') AND (NOW() - INTERVAL '30 days')
            THEN '30-60 days ago'
        WHEN order_date BETWEEN (NOW() - INTERVAL '90 days') AND (NOW() - INTERVAL '60 days')
            THEN '60-90 days ago'
    END AS date_range,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_sales
FROM orders
WHERE order_date BETWEEN (NOW() - INTERVAL '90 days') AND (NOW() - INTERVAL '30 days')
GROUP BY date_range
ORDER BY date_range;

date_trunc - 轻松按日期汇总

间隔是一个非常简单的想法，但是一旦您需要做几个基本汇总，大多数基于日期的数据分析都可以利用date_trunc函数。老实说，大多数人很少会使用到间隔，可能是不太符合普通人的思维方式。

乍一看，date_trunc 的名称可能表明它与格式有关，但请继续留意下面的示例，它远不止于此。date_trunc 是进行分析时查询工具箱中的一个重要部分。date_trunc 允许您按任何日期对数据进行切片和切块，准确查看获取有洞察力的汇总分析所需的内容。

下面是一个示例查询，其中显示了每个月的订单数量和总订单销售额。

SELECT
    date_trunc('month', order_date) AS month,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS monthly_total
FROM
    orders
GROUP BY
    date_trunc('month', order_date)
ORDER BY
    month;

        month        | total_orders | monthly_total 
---------------------+--------------+---------------
 2024-08-01 00:00:00 |           11 |       2699.82
 2024-09-01 00:00:00 |           39 |       8439.41
(2 rows)

GROUP BY确保每行都会收集 date_trunc 部分的汇总。当您将date_trunc与GROUP BY结合使用时，您将根据截断日期聚合结果。这样，您就可以汇总每个唯一的截断日期的数据（如计数、总和、平均值）。

按天汇总

SELECT
    date_trunc('day', order_date) AS day,
    SUM(total_amount) AS daily_total
FROM
    orders
GROUP BY
    date_trunc('day', order_date)
ORDER BY
    day;

         day         | daily_total 
---------------------+-------------
 2024-08-21 00:00:00 |      349.98
 2024-08-22 00:00:00 |      899.98
 2024-08-23 00:00:00 |       34.98

按周汇总

SELECT
    date_trunc('week', order_date) AS week,
    SUM(total_amount) AS weekly_total
FROM
    orders
GROUP BY
    date_trunc('week', order_date)
ORDER BY
    week;

        week         | weekly_total
---------------------+-------------
 2024-08-19 00:00:00 |     1524.92
 2024-08-26 00:00:00 |     2854.84
 2024-09-02 00:00:00 |     4309.72

季度

Postgres 可以用 date_trunc 获得季度，从 1 月 1 日、4 月 1 日、7 月 1 日和 10 月 1 日开始。

SELECT
    date_trunc('quarter', order_date) AS quarter,
    SUM(total_amount) AS weekly_total
FROM
    orders
GROUP BY
    date_trunc('quarter', order_date)
ORDER BY
    quarter;

 2022-01-01 00:00:00+00 |    313872.84
 2022-04-01 00:00:00+00 |    270162.38
 2022-07-01 00:00:00+00 |    295197.26
 2022-10-01 00:00:00+00 |    283051.73

date_trunc CTE 是一种超能力

如果您有一个简单的示例，像上面的一样，则使用基础的 date_trunc 查询会是一个好主意。除此之外，在 CTE 中配合 date_trunc 通常也是一个好主意。这有几个原因：

  • 性能：如果您在大型数据集中重复使用 date_trunc，则使用 CTE 可能会让 PostgreSQL 优化得更好，因为它可以计算一次结果并重复使用。
  • 可读性：将查询拆分为可管理的部分，可使其更易于阅读、维护和重用。

下面是一个使用 date_trunc 按月汇总的示例查询，该查询计算每月的总销售额，然后计算月度销售额变化和浮动百分比：

WITH monthly_sales AS (
    SELECT
        date_trunc('month', order_date) AS month, 
        SUM(total_amount) AS total_sales 
    FROM orders
    WHERE order_date >= NOW() - INTERVAL '6 months' -- Filter for the last 6 months
    GROUP BY date_trunc('month', order_date) -- Group by the truncated month
    ORDER BY month
),
sales_with_change AS (
    SELECT
        month,
        total_sales,
        LAG(total_sales, 1) OVER (ORDER BY month) AS previous_month_sales, -- Get the sales of the previous month
        (total_sales - LAG(total_sales, 1) OVER (ORDER BY month)) AS sales_change,
        CASE
            WHEN LAG(total_sales, 1) OVER (ORDER BY month) IS NOT NULL THEN
                ((total_sales - LAG(total_sales, 1) OVER (ORDER BY month)) / LAG(total_sales, 1) OVER (ORDER BY month)) * 100
            ELSE
                NULL
        END AS percentage_change
    FROM monthly_sales
)
SELECT
    month,
    total_sales,
    previous_month_sales,
    sales_change,
    percentage_change
FROM sales_with_change
ORDER BY month DESC; 

-[ RECORD 1 ]--------+-------------------------
month                | 2024-10-01 00:00:00+00
total_sales          | 64685.65
previous_month_sales | 103188.90
sales_change         | -38503.25
percentage_change    | -37.31336413121954008600
-[ RECORD 2 ]--------+-------------------------
month                | 2024-09-01 00:00:00+00
total_sales          | 103188.90
previous_month_sales | 88512.52
sales_change         | 14676.38
percentage_change    | 16.58113451068843142200

使用 to_char 自定义输出的日期格式

如果您需要以特定格式输出报告，以在另一个系统中使用或只是更可读，您可以将 to_char 函数嵌入到我们前面讨论过的任何查询中。

设置月度格式

例如，要更改月度的输出形式，在我们使用 date_trunc 按月汇总的查询中，只显示月份和年份：

SELECT
    to_char(date_trunc('month', order_date), 'FMMonth YYYY') AS formatted_month,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS monthly_total
FROM
    orders
GROUP BY
    date_trunc('month', order_date)
ORDER BY
    date_trunc('month', order_date);

 formatted_month | total_orders | monthly_total
-----------------+--------------+---------------
 August 2024     |           11 |       2699.82
 September 2024  |           39 |       8439.41

常用的日期文本输出，可以像这样完成：

SELECT TO_CHAR(NOW():: DATE, 'Mon dd, yyyy');

财务季度

要重命名季度，可以通过调用 to_char 来完成，如下所示：

to_char(date_trunc('quarter', order_date), '"Q"Q-YYYY'):

SELECT
    to_char(date_trunc('quarter', order_date), '"Q"Q-YYYY') AS formatted_quarter,
    SUM(total_amount) AS total_amount
FROM
    orders
GROUP BY
    date_trunc('quarter', order_date)
ORDER BY
    date_trunc('quarter', order_date);

 formatted_quarter | total_amount
-------------------+--------------
 Q3-2024           |     11139.23

超强的分组

如果你一直在关注上面的例子，你已经看到了很多GROUP BY。Postgres 有一些更高级的分组功能，来处理此类数据，这对于基于日期的分析非常有帮助。

GROUP BY ROLLUP

ROLLUP是分析报告的一个非常方便的函数。ROLLUP会给你大批量的数据，包括有空值的数据。如果您想按某些类别对您的产品或数据进行快速调查，rollup 是一个很好的工具。您可以将其与我们的 date_trunc 结合使用，以获取按日期销售的产品类别的汇总。

SELECT
    to_char(date_trunc('month', order_date), 'FMMonth YYYY') AS month,
    category,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_amount
FROM
    orders
GROUP BY 
    ROLLUP (date_trunc('month', order_date), category)
ORDER BY
    date_trunc('month', order_date), category;

     month      |  category   | total_orders | total_amount 
----------------+-------------+--------------+--------------
 October 2021   | Books       |            3 |      2375.73
 October 2021   | Clothing    |           18 |     13770.09
 October 2021   | Computers   |           17 |     13005.87
 October 2021   | Electronics |           25 |     16358.96
 October 2021   |             |           63 |     45510.65

GROUP BY CUBE

CUBE函数可更进一步，对您查询的所有维度执行小计和总计。因此，与 ROLLUP 非常相似，我们可以同时查看销售日期和类别。

SELECT
    to_char(date_trunc('month', order_date), 'FMMonth YYYY') AS month,
    category,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_amount
FROM
    orders
GROUP BY 
    CUBE (date_trunc('month', order_date), category)
ORDER BY
    date_trunc('month', order_date), category;

 October 2024   | Books       |            9 |      5574.92
 October 2024   | Clothing    |           19 |     11856.80
 October 2024   | Computers   |           22 |     13002.10
 October 2024   | Electronics |           50 |     34251.83
 October 2024   |             |          100 |     64685.65
                | Books       |          521 |    328242.79
                | Clothing    |         1133 |    739866.25
                | Computers   |         1069 |    680817.70
                | Electronics |         2709 |   1707713.80
                |             |         5432 |   3456640.54

CUBE 有点有趣，因为这些副标题没有标记，它们具有表示总数的空值，如下所示：

img

总结

如您所见，Postgres 可以支持您按日期进行报告和分析！随着越来越多的工具将分析业务负载引入 Postgres，并且 Postgres 具有支撑 OLAP 业务负载的性能，预计将来会有更多的人利用这些如此方便的函数。

一个简短的总结：

间隔 - 适合快速查询，以查看过去一个月左右的情况

date_trunc - 非常棒，可以按天、周、月、季度、年等，为您提供单独的汇总数据行

ROLLUP - 允许您跨其他属性或类别以及日期，进行汇总

CUBE - 使用 CUBE 可以执行小计和总计

to_char - 可以帮助您以特定样式的日期格式或文本字符串，格式化输出

搜索

分类

标签