obliczanki.sql
1.67 KB
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
-- nieadj to 20, 21, 22, 23
-- SGJP
select pos,
case when l_id_od is null then 'aff' else 'neg' end as nie,
count(*)
from leksemy l left outer join odsylacze o on (l.id=o.l_id_od and typods_id in (20, 21, 22, 23))
where slownik='SGJP'
group by pos, nie;
-- wspólne (złe! leksem się liczy tyle razy, ile razy jest w słowniku)
select pos,
case when l_id_od is null then 'aff' else 'neg' end as nie,
count(*)
from leksemy l join leksemy_w_slownikach s1 on (l.id=s1.l_id) join leksemy_w_slownikach s2 on (l.id=s2.l_id) left outer join odsylacze o on (l.id=o.l_id_od and typods_id in (20, 21, 22, 23))
where s1.slownik='SGJP' and s2.slownik='Morfologik'
group by pos, nie;
-- razem
select pos,
case when l_id_od is null then 'aff' else 'neg' end as nie,
count(*)
from leksemy l left outer join odsylacze o on (l.id=o.l_id_od and typods_id in (20, 21, 22, 23))
where slownik in ('SGJP','Morfologik')
group by pos, nie;
-- tylko Morfologik
select pos,
case when l_id_od is null then 'aff' else 'neg' end as nie,
count(*)
from leksemy l left outer join odsylacze o on (l.id=o.l_id_od and typods_id in (20, 21, 22, 23))
where slownik = 'Morfologik'
group by pos, nie;
-- tylko SGJP (wspólne bez not)
select pos,
case when l_id_od is null then 'aff' else 'neg' end as nie,
count(*)
from leksemy l left outer join odsylacze o on (l.id=o.l_id_od and typods_id in (20, 21, 22, 23))
where slownik='SGJP' and not exists (select id from leksemy_w_slownikach where l_id=l.id and slownik='Morfologik')
group by pos, nie;
-- Morfologik wg statusów
select pos, status, count(*) from leksemy l where slownik='Morfologik' group by pos, status order by pos, status;