博客园 首页 私信博主 显示目录 隐藏目录 管理 动画

mysql test sql

sql system_response

use aaa; 
CREATE TABLE `system_response` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `buried_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '埋点ID',
  `page_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '页面名称',
  `event_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '事件名称',
  `page_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '页面URL',
  `user_property` int DEFAULT '1' COMMENT '用户属性:0游客;1会员',
  `user_id` int DEFAULT '0' COMMENT '用户ID：0为游客',
  `mobile_type` int DEFAULT NULL COMMENT '手机类型:0为IOS;1为安卓',
  `mobile_brand` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机品牌:iPhone、华为、小米等',
  `mobile_model` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机型号:P30、P40、P50等',
  `device_system_version` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '设备系统版本号',
  `device_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '设备识别码',
  `app_channel` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '应用发布渠道',
  `app_version` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '应用版本号',
  `use_network` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '使用网络类型',
  `user_ip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户IP地址',
  `session_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '会话ID',
  `webview_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'webview打开时的Id',
  `start_datetime` timestamp  COMMENT '起始时间',
  `finish_datetime` timestamp   COMMENT '完成时间',
  `event_status` int DEFAULT NULL COMMENT '事件状态',
  `content_num` int DEFAULT '0' COMMENT '内容编号，默认为0，错误编码如：403、500等',
  `content_info` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '内容简单描述',
  `remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '备注，存储一些定位信息',
  `create_time` datetime  COMMENT '数据落地时间',
  `group_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '事件组ID',
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_dvice_id` (`device_id`),
  KEY `idx_start_datetime` (`start_datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=186730508368826373 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


select * from system_response_buckup where SUBSTR(app_version,1,1)='3';
//181216490682155017
//182362432273743873
//183677444070146052
//184749803866771459
//185692507877769217
//186838691762503681
//187043133882728453
select max(id) from system_response_buckup  ;

select *  from system_response_buckup  ;

delete from system_response_buckup where id >182362432273743873

load data local infile "C:\\Users\\liuyuan\\Desktop\\SystemResponseFile-7.txt"
into table system_response_buckup
character set utf8mb4
fields terminated by '`'
lines terminated by '\n';


use aaa;
select * from system_response limit 2000000 where SUBSTR(create_time,1,10)='2022-07-25';


select SUBSTR(create_time,1,10) from system_response limit 20000;
select SUBSTR(create_time,1,10),count(0) from system_response where SUBSTR(create_time,1,10)='2022-07-24';

//count(DISTINCT case when 条件 then  去重的字段 end)
select SUBSTR(create_time,1,10),count(DISTINCT device_id) from system_response where SUBSTR(create_time,1,10)='2022-07-24';

select * from system_response where SUBSTR(start_datetime,1,10)='2022-07-26';

--drop table sys_daily_log_count;
create table sys_daily_log_count as select SUBSTR(start_datetime,1,10) `date`,count(0) num from system_response  group by `date`;
select * from sys_daily_log_count;

--drop table sys_daily_dev_count;
create table sys_daily_dev_count as select SUBSTR(start_datetime,1,10) `date`,count(DISTINCT device_id) num from system_response  group by `date`;
select * from sys_daily_dev_count;

--drop table sys_daily_avg_click_count;
create table sys_daily_avg_click_count as select lo.date,de.num,lo.num/de.num avg_click from sys_daily_dev_count de left join sys_daily_log_count lo on de.date=lo.date;
select * from sys_daily_avg_click_count;

select event_name from system_response GROUP BY event_name;
select distinct event_name from system_response;


create table sys_daily_Me_BuyRecord_count as select SUBSTR(start_datetime,1,10)date,COUNT(0)num from system_response where event_name ='Me_BuyRecord' GROUP BY date;
--drop table sys_daily_Me_BuyRecord_count;
select * from sys_daily_Me_BuyRecord_count


create table sys_daily_get_apikey_count as select SUBSTR(start_datetime,1,10)date,COUNT(0)num from system_response where event_name ='非反代_获取apikey_加载完成' GROUP BY date;
--drop table sys_daily_get_apikey_count;
select * from sys_daily_get_apikey_count



create table sys_daily_bindWX_count as select SUBSTR(start_datetime,1,10)date,COUNT(0)num from system_response where event_name ='bindWX' GROUP BY date;
--drop table sys_daily_bindWX_count;
select * from sys_daily_bindWX_count;
select SUM(num) from sys_daily_bindWX_count;


create table sys_daily_my_complain_count as select SUBSTR(start_datetime,1,10)date,COUNT(0)num from system_response where event_name ='我的-我的申诉' GROUP BY date;
--drop table sys_daily_my_complain_count;
select * from sys_daily_my_complain_count;
sql t_punch
create table t_punch(
id int primary key auto_increment,
time varchar(255),
`name` varchar(255),
remark varchar(255),
insertTime datetime
)

select `name`,count(0)num from t_punch_in group by name having num<5;

create table t_punch_in(
id int primary key auto_increment,
time varchar(255),
`name` varchar(255),
gender varchar(5),
remark varchar(255),
insertTime datetime
)
truncate table t_punch_in;
select * from t_punch;
select * from t_punch_in;
desc t_punch_in;


create table t_punch_in_count as
select subStr(time,6),count(*)num   from t_punch group by subStr(time,6);
insert into t_punch_in_count values ('2022-07-23',38);
select * from t_punch_in_count;

update  t_punch_in set gender='M'   where name ='张三'

select name,count(*)num from t_punch group by name HAVING num=1;

update t_punch set name='张三' where name='张三'

select * from t_punch where name='张三'

create table t_punch_in_times as
select name,count(*)num   from t_punch group by name;


