sql - 从同一查询 postgres 中的另一个表中选择计算值的最佳方法？
问题描述
嗨，我有 postgres 数据库和四个表
vehicles -> trips
vehicles -> component_values -> component_types
车辆：
╔════╦══════════════════════════╦════════════════════════╦════════════════╦═════════╗
║ id ║ slug ║ name ║ manufacturer ║ model ║
╠════╬══════════════════════════╬════════════════════════╬════════════════╬═════════╣
║ 1 ║ manufacturer-x-model-3 ║ Manufacturer X Model 3 ║ Manufacturer X ║ Model 3 ║
║ 2 ║ manufacturer-x-model-1 ║ Manufacturer X Model 1 ║ Manufacturer X ║ Model 1 ║
║ 3 ║ manufacturer-x-model-1-1 ║ Manufacturer X Model 1 ║ Manufacturer X ║ Model 1 ║
╚════╩══════════════════════════╩════════════════════════╩════════════════╩═════════╝
行程：
╔═════╦════════════╦═════════════╦═════════════╦═════════════════╗
║ id ║ vehicle_id ║ name ║ mileage ║ recorded_at ║
╠═════╬════════════╬═════════════╬═════════════╬═════════════════╣
║ 1 ║ 1 ║ 10386735 ║ 386734.997 ║ 2/25/2014 13:56 ║
║ 2 ║ 1 ║ 11771530.14 ║ 771530.14 ║ 3/1/2014 19:41 ║
║ 3 ║ 1 ║ 121112028.4 ║ 1112028.39 ║ 3/5/2014 3:23 ║
║ 4 ║ 1 ║ 131406814.9 ║ 1406814.892 ║ 3/8/2014 20:56 ║
║ 5 ║ 1 ║ 141933528.7 ║ 1933528.711 ║ 3/13/2014 0:19 ║
║ 6 ║ 1 ║ 152256488.6 ║ 2256488.579 ║ 3/16/2014 21:15 ║
╚═════╩════════════╩═════════════╩═════════════╩═════════════════╝
组件值：
╔════╦═══════════════════╦═════════╦════════════╦════════════╦═════════════╦═════════════╗
║ id ║ component_type_id ║ trip_id ║ vehicle_id ║ mileage ║ damage ║ damage_eff ║
╠════╬═══════════════════╬═════════╬════════════╬════════════╬═════════════╬═════════════╣
║ 1 ║ 1 ║ 1 ║ 1 ║ 386734.997 ║ 0.002260565 ║ 0.002225831 ║
║ 2 ║ 2 ║ 1 ║ 1 ║ 386734.997 ║ 0.002260306 ║ 0.002238006 ║
║ 3 ║ 3 ║ 1 ║ 1 ║ 386734.997 ║ 0.002261288 ║ 0.002266295 ║
║ 4 ║ 4 ║ 1 ║ 1 ║ 386734.997 ║ 0.002054489 ║ 0.002060029 ║
║ 5 ║ 5 ║ 1 ║ 1 ║ 386734.997 ║ 0.002052669 ║ 0.002107272 ║
║ 6 ║ 6 ║ 1 ║ 1 ║ 386734.997 ║ NULL ║ NULL ║
║ 7 ║ 7 ║ 1 ║ 1 ║ 386734.997 ║ NULL ║ NULL ║
║ 8 ║ 1 ║ 2 ║ 1 ║ 771530.14 ║ 0.004792952 ║ 0.0048514 ║
║ 9 ║ 2 ║ 2 ║ 1 ║ 771530.14 ║ 0.004792404 ║ 0.004710451 ║
║ 10 ║ 3 ║ 2 ║ 1 ║ 771530.14 ║ 0.004794486 ║ 0.004805461 ║
╚════╩═══════════════════╩═════════╩════════════╩════════════╩═════════════╩═════════════╝
组件类型：
╔════╦═════════════════════════════════════╦════════════════╦══════════════════════╗
║ id ║ slug ║ manufacturer ║ name ║
╠════╬═════════════════════════════════════╬════════════════╬══════════════════════╣
║ 6 ║ manufacturer-d-battery ║ Manufacturer D ║ Battery ║
║ 2 ║ manufacturer-b-differential-1 ║ Manufacturer B ║ Differential 1 ║
║ 3 ║ manufacturer-c-driveshaft-1 ║ Manufacturer C ║ Driveshaft 1 ║
║ 5 ║ manufacturer-c-gearbox-output-shaft ║ Manufacturer C ║ Gearbox output shaft ║
║ 1 ║ manufacturer-a-motor-1 ║ Manufacturer A ║ Motor 1 ║
║ 4 ║ manufacturer-c-gearbox-input-shaft ║ Manufacturer C ║ Gearbox input shaft ║
║ 7 ║ usage-profile ║ ║ Usage profile ║
╚════╩═════════════════════════════════════╩════════════════╩══════════════════════╝
我正在尝试获取具有最新行程日期和里程的车辆，以及每辆车的高度和最低损坏部件
所以结果会是这样的：
╔════════════╦══════════════════╦══════════════════════════╦═════════════════════════════════╦════════════════════════════════╦════════════════════════════════╦═══════════════════════════════╗
║ vehicle_id ║ latest_trip_date ║ latest_trip_date_mileage ║ heights_damaged_component_value ║ heights_damaged_component_name ║ lowest_damaged_component_value ║ lowest_damaged_component_name ║
╠════════════╬══════════════════╬══════════════════════════╬═════════════════════════════════╬════════════════════════════════╬════════════════════════════════╬═══════════════════════════════╣
║ 1 ║ 4/19/2014 3:27 ║ 4844305.912 ║ 0.029372972 ║ Gearbox input shaft ║ 0.002052669 ║ Gearbox output shaft ║
║ 2 ║ 5/19/2014 16:13 ║ 5567945.164 ║ 0.029405924 ║ Driveshaft 1 ║ 0.001864137 ║ Gearbox output shaft ║
║ 3 ║ 4/28/2014 12:55 ║ 5286175.763 ║ 0.030745029 ║ Driveshaft 1 ║ 0.001957685 ║ Differential 1 ║
║ 4 ║ 2/25/2014 3:32 ║ 5398006.007 ║ 0.030495792 ║ Driveshaft 1 ║ 0.001814434 ║ Differential 1 ║
║ 5 ║ 4/25/2014 9:51 ║ 5179558.475 ║ 0.032060074 ║ Gearbox input shaft ║ 0.001936431 ║ Differential 1 ║
║ 6 ║ 5/9/2014 7:43 ║ 5234355.804 ║ 0.030576454 ║ Gearbox input shaft ║ 0.002254191 ║ Gearbox output shaft ║
║ 7 ║ 6/21/2014 18:09 ║ 5705722.416 ║ 0.029957374 ║ Driveshaft 1 ║ 0.001653441 ║ Gearbox output shaft ║
║ 8 ║ 4/23/2014 5:25 ║ 5590470.028 ║ 0.031900163 ║ Driveshaft 1 ║ 0.002083733 ║ Gearbox output shaft ║
║ 9 ║ 3/28/2014 20:37 ║ 5598159.883 ║ 0.031208918 ║ Driveshaft 1 ║ 0.00162805 ║ Differential 1 ║
║ 10 ║ 5/24/2014 19:27 ║ 5020795.001 ║ 0.02962505 ║ Gearbox input shaft ║ 0.001729646 ║ Differential 1 ║
╚════════════╩══════════════════╩══════════════════════════╩═════════════════════════════════╩════════════════════════════════╩════════════════════════════════╩═══════════════════════════════╝
我已经尝试过这个查询
select
vehicles.id as vehicle_id,
latest_trips.recorded_at as latest_trip_date,
latest_trips.mileage as latest_trip_date_mileage,
heights_damaged_components.damage as heights_damaged_component_value,
heights_damaged_components.name as heights_damaged_component_name,
lowest_damaged_components.damage as lowest_damaged_component_value,
lowest_damaged_components.name as lowest_damaged_component_name
from vehicles
left join (
SELECT t.id, t.vehicle_id, t.mileage, t.recorded_at
FROM public.trips t
inner JOIN (SELECT vehicle_id, MAX(recorded_at) as latest_trip_date FROM public.trips GROUP BY vehicle_id)
tm ON t.vehicle_id = tm.vehicle_id AND t.recorded_at = tm.latest_trip_date
)
as latest_trips on latest_trips.vehicle_id = vehicles.id
left join (
select ct.name, hd.component_type_id, hd.vehicle_id, hd.damage
from public.component_values as hd
INNER JOIN (
SELECT vehicle_id,
MAX(damage) as heights_damaged_component
FROM public.component_values
GROUP BY vehicle_id
)
hdm ON hd.vehicle_id = hdm.vehicle_id AND hd.damage = hdm.heights_damaged_component
join public.component_types as ct on ct.id = hd.component_type_id
)
as heights_damaged_components on heights_damaged_components.vehicle_id = vehicles.id
left join (
select ct.name, ld.component_type_id, ld.vehicle_id, ld.damage
from public.component_values as ld
INNER JOIN (
SELECT vehicle_id, MIN(damage) as lowest_damaged_component
FROM public.component_values
GROUP BY vehicle_id
)
ldm ON ld.vehicle_id = ldm.vehicle_id AND ld.damage = ldm.lowest_damaged_component
join public.component_types as ct on ct.id = ld.component_type_id
) as lowest_damaged_components on lowest_damaged_components.vehicle_id = vehicles.id
但我有 10000 辆汽车、大行程和 component_values，这个查询在 3 到 6 秒内给了我结果，他们是不是以更好的性能和时间来做到这一点？
我可以在我的情况下使用GENERATED 列吗？如何使用？
请任何帮助，非常感谢提前。
解决方案
推荐阅读
- java - 在二维数组中切换数组大小需要不同的内存量
- c# - 从数据库中搜索记录并在文本框和下拉列表中显示值
- c++ - 如何允许访问向量是类成员的向量元素？
- google-bigquery - BigQuery 问题：shwoing：不兼容的表分区规范
- css - 如何溢出模态中的列？
- c# - 借助 C# (asp-net) 对表格进行简单排序
- kotlin - 如何在 Kotlin 中为多个按钮使用一个事件处理程序（我已经看过带有 switch 语句的 java）
- flask - 为什么 statsmodels 包不能在 Flask 上运行，但在 ipython 上运行？
- terraform - 在模块内的模块之间传递 Terraform 变量？
- visual-studio - Azure DevOps 服务器 - CI - 数据库单元测试