如何基于日期将SQL查询的timestamp数据聚合为数组

问题背景 #

我需要基于timestamp字段聚合数据，为每一天生成对应的task_start数组。
当前使用的查询语句如下：

SELECT date(task_start) AS started, task_start 
  FROM tt_records
  GROUP BY started, task_start
  ORDER BY started DESC;

当前查询输出结果如下：

+------------+------------------------+
| started    | task_start             |
|------------+------------------------|
| 2021-08-30 | 2021-08-30 16:45:55+00 |
| 2021-08-29 | 2021-08-29 06:47:55+00 |
| 2021-08-29 | 2021-08-29 15:41:50+00 |
| 2021-08-28 | 2021-08-28 12:59:20+00 |
| 2021-08-28 | 2021-08-28 14:50:55+00 |
| 2021-08-26 | 2021-08-26 20:46:44+00 |
| 2021-08-24 | 2021-08-24 16:28:05+00 |
| 2021-08-23 | 2021-08-23 16:22:41+00 |
| 2021-08-22 | 2021-08-22 14:01:10+00 |
| 2021-08-21 | 2021-08-21 19:45:18+00 |
| 2021-08-11 | 2021-08-11 16:08:58+00 |
| 2021-07-28 | 2021-07-28 17:39:14+00 |
| 2021-07-19 | 2021-07-19 17:26:24+00 |
| 2021-07-18 | 2021-07-18 15:04:47+00 |
| 2021-06-24 | 2021-06-24 19:53:33+00 |
| 2021-06-22 | 2021-06-22 19:04:24+00 |
+------------+------------------------+

可以看到started列存在重复日期，期望得到的输出如下：

+------------+--------------------------------------------------+
| started    | task_start                                       |
|------------+--------------------------------------------------|
| 2021-08-30 | [2021-08-30 16:45:55+00]                         |
| 2021-08-29 | [2021-08-29 06:47:55+00, 2021-08-29 15:41:50+00] |
| 2021-08-28 | [2021-08-28 12:59:20+00, 2021-08-28 14:50:55+00] |
| 2021-08-26 | [2021-08-26 20:46:44+00]                         |
| 2021-08-24 | [2021-08-24 16:28:05+00]                         |
| 2021-08-23 | [2021-08-23 16:22:41+00]                         |
| 2021-08-22 | [2021-08-22 14:01:10+00]                         |
| 2021-08-21 | [2021-08-21 19:45:18+00]                         |
| 2021-08-11 | [2021-08-11 16:08:58+00]                         |
| 2021-07-28 | [2021-07-28 17:39:14+00]                         |
| 2021-07-19 | [2021-07-19 17:26:24+00]                         |
| 2021-07-18 | [2021-07-18 15:04:47+00]                         |
| 2021-06-24 | [2021-06-24 19:53:33+00]                         |
| 2021-06-22 | [2021-06-22 19:04:24+00]                         |
+------------+--------------------------------------------------+

解决方案 #

你当前的查询同时按started和task_start两个字段分组，所以每个不同的task_start都会生成单独行，无法实现按日期聚合的效果。只需要保留按日期分组，再搭配对应数据库的数组聚合函数即可实现需求：

PostgreSQL #

使用ARRAY_AGG函数直接返回数组类型：

SELECT 
  date(task_start) AS started,
  ARRAY_AGG(task_start ORDER BY task_start) AS task_start
FROM tt_records
GROUP BY started
ORDER BY started DESC;

MySQL #

字符串格式输出

SELECT 
  DATE(task_start) AS started,
  CONCAT('[', GROUP_CONCAT(task_start ORDER BY task_start SEPARATOR ', '), ']') AS task_start
FROM tt_records
GROUP BY started
ORDER BY started DESC;

JSON数组格式输出（MySQL 5.7及以上版本支持）

SELECT 
  DATE(task_start) AS started,
  JSON_ARRAYAGG(task_start ORDER BY task_start) AS task_start
FROM tt_records
GROUP BY started
ORDER BY started DESC;

SQLite #

使用JSON_GROUP_ARRAY生成JSON数组：

SELECT 
  DATE(task_start) AS started,
  JSON_GROUP_ARRAY(task_start) AS task_start
FROM tt_records
GROUP BY started
ORDER BY started DESC;

内容的提问来源于stack exchange，提问作者MrOneTwo