inputs-to-mem.sql

SELECT
    subq.*,
    (subq.total_size_mb / subq.input_count)::INT8 AS average_size_mb,
    (subq.mem_used_mb / subq.total_size_mb)::INT8 AS mem_inputs_ratio,
    (subq.mem_used_mb / greatest(subq.total_size_mb / subq.input_count, 1))::INT8 AS mem_avg_input_ratio
FROM
    (
        SELECT
            subq2.*
        FROM
            (
                SELECT
                    j.id,
                    j.tool_id,
                    j.state,
                    greatest(
                        (
                            SELECT
                                (sum(d.total_size) / 1024 / 1024)::INT8
                            FROM
                                job_to_input_dataset AS jtid
                                JOIN history_dataset_association AS hda ON jtid.dataset_id = hda.id
                                JOIN dataset AS d ON hda.dataset_id = d.id
                            WHERE
                                jtid.job_id = j.id
                        ),
                        1
                    )
                        AS total_size_mb,
                    greatest((SELECT count(jtid.id) FROM job_to_input_dataset AS jtid WHERE jtid.job_id = j.id), 1)
                        AS input_count,
                    (jmn.metric_value / 1024 / 1024)::INT8 AS mem_used_mb
                FROM
                    job AS j JOIN job_metric_numeric AS jmn ON j.id = jmn.job_id
                WHERE
                    j.id >= 33510006
                    AND j.tool_id = 'toolshed.g2.bx.psu.edu/repos/iuc/unicycler/unicycler/0.4.8.0'
                    AND jmn.plugin = 'cgroup'
                    AND jmn.metric_name = 'memory.memsw.max_usage_in_bytes'
                ORDER BY
                    mem_used_mb DESC
            )
                AS subq2
        WHERE
            subq2.total_size_mb > 0 AND subq2.input_count > 0
    )
        AS subq;