RDBMS.MQ.Redis.SQL

####################RDBMS.MQ.Redis.SQL######################
--testing log SQL statements
select log_cd,func_cd,log_dhms,from_ip from log_operate_1709 where
created_date >= to_timestamp('2017-09-14', 'yyyy-mm-dd')
and log_cd <> '日志类型' order BY created_date DESC;
select log_cd,func_cd,log_dhms,infurl,infnm,from_ip from log_pdt_data_1709 where
created_date >= to_timestamp('2017-09-14', 'yyyy-mm-dd')
and log_cd <> '日志类型' ORDER BY created_date DESC;
--count log_operate_1709 and log_pdt_data_1709 tables current date record
select count(log_cd) from log_operate_1709 where log_cd <> '日志类型' and
created_date >= to_timestamp('2017-09-14', 'yyyy-mm-dd');
select count(log_cd) from log_pdt_data_1709 where log_cd <> '日志类型' and
created_date >= to_timestamp('2017-09-14', 'yyyy-mm-dd');

---

---

select count(log_cd) from log_operate_1709 group by log_cd having created_date >= to_timestamp('2017-09-11', 'yyyy-mm-dd');
select count(created_date),created_date from log_operate_1709 where created_date >= to_timestamp('2017-09-11', 'yyyy-mm-dd') ORDER BY created_date DESC;
select count(created_date),created_date from log_pdt_data_1709 where created_date >= to_timestamp('2017-09-11', 'yyyy-mm-dd') ORDER BY created_date DESC;

---

select chain_uid from grp_cmm_unit group by chain_uid having count() >2;
select rate_id from grp_rsv_rate group by rate_id having count() >2;
select package_id from grp_rsv_package group by package_id having count(*) >2;
select * from grp_hk_base_roomtype;
select * from grp_hk_base_roomtype_ml;
select * from grp_hk_unit_roomtype;
select * from grp_hk_unit_roomtype_ml;
select package_id,day1,day2,day3,day4,day5,day6,day7 from grp_rsv_package_price where package_id in(
select package_id from grp_rsv_package_price group by package_id having count(1)>1) ;

select season_cd,day1,day2,day3,day4,day5,day6,day7 from grp_rsv_package_price where season_cd in(
select season_cd from grp_rsv_package_price group by season_cd having count(1)>1) ;

select package_id,start_dt,end_dt,day1,day2,day3 from grp_rsv_package_price t where t.package_id in
(select package_id,season_cd from grp_rsv_package_price group by package_id,season_cd having count(package_id) > 1) and season_cd having count(season_cd) > 1);

select package_id,day1,day2,day3 from grp_rsv_package_price t where t.package_id in
(select package_id from grp_rsv_package_price group by package_id having count(package_id) > 1);

select * from hpt_log_nightaudit where unit_uid='1bd2b793-9a8d-498b-b678-b721d67e20d6'
select count(package_id) from grp_rsv_package_price

select * from grp_rsv_package_group;
select * from grp_rsv_package_group_detail;

select * from hpt_hk_room;
select * from hpt_hk_room_combo;
select * from hpt_hk_room_feature;

select * from hpt_log_nightaudit where unit_uid='1bd2b793-9a8d-498b-b678-b721d67e20d6'
select tablename from pg_tables where schemaname='grp_dev';
select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;
select * from pg_database;
select count() from pg_database;
select count() from pg_tables where schemaname='grp_dev';
select count() from pg_tables where schemaname='pms_dev';
select count() from pg_tables where schemaname='pcm_dev';
select count() from pg_tables where schemaname='log_dev';
select count() from pg_tables where schemaname='stat_dev';
select count() from pg_tables where schemaname='cms_dev';
select count() from pg_tables where schemaname='grp_dev';
select count() from pg_tables where schemaname='grp_dev';
select count() from pg_tables where schemaname='grp_dev';

#############################################################
Jmeter消息类的功能
result = "\n1:"+bsh.args[0]+"\n"; //1：从参数框中获取参数变量，不适用
result+="2:${testBeanshell}\n" //2:直接获取变量，操作方便，非常适用
result+="3:"+vars.get("testBeanshell"); //3：从vars内置变量中获取变量，适用
vars.put("testBeanshell","beanshell_change");//改变变量值
result+="\n4:${testBeanshell}"//4：直接获取不生效，因为jmeter还没有对其进行复制
result+="\n5:"+vars.get("testBeanshell");//:5：值改变，容易理解

