從投注記錄抓取

到報表計算

到結算機制

到封存資料

到放棄

報表的上游

投注記錄抓取

報表計算

先讓我們來看看需求吧

bet_histories by settled_at

bet_histories by feedback_at

cash_logs and manual_deposits and manual_withdraws

donate_histories

flush_users

manual_deposits

and

manual_withdraws

讓我們來看看公式吧

  • 總活動 = 人工存款[活動] - 人工提款[活動] + CashLog 活動
  • 其他 = 人工存款[其他] - 人工提款[其他]
  • 風控調整 = 人工存款[風控調整] - 人工提款[風控調整]
  • 投注盈虧 = 中獎金額 + 總兌現 - 總投注量 + 總返水
  • 遊戲盈虧 = 中獎金額 + 總兌現 - 總投注量 + 平台費 + 總返水
  • 淨輸贏 = 遊戲盈虧 + 總活動 + 風控調整
  • 傭金 = 拆帳比 * 淨輸贏

讓我們用一句 SQL 把報表產出來吧


SELECT
	IFNULL(bet.cash, 0) AS cash,
	IFNULL(bet.cash_winning, 0) AS cash_winning,
	IFNULL(bet.buyback, 0) AS buyback,
	IFNULL(feedback.feedback, 0) AS feedback,
	(IFNULL(manual_deposit_activity.manual_deposit_activity, 0) - n IFNULL(manual_withdraw_activity.manual_withdraw_activity, 0) + n IFNULL(cash_log_activity.cash_log_activity, 0)) AS activity,
	IFNULL(bet.platform_fee, 0) AS platform_fee,
	IFNULL(bet.winlost, 0) AS winlost,
	(IFNULL(bet.winlost, 0) + n IFNULL(feedback.feedback, 0) + n IFNULL(bet.platform_fee, 0) + n IFNULL(manual_deposit_activity.manual_deposit_activity, 0) - n IFNULL(manual_withdraw_activity.manual_withdraw_activity, 0) + n IFNULL(cash_log_activity.cash_log_activity, 0) + n IFNULL(manual_deposit_risk_adjustment.manual_deposit_risk_adjustment, 0) - n IFNULL(manual_withdraw_risk_adjustment.manual_withdraw_risk_adjustment, 0)) AS net_revenue,
	IFNULL(deposit.deposit, 0) AS deposit,
	IFNULL(withdraw.withdraw, 0) AS withdraw,
	IFNULL(flush.flush, 0) AS flush,
	(IFNULL(manual_deposit_other.manual_deposit_other, 0) - n IFNULL(manual_withdraw_other.manual_withdraw_other, 0)) AS other,
	(IFNULL(manual_deposit_risk_adjustment.manual_deposit_risk_adjustment, 0) - n IFNULL(manual_withdraw_risk_adjustment.manual_withdraw_risk_adjustment, 0)) AS risk_adjustment,
	IFNULL(donate.donate, 0) AS donate
