【MySql】存储过程限定月份,限定某天等基础的使用
来源:互联网 发布:statistics软件 编辑:程序博客网 时间:2024/04/28 14:18
- BEGIN
- declare begin_time varchar(10); /****一月的第一天**/
- declare end_time varchar(10); /****月的最后一天**/
- declare month_time varchar(10); /****月份,格式:2013-11**/
- declare releaseCount int default 0; /***首页开屏广告点击量*/
- declare math int default 0;
- set @execSql = concat(' select ifnull( count(*) , 0 ) into @releaseCount from REP_CLICK_COUNT where data_time>=\'',@begin_time,'\' and data_time<=\'',@end_time,'\' and DATA_TYPE=1');
- prepare stmt from @execSql;
- execute stmt;
- #set @end_time = dataTime;
- set @end_time = concat(substring(dataTime,1,7),'-31');
- set @begin_time = concat(substring(@end_time,1,7),'-01');
- set @month_time = substring(@end_time,1,7);
- /**********数字的使用********/
- set @math =\'2102\' ;
- /**********字符 或 变量 的使用********/
- set @math =\'',dataTime,'\' ;
- /************限定insert_time日的*******************/
- where str_to_date(concat(\'',dataTime,'\',\'00:00:00\'),\'%Y-%m-%d %H:%i:%s\')<=insert_time ',' and insert_time<=str_to_date(concat(\'',dataTime,'\',\'23:59:59\'),\'%Y-%m-%d %H:%i:%s\') and version=\'2\' and event_id=\'2102\' and event_source=\'2006\' ');
- /************限定data_time月份的*******************/
- where data_time>=\'',@begin_time,'\' and data_time<=\'',@end_time,'\' and (action_type=1 or action_type=2) and (client_name=\'aMarket2.0\' or client_name=\'aMarket30_Pad\' or client_name=\'aMarketSDK\' or client_name=\'aMarket_Pad\' or client_name=\'GfanSDK\') GROUP BY channel_code ORDER BY count(DISTINCT device_imei,device_mac) DESC');
- /****设置值的写法1*****/
- set @execSql = concat('insert into REP_CLICK_COUNT(data_time,release_count,forum_count,h5_count,add_count,admin_forum_count)',
- ' values(\'',@month_time,'\',\'',@releaseCount,'\',\'',@forumCount,'\',\'',@h5Count,'\',\'',@addCount,'\',\'',@adminforumCount,'\',now())');
- prepare stmt from @execSql;
- execute stmt;
- /****设置值的写法2*****/
- set @execSql = concat('insert into report_gfan_column(data_time,version, column_code,click_count,down_count,click_down_rate,install_count,down_install_rate,insert_time) ',
- ' select data_time, client_version,data_type, count, 0,0.00,0,0.00,now() from report_gfan_to_column where data_time=\'',dataTime,'\' and data_type<=20 ');
- prepare stmt from @execSql;
- execute stmt;
- /************练习1*******************/
- set @execSql = concat('insert into report_gfan_data(data_time,client_version_code,client_version,data_type,count) select \'',dataTime,'\', \'0\', \'0\', 48, count(1) from client_event_log ',
- ' where str_to_date(concat(\'',dataTime,'\',\'00:00:00\'),\'%Y-%m-%d %H:%i:%s\')<=insert_time ',
- ' and insert_time<=str_to_date(concat(\'',dataTime,'\',\'23:59:59\'),\'%Y-%m-%d %H:%i:%s\') and version=\'2\' and event_id=\'2102\' and event_source=\'2006\' ');
- prepare stmt from @execSql;
- execute stmt;
- /************练习2*******************/
- set @execSql = concat('insert into fact_active_month(data_time,channel_code,version,active_count,insert_time) ',
- ' select \'',@month_time,'\', channel_code,\'all\',count(DISTINCT device_imei,device_mac),now() ',
- ' from log_device_active where data_time>=\'',@begin_time,'\' and data_time<=\'',@end_time,'\' and (action_type=1 or action_type=2) and (client_name=\'aMarket2.0\' or client_name=\'aMarket30_Pad\' or client_name=\'aMarketSDK\' or client_name=\'aMarket_Pad\' or client_name=\'GfanSDK\') GROUP BY channel_code ORDER BY count(DISTINCT device_imei,device_mac) DESC');
- prepare stmt from @execSql;
- execute stmt;
- END
0 0
- 【MySql】存储过程限定月份,限定某天等基础的使用
- 【MySql】存储过程限定月份,限定某天等基础的使用
- 【C++】【基础】const限定符的使用
- mysql表的主键、外键、限定、约定、索引、视同、存储过程
- const限定符的使用
- const限定符的使用
- mysql必知必会 - 使用完全限定名
- 存储限定符volatile
- Java基础 泛型限定的应用
- mysql登录IP限定
- android的资源限定符的使用
- 使用const限定类的成员函数
- c++ const限定符的使用
- 构造函数限定符的使用
- 使用限定符
- Android使用限定符
- 存储说明符和限定符
- 限定Map中存储个数
- Callable Future 以及CompletionService
- hdu 5424 Rikka with Graph II(哈密顿路判定)
- (14)event()函数
- Win10上设置共享目录
- 忆丹
- 【MySql】存储过程限定月份,限定某天等基础的使用
- 架构师之路(2)---详解面向过程
- linux——工作管理(job control)
- 模式设计的秘密之单例模式。
- 【Linux】Linux中常用操作命令
- Android Context 上下文 你必须知道的一切
- Android开发教程
- HDU 2602 Bone Collector(01背包问题)
- codeforces 446E E. Divisors(数论+暴力)