如何构建 SQL 以按日期获取分组数据

Question

>我在Postgres数据库中有这样的数据

| id |  name  |    start_date       |      end_date       |
1    Event1  2018-09-14 14:22:00     2018-09-15 14:22:00
2    Event2  2018-09-15 14:22:00     2018-09-15 15:22:00

我需要 SQL group_by日期返回我的响应，如果事件持续时间(end_date、start_date(花了 2 天，我需要在两天内返回他两次数组，这一切都应该按日期排序。所以响应应该看起来像这样。

{
"2018-09-14": [
{
"id": 1,
"name": "Event1",
"start_date": "2018-09-14 14:22:00",
"end_date": "2018-09-15 14:22:00",
}],
"2018-09-15": [{
"id": 1,
"name": "Event1",
"start_date": "2018-09-14 14:22:00",
"end_date": "2018-09-15 14:22:00",
},
{
"id": 2,
"name": "Event2",
"start_date": "2018-09-15 14:22:00",
"end_date": "2018-09-15 15:22:00",
}]
}

你能帮我处理这个 SQL 吗？

Answer 1

demo： db<>fiddle

SELECT 
jsonb_object_agg(dates, data_array)
FROM (
SELECT
dates,
jsonb_agg(data) as data_array
FROM (
SELECT DISTINCT
unnest(ARRAY[start_date::date, end_date::date]) as dates,
row_to_json(events)::jsonb as data
FROM
events
)s
GROUP BY dates
) s

1. 使用row_to_json将表转换为 json 对象。
2. 使用ARRAY[]将两个日期聚合到一个数组中
3. unnest()扩展每个日期的数据。

到目前为止的结果：

dates        data
2018-09-14   {"id": 1, "name": "Event1", "end_date": "2018-09-15 14:22:00", "start_date": "2018-09-14 14:22:00"}
2018-09-15   {"id": 1, "name": "Event1", "end_date": "2018-09-15 14:22:00", "start_date": "2018-09-14 14:22:00"}
2018-09-15   {"id": 2, "name": "Event2", "end_date": "2018-09-15 15:22:00", "start_date": "2018-09-15 14:22:00"}
2018-09-15   {"id": 2, "name": "Event2", "end_date": "2018-09-15 15:22:00", "start_date": "2018-09-15 14:22:00"}

4. DISTINCT消除了start_date == end_date中的所有绑定元素。
5. 按日期分组，将 JSON 元素聚合成一个 JSON 数组 (jsonb_agg(
6. 毕竟，将表分组为 JSON 元素 (jsonb_object_agg( 与key == date和value == json array

Answer 2

如果您只需要行，则只需要以下步骤：

1. 使用ARRAY[]将两个日期聚合到一个数组中
2. unnest()扩展每个日期的数据。
3. DISTINCT消除了start_date == end_date的所有捆绑元素。

查询：

SELECT DISTINCT
unnest(ARRAY[start_date::date, end_date::date]) as dates,
*
FROM
events

结果：

dates        id   name     start_date            end_date
2018-09-14   1    Event1   2018-09-14 14:22:00   2018-09-15 14:22:00
2018-09-15   1    Event1   2018-09-14 14:22:00   2018-09-15 14:22:00
2018-09-15   2    Event2   2018-09-15 14:22:00   2018-09-15 15:22:00

演示：数据库<>小提琴