數(shù)倉(cāng)調(diào)優(yōu)｜阿里資深技術(shù)專家數(shù)倉(cāng)調(diào)優(yōu)經(jīng)驗(yàn)分享（下）

CREATE?TABLE?db.order (
??order_id,
??user_id,
??shop_vip,
??last_trade_time,
??last_cart_time,
??member_grade,
??seller_zone,
??member_credits,
??clustered key?index_mmsi(`user_id`)
)
DISTRIBUTED?BY?HASH(order_id)
PARTITION?BY?VALUE(DATE_FORMAT(last_trade_time, '%Y%m%d')) LIFECYCLE 30
COMMENT?'訂單信息表';

SELECT
??t2.buyer_id,
??t3.seller_id,
??t1.shop_vip,
??t1.last_trade_time,
??t1.last_cart_time,
??t1.member_grade,
??t1.seller_zone,
??t1.member_credits,
??sum(t1.pay_amount)
FROM
??db.order t1
??JOIN?db.dimension_table1 t2 ON?t1.user_id= t2.buyer_id
??JOIN?db.dimension_table2 t3 ON?t1.user_id= t3.seller_id
WHERE
??t1.is_market_target IN('4')
??AND?t1.seller_zone = 1019
??AND?t1.attributes IN('6742081')
??AND?t3.buyer_id = ‘xxxx’
??and?t3.tseller_id = ‘yyyy’
group?by
??t2.buyer_id,
??t3.seller_id,
??t1.shop_vip,
??t1.last_trade_time,
??t1.last_cart_time,
??t1.member_grade,
??t1.seller_zone,
??t1.member_credits;

SELECT?count(1) AS?cnt
??FROM(
SELECT?DISTINCT?t1.buyer_id
??FROM(
SELECT?buyer_id
??FROM?db.order
?WHERE?seller_zone= 11111
???AND?seller_id= 121211121
???AND?algorithm_crowd IN('84')) t1 
?JOIN(
SELECT?user_id AS?buyer_id
??FROM?db.dimension_table1) t2 
??????ON?t1.buyer_id= t2.buyer_id
JOIN(
SELECT?user_id AS?seller_id
??FROM?db.dimension_table2) t3 
??????ON?t1.buyer_id= t3.seller_id
) t;

CREATE?TABLE?output?WITH(oss_dump_endpoint= 'xxxxxx.oss-internal.aliyun-inc.com', oss_dump_bucket_name= 'xxxx', 
?????????????????????????oss_dump_file_name= 'xx_prod/20190710/63218721', 
?????????????????????????oss_dump_is_overwrite= true, 
?????????????????????????oss_dump_compatibility_mode= false, 
?????????????????????????oss_dump_access_key_id= 'xxxxxxxxx', 
?????????????????????????oss_dump_access_key_secret= 'xxxxxxxxxxxxxxxxxxxx', 
?????????????????????????oss_dump_row_del= '\r\n', 
?????????????????????????oss_dump_col_del= '\t', table_type= 'oss_dump', dump_charset_code= 'UTF-8', 
?????????????????????????oss_dump_table_header= 'false', return_dump_result_count= true) as
SELECT?DISTINCT?t1.buyer_id
??FROM(
SELECT?buyer_id
??FROM?db.order
?WHERE?last_cart_time>= 20190610
???AND?last_cart_time< 20190710
???AND?is_market_target IN('1')
???AND?seller_zone= 1018
???AND?seller_id= 3687815378) t1 
JOIN(
SELECT?user_id AS?buyer_id
??FROM?db.dimension_table) t2 
ON?t1.buyer_id= t2.buyer_id 
LIMIT?1000;

CREATE?TABLE?tb__record_info
(
??a_info_id bigint?NOT?NULL?AUTO_INCREMENT,
??domain?varchar?NOT?NULL,
??region varchar?NOT?NULL,
??ip varchar?NOT?NULL,
??result_ts varchar?NOT?NULL,
??time_stamp timestamp?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP,
??key?idx_domain(domain),
??key?idx_time(time_stamp),
??primary key?(a_info_id, domain, time_stamp)
) 
DISTRIBUTE?BY?HASH(domain) 
PARTITION?BY?VALUE(DATE_FORMAT(time_stamp,'%Y%m%d')) LIFECYCLE 60;

SELECT?count(DISTINCT?uid) AS?count?
FROM?login_log 
WHERE?timestamp?>=  
AND?timestamp???<=  
AND?qita1 =  
AND?qita2 = ;

SELECT?Count(*) AS?count?FROM??
(
????SELECT?deviceid
????FROM?login_log 
????WHERE??channel_id = ‘X’ 
????AND?timestamp?>= ‘XXX’
????AND?timestamp?<= ‘YYY’
????GROUP??BY?deviceid
) AS?d1
????LEFT?JOIN?
(
????SELECT?deviceid 
????FROM???login_log 
????WHERE??channel_id = ‘X’ 
????AND?timestamp?< ‘YYY’
) AS?d2
ON?d1.deviceid = d2.deviceid 
WHERE??d1.deviceid IS?NULL;

SELECT
??channel_id,
??count(
????DISTINCT?IF?(
??????datediff(payorder_riqi, login_riqi) = 0,
??????user_id,
??????NULL
????)
??) AS?'liucun_1',
??count(
????DISTINCT?IF?(
??????datediff(payorder_riqi, login_riqi) = 1,
??????user_id,
??????NULL
????)
??) AS?'liucun_2',
??count(
????DISTINCT?IF?(
??????datediff(payorder_riqi, login_riqi) = 2,
??????user_id,
??????NULL
????)
??) AS?'liucun_3',
??count(
????DISTINCT?IF?(
??????datediff(payorder_riqi, login_riqi) = 3,
??????user_id,
??????NULL
????)
??) AS?'liucun_4',
??count(
????DISTINCT?IF?(
??????datediff(payorder_riqi, login_riqi) = 4,
??????user_id,
??????NULL
????)
??) AS?'liucun_5',
??count(
????DISTINCT?IF?(
??????datediff(payorder_riqi, login_riqi) = 5,
??????user_id,
??????NULL
????)
??) AS?'liucun_6',
??count(
????DISTINCT?IF?(
??????datediff(payorder_riqi, login_riqi) = 6,
??????user_id,
NULL
????)
??) AS?'liucun_7',
??count(
????DISTINCT?IF?(
??????datediff(payorder_riqi, login_riqi) = 14,
??????user_id,
??????NULL
????)
??) AS?'liucun_15'
FROM
??pay_order p
??LEFT?JOIN?login_log l ON?p.uid = l.uid
WHERE
??payorder_riqi >= '2019-01-17'
??AND?payorder_riqi <= '2019-01-24'
GROUP?BY
??`channel_id`
ORDER?BY
??`liucun_1`?DESC;