sql 需求总结

-  需求一：统计本地app数量-------------------------------------------------------------------------
--ruleid_stat.sq;
drop table if exists liangde_com.native_ruleid_stat_0101_0130_re;
create table if not exists liangde_com.native_ruleid_stat_0101_0130_re as 
select 
	rule_id,
	count(*) as ruleid_freq
from kpi_team.daily_utterance_report
where language_code='zh-CN'
and yyyymmdd between 20180101 and 20180130
and
(execute_app like "%相册%" 
or execute_app like "%通讯录%" 
or execute_app like "%打电话%" 
or execute_app like "%设置%" 
or execute_app like "%信息%" 
or  execute_app like "%相机%" 
or execute_app like "%提醒%" 
or execute_app like "%时钟%"  
or execute_app like "%计算器%" 
or execute_app like "%日历%" 
or execute_app like "%网页%" 
or execute_app like  "%智能视觉%" 
or execute_app like  "%通知%" 
or execute_app like  "%多窗口%"
or execute_app like  "%最近%" 
or execute_app like  "%共享%" 
or execute_app like  "%智能视觉%" 
or execute_app like  "%比特币家%" 
or execute_app like  "%比特币声音%" 
or execute_app like  "%全局操作%"
or execute_app like  "%邮箱%" )
and rule_id is not NULL
group by rule_id 
order by ruleid_freq desc 
limit 2000000
------------------------------------------------------------------------------------
--app_stat.sq;
drop table if exists liangde_com.native_app_stat_0101_0130;
create table if not exists liangde_com.native_app_stat_0101_0130 as 
select 
	execute_app ,
	count(*) as app_freq
