with "norm" as not materialized (
select
"id" as "id",
(("yearStart" - "a"."yearStart.min")::numeric / "a"."yearStart.d") as "yearStart",
(("cylindersVolumeLiters" - "a"."cylindersVolumeLiters.min")::numeric / "a"."cylindersVolumeLiters.d") as "cylindersVolumeLiters",
(("enginePower" - "a"."enginePower.min")::numeric / "a"."enginePower.d") as "enginePower",
(("cylindersNumber" - "a"."cylindersNumber.min")::numeric / "a"."cylindersNumber.d") as "cylindersNumber",
(("valvesNumber" - "a"."valvesNumber.min")::numeric / "a"."valvesNumber.d") as "valvesNumber"
from
normative_reference.erp_car
left join lateral (
select
min("yearStart") as "yearStart.min",
(max("yearStart")-min("yearStart"))::numeric as "yearStart.d",
min("cylindersVolumeLiters") as "cylindersVolumeLiters.min",
(max("cylindersVolumeLiters")-min("cylindersVolumeLiters"))::numeric as "cylindersVolumeLiters.d",
min("enginePower") as "enginePower.min",
(max("enginePower")-min("enginePower"))::numeric as "enginePower.d",
min("cylindersNumber") as "cylindersNumber.min",
(max("cylindersNumber")-min("cylindersNumber"))::numeric as "cylindersNumber.d",
min("valvesNumber") as "valvesNumber.min",
(max("valvesNumber")-min("valvesNumber"))::numeric as "valvesNumber.d"
from
normative_reference.erp_car ) as "a" on
true
)
insert into normative_reference.erp_car_matrix_similarity
select
"car"."brandId" as "brandId",
"car"."id" as "carId",
"r"."id" as "relatedCarId",
cos_similarity_vector(
array[1, 1, 1, 1, 1, 1, "norm.car"."yearStart", "norm.car"."cylindersVolumeLiters", "norm.car"."enginePower", "norm.car"."cylindersNumber", "norm.car"."valvesNumber"],
array[
case when r."generationId" = "car"."generationId" then 1 else 0 end,
case when r."modelId" = "car"."modelId" then 1 else 0 end,
case when r."brandId" = "car"."brandId" then 1 else 0 end,
case when r."fuelType" = "car"."fuelType" then 1 else 0 end,
case when r."engineCode" = "car"."engineCode" then 1 else 0 end,
case when r."workshopId" = "car"."workshopId" then 1 else 0 end,
"norm.relatedCar"."yearStart",
"norm.relatedCar"."cylindersVolumeLiters",
"norm.relatedCar"."enginePower",
"norm.relatedCar"."cylindersNumber",
"norm.relatedCar"."valvesNumber"
]
) as "similarity"
from normative_reference.erp_car as "car"
cross join normative_reference.erp_car as "r"
left join "norm" as "norm.car" on "norm.car"."id" = "car"."id"
left join "norm" as "norm.relatedCar" on "norm.relatedCar"."id" = "r"."id"