# 運營 - YL_每月月初 top 30 代理數據撈取

>Jira 範例：https://pmo-jira.qyrc452.com/browse/CSAPI-979

#### SQL 範本

SELECT  spa.topuid AS 'Top uid', 
        sra.ChannelID AS 'ChannelID',
        spa.Accounts AS 'account_name',
        SUM(CellScore) / 100 AS 'CellScore'
FROM KYStatis.statis_record_agent_all sra 
INNER JOIN KYDB_NEW.Sys_ProxyAccount spa 
		ON sra.ChannelID = spa.ChannelID 
WHERE 1 = 1 
AND sra.StatisDate >= '2023-10-01' 
AND sra.StatisDate <= '2023-10-31'
AND spa.topuid IN (


    )

GROUP BY 1,2,3
Having SUM(CellScore) > 0;

#### 步驟
1. 運營會整理並提供在統計區間內，全YL 流水排名 top30 總代理 (excel)
2. 將數據貼至 google sheet 頁面中的 rawData 頁面，得到本月份該服有進前30的代理
https://docs.google.com/spreadsheets/d/1pawJmh-bykVMwEZXXCo5-USCPRLujgxuVlz_JCSqx2c/edit?usp=sharing
3. 將各服的總代id 貼近 SQL 樣板裡面的 topuid 中
4. 各服執行相對應的SQL，並將產出的資料附上 jira 單

##### 範例
1. 下載運營提供的 excel
> 運營提供excel 範例

2. 將步驟1的數據貼至 google sheet 中的 rawData頁面，並找到對應的topuid
> rawData 頁面範例
> 在對應頁面找到總代理 id

3. 將總代理 ID 貼至 SQL樣板（提醒日期區間也要更改）
> 34 當範例

SELECT  spa.topuid AS 'Top uid', 
        sra.ChannelID AS 'ChannelID',
        spa.Accounts AS 'account_name',
        SUM(CellScore) / 100 AS 'CellScore'
FROM KYStatis.statis_record_agent_all sra 
INNER JOIN KYDB_NEW.Sys_ProxyAccount spa 
		ON sra.ChannelID = spa.ChannelID 
WHERE 1 = 1 
AND sra.StatisDate >= '2023-10-01'
AND sra.StatisDate <= '2023-10-31'
AND spa.topuid IN (

60037,50035,50077,61787
)

GROUP BY 1,2,3
Having SUM(CellScore) > 0;

4. 各服執行相對應的SQL，並將產出的資料附上 jira 單

# KX 月報數據
> Jira : https://pmo-jira.qyrc452.com/browse/CSAPI-769

#### SQL 樣板

