#sql

select IF(system like ('%iOS 16%'),'iOS 16','iOS 15') as system_name, statistics_tag, version, round(avg(avg_fps), 2) as `平均帧率`, 
round(avg(jank_count/play_time * 600), 2) as `小卡`,
round(avg(big_jank_count/play_time * 600), 2) as `大卡`,
round(avg(mem_end/1024/1024), 2) as `平均结束内存`,
round(avg(avg_lag), 2) as `平均延迟`,
AVG(reconnect_times) as ret,
count(1) as `样本数`
from performance_log_ck
where logtime >= '2022-10-14 20:00:00' and logtime < '2022-10-17 22:00:00' 
and ((system) like ('%iOS 16%') or (system) like ('%iOS 15%'))
and play_time > 0
group by system_name, statistics_tag, version  order by system_name, statistics_tag, version




select IF(device_type like ('%iPhone15%'),'iPhone15','iPhone14') as device_name, IF(system like ('%iOS 16%'),'iOS 16','iOS 15') as system_name, statistics_tag, version, round(avg(avg_fps), 2) as `平均帧率`, 
round(avg(jank_count/play_time * 600), 2) as `小卡`,
round(avg(big_jank_count/play_time * 600), 2) as `大卡`,
round(avg(mem_end/1024/1024), 2) as `平均结束内存`,
round(avg(avg_lag), 2) as `平均延迟`,
AVG(reconnect_times) as ret,
count(1) as `样本数`
from performance_log_ck
where logtime >= '2022-10-14 20:00:00' and logtime < '2022-10-17 22:00:00' 
and ((system) like ('%iOS 16%') or (system) like ('%iOS 15%'))
and play_time > 0
group by device_name, system_name, statistics_tag, version  order by system_name, statistics_tag, version



select IF(device_type like ('%iPhone15%'),'iPhone15','iPhone14') as device_name, IF(system like ('%iOS 16%'),'iOS 16','iOS 15') as system_name, statistics_tag, version, round(avg(avg_fps), 2) as `平均帧率`, 
round(avg(jank_count/play_time * 600), 2) as `小卡`,
round(avg(big_jank_count/play_time * 600), 2) as `大卡`,
round(avg(mem_end/1024/1024), 2) as `平均结束内存`,
round(avg(avg_lag), 2) as `平均延迟`,
AVG(reconnect_times) as ret,
count(1) as `样本数`
from performance_log_ck
where logtime >= '2022-10-14 20:00:00' and logtime < '2022-10-17 22:00:00' 
and ((system) like ('%iOS 16%') or (system) like ('%iOS 15%'))
and play_time > 0
and (statistics_tag == 'Match-Type4v1' or statistics_tag == 'Match-Type4v1' or  statistics_tag == 'Match-Type8v2' or  statistics_tag == 'Match-TypeBattleRoyale' or statistics_tag == 'Match-TypeBattleRoyaleTeam')
group by device_name, system_name, statistics_tag, version  order by system_name, statistics_tag, version



select avg(`平均帧率`)as `ios15的` from (select IF(device_type like ('%iPhone15%'),'iPhone15','iPhone14') as device_name, IF(system like ('%iOS 16%'),'iOS 16','iOS 15') as system_name, statistics_tag, version, round(avg(avg_fps), 2) as `平均帧率`, 
round(avg(jank_count/play_time * 600), 2) as `小卡`,
round(avg(big_jank_count/play_time * 600), 2) as `大卡`,
round(avg(mem_end/1024/1024), 2) as `平均结束内存`,
round(avg(avg_lag), 2) as `平均延迟`,
AVG(reconnect_times) as ret,
count(1) as `样本数`
from performance_log_ck
where logtime >= '2022-10-14 20:00:00' and logtime < '2022-10-17 22:00:00' 
and ((system) like ('%iOS 16%') or (system) like ('%iOS 15%'))
and play_time > 0
and (statistics_tag == 'Match-Type4v1' or statistics_tag == 'Match-Type4v1' or  statistics_tag == 'Match-Type8v2' or  statistics_tag == 'Match-TypeBattleRoyale' or statistics_tag == 'Match-TypeBattleRoyaleTeam')
and (device_name== 'iPhone14')
group by device_name, system_name, statistics_tag, version  order by system_name, statistics_tag, version)where system_name='iOS 15'

select statistics_tag, version,net_type,round(avg(avg_fps), 2) as `平均帧率`, 
round(avg(jank_count/play_time * 600), 2) as `小卡`,
round(avg(big_jank_count/play_time * 600), 2) as `大卡`,
round(avg(mem_end/1024/1024), 2) as `平均结束内存`,
round(avg(avg_lag), 2) as `平均延迟`,
AVG(reconnect_times) as ret,
count(1) as `样本数`
from performance_log_ck
where logtime >= '2022-11-23 10:00:00' and logtime < '2022-11-23 20:00:00'
and play_time > 0
group by statistics_tag, version,net_type  order by statistics_tag, version desc limit 200




