282 lines
10 KiB
SQL
282 lines
10 KiB
SQL
-- Rebuild recharge amount with the current eligible recharge definition:
|
|
-- app/third-party payment + shipping-agent sold coins at 92000 coins/USD + salary exchange.
|
|
|
|
SET @recharge_types = 'HUAWEI,GOOGLE,APPLE,PAYER_MAX,MIFA_PAY,AIRWALLEX,PAYNICORN,STRIPE,PAY_PAL,CLIPSPAY,SHIPPING_AGENT,SALARY_EXCHANGE';
|
|
SET @activity_ids = '2006671533912581569,2006671533988298666,1235571533988298753';
|
|
|
|
CREATE TABLE IF NOT EXISTS user_monthly_recharge_v2_bak_20260507 LIKE user_monthly_recharge_v2;
|
|
INSERT IGNORE INTO user_monthly_recharge_v2_bak_20260507
|
|
SELECT *
|
|
FROM user_monthly_recharge_v2
|
|
WHERE FIND_IN_SET(type, @recharge_types);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_total_recharge_bak_20260507 LIKE user_total_recharge;
|
|
INSERT IGNORE INTO user_total_recharge_bak_20260507
|
|
SELECT *
|
|
FROM user_total_recharge
|
|
WHERE FIND_IN_SET(type, @recharge_types);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_monthly_recharge_user_bak_20260507 LIKE user_monthly_recharge_user;
|
|
INSERT IGNORE INTO user_monthly_recharge_user_bak_20260507
|
|
SELECT *
|
|
FROM user_monthly_recharge_user;
|
|
|
|
CREATE TABLE IF NOT EXISTS user_total_recharge_user_bak_20260507 LIKE user_total_recharge_user;
|
|
INSERT IGNORE INTO user_total_recharge_user_bak_20260507
|
|
SELECT *
|
|
FROM user_total_recharge_user;
|
|
|
|
CREATE TABLE IF NOT EXISTS user_activity_recharge_bak_20260507 LIKE user_activity_recharge;
|
|
INSERT IGNORE INTO user_activity_recharge_bak_20260507
|
|
SELECT *
|
|
FROM user_activity_recharge
|
|
WHERE FIND_IN_SET(activity_id, @activity_ids);
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_recharge_monthly_20260507;
|
|
CREATE TEMPORARY TABLE tmp_recharge_monthly_20260507 (
|
|
user_id BIGINT NOT NULL,
|
|
recharge_date INT NOT NULL,
|
|
recharge_type VARCHAR(50) NOT NULL,
|
|
amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
|
|
PRIMARY KEY (user_id, recharge_date, recharge_type)
|
|
) ENGINE=InnoDB;
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_recharge_daily_20260507;
|
|
CREATE TEMPORARY TABLE tmp_recharge_daily_20260507 (
|
|
user_id BIGINT NOT NULL,
|
|
recharge_date DATE NOT NULL,
|
|
recharge_type VARCHAR(50) NOT NULL,
|
|
amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
|
|
PRIMARY KEY (user_id, recharge_date, recharge_type)
|
|
) ENGINE=InnoDB;
|
|
|
|
INSERT INTO tmp_recharge_monthly_20260507 (user_id, recharge_date, recharge_type, amount)
|
|
SELECT
|
|
t.user_id,
|
|
DATE_FORMAT(t.create_time, '%Y%m') + 0 AS recharge_date,
|
|
t.pay_platform AS recharge_type,
|
|
ROUND(SUM(IFNULL(t.unit_price, 0)), 2) AS amount
|
|
FROM order_purchase_history t
|
|
WHERE t.evn = 'PROD'
|
|
AND t.is_trial_period = 0
|
|
AND t.status = 'COMPLETE'
|
|
AND t.pay_platform IN ('HUAWEI', 'GOOGLE', 'APPLE', 'PAYER_MAX', 'AIRWALLEX', 'PAYNICORN', 'STRIPE', 'PAY_PAL', 'CLIPSPAY')
|
|
GROUP BY t.user_id, recharge_date, recharge_type
|
|
ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount);
|
|
|
|
INSERT INTO tmp_recharge_daily_20260507 (user_id, recharge_date, recharge_type, amount)
|
|
SELECT
|
|
t.user_id,
|
|
DATE(DATE_SUB(t.create_time, INTERVAL 5 HOUR)) AS recharge_date,
|
|
t.pay_platform AS recharge_type,
|
|
ROUND(SUM(IFNULL(t.unit_price, 0)), 2) AS amount
|
|
FROM order_purchase_history t
|
|
WHERE t.evn = 'PROD'
|
|
AND t.is_trial_period = 0
|
|
AND t.status = 'COMPLETE'
|
|
AND t.pay_platform IN ('HUAWEI', 'GOOGLE', 'APPLE', 'PAYER_MAX', 'AIRWALLEX', 'PAYNICORN', 'STRIPE', 'PAY_PAL', 'CLIPSPAY')
|
|
GROUP BY t.user_id, recharge_date, recharge_type
|
|
ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount);
|
|
|
|
INSERT INTO tmp_recharge_monthly_20260507 (user_id, recharge_date, recharge_type, amount)
|
|
SELECT
|
|
t.user_id,
|
|
DATE_FORMAT(IFNULL(t.update_time, t.create_time), '%Y%m') + 0 AS recharge_date,
|
|
'MIFA_PAY' AS recharge_type,
|
|
ROUND(SUM(IFNULL(t.compute_usd_amount, 0)), 2) AS amount
|
|
FROM order_user_purchase_pay t
|
|
WHERE t.evn = 'PROD'
|
|
AND t.factory_code = 'MIFA_PAY'
|
|
AND t.pay_status = 'SUCCESSFUL'
|
|
AND t.receipt_type = 'PAYMENT'
|
|
AND t.refund_status = 'NONE'
|
|
GROUP BY t.user_id, recharge_date
|
|
ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount);
|
|
|
|
INSERT INTO tmp_recharge_daily_20260507 (user_id, recharge_date, recharge_type, amount)
|
|
SELECT
|
|
t.user_id,
|
|
DATE(DATE_SUB(IFNULL(t.update_time, t.create_time), INTERVAL 5 HOUR)) AS recharge_date,
|
|
'MIFA_PAY' AS recharge_type,
|
|
ROUND(SUM(IFNULL(t.compute_usd_amount, 0)), 2) AS amount
|
|
FROM order_user_purchase_pay t
|
|
WHERE t.evn = 'PROD'
|
|
AND t.factory_code = 'MIFA_PAY'
|
|
AND t.pay_status = 'SUCCESSFUL'
|
|
AND t.receipt_type = 'PAYMENT'
|
|
AND t.refund_status = 'NONE'
|
|
GROUP BY t.user_id, recharge_date
|
|
ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount);
|
|
|
|
INSERT INTO tmp_recharge_monthly_20260507 (user_id, recharge_date, recharge_type, amount)
|
|
SELECT
|
|
t.accept_user_id AS user_id,
|
|
DATE_FORMAT(t.create_time, '%Y%m') + 0 AS recharge_date,
|
|
'SHIPPING_AGENT' AS recharge_type,
|
|
SUM(TRUNCATE(IFNULL(t.quantity, 0) / 92000, 2)) AS amount
|
|
FROM likei_wallet.user_freight_balance_running_water t
|
|
WHERE t.origin = 'SHIPMENT'
|
|
AND t.type = 1
|
|
AND t.remark = '货运代理出货'
|
|
AND t.accept_user_id IS NOT NULL
|
|
AND IFNULL(t.quantity, 0) > 0
|
|
GROUP BY t.accept_user_id, recharge_date
|
|
ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount);
|
|
|
|
INSERT INTO tmp_recharge_daily_20260507 (user_id, recharge_date, recharge_type, amount)
|
|
SELECT
|
|
t.accept_user_id AS user_id,
|
|
DATE(DATE_SUB(t.create_time, INTERVAL 5 HOUR)) AS recharge_date,
|
|
'SHIPPING_AGENT' AS recharge_type,
|
|
SUM(TRUNCATE(IFNULL(t.quantity, 0) / 92000, 2)) AS amount
|
|
FROM likei_wallet.user_freight_balance_running_water t
|
|
WHERE t.origin = 'SHIPMENT'
|
|
AND t.type = 1
|
|
AND t.remark = '货运代理出货'
|
|
AND t.accept_user_id IS NOT NULL
|
|
AND IFNULL(t.quantity, 0) > 0
|
|
GROUP BY t.accept_user_id, recharge_date
|
|
ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount);
|
|
|
|
INSERT INTO tmp_recharge_monthly_20260507 (user_id, recharge_date, recharge_type, amount)
|
|
SELECT
|
|
t.user_id,
|
|
DATE_FORMAT(t.create_time, '%Y%m') + 0 AS recharge_date,
|
|
'SALARY_EXCHANGE' AS recharge_type,
|
|
ROUND(SUM(IFNULL(t.amount, 0)), 2) AS amount
|
|
FROM likei_wallet.user_salary_account_running_water t
|
|
WHERE t.type = 1
|
|
AND t.salary_event = 'SALARY_EXCHANGE'
|
|
GROUP BY t.user_id, recharge_date
|
|
ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount);
|
|
|
|
INSERT INTO tmp_recharge_daily_20260507 (user_id, recharge_date, recharge_type, amount)
|
|
SELECT
|
|
t.user_id,
|
|
DATE(DATE_SUB(t.create_time, INTERVAL 5 HOUR)) AS recharge_date,
|
|
'SALARY_EXCHANGE' AS recharge_type,
|
|
ROUND(SUM(IFNULL(t.amount, 0)), 2) AS amount
|
|
FROM likei_wallet.user_salary_account_running_water t
|
|
WHERE t.type = 1
|
|
AND t.salary_event = 'SALARY_EXCHANGE'
|
|
GROUP BY t.user_id, recharge_date
|
|
ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount);
|
|
|
|
INSERT INTO tmp_recharge_monthly_20260507 (user_id, recharge_date, recharge_type, amount)
|
|
SELECT
|
|
t.user_id,
|
|
DATE_FORMAT(t.create_time, '%Y%m') + 0 AS recharge_date,
|
|
'SALARY_EXCHANGE' AS recharge_type,
|
|
ROUND(SUM(IFNULL(t.amount, 0)), 2) AS amount
|
|
FROM likei_wallet.user_salary_diamond_running_water t
|
|
WHERE t.type = 1
|
|
AND t.salary_event IN ('SALARY_EXCHANGE_GOLD_COINS', 'EXCHANGE_GOLD_COINS', 'BILL_EXCHANGE_GOLD_COINS')
|
|
GROUP BY t.user_id, recharge_date
|
|
ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount);
|
|
|
|
INSERT INTO tmp_recharge_daily_20260507 (user_id, recharge_date, recharge_type, amount)
|
|
SELECT
|
|
t.user_id,
|
|
DATE(DATE_SUB(t.create_time, INTERVAL 5 HOUR)) AS recharge_date,
|
|
'SALARY_EXCHANGE' AS recharge_type,
|
|
ROUND(SUM(IFNULL(t.amount, 0)), 2) AS amount
|
|
FROM likei_wallet.user_salary_diamond_running_water t
|
|
WHERE t.type = 1
|
|
AND t.salary_event IN ('SALARY_EXCHANGE_GOLD_COINS', 'EXCHANGE_GOLD_COINS', 'BILL_EXCHANGE_GOLD_COINS')
|
|
GROUP BY t.user_id, recharge_date
|
|
ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount);
|
|
|
|
DELETE FROM user_monthly_recharge_v2
|
|
WHERE FIND_IN_SET(type, @recharge_types);
|
|
|
|
SET @rn := 0;
|
|
INSERT INTO user_monthly_recharge_v2 (id, user_id, type, amount, recharge_date, create_time, update_time)
|
|
SELECT
|
|
7701000000000000000 + (@rn := @rn + 1) AS id,
|
|
user_id,
|
|
recharge_type,
|
|
amount,
|
|
recharge_date,
|
|
NOW(),
|
|
NOW()
|
|
FROM tmp_recharge_monthly_20260507
|
|
WHERE amount > 0
|
|
ORDER BY user_id, recharge_date, recharge_type;
|
|
|
|
DELETE FROM user_total_recharge
|
|
WHERE FIND_IN_SET(type, @recharge_types);
|
|
|
|
SET @rn := 0;
|
|
INSERT INTO user_total_recharge (id, user_id, type, amount, create_time, update_time)
|
|
SELECT
|
|
7702000000000000000 + (@rn := @rn + 1) AS id,
|
|
user_id,
|
|
recharge_type,
|
|
ROUND(SUM(amount), 2) AS amount,
|
|
NOW(),
|
|
NOW()
|
|
FROM tmp_recharge_monthly_20260507
|
|
WHERE amount > 0
|
|
GROUP BY user_id, recharge_type
|
|
ORDER BY user_id, recharge_type;
|
|
|
|
DELETE FROM user_monthly_recharge_user;
|
|
|
|
SET @rn := 0;
|
|
INSERT INTO user_monthly_recharge_user (id, user_id, recharge_date, amount, create_time, update_time)
|
|
SELECT
|
|
7703000000000000000 + (@rn := @rn + 1) AS id,
|
|
user_id,
|
|
recharge_date,
|
|
ROUND(SUM(amount), 2) AS amount,
|
|
NOW(),
|
|
NOW()
|
|
FROM tmp_recharge_monthly_20260507
|
|
WHERE amount > 0
|
|
GROUP BY user_id, recharge_date
|
|
ORDER BY user_id, recharge_date;
|
|
|
|
DELETE FROM user_total_recharge_user;
|
|
|
|
SET @rn := 0;
|
|
INSERT INTO user_total_recharge_user (id, user_id, amount, create_time, update_time)
|
|
SELECT
|
|
7704000000000000000 + (@rn := @rn + 1) AS id,
|
|
user_id,
|
|
ROUND(SUM(amount), 2) AS amount,
|
|
NOW(),
|
|
NOW()
|
|
FROM tmp_recharge_monthly_20260507
|
|
WHERE amount > 0
|
|
GROUP BY user_id
|
|
ORDER BY user_id;
|
|
|
|
DELETE FROM user_activity_recharge
|
|
WHERE FIND_IN_SET(activity_id, @activity_ids);
|
|
|
|
INSERT INTO user_activity_recharge (user_id, activity_id, recharge_date, recharge_type, amount, create_time, update_time)
|
|
SELECT
|
|
d.user_id,
|
|
a.activity_id,
|
|
d.recharge_date,
|
|
d.recharge_type,
|
|
d.amount,
|
|
NOW(),
|
|
NOW()
|
|
FROM tmp_recharge_daily_20260507 d
|
|
JOIN (
|
|
SELECT 2006671533912581569 AS activity_id
|
|
UNION ALL SELECT 2006671533988298666
|
|
UNION ALL SELECT 1235571533988298753
|
|
) a
|
|
WHERE d.amount > 0
|
|
ORDER BY d.user_id, a.activity_id, d.recharge_date, d.recharge_type;
|
|
|
|
SELECT 'tmp_monthly_rows' AS metric, COUNT(*) AS value FROM tmp_recharge_monthly_20260507
|
|
UNION ALL SELECT 'tmp_daily_rows', COUNT(*) FROM tmp_recharge_daily_20260507
|
|
UNION ALL SELECT 'user_monthly_recharge_v2_rows', COUNT(*) FROM user_monthly_recharge_v2 WHERE FIND_IN_SET(type, @recharge_types)
|
|
UNION ALL SELECT 'user_total_recharge_rows', COUNT(*) FROM user_total_recharge WHERE FIND_IN_SET(type, @recharge_types)
|
|
UNION ALL SELECT 'user_monthly_recharge_user_rows', COUNT(*) FROM user_monthly_recharge_user
|
|
UNION ALL SELECT 'user_total_recharge_user_rows', COUNT(*) FROM user_total_recharge_user
|
|
UNION ALL SELECT 'user_activity_recharge_rows', COUNT(*) FROM user_activity_recharge WHERE FIND_IN_SET(activity_id, @activity_ids);
|