SELECT
	n.ChannelID,
	4LevelChannelID.hyle,
	n.NickName,
	n.Accounts,
	n.BusinessAccount,
	n.MoneyType,
	SUM( lastwSU.regUsers ) AS lwRegUsers,
	SUM( lastwSU.validRegUsers ) AS lwValidRegUsers,
	SUM( lastWAGU.ActiveUsers ) AS lwActiveUsers,
	IFNULL( SUM( lastWSRAA.CellScore ), 0 )/ 1000 AS lwCellScore,
	IFNULL( SUM( lastWSRAA.Profit ), 0 )/ 1000 AS lwProfit,
	IFNULL( SUM( lastWSRAA.Profit ) / SUM( lastWSRAA.CellScore ), 0 ) AS lwKill,
	SUM( lastWSRAA.num ) AS lwNum,
	SUM( thiswSU.regUsers ) AS twRegUsers,
	SUM( thiswSU.validRegUsers ) AS twValidRegUsers,
	SUM( thisWAGU.ActiveUsers ) AS twActiveUsers,
	IFNULL( SUM( thisWSRAA.CellScore ), 0 )/ 1000 AS twCellScore,
	IFNULL( SUM( thisWSRAA.Profit ), 0 )/ 1000 AS twProfit,
	IFNULL( SUM( thisWSRAA.Profit ) / SUM( thisWSRAA.CellScore ), 0 ) AS twKill,
	SUM( thisWSRAA.num ) AS twNum,
	IFNULL(( SUM( thiswSU.validRegUsers ) - SUM( lastwSU.validRegUsers )) / SUM( lastwSU.validRegUsers ), 0 ) AS validRegUsers,
	IFNULL(( SUM( thisWAGU.ActiveUsers ) - SUM( lastWAGU.ActiveUsers )) / SUM( lastWAGU.ActiveUsers ), 0 ) AS ActiveUsers,
	IFNULL(( SUM( thisWSRAA.CellScore ) - SUM( lastWSRAA.CellScore )) / SUM( lastWSRAA.CellScore ), 0 ) AS CellScore,
	IFNULL(( SUM( thisWSRAA.Profit ) - SUM( lastWSRAA.Profit ))/ SUM( lastWSRAA.Profit ), 0 ) AS Profit,
	IFNULL(((
				SUM( thisWSRAA.Profit ) / SUM( thisWSRAA.CellScore )) - (
				SUM( lastWSRAA.Profit )) / SUM( lastWSRAA.CellScore ))/(
			SUM( lastWSRAA.Profit ) / SUM( lastWSRAA.CellScore )),
		0 
	) AS 'kill' 
FROM
	KYDB_NEW.Sys_ProxyAccount AS n
	LEFT JOIN (
	SELECT
		CONCAT(
			IFNULL( concat( 6th.UID, '-' ), '' ),
			IFNULL( concat( 5th.UID, '-' ), '' ),
			IFNULL( concat( 4th.UID, '-' ), '' ),
			IFNULL( concat( 3rd.UID, '-' ), '' ),
			IFNULL( concat( 2nd.UID, '-' ), '' ),
			1st.UID,
			'-',
			1st.ChannelID 
		) AS hyle,
		1st.ChannelID 
	FROM
		KYDB_NEW.Sys_ProxyAccount AS 1st
		LEFT JOIN KYDB_NEW.Sys_ProxyAccount AS 2nd ON 1st.UID = 2nd.ChannelID
		LEFT JOIN KYDB_NEW.Sys_ProxyAccount AS 3rd ON 2nd.UID = 3rd.ChannelID
		LEFT JOIN KYDB_NEW.Sys_ProxyAccount AS 4th ON 3rd.UID = 4th.ChannelID
		LEFT JOIN KYDB_NEW.Sys_ProxyAccount AS 5th ON 4th.UID = 5th.ChannelID
		LEFT JOIN KYDB_NEW.Sys_ProxyAccount AS 6th ON 5th.UID = 6th.ChannelID 
	) AS 4LevelChannelID ON 4LevelChannelID.hyle LIKE CONCAT( '%', n.ChannelID, '%' )
	LEFT JOIN (
	SELECT
		ChannelID,
		sum( CellScore ) AS CellScore,
		sum( WinGold + LostGold ) AS Profit,
		sum( WinNum + LostNum ) AS num 
	FROM
		KYStatis.statis_record_agent_all 
	WHERE
		StatisDate <= '2023-08-31' AND StatisDate >= '2023-08-01' 
	GROUP BY
		ChannelID 
	) AS lastWSRAA ON lastWSRAA.ChannelID = 4LevelChannelID.ChannelID
	LEFT JOIN (
	SELECT
		ChannelID,
		sum( CellScore ) AS CellScore,
		sum( WinGold + LostGold ) AS Profit,
		sum( WinNum + LostNum ) AS num 
	FROM
		KYStatis.statis_record_agent_all 
	WHERE
		StatisDate <= '2023-09-30' AND StatisDate >= '2023-09-01' 
	GROUP BY
		ChannelID 
	) AS thisWSRAA ON thisWSRAA.ChannelID = 4LevelChannelID.ChannelID
	LEFT JOIN (
	SELECT
		StatisDate,
		ChannelID,
		SUM( Y_RegUsers ) AS regUsers,
		SUM( DayNewBetUsers ) AS validRegUsers 
	FROM
		KYStatis.statisusers 
	WHERE
		StatisDate <= '2023-08-31' AND StatisDate >= '2023-08-01' 
	GROUP BY
		ChannelID 
	) AS lastwSU ON lastwSU.ChannelID = 4LevelChannelID.ChannelID
	LEFT JOIN (
	SELECT
		StatisDate,
		ChannelID,
		SUM( Y_RegUsers ) AS regUsers,
		SUM( DayNewBetUsers ) AS validRegUsers 
	FROM
		KYStatis.statisusers 
	WHERE
		StatisDate <= '2023-09-30' AND StatisDate >= '2023-09-01' 
	GROUP BY
		ChannelID 
	) AS thiswSU ON thiswSU.ChannelID = 4LevelChannelID.ChannelID
	LEFT JOIN (
	SELECT
		ChannelID,
		count( DISTINCT Account ) AS ActiveUsers 
	FROM
		( SELECT Account, ChannelID FROM KYStatisUsers.statis_allgames202308_users WHERE StatisDate >= '2023-08-01' AND StatisDate <= '2023-08-31' ) AS lastWAG 
	GROUP BY
		ChannelID 
	) AS lastWAGU ON lastWAGU.ChannelID = 4LevelChannelID.ChannelID
	LEFT JOIN (
	SELECT
		ChannelID,
		count( DISTINCT Account ) AS ActiveUsers 
	FROM
		( SELECT Account, ChannelID FROM KYStatisUsers.statis_allgames202309_users WHERE StatisDate >= '2023-09-01' AND StatisDate <= '2023-09-30' ) AS thisWAG 
	GROUP BY
		ChannelID 
	) AS thisWAGU ON thisWAGU.ChannelID = 4LevelChannelID.ChannelID 
