-
Notifications
You must be signed in to change notification settings - Fork 0
/
q2-view.sql
74 lines (72 loc) · 4.56 KB
/
q2-view.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
select -- q2 original version
anon_1."cube.mangaid",
anon_1."cube.plate",
concat(anon_1."cube.plate", '-', anon_1."ifu.name") AS plateifu,
anon_1."ifu.name"
FROM (SELECT
mangadatadb.cube.mangaid AS "cube.mangaid",
mangadatadb.cube.plate AS "cube.plate",
concat(mangadatadb.cube.plate, '-', mangadatadb.ifudesign.name) AS "cube.plateifu",
mangadatadb.ifudesign.name AS "ifu.name",
mangadapdb.cleanspaxelprop5.emline_gflux_ha_6564 AS emline_gflux_ha_6564,
mangadapdb.cleanspaxelprop5.x AS "spaxelprop.x",
mangadapdb.cleanspaxelprop5.y AS "spaxelprop.y"
FROM mangadatadb.cube
JOIN mangadatadb.ifudesign ON mangadatadb.ifudesign.pk = mangadatadb.cube.ifudesign_pk
JOIN mangadapdb.file ON mangadatadb.cube.pk = mangadapdb.file.cube_pk
JOIN mangadapdb.cleanspaxelprop5 ON mangadapdb.file.pk = mangadapdb.cleanspaxelprop5.file_pk
JOIN mangadatadb.pipeline_info AS drpalias ON drpalias.pk = mangadatadb.cube.pipeline_info_pk
JOIN mangadatadb.pipeline_info AS dapalias ON dapalias.pk = mangadapdb.file.pipeline_info_pk
JOIN (SELECT
mangadapdb.cleanspaxelprop5.file_pk AS binfile,
count(mangadapdb.cleanspaxelprop5.pk) AS goodcount
FROM mangadapdb.cleanspaxelprop5
WHERE mangadapdb.cleanspaxelprop5.binid != -1
GROUP BY mangadapdb.cleanspaxelprop5.file_pk) AS bingood
ON bingood.binfile = mangadapdb.cleanspaxelprop5.file_pk
JOIN (SELECT
mangadapdb.cleanspaxelprop5.file_pk AS valfile,
count(mangadapdb.cleanspaxelprop5.pk) AS valcount
FROM mangadapdb.cleanspaxelprop5
WHERE mangadapdb.cleanspaxelprop5.emline_gflux_ha_6564 > 5.0
GROUP BY mangadapdb.cleanspaxelprop5.file_pk) AS goodhacount
ON goodhacount.valfile = mangadapdb.cleanspaxelprop5.file_pk
WHERE drpalias.pk = 25 AND dapalias.pk = 26 AND goodhacount.valcount >= 0.2 * bingood.goodcount) AS anon_1
GROUP BY anon_1."cube.mangaid", anon_1."cube.plate", concat(anon_1."cube.plate", '-', anon_1."ifu.name"),
anon_1."ifu.name";
select -- q2 suetest version
anon_1."cube.mangaid",
anon_1."cube.plate",
concat(anon_1."cube.plate", '-', anon_1."ifu.name") AS plateifu,
anon_1."ifu.name"
FROM (SELECT
suetest.cube.mangaid AS "cube.mangaid",
suetest.cube.plate AS "cube.plate",
concat(suetest.cube.plate, '-', mangadatadb.ifudesign.name) AS "cube.plateifu",
mangadatadb.ifudesign.name AS "ifu.name",
suetest.cleanspaxelprop5.emline_gflux_ha_6564 AS emline_gflux_ha_6564,
suetest.cleanspaxelprop5.x AS "spaxelprop.x",
suetest.cleanspaxelprop5.y AS "spaxelprop.y"
FROM suetest.cube
JOIN mangadatadb.ifudesign ON mangadatadb.ifudesign.pk = suetest.cube.ifudesign_pk
JOIN suetest.file ON suetest.cube.pk = suetest.file.cube_pk
JOIN suetest.cleanspaxelprop5 ON suetest.file.pk = suetest.cleanspaxelprop5.file_pk
JOIN mangadatadb.pipeline_info AS drpalias ON drpalias.pk = suetest.cube.pipeline_info_pk
JOIN mangadatadb.pipeline_info AS dapalias ON dapalias.pk = suetest.file.pipeline_info_pk
JOIN (SELECT
suetest.cleanspaxelprop5.file_pk AS binfile,
count(suetest.cleanspaxelprop5.pk) AS goodcount
FROM suetest.cleanspaxelprop5
WHERE suetest.cleanspaxelprop5.binid != -1
GROUP BY suetest.cleanspaxelprop5.file_pk) AS bingood
ON bingood.binfile = suetest.cleanspaxelprop5.file_pk
JOIN (SELECT
suetest.cleanspaxelprop5.file_pk AS valfile,
count(suetest.cleanspaxelprop5.pk) AS valcount
FROM suetest.cleanspaxelprop5
WHERE suetest.cleanspaxelprop5.emline_gflux_ha_6564 > 5.0
GROUP BY suetest.cleanspaxelprop5.file_pk) AS goodhacount
ON goodhacount.valfile = suetest.cleanspaxelprop5.file_pk
WHERE drpalias.pk = 25 AND dapalias.pk = 26 AND goodhacount.valcount >= 0.2 * bingood.goodcount) AS anon_1
GROUP BY anon_1."cube.mangaid", anon_1."cube.plate", concat(anon_1."cube.plate", '-', anon_1."ifu.name"),
anon_1."ifu.name";