有一個 SQL 陳述句，它從 xtu.dash() 所有 id_units 中獲取 de freq 和 lat 和 log （緯度和經度）
SELECT
du.id_unit , du.freq_int, pos.lat, pos.lon
FROM
xtu.dash(now()::timestamp - interval '1 h', now()::timestamp) du
INNER JOIN
flt_lst_geo() pos ON pos.sdes = du.plate
WHERE
du.freq_int IS NOT NULL
AND du.freq_int > 45
GROUP BY
du.freq_int, pos.lat, pos.lon
我需要的是獲得平均經度和緯度以及 id_units 的總數。
SELECT
COUNT (tm.du.id_unit), AVG(tm.pos.lat), AVG (tm.pos.lon)
FROM
(SELECT
du.id_unit , du.freq_int, pos.lat, pos.lon
FROM
xtu.dash($__timeFrom()::timestamp, $__timeTo()::timestamp) du
INNER JOIN
flt_lst_geo() pos ON pos.sdes = du.plate
WHERE
du.freq_int IS NOT NULL
AND du.freq_int > 45
GROUP BY
du.freq_int, pos.lat, pos.lon) AS tm
但這種方法不起作用
uj5u.com熱心網友回復：
with main as (
SELECT
du.id_unit as id_unit,
du.freq_int as freq_int,
pos.lat as lat,
pos.lon as lon
FROM xtu.dash($__timeFrom()::timestamp, $__timeTo()::timestamp) du
inner join flt_lst_geo() pos on pos.sdes=du.plate
and du.freq_int is not null and du.freq_int > 45
)
select
count(id_unit) as total_id,
avg(freq) as avg_freq,
avg(lat) as avg_lat
from main
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/529781.html
標籤：sql
上一篇：SQL：在丑陋的資料中展平多行