|
作为一名程序员，相信你对sql并不陌生。 如果你对sql不了解，我们先了解SQL是什么？ sql一般指结构化查询语言。结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
佩服那些能把sql写得高效，写得如此行云流水。 下面这条sql 是一位经验丰富的数仓工程师编写的。不仅包含了临时表、左连接、内连接、group by 、 order by 、聚合函数等。编写得很规范，是一个不错的例子。 with -- 活动信息 task_info as ( select id as task_id ,start_time ,if(limit_type=0 ,end_time ,from_unixtime(unix_timestamp(end_time)+task_limit_day*24*3600,'yyyy-MM-dd HH:mm:ss') ) as end_time ,regexp_replace(substr(start_time,1,10),'-','') as start_dt ,if(limit_type=0 ,from_unixtime(unix_timestamp(end_time),'yyyyMMdd') ,from_unixtime(unix_timestamp(end_time)+task_limit_day*24*3600,'yyyyMMdd') ) as end_dt from tb_task where category=0 and is_valid=1 and is_delete=0 ) -- 每天任务列表 ,task_list_day as ( select dt ,task_id ,start_time ,end_time ,start_dt ,end_dt from task_info a join ( select a.p_day as dt from dim_day a join task_info b on 1=1 where a.p_day>=b.start_dt and a.p_day<=b.end_dt ) b on 1=1 ) -- 每天用户访问明细 ,task_visit_detail as ( select dt ,task_id ,start_time ,end_time ,user_id ,if(dt=first_visit_dt,1,0) as is_first_visit_day from ( select a.dt ,b.task_id ,b.start_time ,b.end_time ,a.user_id ,min(a.dt) over(partition by b.task_id ,a.user_id) as first_visit_dt from tb_user_behavior a join task_list_day b on a.dt=b.dt where a.event='PageView' and a.user_id is not null and a.operation_time >= b.start_time and a.operation_time < b.end_time group by a.dt ,b.task_id ,b.start_time ,b.end_time ,a.user_id ) a ) -- 用户领取任务 ,task_receive as ( select a.task_id ,user_id ,regexp_replace(substr(receive_time,1,10),'-','') as receive_dt from tb_task_receive a join task_info b on a.task_id=b.task_id where a.is_valid=1 or a.is_delete =0 )select a.task_id as `任务编号` ,start_time as `任务开始时间` ,end_time as `任务结束时间` ,a.dt as `日期` ,count(a.user_id) as `访问人数` ,sum(a.is_first_visit_day) as `首日访问人数`from task_visit_detail aleft join task_receive b on a.task_id=b.task_id and a.user_id=b.user_idgroup by a.task_id,start_time,end_time,a.dtorder by a.task_id desc,a.dt
欧阳修写的一篇文章《卖油翁》里主人翁说过这样一句话：无他，唯手熟尔。 能把sql编写得如此行云流水，不仅对sql要熟练，逻辑思维也很重要。
|
