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
75
76
77
78
79
80
81
82
83
84
|
-- Dauer der längsten Gespräche
SELECT a.send_time - b.send_time AS "Dauer", date_trunc('day',a.send_time)
FROM ( SELECT MAX(send_time) AS send_time, from_ident
FROM omegle_messages
GROUP BY from_ident ) a
JOIN ( SELECT MIN(send_time) AS send_time, from_ident
FROM omegle_messages
GROUP BY from_ident ) b
ON a.from_ident = b.from_ident
WHERE a.send_time - b.send_time > interval '1 hour'
ORDER BY 1 DESC
LIMIT 10;
-- Poster mit den meisten abgesendeten Nachrichten
SELECT COUNT(*), from_ident, date_trunc('hour',MIN(send_time))
FROM omegle_messages
GROUP BY from_ident
HAVING COUNT(*) > 200
ORDER BY 1 DESC;
-- Anteil von GEsprächen in denen ein Begriff vorkommt
SELECT to_char(a.tag,'DD.MM FMDay'), (b.count/CAST(a.count as real))*100 AS Anteil
FROM (
SELECT date_trunc('day', om1.send_time) AS Tag, COUNT(*)
FROM omegle_messages om1
GROUP BY date_trunc('day', om1.send_time)
) a JOIN (
SELECT date_trunc('day', om1.send_time) AS Tag, COUNT(*)
FROM omegle_messages om1
WHERE from_ident IN ( SELECT DISTINCT from_ident
FROM omegle_messages
WHERE message ILIKE '%hell%')
GROUP BY date_trunc('day', om1.send_time)
) b ON a.Tag = b.Tag;
-- Gesprächsfetzen - DEFEKT
SELECT om1.message AS msgA,
om2.message AS msgB
FROM omegle_messages om1,
omegle_messages om2
WHERE om1.from_ident = om2.to_ident
AND om2.send_time > om1.send_time
AND om2.send_time = ( SELECT MAX(send_time) from omegle_messages om3 WHERE om3.send_time > om1.send_time AND om1.from_ident = om3.to_ident );
AND LENGTH(om1.message) < 100 AND LENGTH(om2.message) < 100;
LIMIT 10
;
-- ALle möglichen Partner
SELECT to_char(MIN(om1.send_time),'DD') || '_' || om1.from_ident
FROM omegle_messages om1
GROUP BY om1.from_ident
LIMIT 4
;
SELECT DISTINCT om1.to_ident, om2.from_ident
FROM omegle_messages om1 JOIN (
SELECT DISTINCT from_ident
FROM omegle_messages
) om2 ON om1.from_ident = om2.from_ident
LIMIT 10;
SELECT initiator,MAX(send_time)
FROM (
SELECT SUBSTR(from_ident||to_ident,0,7) AS "initiator", MAX(send_time) AS "send_time"
FROM omegle_messages om1
WHERE from_ident IS NOT NULL and to_ident IS NOT NULL
GROUP BY from_ident || to_ident
UNION
SELECT SUBSTR(to_ident||from_ident,7,12) AS "initiator", MAX(send_time) AS "send_time"
FROM omegle_messages om1
WHERE from_ident IS NOT NULL and to_ident IS NOT NULL
GROUP BY to_ident||from_ident ) o1
GROUP BY "initiator"
ORDER BY 2
LIMIT 10;
|
