Changes between Version 4 and Version 5 of ComplexReports


Ignore:
Timestamp:
01/27/26 23:48:30 (12 days ago)
Author:
233051
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ComplexReports

    v4 v5  
    11= Complex DB Reports (SQL, Stored Procedures, Relational Algebra) =
    22
    3 [wiki:TopPerformingComponents Top performing components report] \\
     3[wiki:TopPerformingComponents Top performing components] \\
    44[wiki:UserLeaderboard User Leaderboard] \\
     5[wiki:PricetoPerformance Price-to-Performance Efficiency] \\
    56[wiki:UserRegistration User registration] \\
    67[wiki:UserRegistration User registration] \\
    78[wiki:UserRegistration User registration] \\
    8 [wiki:UserRegistration User registration] \\
    9 
    10 === Price-to-Performance Efficiency Analysis ===
    11 
    12 Report on which builds have the most computing power per dollar spent using a "price-to-performance index" calculated with the following formula:
    13 * (CPU Cores * CPU Base Clock) + (GPU VRAM * 100)
    14 
    15 {{{
    16 WITH cpu_per_build AS (
    17     SELECT
    18         b.id AS build_id,
    19         c.name AS cpu_model,
    20         cpu.cores,
    21         cpu.base_clock
    22     FROM build b
    23     JOIN build_component bc ON b.id = bc.build_id
    24     JOIN components c ON bc.component_id = c.id
    25     JOIN cpu ON c.id = cpu.component_id
    26     WHERE c.type = 'CPU'
    27 ),
    28 gpu_per_build AS (
    29     SELECT
    30         b.id AS build_id,
    31         c.name AS gpu_model,
    32         gpu.vram
    33     FROM build b
    34     JOIN build_component bc ON b.id = bc.build_id
    35     JOIN components c ON bc.component_id = c.id
    36     JOIN gpu ON c.id = gpu.component_id
    37     WHERE c.type = 'GPU'
    38 )
    39 
    40 SELECT
    41     b.name AS build_name,
    42     cpu.cpu_model,
    43     gpu.gpu_model,
    44     b.total_price,
    45     (cpu.cores * cpu.base_clock + gpu.vram * 100) AS performance_score,
    46     ROUND(
    47         (cpu.cores * cpu.base_clock + gpu.vram * 100),
    48         4
    49     ) AS price_to_performance_index
    50 FROM build b
    51 JOIN cpu_per_build cpu ON b.id = cpu.build_id
    52 JOIN gpu_per_build gpu ON b.id = gpu.build_id
    53 WHERE b.total_price > 0
    54 ORDER BY price_to_performance_index DESC
    55 LIMIT 20;
    56 }}}
    57 
    58 {{{
    59 cpu_per_build =
    60 π_{b.id → build_id,
    61    c.name → cpu_model,
    62    cpu.cores,
    63    cpu.base_clock
    64 } (
    65   σ_{c.type = 'CPU'} (
    66     (((build b
    67       ⋈_{b.id = bc.build_id} build_component bc)
    68       ⋈_{bc.component_id = c.id} components c)
    69       ⋈_{c.id = cpu.component_id} cpu)
    70   )
    71 )
    72 
    73 gpu_per_build =
    74 π_{b.id → build_id,
    75    c.name → gpu_model,
    76    gpu.vram
    77 } (
    78   σ_{c.type = 'GPU'} (
    79     (((build b
    80       ⋈_{b.id = bc.build_id} build_component bc)
    81       ⋈_{bc.component_id = c.id} components c)
    82       ⋈_{c.id = gpu.component_id} gpu)
    83   )
    84 )
    85 
    86 λ_20 (
    87   τ_{price_to_performance_index DESC} (
    88     π_{b.name → build_name,
    89       cpu.cpu_model,
    90       gpu.gpu_model,
    91       b.total_price,
    92       (cpu.cores * cpu.base_clock + gpu.vram * 100) → performance_score,
    93       ROUND((cpu.cores * cpu.base_clock + gpu.vram * 100), 4) → price_to_performance_index
    94     } (
    95       σ_{b.total_price > 0} (
    96         ((build b
    97           ⋈_{b.id = cpu.build_id} cpu_per_build cpu)
    98           ⋈_{b.id = gpu.build_id} gpu_per_build gpu)
    99       )
    100     )
    101   )
    102 )
    103 }}}