数仓调优｜阿里资深技术专家数仓调优经验分享（下）

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;