Report Project database application record

Source: Internet
Author: User

* Report Center statistics two modes of calculation

/*1. Working mode, use keyword action_date date to query all records */

SELECT * from Db_hw_report.tb_xa_message_export_data

where user_login_name= ' [email protected] '

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 working mode, use the keyword action_date date to query the number of records */

SELECT Count (*) from Db_hw_report.tb_xa_message_export_data

where user_login_name= ' [email protected] '

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 key work mode, query reply time 1 hours or less of the record * *

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= ' [email protected] '

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. All records in non-operating mode, excluding all records of read type data */(filter out availability_date= action_date two dates same)

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= ' [email protected] ' 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;/* Clear the Tables data */

SELECT * FROM db_hw_report.cheshi;/* creates a new table, primarily inserting data to calculate the adjacent work time difference */

SELECT * from Db_hw_report.tb_xa_message_export_data where user_login_name= ' [email protected] ' 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:5 9 ' and Action_taken <> ' Read ' ORDER by action_date;

/*1. Insert record in working mode */

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= ' [email protected] '

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;

* Calculate the adjacent working time difference of the filter content */
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;

Report Project database application record

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.