-
Notifications
You must be signed in to change notification settings - Fork 1
/
update app metric & add unique payment.sql
33 lines (30 loc) · 2.67 KB
/
update app metric & add unique payment.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
BEGIN
SET @app_id = NEW.app_id;
SET @payment_type = NEW.payment_type;
SET @epoch_time = NEW.epoch_time;
SET @epoch_time_day = @epoch_time - MOD(@epoch_time,86400);
SET @sender = NEW.sender;
SET @recipient = NEW.recipient;
SET @amount = NEW.amount;
SET @unique_payment_count = (SELECT COUNT(*) FROM unique_payment WHERE app_id=@app_id AND epoch_time=@epoch_time_day AND sender=@sender AND recipient=@recipient);
SET @spend_is_unique = IF(@payment_type = 3 AND @unique_payment_count = 0, 1, 0);
SET @spend_counter = IF(@payment_type = 3, 1, 0);
SET @spend_amount = IF(@payment_type = 3, @amount, 0);
SET @earn_is_unique = IF(@payment_type = 2 AND @unique_payment_count = 0, 1, 0);
SET @earn_counter = IF(@payment_type = 2, 1, 0);
SET @earn_amount = IF(@payment_type = 2, @amount, 0);
SET @p2p_is_unique = IF(@payment_type = 1 AND @unique_payment_count = 0, 1, 0);
SET @p2p_counter = IF(@payment_type = 1, 1, 0);
SET @p2p_amount = IF(@payment_type = 1, @amount, 0);
INSERT INTO app_metric (`epoch_time`, `app_id`, `spend_unique_count`, `spend_count`, `spend_volume`, `earn_unique_count`, `earn_count`, `earn_volume`, `p2p_unique_count`, `p2p_count`, `p2p_volume`)
VALUES (@epoch_time_day, @app_id, @spend_is_unique, @spend_counter, @spend_amount, @earn_is_unique, @earn_counter, @earn_amount, @p2p_is_unique, @p2p_counter, @p2p_amount)
ON DUPLICATE KEY UPDATE spend_unique_count=spend_unique_count+@spend_is_unique, spend_count=spend_count+@spend_counter, spend_volume=spend_volume+@spend_amount,
earn_unique_count=earn_unique_count+@earn_is_unique, earn_count=earn_count+@earn_counter, earn_volume=earn_volume+@earn_amount,
p2p_unique_count=p2p_unique_count+@p2p_is_unique, p2p_count=p2p_count+@p2p_counter, p2p_volume=p2p_volume+@p2p_amount;
INSERT INTO app_metric (`epoch_time`, `app_id`, `spend_unique_count`, `spend_count`, `spend_volume`, `earn_unique_count`, `earn_count`, `earn_volume`, `p2p_unique_count`, `p2p_count`, `p2p_volume`)
VALUES (@epoch_time_day, 'aggregate', @spend_is_unique, @spend_counter, @spend_amount, @earn_is_unique, @earn_counter, @earn_amount, @p2p_is_unique, @p2p_counter, @p2p_amount)
ON DUPLICATE KEY UPDATE spend_unique_count=spend_unique_count+@spend_is_unique, spend_count=spend_count+@spend_counter, spend_volume=spend_volume+@spend_amount,
earn_unique_count=earn_unique_count+@earn_is_unique, earn_count=earn_count+@earn_counter, earn_volume=earn_volume+@earn_amount,
p2p_unique_count=p2p_unique_count+@p2p_is_unique, p2p_count=p2p_count+@p2p_counter, p2p_volume=p2p_volume+@p2p_amount;
INSERT IGNORE INTO unique_payment (`app_id`, `epoch_time`, `sender`, `recipient`) VALUES (@app_id, @epoch_time_day, @sender, @recipient);
END