GROUP BY
	n.ChannelID,
	n.NickName 
ORDER BY
	sum( thisWSRAA.CellScore ) DESC,
	n.ChannelID;

#### 步驟
1. 將上述SQL 樣板中的日期修改(本月份，上月份統計日期區間)

2. 執行 SQL 並將結果上傳Jira

# 後台無昨日數據（數據總攬，玩家盈利監控，代理點數統計）
> Jira : https://pmo-jira.qyrc452.com/browse/CSAPI-892

#### SQL 範例

-- 北京時間 1004
-- 北京時間 1102

call KYStatisUsers.sp_StatisUsersDayData('2023-11-02');
call KYDB_NEW.sp_statisAgentRecordInfo_everyMonth('2023-11-02');


-- 美東時間 1101 - 1102

call KYStatisUsers_EST.sp_StatisUsersDayData('2023-11-01'); 
call KYDB_NEW.sp_statisAgentRecordInfo_EST_everyMonth('2023-11-01');

call KYStatisUsers_EST.sp_StatisUsersDayData('2023-11-02'); 
call KYDB_NEW.sp_statisAgentRecordInfo_EST_everyMonth('2023-11-02');


-- fix newbetuser 1102
call KYDB_NEW.sp_Statis_GetUsers('2023-11-02','2023-11-02');


-- 為避免代理點數統計導出後新玩家人數為0，這隻SP也需要重跑(他是跑月份的，僅需執行一次)
call KYDB_NEW.sp_statis_agent_month_activeUsers_reguser('2023-11-02');


-- 遊戲數據統計 _ 不重複登入人數
CALL `KYStatisLogin`.`sp_statisLoginData`('20231102');

說明
- 北京時間 ： 填缺失數據的當天
- 美東時間 ： 往前多跑一天

# 會員投注級距及留存數據
> Jira : https://pmo-jira.qyrc452.com/browse/CSAPI-1009

#### SQL 範本 (留存)