FROM (
	SELECT
		0 AS id) AS `main`
	LEFT JOIN (
		SELECT
			0 AS id,
			SUM(bet_daily_records.cash) AS cash,
			SUM(bet_daily_records.cash_winning) AS cash_winning,
			SUM(bet_daily_records.buyback_amount) AS buyback,
			SUM(bet_daily_records.fee) AS platform_fee,
			SUM(bet_daily_records.revenue) AS winlost
		FROM
			`bet_daily_records`
		WHERE
			`set_key` IS NULL
			AND `date` BETWEEN '2020-04-20'
			AND '2020-04-21') AS `bet` ON `main`.`id` = `bet`.`id`
	LEFT JOIN (
		SELECT
			0 AS id, SUM(bet_daily_records.feedback) AS feedback
		FROM
			`bet_daily_records`
		WHERE
			`set_key` IS NULL
			AND `date` BETWEEN '2020-04-20'
			AND '2020-04-21') AS `feedback` ON `main`.`id` = `feedback`.`id`
	LEFT JOIN (
		SELECT
			0 AS id, SUM(deposits.cash) AS manual_deposit_activity
		FROM
			`deposits`
		WHERE
			`method` = 'manual_event'
			AND `finished_at` BETWEEN '2020-04-20 00:00:00'
			AND '2020-04-21 00:00:00') AS `manual_deposit_activity` ON `main`.`id` = `manual_deposit_activity`.`id`
	LEFT JOIN (
		SELECT
			0 AS id, SUM(withdraws.cash) AS manual_withdraw_activity
		FROM
			`withdraws`
		WHERE
			`withdraws`.`type` = 2
			AND `finished_at` BETWEEN '2020-04-20 00:00:00'
			AND '2020-04-21 00:00:00'
			AND `withdraws`.`deleted_at` IS NULL) AS `manual_withdraw_activity` ON `main`.`id` = `manual_withdraw_activity`.`id`
	LEFT JOIN (
		SELECT
			0 AS id, SUM(cash_logs.cash) AS cash_log_activity
		FROM
			`cash_logs`
		WHERE
			`cash_logs`.`type` = 'activity'
			AND `cash_logs`.`operated_at` BETWEEN '2020-04-20 00:00:00'
			AND '2020-04-21 00:00:00') AS `cash_log_activity` ON `main`.`id` = `cash_log_activity`.`id`
	LEFT JOIN (
		SELECT
			0 AS id, SUM(deposits.cash) AS manual_deposit_risk_adjustment
		FROM
			`deposits`
		WHERE
			`method` = 'manual_risk_adjustment'
			AND `finished_at` BETWEEN '2020-04-20 00:00:00'
			AND '2020-04-21 00:00:00') AS `manual_deposit_risk_adjustment` ON `main`.`id` = `manual_deposit_risk_adjustment`.`id`
	LEFT JOIN (
		SELECT
			0 AS id, SUM(withdraws.cash) AS manual_withdraw_risk_adjustment
		FROM
			`withdraws`
		WHERE
			`withdraws`.`type` = 4
			AND `finished_at` BETWEEN '2020-04-20 00:00:00'
			AND '2020-04-21 00:00:00'
			AND `withdraws`.`deleted_at` IS NULL) AS `manual_withdraw_risk_adjustment` ON `main`.`id` = `manual_withdraw_risk_adjustment`.`id`
	LEFT JOIN (
		SELECT
			0 AS id, SUM(deposits.cash) AS deposit
		FROM
			`deposits`
		WHERE
			`status` = 2
			AND `method` NOT in('manual_event', 'manual_other', 'manual_risk_adjustment')
			AND `finished_at` BETWEEN '2020-04-20 00:00:00'
			AND '2020-04-21 00:00:00') AS `deposit` ON `main`.`id` = `deposit`.`id`
	LEFT JOIN (
		SELECT
			0 AS id, SUM(withdraws.cash) AS withdraw
		FROM
			`withdraws`
		WHERE
			`status` = 3
			and(`withdraws`.`type` in(1, 5, 6, 7))
			AND `finished_at` BETWEEN '2020-04-20 00:00:00'
			AND '2020-04-21 00:00:00'
			AND `withdraws`.`deleted_at` IS NULL) AS `withdraw` ON `main`.`id` = `withdraw`.`id`
	LEFT JOIN (
		SELECT
			0 AS id, SUM(bet_daily_records.flush) AS flush
		FROM
			`bet_daily_records`
		WHERE
			`set_key` IS NULL
			AND `date` BETWEEN '2020-04-20'
			AND '2020-04-21') AS `flush` ON `main`.`id` = `flush`.`id`
	LEFT JOIN (
		SELECT
			0 AS id, SUM(deposits.cash) AS manual_deposit_other
		FROM
			`deposits`
		WHERE
			`method` = 'manual_other'
			AND `finished_at` BETWEEN '2020-04-20 00:00:00'
			AND '2020-04-21 00:00:00') AS `manual_deposit_other` ON `main`.`id` = `manual_deposit_other`.`id`
	LEFT JOIN (
		SELECT
			0 AS id, SUM(withdraws.cash) AS manual_withdraw_other
		FROM
			`withdraws`
		WHERE
			`withdraws`.`type` = 3
			AND `finished_at` BETWEEN '2020-04-20 00:00:00'
			AND '2020-04-21 00:00:00'
			AND `withdraws`.`deleted_at` IS NULL) AS `manual_withdraw_other` ON `main`.`id` = `manual_withdraw_other`.`id`
	LEFT JOIN (
		SELECT
			0 AS id, SUM(bet_daily_records.donate) AS donate
		FROM
			`bet_daily_records`
		WHERE
			`set_key` IS NULL
			AND `date` BETWEEN '2020-04-20'
			AND '2020-04-21') AS `donate` ON `main`.`id` = `donate`.`id`

SELECT

FROM baseQuery

LEFT JOIN

LEFT JOIN

SELECT

FROM bet_histories

Report Table

Report

Atom

Column

getSettleStatement

getUnsettleStatement

Report

Column

getSumStatement

user_id

GROUP BY user_id

GROUP BY user_id

ON bet.user_id = baseQuery.user_id

AS bet

SELECT

FROM baseQuery

LEFT JOIN

LEFT JOIN

SELECT

FROM bet_histories

user_id

GROUP BY user_id
ORDER BY cash_winning DESC

GROUP BY user_id

ON bet.user_id = baseQuery.user_id

AS bet

之前有寫一個 Report 的系統文件,更細節的使用方式可以再去看:https://universetech.hackmd.io/rWl3ALGpRqisTVox4xUfvQ

對了,在 Report 系統上線的不久

MySQL 的 CPU 使用率就上看 100% 了

結算機制

仙貝知識

  • 是先做了結算機制才做了 Report 系統,所以 Report 系統有 Settle Mode 跟 Unsettle Mode
  • 原本是每日的凌晨結算昨日的資料,現在改成每分鐘結算有修改過的資料
  • 結算機制其實是結算了:bet_histories, donate_histories, flush_users​,所以有記錄不代表有投注,必須看投注數量不為 0 才代表有投注

結算有兩個部分

  • 每人每天每個遊戲的結算
    user_id - date - game_id
  • 每人每天特定注單組合的結算
    user_id - date - set_key

關於特定注單組合

  1. 現在的特定注單組合只有-> 體育的低賠注單組合
  2. 現在使用特定注單組合的場景 -> 沒有

監控每分鐘結算的方式

  1. datadog 的 trace
  2. parameter 裡的 latest_settle_at

注單的養老院

封存機制

現在

上線

六個月前

三個月前 - 會員等級制度的時間上限

兩個月前 - 後臺原始資料報表的時間上限

可被封存的安全範圍

可被封存

但有點危險

被封存

程式就會錯誤的範圍

因為太麻煩了

而且三個月內的注單都還是有可能被修改,應該是不會封存

所以會員等級制度的程式碼現在還是依賴 bet_histories

如果要封存的話,強烈建議要改這個活動

加餐 - laravel-auditing

講完惹(・ω・)ノ

現在你可以放棄了

從投注記錄抓取到報表

By 球魚

從投注記錄抓取到報表

  • 672