Dada a seguinte estrutura de banco de dados https://drawsql.app/sensor_network/diagrams/db , gostaria de obter todos os sensores_data de um determinado local, enquanto agrupava a resposta usando station.location, sensor_data.time.
Tenho a seguinte consulta:
select
station.location_name,
sensor_data.time,
sensor.type,
sensor_data.value,
sensor.id
from station
inner join
(sensor inner join sensor_data on sensor.id = sensor_data.sensor_id)
on station.sensor_id = sensor.id
where station.location = ?
que me dá as linhas que eu quero, no entanto, gostaria de formatar a resposta de uma forma que todas as linhas que compartilham local e hora estejam na mesma linha. uma saída de exemplo é
| localização | Tempo | tipo | valor | identificação |
|---|---|---|---|---|
| cidade1 | 01-01-2022 08:00:00 | temperatura | 290 | 1 |
| cidade1 | 01-01-2022 09:00:00 | temperatura | 292 | 1 |
| cidade1 | 01-01-2022 08:00:00 | ph | 7 | 2 |
| cidade1 | 01-01-2022 09:00:00 | ph | 8 | 2 |
que eu gostaria de formatar como (ou semelhante a)
[
{"location": "city1", "time": "2022-01-01 08:00:00", "temperature": 290, "ph": 7},
{"location": "city1", "time": "2022-01-01 09:00:00", "temperature": 292, "ph": 8},
]
or
[
{"location": "city1", "time": "2022-01-01 08:00:00", "sensors": [{"id": 1, "type": "temperature", "value": 290}, {"id": 2, "type": "ph", "value": 7}]},
{"location": "city1", "time": "2022-01-01 09:00:00", "sensors": [{"id": 1, "type": "temperature", "value": 292}, {"id": 2, "type": "ph", "value": 8}]}
]
Atualmente, estou fazendo essa formatação em javascript usando funções de array, mas isso se mostrou muito lento. Eu tentei usar group bya consulta, mas para ser justo, estou lutando para entender como usar isso quando você não deseja usar métodos como contagem etc.
Estou usando o nodejs e seu pacote mysql, o banco de dados é mysql:8 e as tabelas estão rodando com o motor innodb
Você pode conseguir isso com subconsultas.
SELECT
st.location,
sd.time,
(
SELECT sd2.value
FROM sensor_data sd2
INNER JOIN sensor s2
ON s2.id = sd2.sensor_id
WHERE sd2.time = sd.time
AND s2.type = "temperature"
) AS temperature,
(
SELECT sd2.value
FROM sensor_data sd2
INNER JOIN sensor s2
ON s2.id = sd2.sensor_id
WHERE sd2.time = sd.time
AND s2.type = "ph"
) AS ph
FROM station st
INNER JOIN sensor s
ON st.sensors = s.id
INNER JOIN sensor_data sd
ON s.id = sd.sensor_id
WHERE st.location = ?
GROUP BY sd.time
Obviamente, isso funcionará apenas se você conhecer a lista de tipos (temperatura, ph) com antecedência e, portanto, poderá escrever subconsultas separadas para cada um deles.
Se você não quiser criar uma subconsulta separada para cada tipo, poderá concatenar os valores em uma única coluna de subconsulta.
SELECT
st.location,
sd.time,
(
SELECT
GROUP_CONCAT(
CONCAT(s2.type, ':', sd2.value)
SEPARATOR ','
)
FROM sensor_data sd2
INNER JOIN sensor s2
ON s2.id = sd2.sensor_id
WHERE sd2.time = sd.time
GROUP BY sd2.time
) AS sensors
FROM station st
INNER JOIN sensor s
ON st.sensors = s.id
INNER JOIN sensor_data sd
ON s.id = sd.sensor_id
WHERE st.location = ?
GROUP BY sd.time;
Este artigo é coletado da Internet.
Se houver alguma infração, entre em [email protected] Delete.
deixe-me dizer algumas palavras