sql - 在数组列上使用内部联接时重复值

Question

我有一个appointment 表，其中两个字段pets 和services 是数组。我正在尝试创建一个查询，列出 starts_at 和 ends_at 时间以及与该约会相关的服务和宠物的名称。

我遇到的问题是，如果 pets 或 services 包含超过 1 个值，我会在输出中遇到重复值。

这是我目前的查询:

SELECT
    appointment.starts_at,
    appointment.ends_at,
    string_agg(service.name, ', ') AS service_names,
    string_agg(pet.name, ', ') AS pet_names
FROM get_appointments(20, CURRENT_DATE, CAST((CURRENT_DATE + INTERVAL '1 day' * 4) AS DATE), null, null) AS appointment
INNER JOIN pet on pet.id = ANY(appointment.pets)
INNER JOIN service on service.id = ANY(appointment.services)
GROUP BY
    appointment.starts_at,
    appointment.ends_at
ORDER BY
    appointment.starts_at ASC;

这是示例输出:

+---------------------+---------------------+--------------------------------+-------------------+
|      starts_at      |       ends_at       |         service_names          |     pet_names     |
+---------------------+---------------------+--------------------------------+-------------------+
| 2017-05-03 07:00:00 | 2017-05-03 07:30:00 | 30 Minute Walk, 30 Minute Walk | Gregor, The Hound |
+---------------------+---------------------+--------------------------------+-------------------+

这是期望的输出:

+---------------------+---------------------+--------------------------------+-------------------+
|      starts_at      |       ends_at       |         service_names          |     pet_names     |
+---------------------+---------------------+--------------------------------+-------------------+
| 2017-05-03 07:00:00 | 2017-05-03 07:30:00 | 30 Minute Walk          | Gregor, The Hound |
+---------------------+---------------------+--------------------------------+-------------------+

我正在运行 Postgres 9.5。

谢谢!

Answer 1

SELECT
    appointment.starts_at,
    appointment.ends_at,
    (select string_agg(service.name, ', ') from service where service.id = ANY(appointment.services)) AS service_names,
    (select string_agg(pet.name, ', ') from pet where pet.id = ANY(appointment.pets)) AS pet_names
FROM get_appointments(20, CURRENT_DATE, CAST((CURRENT_DATE + INTERVAL '1 day' * 4) AS DATE), null, null) AS appointment
GROUP BY
    appointment.starts_at,
    appointment.ends_at
ORDER BY
    appointment.starts_at ASC;

同样使用lateral joins :

SELECT
    appointment.starts_at,
    appointment.ends_at,
    service_names,
    pet_names
FROM get_appointments(20, CURRENT_DATE, CAST((CURRENT_DATE + INTERVAL '1 day' * 4) AS DATE), null, null) AS appointment
  cross join lateral (
    select string_agg(service.name, ', ')  AS service_names
    from service
    where service.id = ANY(appointment.services)) as srv
  cross join lateral (
    select string_agg(pet.name, ', ')  AS pet_names
    from pet
    where pet.id = ANY(appointment.pets)) as pet
GROUP BY
    appointment.starts_at,
    appointment.ends_at
ORDER BY
    appointment.starts_at ASC;