介绍
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 |
您的经理询问您三个要求:
-
每年计算总订单的销售 。
-
计算总订单的销售 2023年每个月。
-
计算总订单的销售 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() p> li>
-
extract()
-
to_char()
离开之前
如果您发现这篇文章有用,请查看这些文章:
非常感谢您与我在一起。希望您喜欢阅读这篇文章。