1.headers ON
2/*
3
4Select the core to be used as reference. Only last day of measurements is used.
5
6*/
7CREATE TEMP VIEW if not exists refCore AS select *
8  from Unary
9  where coreid=5 AND DATE BETWEEN datetime('now','localtime','-23 hours') AND datetime('now', 'localtime');
10  ;
11
12/*
13
14Select the cores to be benchmarked compared with the reference. Only last day of measurements is used.
15
16*/
17CREATE TEMP VIEW if not exists  otherCores AS select *
18  from Unary
19  where coreid != 5 AND DATE BETWEEN datetime('now','localtime','-23 hours') AND datetime('now', 'localtime');
20  ;
21
22/*
23
24Using regression database, compute the ratio using max cycles
25and max degree regression coefficient.
26
27Change name of columns for result
28
29USING(ID,categoryid,NAME) : would have to be extended with any parameter defining the regression
30formula.
31For instamce, for FFT, if ifft is an external parameter then ifft flag should be
32here.
33
34Here we assume ref and others are generated with same settings and currentConfig.csv
35If not the case, the parameters which may be different (like LOOPUNROLL, OPTIMIZED...)
36should be added here so that we join the ref and other cores on common benchmark definition.
37
38We should not compute ratio between configuration of benchmarks which are
39not matching.
40
41If we want to compute ratio between CORE AND PLATFORM then the view above should
42be using CORE AND PLATFORM to filter and define the references.
43
44*/
45select otherCores.ID as ID,
46 CATEGORY.category as CATEGORY,
47 otherCores.NAME as NAME,
48 (1.0*refCore.MAX / otherCores.MAX) as MAXRATIO,
49 (1.0*refCore.MAXREGCOEF / otherCores.MAXREGCOEF) as REGRESSIONRATIO,
50 PLATFORM.platform as PLATFORM,
51 CORE.core as CORE,
52 COMPILERKIND.compiler as COMPILER,
53 COMPILER.version as COMPILERVERSION,
54 TYPE.type as TYPE,
55 otherCores.DATE as DATE
56 from otherCores
57 INNER JOIN refCore ON refCore.ID = otherCores.ID AND refCore.categoryid = otherCores.categoryid AND refCore.NAME = otherCores.NAME
58 INNER JOIN CATEGORY ON CATEGORY.categoryid = otherCores.categoryid
59 INNER JOIN PLATFORM ON PLATFORM.platformid = otherCores.platformid
60 INNER JOIN CORE ON CORE.coreid = otherCores.coreid
61 INNER JOIN COMPILER ON COMPILER.compilerid = otherCores.compilerid
62 INNER JOIN COMPILERKIND ON COMPILERKIND.compilerkindid = COMPILER.compilerkindid
63 INNER JOIN TYPE ON TYPE.typeid = otherCores.typeid;