SELECT  '34' AS 'Env',
        StatisDate AS 'Date',
        SUM(ValidNextRegisterUser) AS '前天注册并下注的玩家',
        SUM(ValidNextLoginUser) AS '前天注册并下注，昨日又登录的玩家',
        SUM(ValidNextLoginUser) / SUM(ValidNextRegisterUser) AS '次日流存',
        SUM(ValidSevenRegisterUser) AS '七天前注册并下注的玩家',
        SUM(ValidSevenLoginUser) AS '七天前注册并下注，昨日又登录的玩家',
        SUM(ValidSevenLoginUser) / SUM(ValidSevenRegisterUser) AS '七日留存',
        SUM(ValidMonthRegisterUser) AS '一个月前注册并下注的玩家',
        SUM(ValidMonthLoginUser) AS '一个月前注册并下注，昨日又登录的玩家',
        SUM(ValidMonthLoginUser) / SUM(ValidMonthRegisterUser) AS '月留存'


FROM KYStatis.statisusers 
WHERE 1 = 1 
AND StatisDate >= '2021-01-01'
AND StatisDate <= '2021-12-31'
GROUP BY 1 , 2 

步驟：
1. 各服執行 SQL ，並將結果匯出 csv
2. 執行 python 腳本 （流水分組 & 留存)
3. 將產出的資料上傳 jria

#### SQL 範本 (會員投注級距)

SELECT  DATE_FORMAT(sa.StatisDate,'%Y-%m') AS 'month'
       ,sa.Account
       ,SUM(sa.CellScore) / 100            AS 'cellScore'
FROM KYStatisUsers.statis_allgames202310_users sa
GROUP BY  1,2

步驟
1. 各服執行 SQL 別匯出 csv
2. 執行 python 腳本 （流水分組 & 留存)
3. 將產出的資料上傳 jria

# 代理點數統計遊戲數據有誤
> Jira : https://pmo-jira.qyrc452.com/browse/CSAPI-987

> sql 示意圖

步驟
1. 檢查並更新 sp_StatisUsersDayData_Rerun
- 確認 sp_StatisUsersDayData 這隻 SP 中的差異僅有最下面註解的部分

2. 刪除原有數據 （通常我會先跳過這部，直接先call SP重跑，不然有時候砍資料運營那邊會跳腳）

3. 執行 SP
- 北京時間為當天
- 美東時間要往前多跑一天

說明：原則上先執行 1, 3 ，如果執行完畢數據還是不正確，則重新執行 1,2,3

# 撈取指定代理遊戲數據（包含子代）
> Jira : https://pmo-jira.qyrc452.com/browse/CSAPI-991

需執行兩個 SQL
> sql 1 select_subAgent

SET @pv= 71305;
SELECT  UID,
        ChannelID 
FROM  (SELECT * FROM KYDB_NEW.Sys_ProxyAccount ORDER BY UID, ChannelID) uid_sorted,
      (SELECT @pv) initialisatio
WHERE (FIND_IN_SET(uid,@pv) OR ChannelID = @pv)
AND  LENGTH(@pv := concat(@pv, ',', ChannelID))
;

> sql 2 select

SELECT  srag.StatisDate  AS 'date',
        srag.ChannelID,
        srag.GameID,
        gi.GameName, 
        SUM(srag.CellScore) / 100 AS 'CellScore',
        SUM(srag.ActiveUsers) AS 'ActiveUsers'