select * from t_punch_in_times where num=6;
select * from t_punch_in_times where num<5;
select * from t_punch where subStr(time,6)='2-07-23';
select * from t_punch where name='张三'
select count(0) from t_punch_in_times 

select name,count(*)num   from t_punch group by name having num<5;
select * from t_punch;

create table t_punch_in as
select `time`,`name`,gender,remark,insertTime  from t_punch

sys a

use 数据库;
desc sys_buried_point_search ;
select * from sys_buried_point_search where event_name='search_result_action';
-
select min(id),MAX(id) from sys_buried_point_search where event_name='search_result';
select min(id),MAX(id) from sys_buried_point_search where event_name='search_relevance';

select * from sys_buried_point_search where id>=373692 and id <=399002 ;

select MAX(id) from sys_buried_point_search ;

select count(0) from sys_buried_point_search where event_name='search_result_action';

select count(*) from sys_buried_point_search group by row_id;

--liuyuan_bigdata_timespend
create table liuyuan_bigdata_timespend(
id int not null auto_increment,
environment varchar(50),
event_name varchar(100),
hbase_data_total_count bigint,
data_size varchar(100),
time_spend bigint,
mysql_data_count bigint,
mysql_data_total_count bigint,
mysql_id_start bigint,
mysql_id_end bigint,
insert_time datetime,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

alter table liuyuan_bigdata_timespend change data_total_count hbase_data_total_count bigint;
alter table liuyuan_bigdata_timespend add mysql_data_aggregate bigint;
alter table liuyuan_bigdata_timespend add insert_time datetime;
alter table liuyuan_bigdata_timespend add mysql_id_start bigint;
alter table liuyuan_bigdata_timespend add mysql_id_end bigint;

insert into liuyuan_bigdata_timespend values (0,'prod','Search_Relevance',39249,'',39761965,373691,373691,1,373691,NOW()
insert into liuyuan_bigdata_timespend values (0,'prod','Search_Result_Action',25312,'',2664998,25311,399002,-373692,-399002,NOW());
insert into liuyuan_bigdata_timespend values (0,'prod','Search_Result',39355,'',40278666,790887,1189889,399003,1189889,NOW());
insert into liuyuan_bigdata_timespend values (0,'prod','Search_Result_Action',34324,'',3705733,34324,1224213,1189890,1224213,NOW());

update liuyuan_bigdata_timespend set data_size='345M' where id =4;
desc liuyuan_bigdata_timespend;
select * from liuyuan_bigdata_timespend;
select 1224213-1189890;
insert into liuyuan_bigdata_timespend values (0,'prod','Search_Result',25312,'',2664998,25311,NOW());



CREATE TABLE `liuyuan_buried_point_search` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `row_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `search_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `group_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `click_index` int DEFAULT NULL,
  `keyword` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `data_click` int DEFAULT NULL,
  `data_id` int DEFAULT NULL,
  `data_index` int DEFAULT NULL,
  `data_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `data_label` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_row_id` (`row_id`),
  KEY `idx_datetime` (`start_date`),
  KEY `idx_event_name_type` (`event_name`,`search_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
alter table liuyuan_buried_point_search add `data_num` INT DEFAULT NULL;
alter table liuyuan_buried_point_search add   `data_page` INT DEFAULT NULL;

truncate table liuyuan_buried_point_search;

select * from liuyuan_buried_point_search ;

select max(id) from sys_buried_point_search 

select * from liuyuan_buried_point_search where event_Name='search_relevance'  and search_type='search' limit 10000;

select * from sys_buried_point_search where event_Name='Search_Result' and click_index!=-1;

select * from sys_buried_point_search where event_Name='Search_Result_Action' and LENGTH(keyword)=0;

select SUBSTR(start_date,1,10) asd,event_Name,COUNT(0) from sys_buried_point_search group by SUBSTR(start_date,1,10),event_Name having event_Name='Search_Result_Action' ORDER BY asd ;

select SUBSTR(start_date,1,13) asd,event_Name,COUNT(0) from sys_buried_point_search group by SUBSTR(start_date,1,13),event_Name having event_Name='Search_Result_Action' ORDER BY asd ;

select SUBSTR(start_date,1,16) asd,event_Name,COUNT(0) from sys_buried_point_search where SUBSTR(start_date,1,13)>'2022-07-21 16' group by SUBSTR(start_date,1,16),event_Name having event_Name='Search_Result_Action' ORDER BY asd ;


select event_Name from liuyuan_buried_point_search GROUP BY event_Name;

select * from liuyuan_buried_point_search  where id=10842;
select * from liuyuan_buried_point_search  where start_date>'2022-7-19';
//10842
select * from liuyuan_buried_point_search where user_id=173797;

load data local infile "C:\\Users\\liuyuan\\Desktop\\SearchRelevanceFile.txt" 
into table sys_buried_point_search 
character set utf8mb4
fields terminated by '`'
lines terminated by '\n';

truncate table liuyuan_buried_point_search;


desc liuyuan_bigdata_timespend;
select * from liuyuan_bigdata_timespend;
insert into liuyuan_bigdata_timespend values(5,'prod','Search_Relevance',41064,'84M',24490,386935,1611148,1224213,1611148,NOW());

load data local infile "C:\\Users\\liuyuan\\Desktop\\SearchRelevanceFile.txt" into table liuyuan_buried_point_search;
LOAD DATA LOCAL INFILE "C:\MyDocs\SQL.txt" INTO TABLE Orders;


select count(0) from system_response;
desc system_response;

select * from system_response where SUBSTR(start_datetime,1,17)>'2022-07-12 00:00' and mobile_model like"Redmi Note 7 Pro";
select * from system_response where SUBSTR(start_datetime,1,17)>'2022-07-27 13:48';

 

posted @ 2022-07-27 16:40  CHANG_09  阅读(58)  评论(0)    收藏  举报