· 5 years ago · Jul 21, 2020, 02:18 PM
1-- SELECT * FROM netSession
2-- WHERE startTime BETWEEN 1594771200000000 AND 1594771500000000
3-- UNION
4
5-- select count() FROM (
6SELECT hash, startTime, endTime, serverIP, serverPort, clientIP, clientPort FROM (
7 SELECT * FROM netSessionUpd WHERE startTime BETWEEN 1594771200000000 AND 1594771260000000 AND interfaceIndex = 'i7_testtraf'
8) AS upd
9LEFT JOIN (
10 SELECT hash, startTime, endTime, serverIP, serverPort, clientIP, clientPort FROM netSession WHERE startTime BETWEEN 1594771200000000 AND 1594771260000000 AND interfaceIndex = 'i7_testtraf'
11) AS ns
12ON upd.hash = ns.hash --AND upd.startTime = ns.startTime
13-- WHERE ns.hash = 0
14-- )
15LIMIT 50
16
17-- SELECT count() FROM netSession WHERE hash IN ()
18
19-- SELECT count() FROM netSessionUpd WHERE hash = 0
20
21
22CREATE TABLE IF NOT EXISTS ns (
23 when DateTime,
24 hash Int64,
25 table String,
26 bytes Int64
27) ENGINE = MergeTree
28PARTITION BY toYYYYMMDD(when)
29ORDER BY (table, when);
30
31CREATE TABLE IF NOT EXISTS upd (
32 when DateTime,
33 hash Int64,
34 table String,
35 bytes Int64
36) ENGINE = MergeTree
37PARTITION BY toYYYYMMDD(when)
38ORDER BY (table, when);
39
40INSERT INTO ns
41 SELECT
42 now() + number * 60 as when,
43 rand() % 100000000,
44 'ns',
45 rand() % 1000
46 FROM system.numbers
47 LIMIT 300;
48
49INSERT INTO upd
50 SELECT
51 now() + number * 60 as when,
52 rand() % 100000000,
53 'upd',
54 rand() % 1000
55 FROM system.numbers
56 LIMIT 250;
57
58INSERT INTO upd
59 SELECT
60 when,
61 hash,
62 'upd-inner',
63 rand() % 1000
64 FROM ns
65 ORDER BY rand()
66 LIMIT 100;
67
68SELECT count() FROM (
69 SELECT hash, table, bytes FROM upd
70 INNER JOIN ns ON upd.hash = ns.hash
71)
72
73-- RIGHT JOIN
74
75SELECT ns.hash, ns.table, ns.bytes, upd.hash, upd.table, upd.bytes
76FROM upd
77RIGHT JOIN ns ON upd.hash = ns.hash
78;;
79SELECT hash, table, bytes
80FROM upd
81RIGHT JOIN ns ON upd.hash = ns.hash
82
83-- RIGHT EXCLUSIVE JOIN
84-- select count() from (
85SELECT ns.hash, ns.table, ns.bytes
86FROM upd
87RIGHT JOIN ns ON upd.hash = ns.hash
88WHERE upd.hash = 0
89-- )
90
91-- LEFT EXCLUSIVE JOIN
92
93SELECT ns.hash, ns.table, ns.bytes, upd.hash, upd.table, upd.bytes
94FROM upd
95LEFT JOIN ns ON upd.hash = ns.hash
96where ns.hash = 0
97;;
98SELECT count() FROM (
99 SELECT ns.hash, ns.table, ns.bytes
100 FROM upd
101 LEFT JOIN ns ON upd.hash = ns.hash
102 WHERE ns.hash = 0
103)
104
105
106INSERT INTO upd
107 SELECT
108 when,
109 hash,
110 'upd-inner-dub',
111 rand() % 1000
112 FROM upd
113 WHERE table = 'upd-inner'
114 ORDER BY rand()
115 LIMIT 10;
116
117SELECT upd.hash, upd.table, upd.bytes --ns.hash, ns.table, ns.bytes,
118FROM upd
119LEFT JOIN ns ON upd.hash = ns.hash
120WHERE ns.hash = 0
121;;
122SELECT ns.hash, ns.table, ns.bytes
123FROM upd
124RIGHT JOIN ns ON upd.hash = ns.hash
125WHERE upd.hash = 0
126;;
127SELECT hash, table, bytes
128FROM upd
129INNER JOIN ns ON upd.hash = ns.hash
130
131
132
133SELECT ns.hash, ns.table, ns.bytes, upd.hash, upd.table, upd.bytes
134-- SELECT hash, groupArray(table), groupArray(bytes)
135FROM ns
136LEFT JOIN upd ON ns.hash = upd.hash
137-- WHERE upd.hash = 0
138-- group by hash
139order by hash
140
141-- в массивах НЕТ данных из табл ns
142select hash, groupArray(table), groupArray(bytes) from (
143SELECT hash, table, bytes
144FROM upd
145LEFT JOIN ns ON upd.hash = ns.hash
146) group by hash
147
148-- Так как при left join табл ns заменит записи из upd своими создавая дубликаты
149-- нужно из группировать в массивы, а дальше разбивать на строки с помощью ARRAY JOIN
150SELECT hash, fullTable as table, fullBytes as bytes FROM (
151SELECT hash, table, bytes, arrayPushBack(partTable, ns.table) as fullTable, arrayPushBack(partBytes, ns.bytes) as fullBytes
152FROM ns
153LEFT JOIN (
154 SELECT hash, groupArray(table) as partTable, groupArray(bytes) as partBytes FROM upd GROUP BY hash
155) AS upd
156ON ns.hash = upd.hash
157) ARRAY JOIN
158fullTable, fullBytes
159-- WHERE upd.hash = 0
160-- group by hash
161order by hash