报表项目 数据库应用记录

SELECT * FROM db_hw_report.tb_xa_message_export_data 

where user_login_name=‘Ahmed.MAhmed@Etisal-int.com‘  

and  channel=‘facebook‘  and  location_name=‘Huawei Arabia (UAE)‘  

and action_date>=‘2017-8-17 00:00:00‘

and action_date<=‘2017-8-17 23:59:59‘

 and  action_taken<>‘read‘;    

/*1.1工作模式下,使用关键字action_date 日期 查询所有记录 个数*/

SELECT count(*) FROM db_hw_report.tb_xa_message_export_data 

where user_login_name=‘Ahmed.MAhmed@Etisal-int.com‘

 and  channel=‘facebook‘  and  location_name=‘Huawei Arabia (UAE)‘  and

action_date>=‘2017-8-17 00:00:00‘ and action_date<=‘2017-8-17 23:59:59‘

 and action_taken=‘reply‘ ;

/*1.2重点  工作模式下, 查询回复时长1小时以内的记录*/

select id,action_date,availability_date,tt from(select id,action_date,availability_date,TIMESTAMPDIFF(MINUTE,availability_date,action_date) tt from db_hw_report. tb_xa_message_export_data

where  user_login_name=‘Ahmed.MAhmed@Etisal-int.com‘    

and  channel=‘facebook‘     and  location_name=‘Huawei Arabia (UAE)‘   

 and action_date>=‘2017-8-17 00:00:00‘ and action_date<=‘2017-8-17 23:59:59‘  and action_taken<> ‘read‘  )as aa where tt>=0 and tt<60;

/*2.非工作模式下的所有记录，排除read类型数据的所有记录*/（筛选出 availability_date=  action_date两个日期相同）

 SELECT a.* FROM (select *,DATE_FORMAT(availability_date,‘%Y-%m-%d‘)as atime,DATE_FORMAT(action_date,‘%Y-%m-%d‘)as btime from db_hw_report.tb_xa_message_export_data

where user_login_name=‘Ahmed.MAhmed@Etisal-int.com‘ and channel=‘facebook‘  

and  location_name=‘Huawei Arabia (UAE)‘

and availability_date>=‘2017-8-1 00:00:00‘

and availability_date<=‘2017-8-16 23:59:59‘  ) as a where a.atime = a.btime ;

truncate table db_hw_report.cheshi;/*清除表数据*/

SELECT * FROM db_hw_report.cheshi;/*创建新表,主要插入数据用来计算  相邻工作时间差*/

SELECT * FROM db_hw_report.tb_xa_message_export_data  where user_login_name=‘Ahmed.MAhmed@Etisal-int.com‘  and  channel=‘facebook‘  and  location_name=‘Huawei Arabia (UAE)‘  and action_date>=‘2017-8-17 00:00:00‘ and action_date<=‘2017-8-17 23:59:59‘  and  action_taken <>‘Read‘  order by action_date ;    

/*1. 插入工作模式下的记录*/

insert into db_hw_report.cheshi (id,action_date) select id,action_date from db_hw_report.tb_xa_message_export_data

where user_login_name=‘Mohammed.Mostafa@Etisal-int.com‘  

and  channel=‘facebook‘

 and  location_name=‘Huawei Arabia (UAE)‘  

and action_date>=‘2017-8-21 00:00:00‘ and action_date<=‘2017-8-21 23:59:59‘

and  action_taken<>‘read‘ order by action_date    ;

*计算筛选内容的相邻工作时间差*/
select r1.id,
       r1.action_date,
       r1.ord_num,
       r2.ord_num,
       r2.id,
       r2.action_date,
       TIMESTAMPDIFF(MINUTE,r1.action_date , r2.action_date) sub_seconds
  from (select (@i := @i + 1) as ord_num,
               info.id,
               info.action_date
          from db_hw_report.cheshi info, (select @i := 1) d
         order by info.action_date) as r1
  left join (select (@j := @j + 1) as ord_num,
                    info.id,
                    info.action_date
               from db_hw_report.cheshi info, (select @j := 0) c
              order by info.action_date) as r2
    on r1.ord_num = r2.ord_num;

报表项目 数据库应用记录

标签：查询   where   sel   order by   second   repo   筛选   facebook   name