qiuqiu/obsidian @ a98a488b0d3fd2b3bc075dc79486e87171a925af

#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

sql语法参考.md 8.4 KB Historia Czysty

sql语法参考.md 8.4 KB

Historia Czysty