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
| WITH client_t1_orders AS (
SELECT clientid, count(*) AS total_orders
FROM if
WHERE clientid = '137849'
GROUP BY clientid
), client_t2_orders AS (
SELECT clientid, count(*) AS total_orders
FROM if
WHERE clientid = '174243'
GROUP BY clientid
),order_compare AS(
SELECT t1.tradingday,t1.clientid as clientid_t1,t2.clientid as clientid_t2,
EXTRACT(EPOCH FROM (t1.inserttime - t2.inserttime )),
CASE WHEN EXTRACT(EPOCH FROM (t1.inserttime - t2.inserttime )) < 60
AND EXTRACT(EPOCH FROM (t1.inserttime - t2.inserttime )) > -60
THEN 1 ELSE 0
END AS same_orders
FROM if t1
INNER JOIN if t2
on t1.tradingday = t2.tradingday
and t1.instrumentid = t2.instrumentid
and t1.direction = t2.direction
and t1.clientid = 137849
and t2.clientid = 174243
)
SELECT t1.total_orders,round(t2.same_orders*1.0/t1.total_orders,4) as rate
FROM(
SELECT min(total_orders) AS total_orders
FROM
(SELECT total_orders
FROM client_t1_orders
UNION ALL
SELECT total_orders
FROM client_t1_orders
)tt
)t1
CROSS JOIN (SELECT clientid_t1 ,clientid_t2,sum(same_orders) AS same_orders
FROM order_compare
GROUP BY clientid_t1 ,clientid_t2
)t2
|
