阿里运SLS日志sql 查询统计示例

-- kuaishou_callback_externalstore 从mysql导入的表

* and clientId: 32 and action:PLAY  | select createDate, count(DISTINCT(openId)) uv, count(openId) pv from log l join kuaishou_callback_externalstore k on l.openId = k.open_id and k.callback is not null and l.createDate >= '2023-03-01' and l.createDate <= '2023-03-31'  and date(k.create_time) = l.createDate   GROUP by l.createDate    order by l.createDate limit 50

 

 

 

-- dts_user_spead_mark_action_externalstore  从mysq导入的表

* and action : {} and createDate: {}  and clientId: {} | select count(DISTINCT(userId)) uv ,count(userId) pv , {} as type from log l join   (select u.user_id,u.group_id from dts_user_spead_mark_action_externalstore u where u.create_time = '{}' and u.client_id = '{}' GROUP by u.user_id,u.group_id )   u on l.userId = u.user_id where  u.group_id = {}

 

再来一个 三表联查 超级复杂sql  我服了我自己

 

 | select createDate ,count(DISTINCT(p.pay_sub_user_id)) uv,count(p.pay_sub_user_id) pv ,sum(p.money_fen) as fen from (  select createDate, subUserId  from log l join  kuaishou_callback_externalstore k on l.openId = k.open_id and k.callback is not null and l.createDate >= '2023-03-01' and l.createDate <= '2023-03-31'  and date(k.create_time) = l.createDate  and l.clientId = 32  GROUP by createDate,subUserId  ) pp left join  dts_pay_order_externalstore  p on pp.subUserId = p.pay_sub_user_id and  pp.createDate = date(p.create_time) and p.pay_status =2 GROUP  by  createDate order by createDate