query.sql

--select distinct DiffPrevFrameBenchmark.diffType from DiffPrevFrameBenchmark;

SELECT ROUND(AVG(compressionRatio),5)
FROM DiffPrevFrameBenchmark
WHERE diffType = 'PrevFrameDiff' AND filename = '16Bit-ZStack.czi' AND level = 6 AND compression = 'BZIP2';

SELECT * FROM
(
    SELECT ((16.0-ROUND(AVG(BitsUsed),5)) * width * height) / (8.0 * 1000.0) as [artemia]
    FROM DiffPrevFrameBenchmark
    WHERE filename = 'AxioZoom_Artemia_AT-1Ch-Z_sect.czi' and diffType = 'PrevFrameDiff_VBC' and
        compression = 'BZIP2' AND level = 6
) a,
(       
    SELECT ((16.0-ROUND(AVG(BitsUsed),5)) * width * height) / (8.0*1000.0) as [artemia_flash]
    FROM DiffPrevFrameBenchmark
    WHERE filename = '40x075_Artemia-Flash-AT-1Ch-Z-sect.czi' and diffType = 'PrevFrameDiff_VBC' and
        compression = 'BZIP2' AND level = 6
) af,
(
    SELECT ((16.0-ROUND(AVG(BitsUsed),5)) * width * height) / (8.0*1000.0) as [llc]
    FROM DiffPrevFrameBenchmark
    WHERE filename = 'LLC-PK1_TubX-emerald_H2B-mCherry-2chZ(SD).czi' AND diffType = 'PrevFrameDiff_VBC' and
        compression = 'BZIP2' AND level = 6
) llc,
(
    SELECT ((16.0-ROUND(AVG(BitsUsed),5)) * width * height) / (8.0*1000.0) as [16BitZStack]
    FROM DiffPrevFrameBenchmark
    WHERE filename = '16Bit-ZStack.czi' and diffType = 'PrevFrameDiff_VBC' and
        compression = 'BZIP2' AND level = 6
) b16;



/*
-- DIFFERENCE BENCHMARK
SELECT * FROM
(
    SELECT ((16.0-ROUND(AVG(BitsUsed),5)) * width * height) / (8.0 * 1000.0) as [artemia]
    FROM DiffPrevFrameBenchmark
    WHERE filename = 'AxioZoom_Artemia_AT-1Ch-Z_sect.czi' and diffType = 'PrevFrameDiff_VBC' and
        compression = 'BZIP2' AND level = 6
) a,
(       
    SELECT ((16.0-ROUND(AVG(BitsUsed),5)) * width * height) / (8.0*1000.0) as [artemia_flash]
    FROM DiffPrevFrameBenchmark
    WHERE filename = '40x075_Artemia-Flash-AT-1Ch-Z-sect.czi' and diffType = 'PrevFrameDiff_VBC' and
        compression = 'BZIP2' AND level = 6
) af,
(
    SELECT ((16.0-ROUND(AVG(BitsUsed),5)) * width * height) / (8.0*1000.0) as [llc]
    FROM DiffPrevFrameBenchmark
    WHERE filename = 'LLC-PK1_TubX-emerald_H2B-mCherry-2chZ(SD).czi' AND diffType = 'PrevFrameDiff_VBC' and
        compression = 'BZIP2' AND level = 6
) llc,
(
    SELECT ((16.0-ROUND(AVG(BitsUsed),5)) * width * height) / (8.0*1000.0) as [16BitZStack]
    FROM DiffPrevFrameBenchmark
    WHERE filename = '16Bit-ZStack.czi' and diffType = 'PrevFrameDiff_VBC' and
        compression = 'BZIP2' AND level = 6
) b16;
*/

/*
SELECT * FROM
(
    SELECT ROUND((vbc.cr - fbc.cr),5) as [artemia_div] FROM
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = 'AxioZoom_Artemia_AT-1Ch-Z_sect.czi' and diffType = 'PrevFrameDiff' and compression = 'BZIP2' AND level = 6
    ) fbc,
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = 'AxioZoom_Artemia_AT-1Ch-Z_sect.czi' and diffType = 'PrevFrameDiff_VBC' and compression = 'BZIP2' AND level = 6
    ) vbc
) artemia_diff,
(
    SELECT ROUND((vbc.cr - fbc.cr),5) as [artemia_flash_diff] FROM
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = '40x075_Artemia-Flash-AT-1Ch-Z-sect.czi' and diffType = 'PrevFrameDiff' and compression = 'BZIP2' AND level = 6
    ) fbc,
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = '40x075_Artemia-Flash-AT-1Ch-Z-sect.czi' and diffType = 'PrevFrameDiff_VBC' and compression = 'BZIP2' AND level = 6
    ) vbc
) artemia_flash_diff,
(
    SELECT ROUND((vbc.cr - fbc.cr),5) as [llc_diff] FROM
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = 'LLC-PK1_TubX-emerald_H2B-mCherry-2chZ(SD).czi' and diffType = 'PrevFrameDiff' and compression = 'BZIP2' AND level = 6
    ) fbc,
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = 'LLC-PK1_TubX-emerald_H2B-mCherry-2chZ(SD).czi' and diffType = 'PrevFrameDiff_VBC' and compression = 'BZIP2' AND level = 6
    ) vbc
) llc_diff,
(
    SELECT ROUND((vbc.cr - fbc.cr),5) as [bit16_diff] FROM
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = '16Bit-ZStack.czi' and diffType = 'PrevFrameDiff' and compression = 'BZIP2' AND level = 6
    ) fbc,
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = '16Bit-ZStack.czi' and diffType = 'PrevFrameDiff_VBC' and compression = 'BZIP2' AND level = 6
    ) vbc
) bit16_diff;
*/

-- COMPRESSION BENCHMARK
/*
SELECT * FROM
(
SELECT ROUND(AVG(compressionRatioZ) - AVG(compressionRatio),5)
FROM CompressionBenchmark
WHERE filename = 'AxioZoom_Artemia_AT-1Ch-Z_sect.czi' and 
    compression = 'BZIP2' AND level = 6
) a,
(       
SELECT ROUND(AVG(compressionRatioZ) - AVG(compressionRatio),5)
FROM CompressionBenchmark
WHERE filename = '40x075_Artemia-Flash-AT-1Ch-Z-sect.czi' and 
    compression = 'BZIP2' AND level = 6
) af,
(
SELECT ROUND(AVG(compressionRatioZ) - AVG(compressionRatio),5)
FROM CompressionBenchmark
WHERE filename = 'LLC-PK1_TubX-emerald_H2B-mCherry-2chZ(SD).czi' AND
    compression = 'BZIP2' AND level = 6
) llc,
(
SELECT ROUND(AVG(compressionRatioZ) - AVG(compressionRatio),5)
FROM CompressionBenchmark
WHERE filename = '16Bit-ZStack.czi' and
    compression = 'BZIP2' AND level = 6
) b16;
*/