Changes between Version 2 and Version 3 of StorageConfigurationOptimizationScore

Timestamp:

01/29/26 16:51:31 (10 days ago)

Author:

233194

Comment:

--

StorageConfigurationOptimizationScore

@@ -102 +102 @@
 {{{
 storage_configs = 
-  π_{b.id ⇒ build_id, b.total_price,
-     ssd.brand ⇒ ssd_brand, ssd.capacity ⇒ ssd_capacity, ssd.price ⇒ ssd_price,
-     hdd.capacity ⇒ hdd_capacity, hdd.price ⇒ hdd_price,
+  π_{b.id ⇒ build_id, 
+     b.total_price,
+     ssd_comp.brand ⇒ ssd_brand, 
+     ssd_storage.capacity ⇒ ssd_capacity, 
+     ssd_comp.price ⇒ ssd_price,
+     hdd_storage.capacity ⇒ hdd_capacity, 
+     hdd_comp.price ⇒ hdd_price,
      CASE 
-       WHEN hdd.capacity IS NULL THEN 'SSD-Only'
-       WHEN ssd.capacity < 512 THEN 'SSD-Boot-HDD-Storage'
+       WHEN hdd_storage.capacity IS NULL THEN 'SSD-Only'
+       WHEN ssd_storage.capacity < 512 THEN 'SSD-Boot-HDD-Storage'
        ELSE 'SSD-Primary-HDD-Archive'
      END ⇒ config_type
   } (
-    σ_{ssd_comp.type = 'SSD' AND (hdd_comp.type = 'HDD' OR hdd_comp.type IS NULL)} (
+    σ_{ssd_comp.type = 'storage' AND
+       b.is_approved = TRUE AND 
+       b.created_at ≥ CURRENT_DATE - INTERVAL '1 year' AND
+       (ssd_storage.type ILIKE '%nvme%' OR 
+        ssd_storage.type ILIKE '%ssd%' OR 
+        ssd_storage.type ILIKE '%m.2%' OR 
+        ssd_storage.form_factor ILIKE '%m.2%') AND
+       (hdd_storage.component_id IS NULL OR
+        hdd_storage.type ILIKE '%hdd%' OR 
+        hdd_storage.form_factor ILIKE '%3.5%')
+    } (
       build b
       ⋈_{b.id = bc_ssd.build_id} build_component bc_ssd
       ⋈_{bc_ssd.component_id = ssd_comp.id} components ssd_comp
-      ⟕_{b.id = bc_hdd.build_id AND bc_hdd.component_id = hdd_comp.id AND hdd_comp.type = 'HDD'} 
-        (build_component bc_hdd ⋈ components hdd_comp)
+      ⋈_{ssd_storage.component_id = ssd_comp.id} storage ssd_storage
+      ⟕_{b.id = bc_hdd.build_id} build_component bc_hdd
+      ⟕_{bc_hdd.component_id = hdd_comp.id} components hdd_comp
+      ⟕_{hdd_storage.component_id = hdd_comp.id} storage hdd_storage
     )
   )
 
 config_performance = 
-  γ_{config_type, ssd_brand;
-     COUNT(build_id) ⇒ builds_count,
-     AVG(ssd_price + COALESCE(hdd_price, 0)) ⇒ avg_storage_cost,
-     AVG(ssd_capacity + COALESCE(hdd_capacity, 0)) ⇒ avg_total_capacity,
+  γ_{sc.config_type, sc.ssd_brand;
+     COUNT(sc.build_id) ⇒ builds_count,
+     AVG(sc.ssd_price + COALESCE(sc.hdd_price, 0)) ⇒ avg_storage_cost,
+     AVG(sc.ssd_capacity + COALESCE(sc.hdd_capacity, 0)) ⇒ avg_total_capacity,
      AVG(rb.value) ⇒ avg_build_rating,
-     AVG((ssd_price + COALESCE(hdd_price, 0)) / total_price) ⇒ storage_cost_ratio
+     AVG((sc.ssd_price + COALESCE(sc.hdd_price, 0)) / NULLIF(sc.total_price, 0)) ⇒ storage_cost_ratio
   } (
-    σ_{b.is_approved = TRUE AND b.created_at ≥ CURRENT_DATE - INTERVAL '1 year'} (
-      storage_configs sc ⋈_{sc.build_id = b.id} build b
-      ⋈_{sc.build_id = rb.build_id} rating_build rb
-    )
+    storage_configs sc
+    ⟕_{sc.build_id = rb.build_id} rating_build rb
   )
+  WHERE builds_count ≥ 2
 
 λ_15 (
   τ_{optimization_score DESC, builds_count DESC} (
-    σ_{builds_count ≥ 5} (
-      π_{cp.config_type,
-         cp.ssd_brand,
-         cp.builds_count,
-         ROUND(cp.avg_storage_cost, 2) ⇒ avg_storage_cost,
-         ROUND(cp.avg_total_capacity, 0) ⇒ avg_total_capacity_gb,
-         ROUND(cp.avg_build_rating, 2) ⇒ avg_build_rating,
-         ROUND(cp.storage_cost_ratio * 100, 1) ⇒ storage_cost_pct,
-         ROUND(
-           (cp.avg_build_rating / 5.0 * 40) +
-           (cp.avg_total_capacity / cp.avg_storage_cost * 0.5) +
-           ((1 - cp.storage_cost_ratio) * 30) +
-           (LOG(cp.builds_count) * 5),
-           2
-         ) ⇒ optimization_score
-      } (config_performance cp)
-    )
+    π_{cp.config_type,
+       cp.ssd_brand,
+       cp.builds_count,
+       ROUND(cp.avg_storage_cost, 2) ⇒ avg_storage_cost,
+       ROUND(cp.avg_total_capacity, 0) ⇒ avg_total_capacity_gb,
+       ROUND(COALESCE(cp.avg_build_rating, 0), 2) ⇒ avg_build_rating,
+       ROUND(COALESCE(cp.storage_cost_ratio, 0) * 100, 1) ⇒ storage_cost_pct,
+       ROUND(
+         (COALESCE(cp.avg_build_rating, 0) / 5.0 * 40) +
+         (cp.avg_total_capacity / NULLIF(cp.avg_storage_cost, 0) * 0.5) +
+         ((1 - COALESCE(cp.storage_cost_ratio, 0)) * 30) +
+         (LN(cp.builds_count + 1) * 5),
+         2
+       ) ⇒ optimization_score
+    } (config_performance cp)
   )
 )