作为一名程序员,相信你对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 a
left join task_receive b on a.task_id=b.task_id and a.user_id=b.user_id
group by a.task_id,start_time,end_time,a.dt
order by a.task_id desc,a.dt
有人问欧阳修,为什么你的文章写那么好。
欧阳修说:无他,唯手熟尔。
能把sql编写得如此行云流水,不仅对sql要熟练,逻辑思维也很重要。