ResponseCode = 500;//回写请求响应代码，借鉴的http响应码
ResponseMessage = "test ok!!";//回写响应信息
IsSuccess = true;//回写响应结果

log.info("\nlog test:"+result);//调用jmeter内置log对象，日志会打印到jmeter.log中
SampleResult.setResponseData("SampleResult:"+result+"\n");//回写响应数据,SampleResult为jmeter内置回写结果对象，有很多可用方法，可以参看源码
return "return:"+result;//这里的返回值，相当于调用SampleResult.setResponseData()方法，注意使用的return关键字，后面的行都不会执行，且SampleResult.setResponseData("SampleResult:"+result+"\n");也不会执行，这里会被return的结果覆盖。但是用SampleResult.setResponseData()方法回写，后续还可以接代码行，因此建议不用return进行回写响应数据。
${__threadNum} :得到testplan的线程数
${__machineName} :得到主机名字
${__time(EEE, d MMM yyyy)} :返回一定格式的时间信息 。${__log(Message)}:写入log文件”…thread Name : Message” 。${__log(Message,OUT)}:输出到命令行。${__log(${VAR},,,VAR=)} ：以 “…thread Name VAR=value”形式输出到log文件。

Jmeter输入类的功能
这里Jason只介绍下${__XPath(File,//XX/XX/@XX)} :XPath方式读了File文件的相应属性.

Jmeter计算类的功能
${__counter(X)} :自增函数,X(true/false)来控制是否分配线程来增加.
${__Random(X,Y,Z)} :随机函数,X为最小值,Y为最大值,Z为存储的变量名.

Jmeter的Properties文件使用的功能类
之前Jason在Jmeter的P函数及properties文件介绍了相关的内容.

Jmeter的变量操作类的功能
${__split(X,Y,Z)} :用来分割变量用的,X为需要分割的变量或者String,Y为存储的变量名,Z为分割符.
${__V} :的使用主要来源于变量中含变量的情况 eg. ${A${N}} 可以用${__V(A${N})} 来显示成功.
${__eval} :主要用在对SQL的处理上,具体使用看下面的例子:

• name=Smith * column=age * table=birthdays * SQL=select ${column} from ${table} where name=’${name}’
  用${__eval(${SQL})}可以得到”select age from birthdays where name=’Smith’”.

Jmeter的String操作类的功能
${__regexFunction} :对前一个响应进行正则规约提取.具体参考这里.
${__char},${__unescape},${__unescapeHtml},${__escapeHtml} :主要用来对字符的编码格式的转换的.

Jmeter的脚本操作类的功能
${__javaScript(X,Y)} :主要是使用Javascript来做一些简单的操作.例如计算和字符处理等等.
eg. ${__javaScript(‘${sp}’.slice(7,99999))}
${__BeanShell(X)} :主要是利用BeanShell来实现一些具体的功能,BeanShell具体我们可以参考BeanShell.

#################################################################
位到秒的时间戳使用${__time(/1000,)}
固定格式的年月日
${__time(yyyyMMdd,)} 20151214 //返回年月日
${__time(HHmmss,)} 092816 //返回时分秒
${__time(yyyyMMdd-HHmmss,)} 20151214-092816 //全部返回
${__time(yyyy:MM dd-HH~mm.ss,)} 2017:06 25-10~24.29 //中间的分隔符可以自定义显示

需要自定义时间 比如要拿到上一月的数据，需要使用intSum函数先把当月的时间减一天后再与其他年月时分秒进行拼接 ${__intSum({__time(MM,nowDay)},-01,nextMonth)}
示例如下： 先获取当前时间

${__time(yyyyMMdd HHmmss,)} 再获取上个月的月份时间：
${__intSum({__time(MM,nowDay)},-01,nextMonth)} 再进行拼接：
${__time(yyyy,)}
${nextMonth}

${__time(dd HHmmss,)} 效果如图： 执行前的脚本 执行后的效果 可以拿到你想要指定格式日期后，你就可以根据数据库的要求进行更新操作了 像我都会在执行操作前先把相关的数据恢复下 恢复默认设置