//ph-整体数据
select statistics_tag, version,case net_type 
when 0 then 'Photon'
when 1 then 'DmmNet'
else '?'
end as `网络类型`, round(avg(avg_fps), 2) as `平均帧率`, 
round(avg(jank_count/play_time * 600), 2) as `小卡`,
round(avg(big_jank_count/play_time * 600), 2) as `大卡`,
round(avg(mem_end/1024/1024), 2) as `平均结束内存`,
round(avg(avg_lag), 2) as `平均延迟`,
AVG(reconnect_times) as ret,
count(1) as `样本数`
from performance_log_ck
where logtime >= '2022-11-23 10:00:00' and logtime < '2022-11-23 20:00:00'
and play_time > 0

group by statistics_tag,version,net_type  order by net_type ASC limit 200

//lv3
SELECT statistics_tag,
        version,case net_type 
        when 0 then 'Photon'
        when 1 then 'DmmNet'
        else '?'
        end as `网络类型`, 
         round(avg(avg_fps),
         2) AS `平均帧率`,
         round(avg(jank_count/play_time * 600),
         2) AS `小卡`,
         round(avg(big_jank_count/play_time * 600),
         2) AS `大卡`,
         round(avg(mem_end/1024/1024),
         2) AS `平均结束内存`,
         round(avg(avg_lag),
         2) AS `平均延迟`,
         AVG(reconnect_times) AS ret,
         count(1) AS `样本数`
FROM performance_log_ck
WHERE logtime >= '2022-11-23 10:00:00'
        AND logtime < '2022-11-23 20:00:00'
        AND system LIKE 'Android%' -- lv4
        AND (not ( NOT (graph_device_version LIKE 'OpenGL ES 2%')
        AND memory >= 4 * 1000
        AND ( (cpu_core_count >= 10
        AND cpu_freq >= 2200) or(cpu_core_count >= 8
        AND cpu_freq >= 3000) ) )) -- lv3
        AND ( NOT (graph_device_version LIKE 'OpenGL ES 2%')
        AND memory >= 3 * 1000
        AND cpu_core_count >= 8
        AND cpu_freq >= 1900 )
        AND play_time > 0
GROUP BY  statistics_tag, version, net_type
ORDER BY  net_type ASC limit 200

//lv0
SELECT statistics_tag,
        version,case net_type 
        when 0 then 'Photon'
        when 1 then 'DmmNet'
        else '?'
        end as `网络类型`, 
         round(avg(avg_fps),
         2) AS `平均帧率`,
         round(avg(jank_count/play_time * 600),
         2) AS `小卡`,
         round(avg(big_jank_count/play_time * 600),
         2) AS `大卡`,
         round(avg(mem_end/1024/1024),
         2) AS `平均结束内存`,
         round(avg(avg_lag),
         2) AS `平均延迟`,
         AVG(reconnect_times) AS ret,
         count(1) AS `样本数`
FROM performance_log_ck
WHERE logtime >= '2022-11-23 10:00:00'
        AND logtime < '2022-11-23 20:00:00'
        AND system LIKE 'Android%' 
        AND (not ( NOT (graph_device_version LIKE 'OpenGL ES 2%')
        AND memory >= 4 * 1000
        AND ( (cpu_core_count >= 10
        AND cpu_freq >= 2200) or(cpu_core_count >= 8
        AND cpu_freq >= 3000) ) ))
        and(not ( NOT (graph_device_version LIKE 'OpenGL ES 2%')
        AND memory >= 3 * 1000
        AND cpu_core_count >= 8
        AND cpu_freq >= 1900 ) )
        and(not ( memory >= 2 * 1000
        AND cpu_core_count >= 4
        AND cpu_freq >= 1800
        AND res_h < 1440 ) ) --lv0
        AND ( memory < 1.5 * 1000
        OR cpu_core_count < 4
        OR cpu_freq < 1600
        OR res_h >= 1440 )
        AND play_time > 0
GROUP BY  statistics_tag, version, net_type
ORDER BY  net_type ASC limit 200


//pf_id筛选
SELECT statistics_tag, version, pf_id, case net_type when 0 then 'Photon' when 1 then 'DmmNet' else '?' end as `网络类型`, round(avg(avg_fps), 2) AS `平均帧率`, round(avg(jank_count/play_time * 600), 2) AS `小卡`, round(avg(big_jank_count/play_time * 600), 2) AS `大卡`, round(avg(mem_end/1024/1024), 2) AS `平均结束内存`, round(avg(avg_lag), 2) AS `平均延迟`, AVG(reconnect_times) AS ret, count(1) AS `样本数` FROM performance_log_ck WHERE logtime >= '2022-11-23 10:00:00' AND logtime < '2022-11-23 20:00:00' AND system LIKE 'Android%' AND (not ( NOT (graph_device_version LIKE 'OpenGL ES 2%') AND memory >= 4 * 1000 AND ( (cpu_core_count >= 10 AND cpu_freq >= 2200) or(cpu_core_count >= 8 AND cpu_freq >= 3000) ) )) and(not ( NOT (graph_device_version LIKE 'OpenGL ES 2%') AND memory >= 3 * 1000 AND cpu_core_count >= 8 AND cpu_freq >= 1900 ) ) and(not ( memory >= 2 * 1000 AND cpu_core_count >= 4 AND cpu_freq >= 1800 AND res_h < 1440 ) ) --lv0 AND ( memory < 1.5 * 1000 OR cpu_core_count < 4 OR cpu_freq < 1600 OR res_h >= 1440 ) AND play_time > 0 AND pf_id == 47 GROUP BY statistics_tag, version, net_type,pf_id ORDER BY statistics_tag ASC limit 200