在PostgreSQL中按年,月或一天按小组
#postgres #sql #database #分析

介绍

PostgreSQL是开发人员每天使用的最佳关系数据库之一。

实际上,PostgreSQL支持广泛的数据操作和分析功能。最重要的分析特征之一是按特定间隔分组数据。

让我们代表PostgreSQL如何优雅处理此功能。

您的经理要求

更深入地挖掘之前,让我们考虑一下您的orders表:

id 价格 创建
1 100 2022-01-12 01:15:00
2 120 2022-02-10 23:44:00
3 80 2023-01-09 08:14:00
4 150 2023-07-09 20:50:00
5 50 2023-07-10 09:33:00
6 30 2023-07-10 15:23:00
7 90 2023-09-23 10:12:00

您的经理询问您三个要求:

  1. 每年计算总订单的销售

  2. 计算总订单的销售 2023年每个月

  3. 计算总订单的销售 2023年7月每天

一年一度的小组

让我们解决第一个要求。

1-使用data_trunc()

此功能,顾名思义,根据特定日期部分截断了日期。您可以对此有更多的了解here

SELECT DATE_TRUNC('YEAR', "createdAt") AS year, SUM("price") AS total
FROM "orders"
GROUP BY year
ORDER BY year

此语句返回:

总计
2022-01-01 00:00:00 220
2023-01-01 00:00:00 400

您可能会注意到,year字段作为时间戳返回到指定的精度,而不是数字。

要返回一年,您可以使用以下内容:

SELECT DATE_PART('YEAR', DATE_TRUNC('YEAR', "createdAt")::TIMESTAMP)::INTEGER AS year
...

::INTEGER铸造了从DATE_PART()返回到整数的双重值。

2-使用提取物()

此功能从日期开始提取一年,月或...。了解更多有关here的信息。

SELECT EXTRACT(YEAR FROM "createdAt")::INTEGER AS year, SUM("price") AS total
FROM "orders"
GROUP BY year
ORDER BY year

此语句返回:

总计
2022 220
2023 400

DATE_TRUNC()函数不同,EXTRACT()函数以双重返回一年。因此,我使用::INTEGER铸造了从EXTRACT()返回到整数的双重值。

3-使用to_char()

此功能将其输入转换为字符串。检查一下here

SELECT TO_CHAR("createdAt", 'YYYY')::INTEGER AS year, SUM("price") AS total
FROM "orders"
GROUP BY year
ORDER BY year

::INTEGER铸造了从TO_CHAR()返回到整数的字符串值。

此语句返回与EXTRACT()相同的结果。

一年的一个月小组

让我们解决第二个要求。

喜欢按年分组,您可以使用相同功能在一年中按月进行分组。

1-使用data_trunc()

SELECT DATE_TRUNC('MONTH', "createdAt") AS month, SUM("price") AS total
FROM "orders"
WHERE DATE_PART('YEAR', "createdAt") = 2023
GROUP BY month
ORDER BY month

此语句返回:

总计
2023-01-01 00:00:00 80
2023-07-01 00:00:00 230
2023-09-01 00:00:00 90

2-使用提取物()

SELECT EXTRACT(MONTH FROM "createdAt")::INTEGER AS month, SUM("price") AS total
FROM "orders"
WHERE DATE_PART('YEAR', "createdAt") = 2023
GROUP BY month
ORDER BY month

此语句返回:

总计
1 80
7 230
9 90

3-使用to_char()

SELECT TO_CHAR("createdAt", 'MM')::INTEGER AS month, SUM("price") AS total
FROM "orders"
WHERE DATE_PART('YEAR', "createdAt") = 2023
GROUP BY month
ORDER BY month

此语句返回与EXTRACT()相同的结果。

一年中的一天小组

让我们解决第三个要求。

1-使用data_trunc()

SELECT DATE_TRUNC('DAY', "createdAt") AS day, SUM("price") AS total
FROM "orders"
WHERE DATE_PART('MONTH', "createdAt") = 7 AND DATE_PART('YEAR', "createdAt") = 2023
GROUP BY day
ORDER BY day

此语句返回:

总计
2023-07-09 00:00:00 150
2023-07-10 00:00:00 80

2-使用提取物()

SELECT EXTRACT(DAY FROM "createdAt")::INTEGER AS day, SUM("price") AS total
FROM "orders"
WHERE DATE_PART('MONTH', "createdAt") = 7 AND DATE_PART('YEAR', "createdAt") = 2023
GROUP BY day
ORDER BY day

此语句返回:

总计
9 150
10 80

3-使用to_char()

SELECT TO_CHAR("createdAt", 'DD')::INTEGER AS day, SUM("price") AS total
FROM "orders"
WHERE DATE_PART('MONTH', "createdAt") = 7 AND DATE_PART('YEAR', "createdAt") = 2023
GROUP BY day
ORDER BY day

此语句返回与EXTRACT()相同的结果。

结论

在本文中,我们知道如何按特定日期,年,月和日进行分组。

,为此,我们使用以下方式介绍了三种不同的方法:

  • date_trunco​​()

  • extract()

  • to_char()

离开之前

如果您发现这篇文章有用,请查看这些文章:

非常感谢您与我在一起。希望您喜欢阅读这篇文章。