FROM KYStatis.statis_record_agent_game srag 
LEFT JOIN KYDB_NEW.GameInfo gi ON srag.GameID = gi. GameID
WHERE 1 = 1 
AND srag.StatisDate >= '2023-10-01'
AND srag.StatisDate <= '2023-10-31'
AND srag.GameID IN (8281, 8282)
AND srag.ChannelID IN (
71305,71356,71357,71358,71359,71360,71361,71362,71437,71438,71439,71483,71484,71546,71547,72009,72020,72031,72032,72033,72034,72044,72045,72047,72053,72054,72055,72078,72115,72116,72129,72210,72211,72213,72214,72230,72243,72244,72245,72267,72305,72346,72352,72358,72359,72397,72472,72473,72529,72530,72532,72533,72573,72574,72575,72621,72623,72624,72625,72626,72639,72640,72641,72642,72643,72644,72663,72664,72665,72666,72667,72668,72669,72670,72671,72704,72719,72768,72769,72770,72786,72810,72814,72818,72822,72825,72827,72830,72836,72842,800006,800007,800013,800014,800015,800027,800036,800039,800040,800071,800072,800086,800087,800088,800092,800096,800097,800101,800113,800151,800207,800217,800220,800231,800239,800275,800276,800285,800288,800289,800324,800333,800334,800335,800336,800337,800338,800339,800340,800341,800342,800343,800519,800520,800521,800522,800523,800524,800525,800526,800527,800528,800529,800530,800531,800532,800533,800534,800535,800536,800597,800598,800599,800600,800601,800602,800603,800604,800605,800606,800607,800608,800609,800610,800611,800612,800613,800614

)

GROUP BY 1,2,3,4 

步驟 ：
1. 先執行 sql 1 找出指定代理旗下所有子代的id
2. 將 sql 1 的結果 放入 sql2 後執行
3. 將產出數據上傳至 jira

# 撈取注單
> Jira : https://pmo-jira.qyrc452.com/browse/CSAPI-1000

步驟
1. 執行SP (`KYDB_NEW`.`sp_GetGameRecord2`)
- 帶入參數 (需改動只有 channelid, 還有統計區間)
- 需注意該SP 日期區間不能超過 7 天（印象中），我通常都只放 5, 6 天，分批次執行

* 如有指定遊戲，則參數多代入gameid

2. 上傳數據至jira

# 遊戲平均時長
> Jira : https://pmo-jira.qyrc452.com/browse/CSAPI-954
> sql 範本

SELECT  "2023-10-20" AS 'Date',
        gm.ServerID,
	    gm.ServerName,
		AVG(UNIX_TIMESTAMP(gr.GameEndTime) - UNIX_TIMESTAMP(gr.GameStartTime)) AS 'AVG_GameTime'
FROM tbnn_record.gameRecord20231020 gr   
LEFT JOIN KYDB_NEW.GameRoomInfo gm ON gr.ServerID = gm.ServerID 
GROUP BY 1 , 2 , 3 

UNION ALL 
SELECT  "2023-10-20" AS 'Date',
        " " ,
		"不分房型" ,
		AVG(UNIX_TIMESTAMP(gr.GameEndTime) - UNIX_TIMESTAMP(gr.GameStartTime)) AS 'AVG_GameTime'
FROM tbnn_record.gameRecord20231020 gr   

;

步驟
1. 執行SQL ( 會需要更改遊戲的db以及日期的表)
2. 上傳數據至 jira

備註：如有超過一天，或是多個遊戲，可直接用 union all 拼接起來（可參考檔案）

# 體育 對賬

分為兩階段
- 階段1 : 提供注單給 體育運營

步驟：
1. 執行python腳本 : 撈取注單(sbo)
- 寫入開始及結束時間
- 注意，檔案裡面會附上 db連線資訊的 json黨，確保這個json 在 python 的工作區
2. 將結果同步體育

- 階段2 : 如果階段 1 所產出的數據與我們後台對不上，則需要重跑統計排程
步驟
1. 檢查並更新 sp_StatisUsersDayData_Rerun
- 確認 sp_StatisUsersDayData 這隻 SP 中的差異僅有最下面註解的部分

2. 刪除原有數據 （通常我會先跳過這部，直接先call SP重跑，不然有時候砍資料運營那邊會跳腳）

3. 執行 SP

- 北京時間為當天
- 美東時間要往前多跑一天

說明：原則上先執行 1, 3 ，如果執行完畢數據還是不正確，則重新執行 1,2,3