select current_date,' ',current_time from gp_configuration_history
;
fplatformid 平台id 1
fplatformshopid 店铺id aaf08dbafc46482f928d25a65337a5c4
fsaleReturnsId 售后申请单id case when frefundid = '34071180688780331' then 169107944542489fb3571f9dd1d1256c else null 创建时间 2019-10-15 15:19:37
fsaleReturnsNumber 售后申请单编号 case when frefundid = '34071180688780331' then BHSR191015000003 else null
frefundProcessStatus case when frefundid = '34071180688780331' then 2 else 1
fsyncFlag 同步时标记退款是否成功,0-失败，1-成功 待确认
fsyncMessage 同步时标记退款失败信息 待确认
fversion 版本号 不需要
ftenantid 租户ID 0
fisReject 是否拒签,0:否,1:是 待确认 物流绑定 是否拒签
fagRefundFlag AG退款是否成功 0-失败，1-成功 不需要
fagRefundMessage AG退款失败原因 null
fagRefundFailCount AG退款失败次数 0
select fisReject,count(1) from ecc.t_sd_tbrefund group by fisReject;
select count(1) from dw_dwd.dwd_app_card_share_bill_fl_d;
show create table dw_dwd.dwd_app_card_share_bill_fl_d;
show create table dw_dwd.dwd_app_card_share_bill_fl_d;
991 7230110093222199318 0241000122779 B77706534895646 6633 126.93 0 126.93 2023-01-10 09:32:23 98172812787713 BUY
992 7230110093222199318 0241000145376 B77706534895646 6633 373.07 0 373.07 2023-01-10 09:32:23 98172812787713 BUY
993 7230110093222199318 0241000122779 CASH_PAY NULL 397.3 397.3 0 2023-01-10 09:32:23 98172812787713 BUY
994 7230110093222199318 0241000145376 CASH_PAY NULL 1167.7 1167.7 0 2023-01-10 09:32:23 98172812787713 BUY
select * from dw_dwd.dwd_app_card_share_bill_fl_d where trade_no='';
select * from dw_dwd.dwd_app_card_share_bill_fl_d order by trade_no;
select ftid,gtin,basic_price_used,expand_price_used,
basic_price_refund,
expand_price_refund,
basic_price_used_initial,
expand_price_used_initial,
affiliate_brand_code,
affiliate_ratio_type,
affiliate_ratio,
affiliate_pay_initial,
affiliate_pay_end,
affiliate_pay_change,
affiliate_refund,
affiliate_real_amount_end,
affiliate_is_end
from dw_tmp.tmp_dwm_jv_item_sales_data_dtl_n_fl_d_14 where ftid='7230110093222199318';
show create table dw_dwd.dwd_app_card_share_bill_fl_d;
select count(1) from dw_dwd.dwd_app_card_share_bill_fl_d;
select count(1) from dw_dwd.dwd_app_card_share_bill_fl_d;
show databases;
use mydemo;
select *
from dwd_personas.dwd_users;
select *
from ads_personas.ads_widetable_userinterest_no2;
select eventid, other
from dwd_personas.dwd_events;
select count(*)
from ods_personas_raw.ods_users;
delete hive_users;
-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201
-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201
-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201
-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201
-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201
-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201
-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201
-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201
-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201
-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201-- 1201
create table dws_personas.dws_train as
select *
from dwd_personas.dwd_train;
create table dws_personas.dws_event_user_friend as
with t1 as (select t.userid, t.eventid, uf.friendid
from dwd_personas.dwd_train --根据用户找到聚会编号和用户的所有朋友
t
left join dwd_personas.dwd_userfriends uf on t.userid = uf.userid),
t2 as (select t.eventid, ea.userid, ea.attendtype
from (select eventid from dwd_personas.dwd_train group by eventid) t
left join dwd_personas.dwd_eventattendees ea on t.eventid = ea.eventid), --聚会所有的用户信息回复(每个用户重复)
t3 as (select t1.eventid, t1.userid, t2.attendtype
from t1
inner join t2
on t1.eventid = t2.eventid and t1.friendid = t2.userid)
select userid,
eventid,
sum(case attendtype when 'invited' then 1 else 0 end) user_event_invited,
sum(case attendtype when 'yes' then 1 else 0 end) user_event_yes,
sum(case attendtype when 'maybe' then 1 else 0 end) user_event_maybe,
sum(case attendtype when 'no' then 1 else 0 end) user_event_no
from t3
group by userid, eventid;
drop table dws_personas.dws_event_friends_prec;
create table dws_personas.dws_event_friends_prec as
with t1 as (select t.userid, if(count(uf.friendid) == 0, 1, count(uf.friendid)) friend_cnt
from (select userid
from dwd_personas.dwd_train
group by userid) t
left join dwd_personas.dwd_userfriends uf on t.userid = uf.userid
group by t.userid),
t2 as (select t.eventid, t.userid, uf.friendid, ea.attendtype
from dwd_personas.dwd_train t
left join dwd_personas.
dwd_eventattendees ea on t.eventid = ea.eventid
inner join dwd_personas.dwd_userfriends uf on t.userid =
uf.friendid),
t3 as (select t.eventid,
t.userid,
sum(case t.attendtype when 'invited' then 1 else 0 end) friend_invited
,
sum(case t.attendtype when 'maybe' then 1 else 0 end) friend_maybe,
sum(case t.attendtype when 'yes' then 1 else 0 end) friend_yes,
sum(case t.attendtype when 'no' then 1 else 0 end) friend_no
from t2 t
group by t.eventid, t.userid),
t4 as (select t3.*, t1.friend_cnt
from t1
left join t3 on t1.userid = t3.userid)
select t4.eventid,
t4.userid,
friend_invited / friend_cnt friend_invited_prec,
friend_maybe / friend_cnt friend_maybe_prec,
friend_yes / friend_cnt friend_yes_prec,
friend_no / friend_cnt friend_no_prec
from t4;
drop table dws_personas.dws_isFriend;
create table dws_personas.dws_isFriend as
select userid, eventid, if(sum(isfriend) = 0, 0, 1) isFriend
from (select t.userid, t.eventid, if(e.userid = f.friendid, 1, 0) isFriend
from dwd_personas.dwd_train t
left join
dwd_personas.dwd_userfriends f on t.userid = f.userid
left join dwd_personas.dwd_events e on t.eventid
= e.eventid) t
group by t.userid, t.eventid;
drop table dws_personas.dws_event_friends_prec;
create table dws_personas.dws_event_friends_prec as
select uf.userid,
uf.eventid,
round(uf.user_event_invited / fc.friend_cnt, 2) friend_invited,
round(uf.user_event_maybe / fc.friend_cnt, 2) friend_maybe,
round(uf.user_event_yes / fc.friend_cnt, 2) friend_yes,
round(uf.user_event_no / fc.friend_cnt, 2) friend_no
from dws_personas.dws_userfriend_count fc
inner join dws_personas.dws_event_user_friend uf on uf.userid = fc.userid;
drop table ads_personas.ads_widetable_userinterest_no1;
create database ads_personas;
create table ads_personas.ads_widetable_userinterest_no1 as
with t1 as (select t.eventid
, t.userid
, t.label
, e.grouptype
, e.party_time
, e.party_month
, e.party_quarter
, e.party_week
, e.prec_group_count
, e.prec_lat
, e.prec_lng
from dws_personas.dws_train t
left join dws_personas.dws_dim_events e on t.eventid = e.eventid),
t2 as (select t.*, ic.age, ic.gender, ic.issamecity, ic.locale, ic.member_day, ic.timezone
from t1 t
left join dws_personas.dws_dim_users_iscity ic on t.userid = ic.userid and t.eventid = ic.eventid),
t3 as (select t.*, uf.user_event_invited, uf.user_event_maybe, uf.user_event_no, uf.user_event_yes
from t2 t
left join dws_personas.dws_event_user_friend uf
on t.eventid = uf.eventid and t.userid = uf.userid),
t4 as (select t.*, ei.event_cnt, ei.event_invited_cnt, ei.event_maybe_cnt, ei.event_no_cnt, ei.event_yes_cnt
from t3 t
left join dws_personas.dws_event_invited ei on t.eventid = ei.eventid),
t5 as (select t.*, ui.invited_cnt, ui.maybe_cnt, ui.no_cnt, ui.user_cnt, ui.yes_cnt
from t4 t
left join dws_personas.dws_user_invited ui on t.userid = ui.userid),
t6 as (select t.*, fp.friend_invited, fp.friend_maybe, fp.friend_no, fp.friend_yes
from t5 t
left join dws_personas.dws_event_friends_prec fp
on t.userid = fp.userid and t.eventid = fp.eventid),
t7 as (select t.*, di.avg_age, di.prec_male, di.prec_female
from t6 t
left join dws_personas.dws_tmp_dim_eventinfo di on t.eventid = di.eventid),
t8 as (select t.*, ifr.isfriend
from t7 t
left join dws_personas.dws_isfriend ifr on t.eventid = ifr.eventid and t.userid = ifr.userid),
t9 as (select t.*, ef.event_frequency
from t8 t
left join dws_personas.dws_user_eventfrequency ef on t.userid = ef.userid),
t10 as (select t.*, ut.friend_cnt
from t9 t
left join dws_personas.dws_userfriend_count ut on t.userid = ut.userid)
select t.*, at.appointment
from t10 t
left join dws_personas.dws_user_appoint_cnt at on t.userid = at.userid;
select *
from ads_personas.ads_widetable_userinterest_no1;
select count(*)
from ads_personas.ads_widetable_userinterest_no1
where appointment is null;
drop table ads_personas.ads_widetable_userinterest_no2;
select *
from ads_personas.ads_widetable_userinterest_no2;
drop table ads_personas.ads_widetable_userinterest_no2;
create table ads_personas.ads_widetable_userinterest_no2 as
with t1 as (select avg(avg_age) aage,
avg(prec_male) prec_amale,
(1 - avg(prec_male)) prec_afemale
from ads_personas.ads_widetable_userinterest_no1),
t2 as (select un.*, t.*
from ads_personas.ads_widetable_userinterest_no1 un
cross join t1 t)
select eventid,
userid,
label,
grouptype,
party_time as party_hour,
party_month,
party_quarter,
party_week,
prec_group_count,
prec_lat,
prec_lng,
age,
gender,
issamecity,
locale,
member_day,
timezone,
citylevel,
provincelevel,
countrylevel,
nvl(user_event_invited, 0) user_event_invited,
nvl(user_event_maybe, 0) user_event_maybe,
nvl(user_event_no, 0) user_event_no,
nvl(user_event_yes, 0) user_event_yes,
event_cnt,
event_invited_cnt,
event_maybe_cnt,
event_yes_cnt,
event_no_cnt,
invited_cnt,
maybe_cnt,
no_cnt,
user_cnt,
yes_cnt,
nvl(friend_invited, 0) friend_invited,
nvl(friend_maybe, 0) friend_maybe,
nvl(friend_no, 0) friend_no,
nvl(friend_yes, 0) friend_yes,
nvl(avg_age, round(aage)) avg_age,
nvl(prec_male, prec_amale) prec_male,
nvl(prec_female, prec_afemale) prec_female,
isfriend,
event_frequency,
friend_cnt
from t2;
select *
from ads_personas.ads_widetable_userinterest_no2;
select *
from ads_personas.ads_widetable_userinterest_no2;
select *
from dwd_personas.dwd_locale;
select *
from ads_personas.ads_widetable_userinterest_no2;
//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207
//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207
//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207
//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207
//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207
//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207
//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207//1207
select *
from dws_personas.dws_user_invited;
create database mydemo;
use mydemo;
show tables;
create table demo
(
id string,
name string
);
select *
from demo;
-- 1208
-- 1208
-- 1208
-- 1208
-- 1208
-- 1208
-- 1208
-- 1208
-- 1208
-- 1208
-- 1208
-- 1208
-- 1208
-- 1208
//spark ods数据库
//spark ods数据库
//spark ods数据库
//spark ods数据库
//spark ods数据库
drop database if exists spark_ods;
create database spark_ods;
-- spark ods_userFriends
-- spark ods_userFriends
-- spark ods_userFriends
-- spark ods_userFriends
-- spark ods_userFriends
-- spark ods_userFriends
-- spark ods_userFriends
create table spark_dws.dws_userFriends
(
user_id string,
friendid string
)
row format delimited fields terminated by ','
stored as orc;
select *
from spark_ods.ods_userFriends;
select count(*)
from spark_ods.ods_userFriends;
///spark ods_eventAttendees
///spark ods_eventAttendees
///spark ods_eventAttendees
///spark ods_eventAttendees
///spark ods_eventAttendees
///spark ods_eventAttendees
///spark ods_eventAttendees
create table spark_dws.dws_eventAttendees
(
eventid string,
user_id string,
attendtype string
)
row format delimited fields terminated by ','
stored as orc;
select *
from spark_ods.ods_eventAttendees;
select count(*)
from spark_ods.ods_eventAttendees;
//创建用户维度表
//创建用户维度表
//创建用户维度表
//创建用户维度表
//创建用户维度表
//创建用户维度表
//创建用户维度表
//创建用户维度表
//创建用户维度表
-- drop table spark_dws.dws_users;
create table spark_dws.dws_users
(
user_id string,
locale string,
age string,
gender string,
member_day string,
location string,
timezone string,
invited_cnt string,
yes_cnt string,
maybe_cnt string,
no_cnt string,
user_cnt string,
friend_cnt string
)
row format delimited fields terminated by ','
stored as orc;
select count(*)
from spark_dws.dws_users;
select *
from spark_dws.dws_users;
desc spark_ods.ods_users;
select count(*)
from spark_ods.ods_users;
select *
from spark_ods.ods_users
limit 500;
//1209
//1209
//1209
//1209
//1209
//1209
//1209
-- 计算城市级别 省市级别 国家级别
drop table dws_personas.dws_dim_citylevel;
create table dws_personas.dws_dim_citylevel as
with t1 as (select e.city, count(e.eventid) ent
from dws_personas.dws_dim_events e
where trim(e.city) != ''
group by e.city)
select city, row_number() over (order by ent desc ) level
from t1;
select *
from dws_personas.dws_dim_citylevel;
create table dws_personas.dws_dim_provincelevel as
with t1 as (select e.province, count(e.eventid) ent
from dws_personas.dws_dim_events e
where trim(e.province) != ''
group by e.province)
select province, row_number() over (order by ent desc ) level
from t1;
select *
from dws_personas.dws_dim_provincelevel;
create table dws_personas.dws_dim_countrylevel as
with t1 as (select e.country, count(e.eventid) ent
from dws_personas.dws_dim_events e
where trim(e.country) != ''
group by e.country)
select country, row_number() over (order by ent desc ) level
from t1;
select *
from dws_personas.dws_dim_countrylevel;
//
drop table dws_personas.dws_dim_events;
create table dws_personas.dws_dim_events as
with t1 as (select max(lat) max_lat, min(lat) min_lat, max(lng) max_lng, min(lng) min_lng from dwd_personas.dwd_events),
t2 as (select e.eventid,
e.userid,
e.party_time,
e.party_week,
e.party_month,
e.party_quarter,
regexp_extract(e.starttime, '(.*)T.*', 1) starttime,
e.city,
e.province,
e.country,
nvl(round((e.lat - t.min_lat) / (t.max_lat - t.min_lat), 2), 0) prec_lat,
nvl(round((e.lng - t.min_lng) / (t.max_lng - t.min_lng), 2), 0) prec_lng
from dwd_personas.dwd_events e
cross join t1 t),
t3 as (select t.*, g.prediction grouptype
from t2 t
inner join dwd_personas.dwd_event_group g on t.eventid = g.eventid),
t4 as (select prediction grouptype, count(eventid) event_group_count
from dwd_personas.dwd_event_group
group by prediction),
t5 as (select max(event_group_count) max_cnt, min(event_group_count) min_cnt from t4)
select t3.*
, round((t4.event_group_count - t5.min_cnt) / (t5.max_cnt - t5.min_cnt), 2) prec_group_count
, if(trim(t3.city) == '', 0, cl.level) citylevel
, if(trim(t3.province) == '', 0, pr.level) provincelevel
, if(trim(t3.country) == '', 0, ct1.level) countrylevel
from t3
inner join t4 on t3.grouptype = t4.grouptype
cross join t5
left join dws_personas.dws_dim_citylevel cl
on t3.city = cl.city
left join dws_personas.dws_dim_provincelevel pr
on t3.province = pr.province
left join dws_personas.dws_dim_countrylevel ct1 on t3.country = ct1.country;
select *
from dws_personas.dws_dim_events;
desc dws_personas.dws_dim_events;
//改ads
//改ads
//改ads
//改ads
//改ads
//改ads
//改ads
//改ads
//改ads
//改ads
//改ads
//改ads
//改ads
//改ads
drop table ads_personas.ads_widetable_userinterest_no1;
select *
from ads_personas.ads_widetable_userinterest_no1;
create table ads_personas.ads_widetable_userinterest_no1 as
with t1 as (select t.eventid
, t.userid
, t.label
, e.grouptype
, e.party_time
, e.party_month
, e.party_quarter
, e.party_week
, e.prec_group_count
, e.prec_lat
, e.prec_lng
, e.citylevel
, e.provincelevel
, e.countrylevel
from dws_personas.dws_train t
left join dws_personas.dws_dim_events e on t.eventid = e.eventid),
t2 as (select t.*, ic.age, ic.gender, ic.issamecity, ic.locale, ic.member_day, ic.timezone
from t1 t
left join dws_personas.dws_dim_users_iscity ic on t.userid = ic.userid and t.eventid = ic.eventid),
t3 as (select t.*, uf.user_event_invited, uf.user_event_maybe, uf.user_event_no, uf.user_event_yes
from t2 t
left join dws_personas.dws_event_user_friend uf
on t.eventid = uf.eventid and t.userid = uf.userid),
t4 as (select t.*, ei.event_cnt, ei.event_invited_cnt, ei.event_maybe_cnt, ei.event_no_cnt, ei.event_yes_cnt
from t3 t
left join dws_personas.dws_event_invited ei on t.eventid = ei.eventid),
t5 as (select t.*, ui.invited_cnt, ui.maybe_cnt, ui.no_cnt, ui.user_cnt, ui.yes_cnt
from t4 t
left join dws_personas.dws_user_invited ui on t.userid = ui.userid),
t6 as (select t.*, fp.friend_invited, fp.friend_maybe, fp.friend_no, fp.friend_yes
from t5 t
left join dws_personas.dws_event_friends_prec fp
on t.userid = fp.userid and t.eventid = fp.eventid),
t7 as (select t.*, di.avg_age, di.prec_male, di.prec_female
from t6 t
left join dws_personas.dws_tmp_dim_eventinfo di on t.eventid = di.eventid),
t8 as (select t.*, ifr.isfriend
from t7 t
left join dws_personas.dws_isfriend ifr on t.eventid = ifr.eventid and t.userid = ifr.userid),
t9 as (select t.*, ef.event_frequency
from t8 t
left join dws_personas.dws_user_eventfrequency ef on t.userid = ef.userid),
t10 as (select t.*, ut.friend_cnt
from t9 t
left join dws_personas.dws_userfriend_count ut on t.userid = ut.userid)
select t.*, at.appointment
from t10 t
left join dws_personas.dws_user_appoint_cnt at on t.userid = at.userid;
//
//
//
//
//
drop table ads_personas.ads_widetable_userinterest_no2;
select timezone, citylevel, provincelevel, countrylevel
from ads_personas.ads_widetable_userinterest_no2;
desc ads_personas.ads_widetable_userinterest_no2;
create table ads_personas.ads_widetable_userinterest_no2 as
with t1 as (select avg(avg_age) aage,
avg(prec_male) prec_amale,
(1 - avg(prec_male)) prec_afemale,
max(event_cnt) max_event_cnt,
min(event_cnt) min_event_cnt,
max(event_invited_cnt) max_event_invited_cnt,
min(event_invited_cnt) min_event_invited_cnt,
max(friend_cnt) max_friend_cnt,
min(friend_cnt) min_friend_cnt,
max(event_yes_cnt) max_event_yes_cnt,
min(event_yes_cnt) min_event_yes_cnt,
max(event_no_cnt) max_event_no_cnt,
min(event_no_cnt) min_event_no_cnt,
max(event_maybe_cnt) max_event_maybe_cnt,
min(event_maybe_cnt) min_event_maybe_cnt
from ads_personas.ads_widetable_userinterest_no1),
t2 as (select un.*, t.*
from ads_personas.ads_widetable_userinterest_no1 un
cross join t1 t),
t3 as (select max(citylevel) maxcntlevel, max(provincelevel) maxpntlevel, max(countrylevel) maxcnntlevel from t2)
select eventid,
userid,
label,
grouptype,
party_time as party_hour,
party_month,
party_quarter,
party_week,
prec_group_count,
prec_lat,
prec_lng,
age,
gender,
issamecity,
locale,
member_day,
timezone,
round(citylevel / maxcntlevel, 3) citylevel,
round(provincelevel / maxpntlevel, 3) provincelevel,
round(countrylevel / maxcnntlevel, 3) countrylevel,
nvl(user_event_invited, 0) user_event_invited,
nvl(user_event_maybe, 0) user_event_maybe,
nvl(user_event_no, 0) user_event_no,
nvl(user_event_yes, 0) user_event_yes,
round((event_cnt - min_event_cnt) / (max_event_cnt - min_event_cnt), 3) event_cnt,
round((event_invited_cnt - min_event_invited_cnt) / (max_event_invited_cnt - min_event_invited_cnt),
3) event_invited_cnt,
round((event_maybe_cnt - min_event_maybe_cnt) / (max_event_maybe_cnt - min_event_maybe_cnt), 3) event_maybe_cnt,
round((event_yes_cnt - min_event_yes_cnt) / (max_event_yes_cnt - min_event_yes_cnt), 3) event_yes_cnt,
round((event_no_cnt - min_event_no_cnt) / (max_event_no_cnt - min_event_no_cnt), 3) event_no_cnt,
invited_cnt,
maybe_cnt,
no_cnt,
user_cnt,
yes_cnt,
nvl(friend_invited, 0) friend_invited,
nvl(friend_maybe, 0) friend_maybe,
nvl(friend_no, 0) friend_no,
nvl(friend_yes, 0) friend_yes,
nvl(avg_age, round(aage)) avg_age,
nvl(prec_male, prec_amale) prec_male,
nvl(prec_female, prec_afemale) prec_female,
isfriend,
event_frequency,
round((friend_cnt - min_friend_cnt) / (max_friend_cnt - min_friend_cnt), 3) friend_cnt,
nvl(appointment, 0) appointment
from t2
cross join t3;
select user_id, age, gender
from spark_ods.ods_users;
select user_id, eventid, attendtype
from spark_ods.ods_eventAttendees
where attendtype = 'invited';
select *
from dwd_personas.dwd_event_group;
desc dwd_personas.dwd_event_group;
desc spark_ods.ods_users;
desc spark_ods.ods_eventAttendees;
drop table spark_ods.ods_users;
select *
from spark_ods.ods_users;
//
drop database if exists spark_dws cascade;
create database spark_dws;
create table spark_dws.dws_userFriends
(
userF
);
//1210
//1210
//1210
//1210
//1210
//1210
//1210
show databases;
select count(*)
from (select user_id from spark_dws.dws_eventAttendees where attendtype = 'invited' group by user_id) a;
select userid
from dwd_personas.dwd_eventattendees
group by userid;
select *
from spark_dws.dws_eventAttendees;
select count(*)
from spark_dws.dws_eventAttendees;
select count(*)
from spark_dws.dws_users;
select count(*)
from dwd_personas.dwd_users;
select *
from (select user_id from spark_dws.dws_eventAttendees where attendtype = 'invited' group by user_id) a
inner join
dwd_personas.dwd_users b on a.user_id = b.userid;
select count(*)
from spark_dws.dws_users;
select count(prediction)
from dwd_personas.dwd_event_group
group by prediction;
select userid, eventid
from dwd_personas.dwd_eventattendees
where attendtype != 'invited'
group by userid, eventid
having count(attendtype) > 1;
select *
from dwd_personas.dwd_eventattendees
limit 500;
select count(*)
from (select eventid from dwd_personas.dwd_eventattendees et where attendtype = 'invited' group by eventid) a;
select count(*)
from (select eventid from dwd_personas.dwd_eventattendees group by eventid) a
inner join
dwd_personas.dwd_events b on a.eventid = b.eventid;
show databases;
select *
from demo;
//1211
//1211
//1211
//1211
drop table spark_dws.dws_events;
create table spark_dws.dws_events
(
eventid string,
user_id string,
party_hour string,
party_week string,
party_month string,
party_quarter string,
start_time string,
city string,
province string,
country string,
citylevel string,
provincelevel string,
countrylevel string,
grouptype string,
prec_male string,
prec_female string,
avg_age string,
prec_lat string,
prec_lng string,
event_invited_cnt string,
event_yes_cnt string,
event_maybe_cnt string,
event_no_cnt string,
event_cnt string
)
row format delimited fields terminated by ','
stored as orc;
select *
from spark_dws.dws_users;
select *
from spark_dws.dws_events;
select count(*)
from spark_dws.dws_events;
select count(*)
from spark_dws.dws_userFriends;
select regexp_extract(starttime, '(.*)0', 1), starttime
from dwd_personas.dwd_events;
select regexp_extract('2012-08-11T19:30:00TY.000Z', '(.*)T.*', 1);
select *
from ods_personas_raw.ods_users;
select *
from ods_personas.ods_hbase_eventattendees;
drop table train;
create external table train
(
user_id string,
eventid string,
invited string,
timestamp string
)
row format delimited fields terminated by ','
location 'hdfs://192.168.10.136:9000/personas/test';
select *
from train;
select *
from spark_dws.dws_eventAttendees
where attendtype = 'maybe';
select userid, eventid
from dwd_personas.dwd_train
group by userid, eventid;
select *
from dwd_personas.dwd_train;
//整理的预测数据集
//整理的预测数据集
//整理的预测数据集
//整理的预测数据集 spark_dws.dws_test
create table spark_dws.dws_test
(
eventid string,
user_id string,
isFriend string,
user_event_invited string,
user_event_yes string,
user_event_maybe string,
user_event_no string,
event_frequency string,
isSameCity string
)
row format delimited fields terminated by ','
stored as orc;
select *
from spark_dws.dws_test;
select *
from spark_dws.dws_test;
select count(*)
from spark_dws.dws_test;
//1214
//1214
//1214
select *
from ods_personas_raw.ods_eventattendees;
select *
from dwd_personas.dwd_eventattendees;
-- 1216
select *
from
--1218
;
create database exam;
drop table ex_exam_after_sales_service;
create external table ex_exam_after_sales_service
(
createMonth string,
createTime string,
createPerson string,
orderCode string,
productCode string,
serviceReasonType string,
serviceReasonDetail string,
productNum string,
returnOrderCode string,
resendProductCode string,
resendPorductDetail string,
resendOrderCode string,
excutePerson string,
state string,
finishTime string,
provice string,
city string
) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties (
'separatorChar' = ',',
'quoteChar' = '\"',
'escapeChar' = '\\'
)
location '/app/data/exam'
tblproperties ('skip.header.line.count' = '1');
select *
from ex_exam_after_sales_service;
///
create external table if not exists ex_exam_after_sales_service_statistics
(
key string,
serviceReasonDetailCount int
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties
("hbase.columns.mapping" = ":key,statistics:serviceReasonDetailCount")
tblproperties ("hbase.table.name" = "exam:after_sales_service");
with t1 as (select concat(createMonth, serviceReasonDetail) as key
from ex_exam_after_sales_service
)
insert
into ex_exam_after_sales_service_statistics
select key, count(key)
from t1
group by key;
select *
from ex_exam_after_sales_service_statistics;
use exam;
//1221 exam 2020 10月份卷子
drop database if exists exam;
create database exam;
use exam;
show tables;
drop table UserBehavior;
create external table UserBehavior
(
user_id int,
item_id int,
category_id int,
behavior_type string,
time bigint
) row format delimited fields terminated by ','
stored as textfile
location '/data/userbehavior';
select *
from userbehavior;
drop table userbehavior_hbase;
CREATE EXTERNAL TABLE IF NOT EXISTS userbehavior_hbase
(
user_id int,
item_id int,
category_id int,
behavior_type string,
time bigint
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties ("hbase.columns.mapping" =
":key,info:item_id,info:category_id,info:behavior_type,info:time")
tblproperties ("hbase.table.name" = "exam1:userbehavior");
insert overwrite table userbehavior_hbase
select *
from userbehavior;
drop table if exists userbehavior_partitioned;
create table userbehavior_partitioned
(
user_id int,
item_id int,
category_id int,
behavior_type string,
time string
)
partitioned by (dt string)
stored as orc;
show tables;
//开启动态分区
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrict;
-- 将数据插入到分区表中
select *
from userbehavior;
select *
from userbehavior_hbase;
select *
from userbehavior_partitioned;
insert overwrite table userbehavior_partitioned partition (dt)
select user_id,
item_id,
category_id,
behavior_type,
from_unixtime(time, 'YYYY-MM-dd HH:mm:ss') as time,
substring(from_unixtime(time, 'YYYY-MM-dd HH:mm:ss'), 1, 10) dt
from userbehavior;
with pay_F AS (SELECT user_id, COUNT(*) AS A
FROM exam.userbehavior_partitioned
WHERE behavior_type = 'buy'
GROUP BY user_id)
SELECT user_id,
(CASE
WHEN A BETWEEN 1 AND 32 THEN 0
WHEN A BETWEEN 33 AND 64 THEN 1
WHEN A BETWEEN 65 AND 96 THEN 2
WHEN A BETWEEN 97 AND 128 THEN 3
WHEN A BETWEEN 129 AND 161 THEN 4
ELSE null END) AS F
FROM pay_F
ORDER BY F DESC;
//1222 9月
create database spu_db;
drop table ex_spu;
create external table ex_spu
(
spu_id string,
shop_id string,
shop_name string,
category_name string,
spu_name string,
spu_price double,
spu_originprice double,
month_sales int,
praise_num int,
spu_unit string,
spu_desc string,
spu_image string
)
row format delimited fields terminated by ','
location 'hdfs://192.168.10.136:9000/app/data/exam2'
tblproperties ("skip.header.line.count" = "1");
drop table ex_spu;
select *
from ex_spu;
drop table ex_spu_hbase;
create external table ex_spu_hbase
(
key string,
sales double,
praise int
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,result:sales,result:praise")
TBLPROPERTIES ("hbase.table.name" = "exam2:spu");
insert overwrite table ex_spu_hbase
select concat(shop_id, shop_name) as key,
sales,
praise
from (select shop_id,
shop_name,
sum(spu_price * month_sales) as sales,
sum(praise_num) as praise
from ex_spu
group by shop_id, shop_name) t;
select *
from ex_spu_hbase;
drop table ex_spu_hbase;
select concat(shop_id, shop_name) as key,
sales,
praise
from (select shop_id,
shop_name,
sum(spu_price * month_sales) as sales,
sum(praise_num) as praise
from ex_spu
group by shop_id, shop_name) t;
select shop_id,
shop_name,
sum(spu_price * month_sales) as sales,
sum(praise_num) as praise
from ex_spu
group by shop_id, shop_name;
select *
from ex_spu;
select *
from ex_spu_hbase;
select *
from ex_spu;
select shop_name, count(*)
from ex_spu
group by shop_name;
//21年 2月份卷子
create database exam;
use exam;
create external table ex_exam_record
(
id string,
confirmedCount string,
recordDate string,
province string
)
row format delimited fields terminated by ','
stored as textfile
location '/app/data/exam4'
tblproperties ("skip.header.line.count" = "1");
select *
from ex_exam_record;
drop table ex_exam_record;
create external table ex_exam_covid19_record
(
key string,
confirmedCount string,
confirmIncrease string
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,record:confirmCount,record:confirmIncrease")
TBLPROPERTIES ("hbase.table.name" = "exam:covid19");
select *
from ex_exam_covid19_record;
insert into table ex_exam_covid19_record
select concat(province, recordDate), confirmedCount
from ex_exam_record;
select *
from ex_exam_record;
insert into table ex_exam_covid19_record
select concat(province, recordDate),
confirmedCount,
confirmedCount - if(pre_confirmedCount is null, 0, pre_confirmedCount)
from (select id,
confirmedCount,
recordDate,
province,
lag(confirmedCount) over (partition by province order by id) as pre_confirmedCount
from ex_exam_record) tb1;
//
create database exam03;
create external table exam03.test
(
id string,
confirmedCount string,
confirmedIncr string,
curedCount string,
curedIncr string,
currentConfirmedCount string,
currentConfirmedIncr string,
dateId string,
deadCount string,
deadIncr string,
suspectedCount string,
suspectedCountIncr string,
provinceName string,
provinceShortName string
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties
(
'separatorChar' = ',',
'quoteChar' = '\"',
'escapeChar' = '\\'
)
location '/app/data/exam3'
tblproperties ('skip.header.line.count' = '1');
select *
from exam03.test;
//12 23 20年7月卷子
create database exam;
use exam;
CREATE EXTERNAL TABLE IF NOT EXISTS ex_exam_record2
(
topic_id string,
student_id string,
question_id string,
score float
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE LOCATION '/app/data/result';
select *
from exam.ex_exam_record2;
CREATE EXTERNAL TABLE IF NOT EXISTS ex_exam_anlysis
(
student_id string,
total_score float,
question_count int,
accuracy float
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties ("hbase.columns.mapping" =
":key,accuracy:total_score,accuracy:question_count,accuracy:accuracy")
tblproperties ("hbase.table.name" = "exam:analysis");
select *
from ex_exam_anlysis;
CREATE EXTERNAL TABLE IF NOT EXISTS ex_exam_question
(
student_id string,
right string,
half string,
error string
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties ("hbase.columns.mapping" = ":key,question:right,question:half,question:error")
tblproperties ("hbase.table.name" = "exam:analysis");
insert into table ex_exam_anlysis
select student_id, sum(score) as total_score, count(1) as question_count, sum(score) / count(1) as accuracy
from ex_exam_record2
group by student_id;
select *
from ex_exam_anlysis;
select *
from ex_exam_question;
with table1 as
(select student_id, score, concat_ws(",", collect_set(question_id)) as questions_id
from ex_exam_record2
group by student_id, score),
r1 as (select student_id, questions_id from table1 where score = 1),
r2 as (select student_id, questions_id from table1 where score = 0),
r3 as (select student_id, questions_id from table1 where score = 0.5)
insert
into table ex_exam_question
select r1.student_id as student_id, r1.questions_id as right, r2.questions_id as error, r3.questions_id as half
from r1
full join r2 on r1.student_id = r2.student_id
full join r3 on r1.student_id = r3.student_id;
select *
from ex_exam_question;
//1223 2020 11 月卷子
use exam;
CREATE EXTERNAL TABLE IF NOT EXISTS ex_exam_record3
(
id string,
confirmedCount int,
confirmedIncr int,
recordDate string,
countryName string,
countryShortCode string,
continent string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/app/data/exam6';
select *
from ex_exam_record3;
CREATE EXTERNAL TABLE IF NOT EXISTS ex_exam_covid19_record
(
key string,
maxIncreaseCountry string,
maxIncreaseCount int
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties
("hbase.columns.mapping" = ":key,record:maxIncreaseCountry,record:maxIncreaseCount")
tblproperties ("hbase.table.name" = "exam:covid19_world");
select *
from ex_exam_covid19_record;
drop table ex_exam_covid19_record;
insert into table ex_exam_covid19_record
select concat(continent, recordDate), countryName, confirmedIncr
from (
select continent,
countryName,
recordDate,
confirmedIncr,
RANK() OVER (PARTITION BY
continent,recordDate ORDER BY confirmedIncr DESC) AS increaseId
from ex_exam_record3) tb
where tb.increaseId = 1;
select *
from ex_exam_covid19_record;
//1223
use exam;
show tables;
drop table exam.userbehavior;
create external table userbehavior
(
user_id string,
item_id string,
category_id string,
behavior_type string,
time string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
location '/app/data/exam';
select *
from userbehavior;
drop table userbehavior_hbase;
drop table userbehavior_hbase;
create external table userbehavior_hbase
(
user_id string,
item_id string,
category_id string,
behavior_type string,
time string
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties
("hbase.columns.mapping" = ":key,info:item_id,info:category_id,info:behavior_type,info:time")
tblproperties ("hbase.table.name" = "exam:userbehavior");
insert into table exam.userbehavior_hbase
select *
from exam.userbehavior;
select *
from userbehavior_hbase;
drop table userbehavior_partitioned;
create table userbehavior_partitioned
(
user_id string,
item_id string,
category_id string,
behavior_type string,
time string
) partitioned by (dt string)
stored as orc;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.dynamic.partition=true;
insert into table exam.userbehavior_partitioned partition (dt)
select user_id,
item_id,
category_id,
behavior_type,
from_unixtime(cast(time as INT), 'YYYY-MM-dd HH:mm:ss') as time,
substring(from_unixtime(cast(time as INT), 'YYYY-MM-dd HH:mm:ss'), 1, 10) dt
from exam.userbehavior;
desc exam.userbehavior_partitioned;
select *
from userbehavior_partitioned;
select *
from userbehavior_partitioned
where dt = '2017-11-10';
//1225
with t1 as (
select user_id, count(user_id) cou from exam.userbehavior_partitioned where behavior_type = 'buy' group by user_id
)
select user_id,
(case
when cou between 1 and 32 then 0
when cou between 33 and 64 then 1
when cou between 65 and 96 then 2
when cou between 97 and 128 then 3
when cou between 129 and 161 then 4
else null end
) as t
from t1
order by t desc;
with t1 as (
select user_id, datediff('2017-12-03', max(`time`)) dif
from exam.userbehavior_partitioned
where behavior_type = 'buy'
and dt >= '2017-11-03'
group by user_id
)
select user_id,
(case
when dif between 0 and 6 then 4
when dif between 7 and 12 then 3
when dif between 13 and 18 then 2
when dif between 19 and 24 then 1
when dif between 25 and 30 then 0
else null end) dif
from t1
order by dif desc;
//1226
use exam;
drop table exam.ex_exam_record;
drop table exam.ex_exam_anlysis;
drop table ex_exam_question;
create external table ex_exam_record
(
topic_id string,
student_id string,
question_id string,
score string
)
row format delimited fields terminated by '\t'
location 'hdfs://192.168.10.136:9000/app/data/result';
select *
from exam.ex_exam_record;
drop table ex_exam_record;
select * from exam.ex_exam_record;
create external table ex_exam_anlysis
(
student_id string,
total_score float,
question_count int,
accuracy float
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties
("hbase.columns.mapping" = ":key,accuracy:total_score,accuracy:question_count,accuracy:accuracy")
tblproperties ("hbase.table.name" = "exam:analysis");
drop table ex_exam_question;
create external table ex_exam_question(
student_id string,
right string,
half string,
error string
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties
("hbase.columns.mapping" = ":key,question:right,question:half,question:error")
tblproperties ("hbase.table.name" = "exam:analysis");
select * from ex_exam_question;
insert into table ex_exam_question
select student_id,
(select count(*) from exam.ex_exam_record where score=1 group by score )
from exam.ex_exam_record group by score;
use exam;
with table1 as
(select student_id,score, concat_ws(",",collect_set(question_id)) as questions_id from exam.ex_exam_record group by student_id,score)
, r1 as (select student_id, questions_id from table1 where score = 1),
r2 as (select student_id, questions_id from table1 where score = 0),
r3 as (select student_id, questions_id from table1 where score = 0.5)
select r1.student_id as student_id, r1.questions_id as right,
r2.questions_id as error, r3.questions_id as half from r1 full join r2 on r1.student_id = r2.student_id
full join r3 on r1.student_id = r3.student_id;
select student_id,score, concat_ws(",",collect_set(question_id)) as questions_id from exam.ex_exam_record group by student_id,score;
with table1 as
(select student_id,score, concat_ws(",",collect_set(question_id)) as questions_id from exam.ex_exam_record group by student_id,score)
select student_id, questions_id from table1 where score = 1;
//1226 exam 2021 11
create database market;
use market;
create external table ex_allprovinces(
name string,
abbr string
)
row format delimited fields terminated by '\t'
location '/app/data/allprovinces/';
select * from ex_allprovinces;
use market;
drop table ex_products;
create external table ex_products(
name string,
price float,
craw_time string,
market string,
province string,
city string
)
row format delimited fields terminated by '\t'
location '/app/data/events/products';
select * from ex_products;
use market;
drop table ex_province_market;
create external table ex_province_market(
rowkey string,
marketCount string,
provicneName string
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties
("hbase.columns.mapping" = ":key,market:count,info:name")
tblproperties ("hbase.table.name" = "exam:province_market");
with t1
as (
select province,count(distinct(market))con from ex_products where province!="" group by province )
insert into table ex_province_market
select t2.abbr,t1.con,t1.province from t1 inner join ex_allprovinces t2 on t1.province=t2.name ;
select count(distinct province) from ex_products where province!=""or province is not null;
show databases ;
select * from
(select 1 from dwd_sale.dwd_customers)a
join
(select 2 from dwd_sale.dwd_customers)b;
select * from
(select distinct user_id from train limit 3)a
join
(select distinct invited from train limit 3)b;
//1230自己重新做一遍项目 用户兴趣预测项目 从ods hbase 映射 hive开始做
create database ods1_personas_raw;
create database ods1_personas;
use ods1_personas;
create external table ods1_personas.ods1_hbase_eventattendees
(
id string,
userid string,
friendid string,
attendtype string
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties
("hbase.columns.mapping" = ":key,base:user_id,base:friend_id,base:attend_type")
tblproperties ("hbase.table.name" = "exp:hbase_eventAttendees");
select *
from ods1_personas.ods1_hbase_eventattendees;
create external table ods1_personas.ods1_hbase_events
(
id string,
eventid string,
userid string,
starttime string,
zip string,
city string,
state string,
country string,
lat string,
lng string,
other string
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties
("hbase.columns.mapping" =
":key,base:event_id,base:user_id,base:start_time,base:zip,base:city,base:state,base:country,base:lat,base:lng,base:other")
tblproperties ("hbase.table.name" = "exp:hbase_events");
create external table ods1_personas.ods1_hbase_users
(
id string,
userid string,
birthyear string,
gender string,
joinedat string,
locale string,
location string,
timezone string
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties
("hbase.columns.mapping" =
":key,base:user_id,base:birthyear,base:gender,base:joinedAt,base:locale,base:location,base:timezone")
tblproperties ("hbase.table.name" = "exp:hbase_users");
create external table ods1_personas.ods1_hbase_userfriends
(
id string,
userid string,
friendid string
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties
("hbase.columns.mapping" =
":key,base:user_id,base:friend_id")
tblproperties ("hbase.table.name" = "exp:hbase_userFriends");
select *
from ods1_hbase_userfriends;
create external table ods1_personas.ods1_hbase_train
(
id string,
userid string,
eventid string,
invited string,
time string,
interested string,
not_interested string
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties
("hbase.columns.mapping" =
":key,base:user_id,base:event,base:invited,base:timestamp,base:interested,base:not_interested")
tblproperties ("hbase.table.name" = "exp:hbase_train");
use ods1_personas_raw;
drop table ods1_personas_raw.ods_eventAttendees;
create table ods1_personas_raw.ods_eventAttendees(
userid string,
friendid string,
attendtype string
)
row format delimited fields terminated by ','
stored as orc;
insert overwrite table ods1_personas_raw.ods_eventAttendees select userid,friendid,attendtype from ods1_personas.ods1_hbase_eventAttendees;
select * from ods1_personas_raw.ods_eventAttendees;
drop table ods1_personas_raw.ods_events;
create table ods1_personas_raw.ods_events(
eventid string,
userid string,
starttime string,
zip string,
city string,
state string,
country string,
lat string,
lng string,
other string
)
row format delimited fields terminated by ','
stored as orc;
insert overwrite table ods1_personas_raw.ods_events
select eventid,userid,starttime,zip,city,state,country,lat,lng,other from ods1_personas.ods1_hbase_events;
select * from ods1_personas_raw.ods_events;
drop table ods1_personas_raw.ods_users;
create table ods1_personas_raw.ods_users
(
userid string,
birthyear string,
gender string,
joinedat string,
locale string,
location string,
timezone string
);
insert overwrite table ods1_personas_raw.ods_users
select userid,birthyear,gender,joinedat,locale,location,timezone from ods1_personas.ods1_hbase_users;
drop table ods1_personas_raw.ods_userfriends;
create table ods1_personas_raw.ods_userfriends
(
userid string,
friendid string
);
insert overwrite table ods1_personas_raw.ods_userfriends
select userid,friendid from ods1_personas.ods1_hbase_userfriends;
drop table ods1_personas_raw.ods_train;
create table ods1_personas_raw.ods_train
(
userid string,
eventid string,
invited string,
time string,
interested string,
not_interested string
);
insert overwrite table ods1_personas_raw.ods_train
select userid,eventid,invited,`time`,interested,not_interested from ods1_personas.ods1_hbase_train;
create external table ods1_personas_raw.ods_locale
(
localid string,
locale string
)
row format delimited fields terminated by '\t'
location '/users/locale/';
drop table ods1_personas_raw.ods_locale;
create table ods1_personas_raw.ods_locale(
localid string,
locale string
)
row format delimited fields terminated by '\t';
load data inpath '/users/locale/locale.txt' overwrite into table ods1_personas_raw.ods_locale;
create database dwd1_personas;
//
select * from ods1_personas_raw.ods_userfriends;
select * from ods1_personas_raw.ods_train;
select * from ods1_personas_raw.ods_eventattendees;
select * from ods1_personas_raw.ods_users;
select * from ods1_personas_raw.ods_locale;
select * from ods_personas_raw.ods_events;
select * from ods1_personas_raw.ods_events;
drop table ods1_personas_raw.ods_eventattendees;
select * from ods1_personas_raw.ods_events;
set hive.exec.mode.local.auto.inputbytes.max=172233234;
set hive.exec.mode.local.auto.inputbytes.max;
select * from ods1_personas_raw.ods_users where gender='female';
select * from ods1_personas_raw.ods_users where locale='es_MX';
set hive.exec.mode.local.auto=false;
select uf.userid,uf.friendid from ods_personas_raw.ods_userFriends uf inner join ods_personas_raw.ods_users u on uf.userid=u.userid;
select count(*) from ods_personas_raw.ods_userFriends;
select count(*) from ods1_personas_raw.ods_userfriends;
set hive.exec.mode.local.auto.inputbytes.max=999650903;
-- drop database if exists dwd_personas
-- create database dwd_personas
--ods_userFriends ods_eventAttendees 不需要清洗
-- 清洗用户 ods_users
-- 创建临时用户预测地址表(根据用户朋友的最多的地址找用户地址)
-- select * from ods_personas_raw.ods_users where locale='es_MX'
create temporary table dwd_personas.dwd_tmp_userAddr as
with
t1 as (select uf.userid,uf.friendid from ods_personas_raw.ods_userFriends uf inner join ods_personas_raw.ods_users u on uf.userid=u.userid ),
t2 as (select t.userid,t.friendid,u.location as friendaddr from t1 t inner join ods_personas_raw.ods_users u on t.friendid=u.userid where trim(u.location)!=''),
t3 as (select userid,friendaddr,count(friendid) fct from t2 group by userid,friendaddr),
t4 as (select userid,friendaddr,row_number() over(partition by userid order by fct desc) rank from t3)
select userid,friendaddr from t4 where rank=1;
with
t1 as (select uf.userid,uf.friendid from ods1_personas_raw.ods_userFriends uf inner join ods1_personas_raw.ods_users u on uf.userid=u.userid ),
t2 as (select t.userid,t.friendid,u.location as friendaddr from t1 t
inner join ods1_personas_raw.ods_users u on t.friendid=u.userid where trim(u.location)!=''),
t3 as (select userid,friendaddr,count(friendid) fct from t2 group by userid,friendaddr)
select userid,friendaddr,row_number() over(partition by userid order by fct desc) rank from t3;
show tables ;
select count(*) from ods_userfriends;
set hive.exec.mode.local.auto.inputbytes.max=999999999;
select * from exam.ex_exam_covid19_record;
drop database if exists exam;
show databases ;
show tables;
insert into xxx values (6,'asa',70);
select id,count(distinct month) cnt from xxx group by id order by cnt desc limit 5;
show tables ;
create table org(
ORG_NO string,
CHILD_ORG_NO String
);
create table tg(
tg_no string,
org_no string
);
create table final(
ORG_NO string,
TG_NUM int
);
select tg_no, org_no from tg;
insert into table final
select
;
drop table a;
create table a(
a1 string,
a2 string
);
create table b(
b1 string,
b2 string
);
insert into table b values ("a","c");
select * from b;
select * from a left join b on a.a1=b.b1;
select count(*) from org group by ORG_NO;
show databases ;
use mysql;
show tables ;
use mydemo;
show tables ;
drop table if exists `user_profile`;
drop table if exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
select device_id,gpa,age from user_profile order by gpa desc ,age desc ;
select t1.cnt1,t1.cnt2 from (select substring(date,1,7) newdate,count(distinct device_id) cnt1,count(question_id)cnt2 from question_practice_detail group by substring(date,1,7) )t1
where t1.newdate='2021-08';
# 125 sql 待定要优化 初始
select now() timestamp,
sku.skuid,
sku.modelid,
sku.skugls,
sku.skuprice,
sku.skuqty,
sku.fcreated,
model.modelali,
model.modelgls,
model.ftitle,
model.modelprice,
model.fpicurl,
model.modelqty,
model.fapprovestatus
from (select tt1.fid as skuid,
tt1.fparentid as modelid,
tt1.fouterid as skugls,
tt1.fprice as skuprice,
tt1.fquantity as skuqty,
tt1.fcreated,
tt1.rk
from (select t1.fid,
t1.fparentid,
t1.fouterid,
t1.fprice,
t1.fquantity,
t1.fcreated,
if(@glscode = t1.fouterid, @rank := @rank + 1, @rank := 1) as rk,
@glscode := t1.fouterid
from (select concat(a.sku_id, '') as fid,
concat(a.num_iid, '') as fparentid,
concat(a.sku_outer_id, '') as fouterid,
round(a.sku_price, 2) as fprice,
a.sku_quantity fquantity,
a.sku_created fcreated
from order_realtime.tb_sku_api_realtime as a
where (a.sku_outer_id is not null and a.sku_outer_id <> '2')
order by fouterid, fcreated desc) t1,
(select @glscode := null, @rank := 0) t2) tt1
where tt1.rk = 1) sku
left join(select tt1.fid as modelid,
tt1.fnumiid as modelali,
tt1.fouterid as modelgls,
tt1.ftitle,
tt1.fpicurl,
tt1.fprice as modelprice,
tt1.fnum as modelqty,
tt1.fapprovestatus,
tt1.fcreated,
tt1.rk
from (select t1.fid,
t1.fnumiid,
t1.fouterid,
t1.ftitle,
t1.fpicurl,
t1.fprice,
t1.fnum,
t1.fapprovestatus,
t1.fcreated,
if(@glscode = t1.fouterid, @rank := @rank + 1, @rank := 1) as rk,
@glscode := t1.fouterid
from (select concat(a.fid, '') as fid,
concat(a.fnumiid, '') as fnumiid,
concat(a.fouterid, '') as fouterid,
a.ftitle,
concat(a.fpicurl, '') as fpicurl,
round(a.fprice, 2) as fprice,
a.fnum,
a.fapprovestatus,
a.fcreated
from (select num_iid as fid,num_iid fnumiid,outer_id fouterid,title ftitle,pic_url fpicurl,price fprice,
num fnum, approve_status fapprovestatus,created fcreated
from order_realtime.tb_sku_api_realtime group by num_iid) as a
where a.fouterid is not null
order by a.fouterid, a.fcreated desc) t1,
(select @glscode := null, @rank := 0) t2) tt1
where tt1.rk = 1) model on sku.modelid = model.modelid
order by sku.modelid;
select count(1),count(distinct concat(num_iid,sku_id))
from order_realtime.tb_sku_api_realtime;
select count(1)
from (select num_iid
from order_realtime.tb_sku_api_realtime
group by num_iid)t;
#124
select str_to_date(ee.order_consign_time, '%Y-%m-%d %H:%i:%s') as FCONSIGNTIME,
now() as TimeStamp,
aa.oid as fid,
0 as fadvanceStatus,
null as falipayno,
aa.attribute as fattribute,
aa.buyer_nick as fbuyernick,
aa.created as fcreated,
null as fcsstatus,
aa.good_status as fgoodstatus,
if(aa.has_good_return, 1, 0) as fhasgoodreturn,
aa.modified as fmodified,
aa.num as fnum,
null as fnumiid,
aa.oid as foid,
aa.operation_contraint as foperationcontraint,
aa.order_status as forderstatus,
aa.outer_id as fouterid,
aa.payment as fpayment,
aa.price as fprice,
aa.reason as freason,
aa.refund_fee as frefundfee,
aa.refund_id as frefundid,
aa.refund_phase as frefundphase,
aa.refund_version as frefundversion,
aa.seller_nick as fsellernick,
aa.sku as fsku,
aa.status as fstatus,
aa.tid as ftid,
aa.title as ftitle,
'1' as fplatformid,
'aaf08dbafc46482f928d25a65337a5c4' as fplatformshopid,
aa.created as fcreatetime,
aa.last_update_time as flastupdatetime,
aa.total_fee as ftotalfee,
null as fsaleReturnsId,
null as fsaleReturnsNumber,
1 as frefundProcessStatus,
null as fsyncFlag,
null as fsyncMessage,
null as fversion,
aa.sid as fsid,
0 as ftenantid,
null as fisReject,
null as fagRefundFlag,
null as fagRefundMessage,
0 as fagRefundFailCount,
aa.company_name as fcompanyName,
null as fgoodReturnTime,
concat(aa.oid, '') as OID
from order_realtime.tb_refund_topic_api_realtime as aa
inner join (select bb.oid FOID, max(bb.modified) as max_modify_time
from order_realtime.tb_refund_topic_api_realtime as bb
where bb.oid in (select oid FOID
from order_realtime.tb_refund_topic_api_realtime
where created >= concat(CURRENT_DATE - interval 1 month, ' 00:00:00')
group by FOID)
group by bb.oid) cc on aa.modified = cc.max_modify_time and aa.oid = cc.FOID
left join order_realtime.tb_order_api_fullinfo_realtime ee on aa.refund_id = ee.order_refund_id
where aa.created >= concat(CURRENT_DATE - interval 1 month, ' 00:00:00')
;
select count(1)
from order_realtime.tb_refund_topic_api_realtime as aa
inner join (select bb.oid FOID, max(bb.modified) as max_modify_time
from order_realtime.tb_refund_topic_api_realtime as bb
where bb.oid in (select oid FOID
from order_realtime.tb_refund_topic_api_realtime
where created >= concat(CURRENT_DATE - interval 1 month, ' 00:00:00')
group by FOID)
group by bb.oid) cc on aa.modified = cc.max_modify_time and aa.oid = cc.FOID
left join order_realtime.tb_order_api_fullinfo_realtime ee on aa.refund_id = ee.order_refund_id
where aa.created >= concat(CURRENT_DATE - interval 1 month, ' 00:00:00')
;
select count(1),count(distinct refund_id)
from
order_realtime.tb_refund_topic_api_realtime where created>'2023-07-01';
select count(1)
from
order_realtime.tb_order_api_fullinfo_realtime where created>'2023-07-01';
select count(1)
from
(select tid
from
order_realtime.tb_order_api_fullinfo_realtime
where created>'2023-07-01'
group by tid)t;
# 121
select FID,
FADJUSTFEE,
FCONSIGNTIME,
FDISCOUNTFEE,
FENDTIME,
FINVOICENO,
FLOGISTICSCOMPANY,
FNUM,
FOID,
FORDERFROM,
FOUTERIID,
FOUTERSKUID,
FPAYMENT,
FPICPATH,
FPRICE,
FREFUNDID,
FREFUNDSTATUS,
FSKUPROPERTIESNAME,
FSTATUS,
FTITLE,
FTOTALFEE,
buyernick,
FCREATED,
FPAYTIME,
FTYPE,
FTID,
FRECEIVERSTATE,
FRECEIVERCITY,
FRECEIVERDISTRICT,
FSTEPPAIDFEE,
FSTEPTRADESTATUS,
step_flag,
CURRENT_TIMESTAMP as update_time
from (select a.order_oid fid,
round(a.order_adjust_fee, 2) as `FADJUSTFEE`,
str_to_date(a.order_consign_time, '%Y-%m-%d %H:%i:%s') as `FCONSIGNTIME`,
round(a.`order_discount_fee`, 2) as `FDISCOUNTFEE`,
a.order_end_time FENDTIME,
a.order_invoice_no FINVOICENO,
a.order_logistics_company FLOGISTICSCOMPANY,
a.order_num FNUM,
concat(a.order_oid, '') as `FOID`,
a.order_order_from FORDERFROM,
a.order_outer_iid FOUTERIID,
a.order_outer_sku_id FOUTERSKUID,
round(a.`order_payment`, 2) as `FPAYMENT`,
a.order_pic_path FPICPATH,
round(a.`order_price`, 2) as `FPRICE`,
a.order_refund_id FREFUNDID,
a.order_refund_status FREFUNDSTATUS,
a.order_sku_properties_name FSKUPROPERTIESNAME,
a.order_status FSTATUS,
a.order_title FTITLE,
round(a.order_total_fee, 2) as `FTOTALFEE`,
case when (a.pay_time is not null and a.order_status = 'WAIT_BUYER_PAY') then 1 else 0 end as `step_flag`,
a.buyer_nick as `buyernick`,
a.created as `FCREATED`,
a.pay_time as `FPAYTIME`,
a.type as `FTYPE`,
concat(a.tid, '') as `FTID`,
a.receiver_state as `FRECEIVERSTATE`,
a.receiver_city as `FRECEIVERCITY`,
a.receiver_district as `FRECEIVERDISTRICT`,
round(a.step_paid_fee, 2) as `FSTEPPAIDFEE`,
a.step_trade_status as `FSTEPTRADESTATUS`
from order_realtime.tb_order_api_fullinfo_realtime as a
where a.pay_time >= concat(CURRENT_DATE, ' 00:00:00')) as sales;
select count(1)
from
order_realtime.tb_order_api_fullinfo_realtime a
where a.pay_time >= concat('2023-07-01', ' 00:00:00') and
a.pay_time <= concat('2023-08-01', ' 00:00:00') ;
# 120 待优化索引
select sku.skuid,
sku.skugls,
sku.skuqty,
model.modelali,
model.ftitle,
model.fpicurl,
model.modelqty,
sku.modelid,
sku.skuprice,
sku.fcreated,
model.modelgls,
model.modelprice,
model.fapprovestatus,
current_timestamp as update_time
from (select tt1.fid as skuid,
tt1.fparentid as modelid,
tt1.fouterid as skugls,
tt1.fprice as skuprice,
tt1.fquantity as skuqty,
tt1.fcreated,
tt1.rk
from (select t1.fid,
t1.fparentid,
t1.fouterid,
t1.fprice,
t1.fquantity,
t1.fcreated,
if(@glscode = t1.fouterid, @rank := @rank + 1, @rank := 1) as rk,
@glscode := t1.fouterid
from (select concat(a.sku_id, '') as fid,
concat(a.num_iid, '') as fparentid,
concat(a.sku_outer_id, '') as fouterid,
round(a.sku_price, 2) as fprice,
a.sku_quantity fquantity,
a.sku_created fcreated
from order_realtime.tb_sku_api_realtime as a
where (a.sku_outer_id is not null and a.sku_outer_id <> '2')
order by fouterid, fcreated desc) t1,
(select @glscode := null, @rank := 0) t2) tt1
where tt1.rk = 1) sku
left join (select tt1.fid as modelid,
tt1.fnumiid as modelali,
tt1.fouterid as modelgls,
tt1.ftitle,
tt1.fpicurl,
tt1.fprice as modelprice,
tt1.fnum as modelqty,
tt1.fapprovestatus,
tt1.fcreated,
tt1.rk
from (select t1.fid,
t1.fnumiid,
t1.fouterid,
t1.ftitle,
t1.fpicurl,
t1.fprice,
t1.fnum,
t1.fapprovestatus,
t1.fcreated,
if(@glscode = t1.fouterid, @rank := @rank + 1, @rank := 1) as rk,
@glscode := t1.fouterid
from (select concat(a.fid, '') as fid,
concat(a.fnumiid, '') as fnumiid,
concat(a.fouterid, '') as fouterid,
a.ftitle,
concat(a.fpicurl, '') as fpicurl,
round(a.fprice, 2) as fprice,
a.fnum,
a.fapprovestatus,
a.fcreated
from (select num_iid as fid,num_iid fnumiid,outer_id fouterid,title ftitle,pic_url fpicurl,price fprice,
num fnum, approve_status fapprovestatus,created fcreated
from order_realtime.tb_sku_api_realtime group by num_iid) as a
where a.fouterid is not null
order by a.fouterid, a.fcreated desc) t1,
(select @glscode := null, @rank := 0) t2) tt1
where tt1.rk = 1) model on sku.modelid = model.modelid
order by sku.modelid
;
# 118 待优化索引
select sku.skuid, sku.skugls, sku.skuqty, model.modelali, model.ftitle, model.fpicurl, model.modelqty
from (select tt1.fid as skuid,
tt1.fparentid as modelid,
tt1.fouterid as skugls,
tt1.fprice as skuprice,
tt1.fquantity as skuqty,
tt1.fcreated,
tt1.rk
from (select t1.fid,
t1.fparentid,
t1.fouterid,
t1.fprice,
t1.fquantity,
t1.fcreated,
if(@glscode = t1.fouterid, @rank := @rank + 1, @rank := 1) as rk,
@glscode := t1.fouterid
from (select concat(a.sku_id, '') as fid,
concat(a.num_iid, '') as fparentid,
concat(a.sku_outer_id, '') as fouterid,
round(a.sku_price, 2) as fprice,
a.sku_quantity fquantity,
a.sku_created fcreated
from order_realtime.tb_sku_api_realtime as a
where (a.sku_outer_id is not null and a.sku_outer_id <> '2')
order by fouterid, sku_modified desc) t1,
(select @glscode := null, @rank := 0) t2) tt1
where tt1.rk = 1) sku
left join(select tt1.fid as modelid,
tt1.fnumiid as modelali,
tt1.fouterid as modelgls,
tt1.ftitle,
tt1.fpicurl,
tt1.fprice as modelprice,
tt1.fnum as modelqty,
tt1.fapprovestatus,
tt1.fcreated,
tt1.rk
from (select t1.fid,
t1.fnumiid,
t1.fouterid,
t1.ftitle,
t1.fpicurl,
t1.fprice,
t1.fnum,
t1.fapprovestatus,
t1.fcreated,
if(@glscode = t1.fouterid, @rank := @rank + 1, @rank := 1) as rk,
@glscode := t1.fouterid
from (select concat(a.fid, '') as fid,
concat(a.fnumiid, '') as fnumiid,
concat(a.fouterid, '') as fouterid,
a.ftitle,
concat(a.fpicurl, '') as fpicurl,
round(a.fprice, 2) as fprice,
a.fnum,
a.fapprovestatus,
a.fcreated
from (select num_iid as fid,num_iid fnumiid,outer_id fouterid,title ftitle,pic_url fpicurl,price fprice,
num fnum, approve_status fapprovestatus,created fcreated,modified fmodified
from order_realtime.tb_sku_api_realtime group by num_iid) as a
where a.fouterid is not null
order by a.fouterid, a.fmodified desc) t1,
(select @glscode := null, @rank := 0) t2) tt1
where tt1.rk = 1) model on sku.modelid = model.modelid
order by sku.modelid
;
# 117
select cc.fid,
cc.fbuyernick,
cc.fcreated,
cc.fdiscountfee,
cc.fendtime,
cc.fnum,
cc.pay_time,
cc.fpayment,
cc.receiver_address,
cc.receiver_state,
cc.receiver_city,
cc.receiver_district,
cc.status,
cc.tid,
cc.foid,
cc.type,
cc.fsteppaidfee,
cc.presell_pay_status,
cc.fouteriid,
cc.fouterskuid,
hh.ftitle as ftitle_modified,
current_timestamp
from (select bb.tid fid,
bb.buyer_nick fbuyernick,
bb.created fcreated,
round(cast(bb.order_discount_fee as decimal), 2) as fdiscountfee,
bb.order_end_time fendtime,
bb.order_num fnum,
bb.pay_time,
round(cast(bb.order_payment as decimal), 2) as fpayment,
bb.receiver_address,
bb.receiver_state,
bb.receiver_city,
bb.receiver_district,
bb.status,
bb.tid,
cast(bb.order_oid as char) as foid,
bb.type,
round(cast(case when bb.order_payment = 0 then 0 else bb.step_paid_fee end as decimal), 2) as fsteppaidfee,
bb.step_trade_status as presell_pay_status,
bb.order_outer_iid fouteriid,
bb.order_outer_sku_id fouterskuid
from order_realtime.tb_order_api_fullinfo_realtime as bb
where bb.order_type = 'step'
and bb.pay_time >= concat(current_date - interval 1 month, ' 00:00:00')
and bb.pay_time < date_add(concat(current_date, ' 00:00:00'), interval 1 day)) as cc
left join (select ee.fouterskuid, ff.ftitle
from (select max(bb.created) as maxcreatetime, bb.order_outer_sku_id as fouterskuid
from order_realtime.tb_order_api_fullinfo_realtime as bb
where bb.type = 'step'
and bb.pay_time >= concat(current_date - interval 1 month, ' 00:00:00')
and bb.pay_time < date_add(concat(current_date, ' 00:00:00'), interval 1 day)
group by bb.order_outer_sku_id) as ee
join (select bb.created fcreated, bb.order_title ftitle, bb.order_sku_id fouterskuid
from order_realtime.tb_order_api_fullinfo_realtime as bb
where bb.type = 'step'
and bb.pay_time >= concat(current_date - interval 1 month, ' 00:00:00')
and bb.pay_time < date_add(concat(current_date, ' 00:00:00'), interval 1 day)) ff
on ee.fouterskuid = ff.fouterskuid and ee.maxcreatetime = ff.fcreated
group by ee.fouterskuid, ff.ftitle) hh on cc.fouterskuid = hh.fouterskuid
;
select count(1)
from (select bb.tid fid,
bb.buyer_nick fbuyernick,
bb.created fcreated,
round(cast(bb.order_discount_fee as decimal), 2) as fdiscountfee,
bb.order_end_time fendtime,
bb.order_num fnum,
bb.pay_time,
round(cast(bb.order_payment as decimal), 2) as fpayment,
bb.receiver_address,
bb.receiver_state,
bb.receiver_city,
bb.receiver_district,
bb.status,
bb.tid,
cast(bb.order_oid as char) as foid,
bb.type,
round(cast(case when bb.order_payment = 0 then 0 else bb.step_paid_fee end as decimal), 2) as fsteppaidfee,
bb.step_trade_status as presell_pay_status,
bb.order_outer_iid fouteriid,
bb.order_outer_sku_id fouterskuid
from order_realtime.tb_order_api_fullinfo_realtime as bb
where bb.order_type = 'step'
and bb.pay_time >= concat(current_date - interval 1 month, ' 00:00:00')
and bb.pay_time < date_add(concat(current_date, ' 00:00:00'), interval 1 day)) as cc
left join (select ee.fouterskuid, ff.ftitle
from (select max(bb.created) as maxcreatetime, bb.order_outer_sku_id as fouterskuid
from order_realtime.tb_order_api_fullinfo_realtime as bb
where bb.type = 'step'
and bb.pay_time >= concat(current_date - interval 1 month, ' 00:00:00')
and bb.pay_time < date_add(concat(current_date, ' 00:00:00'), interval 1 day)
group by bb.order_outer_sku_id) as ee
join (select bb.created fcreated, bb.order_title ftitle, bb.order_sku_id fouterskuid
from order_realtime.tb_order_api_fullinfo_realtime as bb
where bb.type = 'step'
and bb.pay_time >= concat(current_date - interval 1 month, ' 00:00:00')
and bb.pay_time < date_add(concat(current_date, ' 00:00:00'), interval 1 day)) ff
on ee.fouterskuid = ff.fouterskuid and ee.maxcreatetime = ff.fcreated
group by ee.fouterskuid, ff.ftitle) hh on cc.fouterskuid = hh.fouterskuid;
# 115
select cc.fid, cc.fbuyernick, cc.fbuyeralipayno, cc.fbuyeropenuid, cc.fcreated, cc.fdiscountfee, cc.fendtime, cc.fnum, cc.fpaytime, cc.fpayment, cc.fpicpath, cc.freceiveraddress, cc.freceiverstate, cc.freceivercity, cc.freceiverdistrict, cc.fstatus, cc.ftid, cc.foid, cc.ftype, cc.fsteppaidfee, cc.presellpaystatus, cc.fouteriid, cc.ftitle, cc.fouterskuid, cc.fnumiid, hh.ftitle as ftitle_modified, now() as updated_time from (select bb.tid fid, bb.buyer_nick fbuyernick, null fbuyeralipayno, bb.buyer_open_uid fbuyeropenuid, bb.created fcreated, round(cast(bb.order_discount_fee as decimal), 2) as fdiscountfee, bb.order_end_time fendtime, bb.order_num fnum, bb.pay_time fpaytime, round(cast(bb.order_payment as decimal), 2) as fpayment, bb.order_pic_path fpicpath, bb.receiver_address freceiveraddress, bb.receiver_state freceiverstate, bb.receiver_city freceivercity, bb.receiver_district freceiverdistrict, bb.status fstatus, cast(bb.tid as char) as ftid, cast(bb.order_oid as char) as foid, bb.type ftype, round(cast(case when bb.order_payment = 0 then 0 else bb.step_paid_fee end as decimal), 2) as fsteppaidfee, bb.step_trade_status as presellpaystatus, bb.order_outer_iid fouteriid, bb.order_title ftitle, bb.order_outer_sku_id fouterskuid, cast(bb.order_num_iid as char) as fnumiid from order_realtime.tb_order_api_fullinfo_realtime bb where bb.type = 'step' and bb.pay_time >= concat(current_date, ' 00:00:00') and bb.pay_time < date_add(concat(current_date, ' 00:00:00'), interval 1 day)) as cc left join (select gg.fouterskuid, gg.ftitle from (select ee.fouterskuid, ff.ftitle from (select dd.fouterskuid, max(dd.fcreated) as maxcreatetime from (select bb.created fcreated, bb.order_outer_sku_id fouterskuid from order_realtime.tb_order_api_fullinfo_realtime bb where bb.type = 'step' and bb.order_outer_sku_id in (select bb.order_outer_sku_id fouterskuid from order_realtime.tb_order_api_fullinfo_realtime bb where bb.type = 'step' and bb.pay_time >= concat(current_date, ' 00:00:00') and bb.pay_time < date_add(concat(current_date, ' 00:00:00'), interval 1 day) group by bb.order_outer_sku_id)) as dd group by dd.fouterskuid) as ee join (select bb.created fcreated, bb.order_title ftitle, bb.order_outer_sku_id fouterskuid from order_realtime.tb_order_api_fullinfo_realtime bb where bb.type = 'step' and bb.order_outer_sku_id in (select bb.order_outer_sku_id fouterskuid from order_realtime.tb_order_api_fullinfo_realtime bb where bb.type = 'step' and bb.pay_time >= concat(current_date, ' 00:00:00') and bb.pay_time < date_add(concat(current_date, ' 00:00:00'), interval 1 day) group by bb.order_outer_sku_id)) ff on ee.fouterskuid = ff.fouterskuid and ee.maxcreatetime = ff.fcreated) gg group by gg.fouterskuid, gg.ftitle) hh on cc.fouterskuid = hh.fouterskuid
;
select cc.fid,
cc.fbuyernick,
cc.fbuyeralipayno,
cc.fbuyeropenuid,
cc.fcreated,
cc.fdiscountfee,
cc.fendtime,
cc.fnum,
cc.fpaytime,
cc.fpayment,
cc.fpicpath,
cc.freceiveraddress,
cc.freceiverstate,
cc.freceivercity,
cc.freceiverdistrict,
cc.fstatus,
cc.ftid,
cc.foid,
cc.ftype,
cc.fsteppaidfee,
cc.presellpaystatus,
cc.fouteriid,
cc.ftitle,
cc.fouterskuid,
cc.fnumiid,
hh.ftitle as ftitle_modified,
now() as updated_time
from (select bb.tid fid,
bb.buyer_nick fbuyernick,
null fbuyeralipayno,
bb.buyer_open_uid fbuyeropenuid,
bb.created fcreated,
round(cast(bb.order_discount_fee as decimal), 2) as fdiscountfee,
bb.order_end_time fendtime,
bb.order_num fnum,
bb.pay_time fpaytime,
round(cast(bb.order_payment as decimal), 2) as fpayment,
bb.order_pic_path fpicpath,
bb.receiver_address freceiveraddress,
bb.receiver_state freceiverstate,
bb.receiver_city freceivercity,
bb.receiver_district freceiverdistrict,
bb.status fstatus,
cast(bb.tid as char) as ftid,
cast(bb.order_oid as char) as foid,
bb.type ftype,
round(cast(case when bb.order_payment = 0 then 0 else bb.step_paid_fee end as decimal), 2) as fsteppaidfee,
bb.step_trade_status as presellpaystatus,
bb.order_outer_iid fouteriid,
bb.order_title ftitle,
bb.order_outer_sku_id fouterskuid,
cast(bb.order_num_iid as char) as fnumiid
from order_realtime.tb_order_api_fullinfo_realtime bb
where bb.type = 'step'
and bb.pay_time >= concat(current_date, ' 00:00:00')
and bb.pay_time < date_add(concat(current_date, ' 00:00:00'), interval 1 day)) as cc
left join (select gg.fouterskuid, gg.ftitle
from (select ee.fouterskuid, ff.ftitle
from (select dd.fouterskuid, max(dd.fcreated) as maxcreatetime
from (select bb.created fcreated, bb.order_outer_sku_id fouterskuid
from order_realtime.tb_order_api_fullinfo_realtime bb
where bb.type = 'step'
and bb.order_outer_sku_id in (select bb.order_outer_sku_id fouterskuid
from order_realtime.tb_order_api_fullinfo_realtime bb
where bb.type = 'step'
and bb.pay_time >= concat(current_date, ' 00:00:00')
and bb.pay_time <
date_add(concat(current_date, ' 00:00:00'), interval 1 day)
group by bb.order_outer_sku_id)) as dd
group by dd.fouterskuid) as ee
join (select bb.created fcreated,
bb.order_title ftitle,
bb.order_outer_sku_id fouterskuid
from order_realtime.tb_order_api_fullinfo_realtime bb
where bb.type = 'step'
and bb.order_outer_sku_id in (select bb.order_outer_sku_id fouterskuid
from order_realtime.tb_order_api_fullinfo_realtime bb
where bb.type = 'step'
and bb.pay_time >= concat(current_date, ' 00:00:00')
and bb.pay_time <
date_add(concat(current_date, ' 00:00:00'), interval 1 day)
group by bb.order_outer_sku_id)) ff
on ee.fouterskuid = ff.fouterskuid and ee.maxcreatetime = ff.fcreated) gg
group by gg.fouterskuid, gg.ftitle) hh on cc.fouterskuid = hh.fouterskuid
;
# 125 sql 待定要优化 初始
select now() timestamp, sku.skuid, sku.modelid, sku.skugls, sku.skuprice, sku.skuqty, sku.fcreated, model.modelali, model.modelgls, model.ftitle, model.modelprice, model.fpicurl, model.modelqty, model.fapprovestatus from (select tt1.fid as skuid, tt1.fparentid as modelid, tt1.fouterid as skugls, tt1.fprice as skuprice, tt1.fquantity as skuqty, tt1.fcreated, tt1.rk from (select t1.fid, t1.fparentid, t1.fouterid, t1.fprice, t1.fquantity, t1.fcreated, if(@glscode = t1.fouterid, @rank := @rank + 1, @rank := 1) as rk, @glscode := t1.fouterid from (select concat(a.sku_id, '') as fid, concat(a.num_iid, '') as fparentid, concat(a.sku_outer_id, '') as fouterid, round(a.sku_price, 2) as fprice, a.sku_quantity fquantity, a.sku_created fcreated from order_realtime.tb_sku_api_realtime as a where (a.sku_outer_id is not null and a.sku_outer_id <> '2') order by fouterid, fcreated desc) t1 join (select sku_outer_id fouterid,max(sku_created) fcreated from order_realtime.tb_sku_api_realtime group by sku_outer_id) t2 on t1.fouterid=t2.fouterid and t1.fcreated=t2.fcreated ) tt1 where tt1.rk = 1) sku left join(select tt1.fid as modelid, tt1.fnumiid as modelali, tt1.fouterid as modelgls, tt1.ftitle, tt1.fpicurl, tt1.fprice as modelprice, tt1.fnum as modelqty, tt1.fapprovestatus, tt1.fcreated from (select t1.fid, t1.fnumiid, t1.fouterid, t1.ftitle, t1.fpicurl, t1.fprice, t1.fnum, t1.fapprovestatus, t1.fcreated from (select concat(a.fid, '') as fid, concat(a.fnumiid, '') as fnumiid, concat(a.fouterid, '') as fouterid, a.ftitle, concat(a.fpicurl, '') as fpicurl, round(a.fprice, 2) as fprice, a.fnum, a.fapprovestatus, a.fcreated from (select num_iid as fid,num_iid fnumiid,outer_id fouterid,title ftitle,pic_url fpicurl,price fprice, num fnum, approve_status fapprovestatus,created fcreated from order_realtime.tb_sku_api_realtime where outer_id is not null group by num_iid) as a order by a.fouterid, a.fcreated desc) t1 join ( select outer_id fouterid,max(created) as fcreated from order_realtime.tb_sku_api_realtime group by fouterid ) t2 on t1.fouterid=t2.fouterid and t1.fcreated=t2.fcreated ) tt1 ) model on sku.modelid = model.modelid order by sku.modelid;
# 120 待优化索引
select sku.skuid, sku.skugls, sku.skuqty, model.modelali, model.ftitle, model.fpicurl, model.modelqty, sku.modelid, sku.skuprice, sku.fcreated, model.modelgls, model.modelprice, model.fapprovestatus, current_timestamp as update_time from (select tt1.fid as skuid, tt1.fparentid as modelid, tt1.fouterid as skugls, tt1.fprice as skuprice, tt1.fquantity as skuqty, tt1.fcreated, tt1.rk from (select t1.fid, t1.fparentid, t1.fouterid, t1.fprice, t1.fquantity, t1.fcreated, if(@glscode = t1.fouterid, @rank := @rank + 1, @rank := 1) as rk, @glscode := t1.fouterid from (select concat(a.sku_id, '') as fid, concat(a.num_iid, '') as fparentid, concat(a.sku_outer_id, '') as fouterid, round(a.sku_price, 2) as fprice, a.sku_quantity fquantity, a.sku_created fcreated from order_realtime.tb_sku_api_realtime as a where (a.sku_outer_id is not null and a.sku_outer_id <> '2') order by fouterid, fcreated desc) t1, (select @glscode := null, @rank := 0) t2) tt1 where tt1.rk = 1) sku left join (select tt1.fid as modelid, tt1.fnumiid as modelali, tt1.fouterid as modelgls, tt1.ftitle, tt1.fpicurl, tt1.fprice as modelprice, tt1.fnum as modelqty, tt1.fapprovestatus, tt1.fcreated from (select t1.fid, t1.fnumiid, t1.fouterid, t1.ftitle, t1.fpicurl, t1.fprice, t1.fnum, t1.fapprovestatus, t1.fcreated from (select concat(a.fid, '') as fid, concat(a.fnumiid, '') as fnumiid, concat(a.fouterid, '') as fouterid, a.ftitle, concat(a.fpicurl, '') as fpicurl, round(a.fprice, 2) as fprice, a.fnum, a.fapprovestatus, a.fcreated from (select num_iid as fid,num_iid fnumiid,outer_id fouterid,title ftitle,pic_url fpicurl,price fprice, num fnum, approve_status fapprovestatus,created fcreated from order_realtime.tb_sku_api_realtime group by num_iid) as a where a.fouterid is not null order by a.fouterid, a.fcreated desc) t1 join ( select outer_id fouterid,max(created) as fcreated from order_realtime.tb_sku_api_realtime group by fouterid ) t2 on t1.fouterid=t2.fouterid and t1.fcreated=t2.fcreated ) tt1 ) model on sku.modelid = model.modelid order by sku.modelid
;
# 118 待优化索引
select sku.skuid,sku.skugls,sku.skuqty,model.modelali,model.ftitle,model.fpicurl,model.modelqty from (select tt1.fid as skuid, tt1.fparentid as modelid, tt1.fouterid as skugls, tt1.fprice as skuprice, tt1.fquantity as skuqty, tt1.fcreated, tt1.rk from (select t1.fid, t1.fparentid, t1.fouterid, t1.fprice, t1.fquantity, t1.fcreated, if(@glscode = t1.fouterid, @rank := @rank + 1, @rank := 1) as rk, @glscode := t1.fouterid from (select concat(a.num_iid, '') as fid, concat(a.sku_id, '') as fparentid, concat(a.sku_outer_id, '') as fouterid, round(a.sku_price, 2) as fprice, a.sku_quantity fquantity, a.sku_created fcreated from order_realtime.tb_sku_api_realtime as a where (a.sku_outer_id is not null and a.sku_outer_id <> '2') order by fouterid, sku_modified desc) t1, (select @glscode := null, @rank := 0) t2) tt1 where tt1.rk = 1) sku left join(select tt1.fid as modelid, tt1.fnumiid as modelali, tt1.fouterid as modelgls, tt1.ftitle, tt1.fpicurl, tt1.fprice as modelprice, tt1.fnum as modelqty, tt1.fapprovestatus, tt1.fcreated from (select t1.fid, t1.fnumiid, t1.fouterid, t1.ftitle, t1.fpicurl, t1.fprice, t1.fnum, t1.fapprovestatus, t1.fcreated from (select concat(a.fid, '') as fid, concat(a.fnumiid, '') as fnumiid, concat(a.fouterid, '') as fouterid, a.ftitle, concat(a.fpicurl, '') as fpicurl, round(a.fprice, 2) as fprice, a.fnum, a.fapprovestatus, a.fcreated from (select num_iid as fid, num_iid fnumiid, outer_id fouterid, title ftitle, pic_url fpicurl, price fprice, num fnum, approve_status fapprovestatus, created fcreated, modified fmodified from order_realtime.tb_sku_api_realtime group by num_iid) as a where a.fouterid is not null order by a.fouterid, a.fmodified desc) t1 join ( select outer_id fouterid,max(created) as fcreated from order_realtime.tb_sku_api_realtime group by fouterid ) t2 on t1.fouterid=t2.fouterid and t1.fcreated=t2.fcreated ) tt1 ) model on sku.modelid = model.modelid order by sku.modelid
;
use order_realtime;
select *
from
tb_order_api_fullinfo_realtime where order_oid in
('3426241791019036149'
,'1929323209792231475'
,'3426634046484787541'
,'3425697108131301838'
,'1929237385155240492'
,'3425767848063024559'
,'3425842656465473939'
,'3426593727543101157'
,'3426635990224970053'
,'1929256862726004775'
,'3426636350583464015'
,'3426532562040134804'
,'3426383883081300825'
,'3425842656466473939'
,'1929237385154240492'
,'3425602212668105131'
,'3426532562041134804'
,'3426634046483787541'
,'3426636350582464015'
,'3426505526823286842'
,'3425729976690951306'
,'3426593583690693130'
,'3426635990225970053'
,'3426636350581464015'
,'3426597975467189524'
,'3427909707412119228'
,'3427905531764172340'
,'3427905531765172340'
,'1929836498930581969'
,'3426597975466189524'
,'1929693648971728288'
,'1930396226355168690'
,'1930261767664911389'
,'1929693648972728288'
,'1930396226354168690'
,'1929693648970728288'
,'3428494272396237143'
,'3429086905271468365'
,'3429285986935827621'
,'3428490024507788441'
,'3428490024510788441'
,'3428490024509788441'
,'3428489268567675522'
,'3428490024508788441'
,'3429082765487349627'
,'3429251499057643052'
,'1930396226353168690'
,'3430602903699256356'
,'3430658126846481253'
,'3430658234238363155'
,'3430607979057828913'
,'3430658234236363155'
,'3430602903700256356'
,'3430658234237363155'
,'1930298412218343096'
,'3430454905572679914'
,'3430602903698256356'
,'1931652985788323667'
,'3431168568196628639'
,'3431164608561845255'
,'3431765629685207061'
,'3432632112168981253'
,'3433403775609792654'
,'1931543040211794573'
,'3433407807137413117'
,'3433408743298114429');
select sku_id
from
tb_sku_api_realtime
select *
from
tb_sku_api_realtime where sku_id in
(
'4637582023162'
,'4459881778473'
,'4878142188201'
,'4219618801765'
,'4219524857103'
,'4463194939461'
,'5117576730052'
,'4463104651687'
,'4381159103808'
,'4451664432570'
,'4692492790038'
,'4292484301346'
,'4383186795171'
,'4409047122135'
,'4300658389932'
,'4615757229988'
,'5114433146502'
,'4459882186103'
,'4340008684091'
,'4459882094493'
,'4411789435003'
,'4414126957897'
,'4307583872840'
,'4414166726209'
,'4302936016350'
,'4411789435004'
,'4241635144385'
,'4411272778352'
,'4241547169891'
,'4323290044023'
,'4241830221049'
,'4595303902499'
,'4654036907506'
,'4292484177598'
,'4467942923536'
,'4411043494562'
,'4749808307225'
,'4459881958254'
,'4463901687335'
,'4240342344750'
,'4292484177597'
,'4571950778672'
,'4568291411049'
,'4471294902158'
,'4491935634157'
,'4241509641977'
,'4307583872838'
,'4238310136654'
,'4211071865696'
,'4292484301345'
,'4571950778666'
,'5117013926942'
,'4332868797006'
,'4459882094486'
,'4383186795168'
,'4424532354860'
,'4297656048159'
,'4463263663066'
,'4381186171280'
,'4491935634156'
,'4459881958258'
,'4292484553004'
,'4291811236145'
,'4273196465648'
,'4241509641981'
,'4292573485630'
,'4300152712701'
,'4610417099936'
,'4218897153970'
,'4378241182540'
,'4424532354863'
,'4288881348147'
,'4463901687333'
,'4223890149918'
,'4463263663058'
,'4920674284837'
);
select *
from
tb_order_api_fullinfo_realtime where order_oid in
(
'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3426468661492078814'
,'3451159803635765461'
,'3451159803635765461'
,'3451159803635765461'
,'3451159803635765461'
,'3451159803635765461'
,'3451159803635765461'
,'3451159803635765461'
,'3451159803635765461'
,'3451159803635765461'
,'3451159803635765461'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3426469129528884059'
,'3426654351273884059'
,'3450979657261765461'
,'3450979657261765461'
,'3450979657261765461'
,'3450979657261765461'
,'3450979657261765461'
,'3450979657261765461'
,'3450979657261765461'
,'3450979657261765461'
,'3450979657261765461'
,'3450979657261765461'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3426698954801884059'
,'3450374064876765461'
,'3450374064876765461'
,'3450374064876765461'
,'3450374064876765461'
,'3450374064876765461'
,'3450374064876765461'
,'3450374064876765461'
,'3450374064876765461'
,'3450374064876765461'
,'3450374064876765461'
)
select delivery_no,
product_code,
accept_order_time,
ynap_confirm_delivery_time,
warehouse_take_delivery_time,
aswarehouse_delivery_time,
check1,
check2,
check3,
current_timestamp as update_time,
concat(delivery_no,'_',product_code) as id
from (
select b.delivery_no,
a.product_code,
max(c.create_time) as accept_order_time, # 接收到订单时间
max(DATE_ADD(d.ynap_update_time,INTERVAL 8 HOUR)) as ynap_confirm_delivery_time, # ynap确认发货时间
max(a.asn_inbound) as warehouse_take_delivery_time, # 仓库收货时间
max(e.delivery_time) aswarehouse_delivery_time , # 仓库发货时间
case when (unix_timestamp(max(DATE_ADD(d.ynap_update_time,INTERVAL 8 HOUR)))-unix_timestamp(max(c.create_time)))/24/60/60 >=1 then 1 else 0 end as check1,
case when (unix_timestamp(max(a.asn_inbound))-unix_timestamp(max(DATE_ADD(d.ynap_update_time,INTERVAL 8 HOUR))))/24/60/60 >=7 then 1 else 0 end as check2,
case when (unix_timestamp(max(e.delivery_time))-unix_timestamp(max(a.asn_inbound)))/24/60/60 >=2 then 1 else 0 end as check3
from (
select *
from zhongtai_server.pmc_purchase_order_item
where deleted=0
and (asn_inbound is null or asn_inbound >=date_sub(current_timestamp, INTERVAL 14 DAY))
)a
left join (
select substring_index(oms_order_no,'_',-1) delivery_no,
substring_index(oms_order_no,'_',1) as order_no,
voucher_no
from zhongtai_server.pmc_purchase_order
where deleted=0
)b
on a.purchase_po=b.voucher_no
left join (
select shop_order,
gtin,
create_time
from zhongtai_server.pmc_push_ynap
where push_status=1
and order_status=2
and operation_status=1
and deleted=0
and (create_time is null or create_time >=date_sub(current_timestamp, INTERVAL 14 DAY))
)c
on b.order_no=c.shop_order
left join (
select shop_order,
gtin,
ynap_update_time
from zhongtai_server.pmc_replenish_ynap
where ynap_status='Item Shipped'
and deleted=0
and (ynap_update_time is null or ynap_update_time>=date_sub(current_timestamp, INTERVAL 14 DAY))
)d
on b.order_no=d.shop_order
and a.product_code=d.gtin
left join (
select delivery_time,
order_no,
delivery_no
from zhongtai_server.oms_order_delivery
where deleted=0
and (delivery_time is null or delivery_time >=date_sub(current_timestamp, INTERVAL 14 DAY))
)e
on b.order_no=e.order_no
and b.delivery_no=e.delivery_no
where b.delivery_no is not null
and product_code is not null
group by b.delivery_no,a.product_code
)t
where (t.check1=1 or t.check2=1 or t.check3=1)
and delivery_no='8805359508008919051'
select *
from auth_info.t_db_info
where host like '%g79231c%';
select *
from auth_info.t_db_info_import
where host like '%g79231%';
select *
from auth_info.t_db_info_import
where db_id like '%shuyun_crm_penap_datamodel%';
select * from d_p_n_mainorder_259 where orderid='3458541674221933912';
select * from d_p_n_mainorder_259 where orderid like '%7230729141649519700%';
select * from d_p_n_mainorder_259 where orderid like '%1230801172734349756%';
use auth_info;
select * from t_db_info_import where db_id='supu_hudong_interactive_center';
select * from t_db_info_export;
select id,
tagId,
occurrenceDt,
detectionDt,
mark,
originId,
channelType,
lastSync,
syncStatus,
idempotent
from d_p_n_sendtag_758;
show create table d_p_n_sendtag_758;
select * from d_p_n_sendtag_758 where ;
select count(1) from d_p_n_sendtag_758;
扫一扫
1336
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
