sql - 如何构建 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

我需要返回响应 group_by 日期的 SQL，如果事件持续时间（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 吗？

标签： sqljsonpostgresql

Answer 1

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

1. 将两个日期聚合到一个数组中ARRAY[]
2. unnest()扩展每个日期的数据。
3. DISTINCT消除所有绑定元素 where 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

演示：db<>小提琴