from kpi_team.daily_utterance_report
where language_code='zh-CN'
and yyyymmdd between 20180101 and 20180130
and
(execute_app like "%相册%" 
or execute_app like "%通讯录%" 
or execute_app like "%打电话%" 
or execute_app like "%设置%" 
or execute_app like "%信息%" 
or  execute_app like "%相机%" 
or execute_app like "%提醒%" 
or execute_app like "%时钟%"  
or execute_app like "%计算器%" 
or execute_app like "%日历%" 
or execute_app like "%网页%" 
or execute_app like  "%智能视觉%" 
or execute_app like  "%通知%" 
or execute_app like  "%多窗口%"
or execute_app like  "%最近%" 
or execute_app like  "%共享%" 
or execute_app like  "%智能视觉%" 
or execute_app like  "%比特币家%" 
or execute_app like  "%比特币声音%" 
or execute_app like  "%全局操作%"
or execute_app like  "%邮箱%" 
and rule_id != '网页_OpenQnA'
group by execute_app
order by app_freq desc 
limit 200000

需求二

总计 闹钟各地占比 case When 的用法

--------------------------------------------------------------------------------------
07:20的闹钟设定好了
drop table if exists liangde_com.alarm_idhm_0101_0130;
create table if not exists liangde_com.alarm_idhm_0101_0130 as 
SELECT 
		concat(kpi_team.decrypt_bxb(svcid),'_',sid,'_',uid) as single_id,
		kpi_team.decrypt_bxb(svcid) as svcid,
		sid,
		uid,
		nlg_output,
		regexp_extract(nlg_output,'([0-9]+):([0-9]+)', 0) as clock_time,
		regexp_extract(nlg_output,'([0-9]+):([0-9]+)', 1) as clock_hour,
		regexp_extract(nlg_output,'([0-9]+):([0-9]+)', 2) as clock_min,
		yyyymmdd
	from kpi_team.daily_utterance_report 
	where yyyymmdd BETWEEN '20180101' and '20180130'
	and language_code = 'zh-CN'
	and rule_id = 'Clock_1012'
	and nlg_output is not NULL;
	
drop table if exists liangde_com.alarm_idhm_halfh_0101_0130;
create table if not exists liangde_com.alarm_idhm_halfh_0101_0130 as 
SELECT 
	*,
	case 
	when clock_hour == 00 and clock_min between 00 and 30 then 1
	when clock_hour == 00 and clock_min between 31 and 60 then 2
	when clock_hour == 01 and clock_min between 00 and 30 then 3
	when clock_hour == 01 and clock_min between 31 and 60 then 4
	when clock_hour == 02 and clock_min between 00 and 30 then 5
	when clock_hour == 02 and clock_min between 31 and 60 then 6
	when clock_hour == 03 and clock_min between 00 and 30 then 7
	when clock_hour == 03 and clock_min between 31 and 60 then 8
	when clock_hour == 04 and clock_min between 00 and 30 then 9
	when clock_hour == 04 and clock_min between 31 and 60 then 10
	when clock_hour == 05 and clock_min between 00 and 30 then 11
	when clock_hour == 05 and clock_min between 31 and 60 then 12
	when clock_hour == 06 and clock_min between 00 and 30 then 13
	when clock_hour == 06 and clock_min between 31 and 60 then 14
	when clock_hour == 07 and clock_min between 00 and 30 then 15
	when clock_hour == 07 and clock_min between 31 and 60 then 16
	when clock_hour == 08 and clock_min between 00 and 30 then 17
	when clock_hour == 08 and clock_min between 31 and 60 then 18
	when clock_hour == 09 and clock_min between 00 and 30 then 19
	when clock_hour == 09 and clock_min between 31 and 60 then 20
	when clock_hour == 10 and clock_min between 00 and 30 then 21
	when clock_hour == 10 and clock_min between 31 and 60 then 22
	when clock_hour == 11 and clock_min between 00 and 30 then 23
	when clock_hour == 11 and clock_min between 31 and 60 then 24
	when clock_hour == 12 and clock_min between 00 and 30 then 25
	when clock_hour == 12 and clock_min between 31 and 60 then 26
	when clock_hour == 13 and clock_min between 00 and 30 then 27
	when clock_hour == 13 and clock_min between 31 and 60 then 28
	when clock_hour == 14 and clock_min between 00 and 30 then 29
	when clock_hour == 14 and clock_min between 31 and 60 then 30
	when clock_hour == 15 and clock_min between 00 and 30 then 31
	when clock_hour == 15 and clock_min between 31 and 60 then 32
	when clock_hour == 16 and clock_min between 00 and 30 then 33
	when clock_hour == 16 and clock_min between 31 and 60 then 34
	when clock_hour == 17 and clock_min between 00 and 30 then 35
	when clock_hour == 17 and clock_min between 31 and 60 then 36
	when clock_hour == 18 and clock_min between 00 and 30 then 37
	when clock_hour == 18 and clock_min between 31 and 60 then 38
	when clock_hour == 19 and clock_min between 00 and 30 then 39
	when clock_hour == 19 and clock_min between 31 and 60 then 40
	when clock_hour == 20 and clock_min between 00 and 30 then 41
	when clock_hour == 20 and clock_min between 31 and 60 then 42
	when clock_hour == 21 and clock_min between 00 and 30 then 43
	when clock_hour == 21 and clock_min between 31 and 60 then 44
	when clock_hour == 22 and clock_min between 00 and 30 then 45
	when clock_hour == 22 and clock_min between 31 and 60 then 46
	when clock_hour == 23 and clock_min between 00 and 30 then 47
	when clock_hour == 23 and clock_min between 31 and 60 then 48
	end as half_hour_id
from liangde_com.alarm_idhm_0101_0130 
where clock_time != '';
------------------------------------------
drop table if exists liangde_com.halfh_stat_0101_0130;
create table if not exists liangde_com.halfh_stat_0101_0130 as
select 
	half_hour_id,
	count(*) as freq
	from liangde_com.alarm_idhm_halfh_0101_0130
where half_hour_id is not null
group by half_hour_id
order by half_hour_id asc
limit 100;



-----------------------------------------------------------------------------按块统计--------------------------------------------------------------------------
drop table if exists liangde_com.alarm_idhm_block_0101_0130;
create table if not exists liangde_com.alarm_idhm_block_0101_0130 as 
SELECT 
	*,
	case 
	when clock_hour >= 00 and clock_hour < 05 then 1
	when clock_hour >= 05 and clock_hour < 08 then 2
	when clock_hour >= 08 and clock_hour < 11 then 3
	when clock_hour >= 11 and clock_hour < 13 then 4
	when clock_hour >= 13 and clock_hour < 17 then 5
	when clock_hour >= 17 and clock_hour < 19 then 6
	when clock_hour >= 19 and clock_hour < 20 then 7
	when clock_hour >= 20 and clock_hour < 24 then 8
	end as half_hour_id
from liangde_com.alarm_idhm_0101_0130 
where clock_time != '';




drop table if exists liangde_com.block_stat_0101_0130;
create table if not exists liangde_com.block_stat_0101_0130 as
select 
	half_hour_id,
	count(*) as freq
	from liangde_com.alarm_idhm_block_0101_0130
where half_hour_id is not null
group by half_hour_id
order by half_hour_id asc
limit 100;