阿里运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