Tengo algunas estaciones almacenadas en una colección stations simple:

+----+-----------+
| id | name      |
+----+-----------+
| 1  | Station A |
+----+-----------+
| 2  | Station B |
+----+-----------+
| 3  | Station C |
+----+-----------+
| 4  | Station D |
+----+-----------+

Y tengo algunos viajes almacenados en la colección rides:

+----+---------------+-------------+
| id | fromStationId | toStationId |
+----+---------------+-------------+
| 1  | 3             | 4           |
+----+---------------+-------------+
| 2  | 2             | 1           |
+----+---------------+-------------+
| 3  | 1             | 1           |
+----+---------------+-------------+
| 4  | 3             | 2           |
+----+---------------+-------------+

Me gustaría crear una lista de conteo de todos los viajes entre estaciones entre todos los pares posibles de nombres fromStation y nombres toStation con el resultado como este:

[
  {
    "fromStation": "Station A",
    "toStation": "Station A",
    "count": 1196
  },
  {
    "fromStation": "Station A",
    "toStation": "Station B",
    "count": 1
  },
  {
    "fromStation": "Station A",
    "toStation": "Station C",
    "count": 173
  },
]

And so on for all other combinations...

¿Cómo obtengo todas las combinaciones posibles de dos pares de nombres de estaciones y luego cuento el número de viajes entre ellos? Estoy usando la última versión de Postgres.

0
Daksh Shah 13 mar. 2021 a las 19:29

2 respuestas

La mejor respuesta

Primero agregue los viajes, luego resuelva los ID a nombres:

SELECT f.name AS from_station, t.name AS to_station, count
FROM  (
   SELECT from_station_id, to_station_id, count(*) AS count
   FROM   rides
   GROUP  BY 1, 2
   ) r
JOIN   stations f ON f.id = r.from_station_id
JOIN   stations t ON t.id = r.to_station_id
ORDER  BY 1, 2;  -- optional order

Por supuesto, eso solo produce combinaciones con atracciones reales. Si necesita incluir combinaciones sin paseos, necesita una combinación OUTER a un producto cartesiano de la tabla stations consigo mismo. Algo como:

-- include all combinations (even without rides)
SELECT from_station, to_station, COALESCE(count, 0) AS count
FROM  (
   SELECT from_station_id, to_station_id, count(*) AS count
   FROM   rides
   GROUP  BY 1, 2
   ) r
RIGHT  JOIN (
   SELECT f.id AS from_id, f.name AS from_station
        , t.id AS to_id  , t.name AS to_station
   FROM   stations f CROSS JOIN stations t
   ) s ON  s.from_id = r.from_station_id
      AND  s.to_id   = r.to_station_id
ORDER  BY 1, 2;  -- optional order

Nuevamente, es más barato agregar viajes antes unirse a las estaciones.

Para resumirlo como una matriz o registros JSON, simplemente:

SELECT json_agg(sub)
FROM  (
   -- query from above
   ) sub;

db <> fiddle aquí

1
Erwin Brandstetter 14 mar. 2021 a las 06:56

demos: db <> fiddle

SELECT 
    c.from_station,
    c.to_station,
    COUNT(*)
FROM stations s1
JOIN stations s2 ON s1.station <> s2.station                                     -- 1
JOIN connections c ON s1.station = c.from_station AND s2.station = c.to_station  -- 2
GROUP BY c.from_station, c.to_station                                            -- 3
  1. Cree una autounión en la mesa de la estación. La condición de unión <> asegura que no se unirá la misma estación. Todas las demás estaciones se unirán entre sí, lo que crea todas las combinaciones.
  2. Une este resultado en tu tabla de conexiones usando ambas columnas de estación de la autounión para los puntos from y to de las conexiones.
  3. Ahora puede agrupar por from y to y COUNT(*) agregar esto.

Si desea reconocer los casos en los que la estación from es igual a la estación to, puede cambiar

JOIN stations s2 ON s1.station <> s2.station

En un simple:

CROSS JOIN stations s2

Si desea obtener un objeto JSON como se muestra en la pregunta:

SELECT 
    json_agg(connection)                      -- 2
FROM (
    SELECT 
        json_build_object(                    -- 1
            'fromStation', c.from_station,
            'toStation', c.to_station,
            'count', COUNT(*)
        ) as connection
    FROM stations s1
    JOIN stations s2 ON s1.station <> s2.station
    JOIN connections c ON s1.station = c.from_station AND s2.station = c.to_station
    GROUP BY c.from_station, c.to_station
) s
  1. Cree su objeto JSON a partir de las columnas que creó anteriormente
  2. Agréguelos en una matriz JSON.
1
S-Man 13 mar. 2021 a las 16:41